<DataWarehouseScript ID="Microsoft.SystemCenter.OperationsManager.Reports.2007.Script.DataVolumesDetailsBySourceDataGet" Accessibility="Internal"> <Install>
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_Report_DataVolumenDetailsBySrcDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_DataVolumenDetailsBySrcDataGet] AS RETURN 1')
END
GO
Create Table #TempTopNMpsSrc
(
ManagementPackRowId int
)
If @TopN <= 0
BEGIN
Select 0 As RowNumber,
SourceTypeId,
SourceTypeDisplay,
SourceId,
SourceDisplay,
0.00 As PrcVol,
0.00 As Slope,
0.00 As CountBySource
From #TmpDataCountsSrc
END
ELSE
BEGIN
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Options to Use
Insert into #TmpOpsSrc
Select OptList.OptionId.value('.','int') as OptionId
From @DTXML.nodes('/*/Value') As OptList(OptionId)
Order By OptionId
Select @APMDataExists = Case
When EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[apm].[EVENT]') AND type in (N'U')) Then 1
Else 0
End
Select @ShowDiscs = Case
When Count(OptionId) > 0 Then 1
ELSE 0
END
From #TmpOpsSrc where OptionId = 1
Select @ShowAlerts = Case
When Count(OptionId) > 0 Then 1
ELSE 0
END
From #TmpOpsSrc where OptionId = 2
Select @ShowPerfs = Case
When Count(OptionId) > 0 Then 1
ELSE 0
END
From #TmpOpsSrc where OptionId = 3
Select @ShowEvents = Case
When Count(OptionId) > 0 Then 1
ELSE 0
END
From #TmpOpsSrc where OptionId = 4
Select @ShowStates = Case
When Count(OptionId) > 0 Then 1
ELSE 0
END
From #TmpOpsSrc where OptionId = 5
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Get Mp To Filter
Insert Into #TmpMPsSrc
Select Mp.ManagementPackRowId,
Mp.ManagementPackSystemName
From @MPXML.nodes('/*/Value') As MpList(ManagementPackRowId)
Inner Join vManagementPack (NoLock) Mp
On Mp.ManagementPackRowId = MpList.ManagementPackRowId.value('.','int')
Order By ManagementPackRowId
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Get MANAGEMENT GROUPS To Filter
Insert Into #TmpMGPSrc
Select Mgp.ManagementGroupRowId,
Mgp.ManagementGroupDefaultName
From @MGPXML.nodes('/*/Value') As MgpList(ManagementGroupGuid)
Inner Join vManagementGroup (NoLock) Mgp
On Mgp.ManagementGroupGuid = MgpList.ManagementGroupGuid.value('.','uniqueidentifier')
Order By ManagementGroupRowId
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Get Filtered ManagementPackIds
Insert into #TempFilterdMpsSrc
Select distinct Mpc.ManagementPackRowId
From #TmpMPsSrc Mpc
Inner Join vManagementPackVersion Mpv
On Mpv.ManagementPackRowId = Mpc.ManagementPackRowId
Inner Join vManagementGroupManagementPackVersion Mgmpv
On Mgmpv.ManagementPackVersionRowId = Mpv.ManagementPackVersionRowId
Inner Join #TmpMGPSrc (NoLock) Mgp
On Mgp.ManagementGroupRowId = Mgmpv.ManagementGroupRowId
--Filtered Dates from Calendar
Insert into #TmpFilCalDtsSrc
Select Dt.DateValue
From vDate Dt
Where CONVERT(VARCHAR(8), Dt.DateValue, 112) BETWEEN CONVERT(VARCHAR(8) , @StartDate, 112) AND CONVERT(VARCHAR(8) , @EndDate, 112)
If @ShowDiscs = 1
BEGIN
--Create Temp Table to Store Filtered Data
Create Table #TmpDisFilSrc
(
ManagedEntityRowId int,
ManagedEntityTypeRowId int,
DateValue datetime,
DataCount bigint
)
--Counts By Date of Initial Discoveries base on Managed Entities
Insert into #TmpDisFilSrc
Select Me.ManagedEntityRowId,
Me.ManagedEntityTypeRowId,
DateAdd(hh,@AdjHours,Memg.FromDateTime) as FromDateTime,
Count(Me.ManagedEntityRowId) as DataCount
From vManagedEntity Me
Inner Join vManagedEntityType Met
On Me.ManagedEntityTypeRowId = Met.ManagedEntityTypeRowId
Inner Join vManagedEntityManagementGroup Memg
On Me.ManagedEntityRowId = Memg.ManagedEntityRowId
And Me.ManagementGroupRowId in (Select ManagementGroupRowId From #TmpMGPSrc)
Where DateAdd(hh,@AdjHours,Memg.FromDateTime) BETWEEN @StartDate AND @EndDate
Group By Me.ManagedEntityRowId, Me.ManagedEntityTypeRowId, Memg.FromDateTime
--Counts By Date of Initial Discoveries base on Type Managed Entities
Insert into #TmpDisFilSrc
Select Me.ManagedEntityRowId,
Me.ManagedEntityTypeRowId,
DateAdd(hh,@AdjHours,Tmt.FromDateTime) as FromDateTime,
Count(Me.ManagedEntityRowId) as DataCount
From vManagedEntity Me
Inner Join vTypedManagedEntity Tmt
On Me.ManagedEntityTypeRowId = Tmt.ManagedEntityTypeRowId
And Me.ManagedEntityRowId = Tmt.ManagedEntityRowId
Inner Join vManagedEntityManagementGroup Memg
On Me.ManagedEntityRowId = Memg.ManagedEntityRowId
And Me.ManagementGroupRowId in (Select ManagementGroupRowId From #TmpMGPSrc)
Where DateAdd(hh,@AdjHours,Tmt.FromDateTime) BETWEEN @StartDate AND @EndDate
Group By Me.ManagedEntityRowId, Me.ManagedEntityTypeRowId, Tmt.FromDateTime
--Counts By Date of Properties Changes Submission base on Managed Entities
Insert into #TmpDisFilSrc
Select Me.ManagedEntityRowId,
Me.ManagedEntityTypeRowId,
DateAdd(hh,@AdjHours,Mep.FromDateTime) as FromDateTime,
Count(Mep.ManagedEntityRowId) as DataCount
From vManagedEntityProperty Mep
Inner Join vManagedEntity Me
On Mep.ManagedEntityRowId = Me.ManagedEntityRowId
And Me.ManagementGroupRowId in (Select ManagementGroupRowId From #TmpMGPSrc)
Where DateAdd(hh,@AdjHours,Mep.FromDateTime) BETWEEN @StartDate AND @EndDate
Group By Me.ManagedEntityRowId, Me.ManagedEntityTypeRowId, Mep.FromDateTime
--Discovery Count By Source, Source Type and By Date of Initial Discoveries
Insert into #TmpDataCountsSrc
Select 1 as DataType,
Mpc.ManagementPackRowId,
Dis.ManagedEntityTypeRowId as SourceTypeId,
COALESCE(Ltp.Name,Mtp.ManagedEntityTypeDefaultName) as SourceTypeDisplay,
Dis.ManagedEntityRowId as SourceId,
COALESCE(Loc.Name,Met.DisplayName) as SourceDisplay,
COALESCE(Met.Path,Met.FullName) as SourcePathFullName,
Dt.DateValue as [Date],
IsNull(SUM(Dis.DataCount),0) as DataCount
From #TmpDisFilSrc Dis
Inner Join #TmpFilCalDtsSrc Dt
On CONVERT(VARCHAR(8), Dt.DateValue, 112) = CONVERT(VARCHAR(8) , Dis.DateValue, 112)
Inner Join vManagedEntity Met
On Met.ManagedEntityRowId = Dis.ManagedEntityRowId
Inner Join vManagedEntityType Mtp
On Mtp.ManagedEntityTypeRowId = Dis.ManagedEntityTypeRowId
Inner Join #TempFilterdMpsSrc Mpc
On Mpc.ManagementPackRowId = Mtp.ManagementPackRowId
Left Outer Join vDisplayString (NoLock) Loc
On Loc.ElementGuid = Mtp.ManagedEntityTypeGuid
And Loc.LanguageCode = @LanguageCode
Left Outer Join vDisplayString (NoLock) Ltp
On Ltp.ElementGuid = Mtp.ManagedEntityTypeGuid
And Ltp.LanguageCode = @LanguageCode
Group By Mpc.ManagementPackRowId, Dis.ManagedEntityTypeRowId, Ltp.Name, Mtp.ManagedEntityTypeDefaultName, Dis.ManagedEntityRowId, Loc.Name, Met.DisplayName, Dt.DateValue, Met.Path, Met.FullName
IF OBJECT_ID('tempdb..#TmpDisFilWrk') IS NOT NULL
BEGIN
drop table #TmpDisFilSrc
END
END
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
If @ShowAlerts = 1
BEGIN
--Alerts Count by Source
Insert into #TmpDataCountsSrc
Select 2 as DataType,
Mpc.ManagementPackRowId,
Mtp.ManagedEntityTypeRowId as SourceTypeId,
COALESCE(Ltp.Name,Mtp.ManagedEntityTypeDefaultName) as SourceTypeDisplay,
Met.ManagedEntityRowId as SourceId,
COALESCE(Loc.Name,Met.DisplayName) as SourceDisplay,
COALESCE(Met.Path,Met.FullName) as SourcePathFullName,
Dt.DateValue as Date,
IsNull(Count(Distinct Alr.AlertGuid),0) as DataCount
From Alert.vAlert (NoLock) Alr
LEFT OUTER JOIN vMonitor (NoLock) Mon
ON Mon.MonitorRowId = Alr.WorkflowRowId
AND Alr.MonitorAlertInd = 1
LEFT OUTER JOIN vRule Rul
ON Rul.RuleRowId = Alr.WorkflowRowId
AND Alr.MonitorAlertInd = 0
Inner Join #TmpFilCalDtsSrc Dt
On CONVERT(VARCHAR(8), Dt.DateValue, 112) = CONVERT(VARCHAR(8) , DateAdd(hh,@AdjHours,Alr.RaisedDateTime), 112)
Inner Join #TempFilterdMpsSrc Mpc
On Mon.ManagementPackRowId = Mpc.ManagementPackRowId
Or Rul.ManagementPackRowId = Mpc.ManagementPackRowId
Inner Join vManagedEntity (NoLock) Met
On Met.ManagedEntityRowId = Alr.ManagedEntityRowId
Inner Join #TmpMGPSrc Mg
On Met.ManagementGroupRowId = Mg.ManagementGroupRowId
Inner Join vManagedEntityType (NoLock) Mtp
On Mtp.ManagedEntityTypeRowId = Met.ManagedEntityTypeRowId
Left Outer Join vDisplayString (NoLock) Loc
On Loc.ElementGuid = Met.ManagedEntityGuid
And Loc.LanguageCode = @LanguageCode
Left Outer Join vDisplayString (NoLock) Ltp
On Ltp.ElementGuid = Mtp.ManagedEntityTypeGuid
And Ltp.LanguageCode = @LanguageCode
where DateAdd(hh,@AdjHours,Alr.RaisedDateTime) BETWEEN @StartDate AND @EndDate
Group By Mpc.ManagementPackRowId, Loc.Name,Ltp.Name,Mtp.ManagedEntityTypeRowId, Mtp.ManagedEntityTypeDefaultName, Met.ManagedEntityRowId, Met.DisplayName, Dt.DateValue, Met.Path, Met.FullName
END
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Getting Filtered Perfs Data Count By Dates
Insert Into #TempPrfDtFilSrc
Select PerfCTE.PerformanceRuleInstanceRowId,
PerfCTE.ManagedEntityRowId,
SUM(PerfCTE.SampleCount) As SampleCount,
DateAdd(hh,@AdjHours,PerfCTE.DateTime) as [DateTime]
From Perf.vPerfDaily PerfCTE
Inner Join vManagedEntity Met
On Met.ManagedEntityRowId = PerfCTE.ManagedEntityRowId
Inner Join #TmpMGPSrc Mg
On Met.ManagementGroupRowId = Mg.ManagementGroupRowId
Where CONVERT(VARCHAR(8), DateAdd(hh,@AdjHours,PerfCTE.DateTime), 112) BETWEEN CONVERT(VARCHAR(8) , @StartDate, 112) AND CONVERT(VARCHAR(8) , @EndDate, 112)
Group By PerfCTE.PerformanceRuleInstanceRowId, PerfCTE.ManagedEntityRowId, PerfCTE.DateTime
Insert into #TmpDataCountsSrc
Select Distinct 3 as DataType,
Mpc.ManagementPackRowId,
Mtp.ManagedEntityTypeRowId as SourceTypeId,
COALESCE(Ltp.Name,Mtp.ManagedEntityTypeDefaultName) as SourceTypeDisplay,
Prf.ManagedEntityRowId as SourceId,
COALESCE(Loc.Name,Met.DisplayName) as SourceDisplay,
COALESCE(Met.Path,Met.FullName) as SourcePathFullName,
Dt.DateValue as Date,
IsNull(SUM(Prf.SampleCount),0) as CountByDate
From #TempPrfDtFilSrc Prf
Inner Join vPerformanceRuleInstance (NoLock) Pri
On Prf.PerformanceRuleInstanceRowId = Pri.PerformanceRuleInstanceRowId
Inner Join vRule (NoLock) Rul
ON Pri.RuleRowId = Rul.RuleRowId
Inner Join #TmpFilCalDtsSrc Dt
On CONVERT(VARCHAR(8), Dt.DateValue, 112) = CONVERT(VARCHAR(8) , Prf.[DateTime], 112)
Inner Join #TempFilterdMpsSrc (NoLock) Mpc
On Mpc.ManagementPackRowId = Rul.ManagementPackRowId
Inner Join vManagedEntity (NoLock) Met
On Met.ManagedEntityRowId = Prf.ManagedEntityRowId
Inner Join vManagedEntityType (NoLock) Mtp
On Mtp.ManagedEntityTypeRowId = Met.ManagedEntityTypeRowId
Left Outer Join vDisplayString (NoLock) Loc
On Loc.ElementGuid = Met.ManagedEntityGuid
And Loc.LanguageCode = @LanguageCode
Left Outer Join vDisplayString (NoLock) Ltp
On Ltp.ElementGuid = Mtp.ManagedEntityTypeGuid
And Ltp.LanguageCode = @LanguageCode
Group By Mpc.ManagementPackRowId, Mtp.ManagedEntityTypeRowId, Ltp.Name, Mtp.ManagedEntityTypeDefaultName, Prf.ManagedEntityRowId, Loc.Name, Met.DisplayName, Dt.DateValue, Met.Path, Met.FullName
IF OBJECT_ID('tempdb..#TempPrfDtFilSrc') IS NOT NULL
BEGIN
drop table #TempPrfDtFilSrc
END
END
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Getting APM Events If Exists Table
If @APMDataExists = 1
BEGIN
Create Table #TempAPMEventMachines
(
ManagedEntityRowId int,
MachineID int
)
INSERT INTO #TempAPMEventMachines
SELECT
DISTINCT MEN.ManagedEntityRowId,
DP.MACHINEID
FROM
vManagedEntity AS MEN
INNER JOIN (
SELECT
SUBSTRING(machine, CHARINDEX('\', machine)+1, LEN(machine) ) As DisplayName ,
MACHINEID
FROM apm.MACHINE
) AS DP ON CHARINDEX(DP.DisplayName, MEN.DisplayName,0) > 0
WHERE
MEN.ManagedEntityTypeRowId in (select ManagedEntityTypeRowId from vManagedEntityType where ManagedEntityTypeSystemName = 'Microsoft.SystemCenter.Apm.ApmAgent')
Insert Into #TempEvtDtFilSrc
SELECT RL.RuleRowId,
APMMC.ManagedEntityRowId,
COUNT(EvtAPM.EventId),
DateAdd(hh,@AdjHours,EvtAPM.UTCEVENTDATE) as DateValue
FROM vRule RL
Inner Join vManagementPack MP
On RL.ManagementPackRowId = MP.ManagementPackRowId
And MP.ManagementPackSystemName = 'Microsoft.SystemCenter.Apm.Infrastructure'
And RL.RuleSystemName = 'Microsoft.SystemCenter.Apm.CollectApplicationDiagnosticsEvents'
Inner Join apm.EVENT EvtAPM
On RL.RuleRowId IS NOT NULL
Inner Join #TempAPMEventMachines APMMC
On APMMC.MACHINEID = EvtAPM.MACHINEID
Where DateAdd(hh,@AdjHours,EvtAPM.UTCEVENTDATE) BETWEEN @StartDate AND @EndDate
Group By RL.RuleRowId, APMMC.ManagedEntityRowId, EvtAPM.UTCEVENTDATE
Insert Into #TempEvtDtFilSrc
SELECT RL.RuleRowId,
APMMC.ManagedEntityRowId,
COUNT(EvtAPM.CSEVENTID),
DateAdd(hh,@AdjHours,EvtAPM.UTCDATE) as DateValue
FROM vRule RL
Inner Join vManagementPack MP
On RL.ManagementPackRowId = MP.ManagementPackRowId
And MP.ManagementPackSystemName = 'Microsoft.SystemCenter.Apm.Infrastructure'
And RL.RuleSystemName = 'Microsoft.SystemCenter.Apm.CollectApplicationDiagnosticsEvents'
Inner Join apm.CSEVENT EvtAPM
On RL.RuleRowId IS NOT NULL
Inner Join #TempAPMEventMachines APMMC
On APMMC.MACHINEID = EvtAPM.MACHINEID
Where DateAdd(hh,@AdjHours,EvtAPM.UTCDATE) BETWEEN @StartDate AND @EndDate
Group By RL.RuleRowId, APMMC.ManagedEntityRowId, EvtAPM.UTCDATE
END
--Getting Filtered Events Data Count By Dates
Insert Into #TempEvtDtFilSrc
SELECT ErlCTE.RuleRowId,
ErlCTE.ManagedEntityRowId,
COUNT(ErlCTE.EventOriginId),
DateAdd(hh,@AdjHours,EvtCTE.DateTime) as DateValue
FROM Event.vEvent EvtCTE
Inner Join Event.vEventRule ErlCTE
On EvtCTE.EventOriginId = ErlCTE.EventOriginId
Inner Join vManagedEntity Met
On Met.ManagedEntityRowId = ErlCTE.ManagedEntityRowId
Inner Join #TmpMGPSrc Mg
On Met.ManagementGroupRowId = Mg.ManagementGroupRowId
Where DateAdd(hh,@AdjHours,EvtCTE.DateTime) BETWEEN @StartDate AND @EndDate
Group By ErlCTE.RuleRowId, ErlCTE.ManagedEntityRowId, EvtCTE.DateTime
Insert into #TmpDataCountsSrc
Select 4 as DataType,
Mpc.ManagementPackRowId,
Mtp.ManagedEntityTypeRowId as SourceTypeId,
COALESCE(Ltp.Name,Mtp.ManagedEntityTypeDefaultName) as SourceTypeDisplay,
Evt.ManagedEntityRowId as SourceId,
COALESCE(Loc.Name,Met.DisplayName) as SourceDisplay,
COALESCE(Met.Path,Met.FullName) as SourcePathFullName,
Dt.DateValue as Date,
IsNull(SUM(Evt.EventCount),0) as CountByDate
From #TempEvtDtFilSrc Evt
Inner Join vRule Rul
ON Evt.RuleRowId = Rul.RuleRowId
Inner Join #TmpFilCalDtsSrc Dt
On CONVERT(VARCHAR(8), Dt.DateValue, 112) = CONVERT(VARCHAR(8) , Evt.[DateTime], 112)
Inner Join #TempFilterdMpsSrc Mpc
On Mpc.ManagementPackRowId = Rul.ManagementPackRowId
Inner Join vManagedEntity (NoLock) Met
On Met.ManagedEntityRowId = Evt.ManagedEntityRowId
Inner Join vManagedEntityType (NoLock) Mtp
On Mtp.ManagedEntityTypeRowId = Met.ManagedEntityTypeRowId
Left Outer Join vDisplayString (NoLock) Loc
On Loc.ElementGuid = Met.ManagedEntityGuid
And Loc.LanguageCode = @LanguageCode
Left Outer Join vDisplayString (NoLock) Ltp
On Ltp.ElementGuid = Mtp.ManagedEntityTypeGuid
And Ltp.LanguageCode = @LanguageCode
Group By Mpc.ManagementPackRowId, Mtp.ManagedEntityTypeRowId, Ltp.Name, Mtp.ManagedEntityTypeDefaultName, Evt.ManagedEntityRowId, Loc.Name, Met.DisplayName, Dt.DateValue, Met.Path, Met.FullName
IF OBJECT_ID('tempdb..#TempEvtDtFilSrc') IS NOT NULL
BEGIN
drop table #TempEvtDtFilSrc
END
IF OBJECT_ID('tempdb..#TempAPMEventMachines') IS NOT NULL
BEGIN
drop table #TempAPMEventMachines
END
END
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
If @ShowStates = 1
BEGIN
--Create Temp Filtered State Table
Create Table #TempSteDtFilSrc
(
MonitorRowId int,
ManagedEntityRowId int,
[DateTime] datetime,
DataCount bigint
)
--Getting Filtered State changes Count By Dates
Insert Into #TempSteDtFilSrc
Select Mn.MonitorRowId,
Mn.ManagedEntityRowId,
DateAdd(hh,@AdjHours,SteCTE.DateTime) as DateValue,
Count(SteCTE.ManagedEntityMonitorRowId) as DataCount
From State.vStateRaw SteCTE
Inner Join vManagedEntityMonitor Mn
On Mn.ManagedEntityMonitorRowId = SteCTE.ManagedEntityMonitorRowId
Inner Join vManagedEntity Met
On Met.ManagedEntityRowId = Mn.ManagedEntityRowId
Inner Join #TmpMGPSrc Mg
On Met.ManagementGroupRowId = Mg.ManagementGroupRowId
Where DateAdd(hh,@AdjHours,SteCTE.DateTime) BETWEEN @StartDate AND @EndDate
Group By Mn.MonitorRowId, Mn.ManagedEntityRowId, SteCTE.DateTime
--States Count By Source
Insert into #TmpDataCountsSrc
Select 5 as DataType,
Mpc.ManagementPackRowId,
Mtp.ManagedEntityTypeRowId as SourceTypeId,
COALESCE(Ltp.Name,Mtp.ManagedEntityTypeDefaultName) as SourceTypeDisplay,
Ste.ManagedEntityRowId as SourceId,
COALESCE(Loc.Name,Met.DisplayName) as SourceDisplay,
COALESCE(Met.Path,Met.FullName) as SourcePathFullName,
Dt.DateValue as Date,
IsNull(SUM(Ste.DataCount),0) as CountByDate
From #TempSteDtFilSrc Ste
Inner Join vMonitor Mon
On Mon.MonitorRowId = Ste.MonitorRowId
Inner Join #TmpFilCalDtsSrc Dt
On CONVERT(VARCHAR(8), Dt.DateValue, 112) = CONVERT(VARCHAR(8) , Ste.[DateTime], 112)
Inner Join #TempFilterdMpsSrc Mpc
On Mpc.ManagementPackRowId = Mon.ManagementPackRowId
Inner Join vManagedEntity (NoLock) Met
On Met.ManagedEntityRowId = Ste.ManagedEntityRowId
Inner Join vManagedEntityType (NoLock) Mtp
On Mtp.ManagedEntityTypeRowId = Met.ManagedEntityTypeRowId
Left Outer Join vDisplayString (NoLock) Loc
On Loc.ElementGuid = Met.ManagedEntityGuid
And Loc.LanguageCode = @LanguageCode
Left Outer Join vDisplayString (NoLock) Ltp
On Ltp.ElementGuid = Mtp.ManagedEntityTypeGuid
And Ltp.LanguageCode = @LanguageCode
Group By Mpc.ManagementPackRowId, Mtp.ManagedEntityTypeRowId, Ltp.Name, Mtp.ManagedEntityTypeDefaultName, Ste.ManagedEntityRowId, Loc.Name, Met.DisplayName, Dt.DateValue, Met.Path, Met.FullName
IF OBJECT_ID('tempdb..#TempSteDtFilSrc') IS NOT NULL
BEGIN
drop table #TempSteDtFilSrc
END
END
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Calculate Total Volume
Select @TotVol = IsNull(Sum(DataCount),0) From #TmpDataCountsSrc
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Calculate SumX
Insert into #TempDaysSrc
Select ROW_NUMBER() OVER(ORDER BY DateValue) AS DayNumber,
DateValue as Date
From #TmpFilCalDtsSrc (NoLock) Dt
Select @SumX = SUM(DayNumber) from #TempDaysSrc
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Calculate SumX2
Select @SumX2 = Sum(x0.DayNumber*x1.DayNumber)
From #TempDaysSrc x0
Inner Join #TempDaysSrc x1
On x0.DayNumber = x1.DayNumber
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Calculate Top Values for Equation by Source and SourceType
Insert into #TempTopCalSrc
Select Tot.SourceId,
Tot.SourceTypeId,
@DaysNumber as CntDys,
Sum(Tot.SXY) as SumXY,
Sum(Tot.DataCount) as SumY,
@SumX as SumX
From
(
Select CnSrc.SourceId,
CnSrc.SourceTypeId,
CnSrc.Date,
Sum(CnDy.DayNumber*CnSrc.DataCount) as SXY,
Sum(CnSrc.DataCount) as DataCount
From #TmpDataCountsSrc CnSrc
Inner Join #TempDaysSrc CnDy
On CnSrc.Date = CnDy.Date
Group by CnSrc.SourceId, CnSrc.SourceTypeId, CnSrc.Date
) as Tot
Group by Tot.SourceId, Tot.SourceTypeId
Order by Tot.SourceId, Tot.SourceTypeId
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Calculate Bottom Values for Equation by Source and SourceType
Insert into #TempBottomCalSrc
Select SourceId,
SourceTypeId,
@DaysNumber as CntDys,
@SumX2 as SumX2,
@SumX * @SumX as SumXxSumX
From #TmpDataCountsSrc CnSrc
Group by CnSrc.SourceId, CnSrc.SourceTypeId
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Calculate Final Result of Equation by WorkFlow
Insert into #TempCalBySrc
Select Tp.SourceId,
Tp.SourceTypeId,
((Tp.CntDys * Tp.SumXY)-(Tp.SumY * Tp.SumX)) as TopVal,
((Bt.CntDys * Bt.SumX2)-SumXxSumX) as BottomVal,
Case
When ((Bt.CntDys * Bt.SumX2)-SumXxSumX) > 0 Then (CAST(((Tp.CntDys * Tp.SumXY)-(Tp.SumY * Tp.SumX)) as Decimal) / CAST(((Bt.CntDys * Bt.SumX2)-SumXxSumX) as Decimal))
Else 0.00
End As Slope
From #TempTopCalSrc Tp
Inner Join #TempBottomCalSrc Bt
On Tp.SourceId = Bt.SourceId
And Tp.SourceTypeId = Bt.SourceTypeId
Order By Tp.SourceId, Tp.SourceTypeId
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
Insert into #TempTopNMpsSrc
Select Top (@TopN) Mbv.ManagementPackRowId
From
(
Select
Mpc.ManagementPackRowId,
Case
When @TotVol = 0 Then 0
Else ( ( CAST( ( IsNull(Sum(Cnts.DataCount),0) ) AS decimal)/@TotVol ) * 100 )
End AS PrcVol
From #TempFilterdMpsSrc Mpc
Left Outer Join #TmpDataCountsSrc Cnts
On Mpc.ManagementPackRowId = Cnts.ManagementPackRowId
Group By Mpc.ManagementPackRowId
) As Mbv
Order By Mbv.PrcVol desc
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Final Results
Select Top (@TopN)
ROW_NUMBER() OVER(ORDER BY Cnts.PrcVol Desc) AS 'RowNumber',
Cnts.SourceId,
Cnts.SourceDisplay,
Cnts.SourceTypeId,
Cnts.SourceTypeDisplay,
Cnts.SourcePathFullName,
Cnts.PrcVol,
IsNull(Cl.Slope,0) as Slope,
Cnts.CountBySource
From
(
Select DataType,
SourceId,
SourceDisplay,
SourceTypeId,
SourceTypeDisplay,
SourcePathFullName,
Case
When @TotVol = 0 Then 0
Else ((CAST(Sum(IsNull(DataCount,0)) AS decimal)/@TotVol)*100)
End AS PrcVol,
Sum(IsNull(DataCount,0)) as CountBySource
From #TmpDataCountsSrc CntSrc
Inner Join #TempTopNMpsSrc Mpv
On Mpv.ManagementPackRowId = IsNull(CntSrc.ManagementPackRowId,Mpv.ManagementPackRowId)
Group By DataType,
SourceId,
SourceDisplay,
SourceTypeId,
SourceTypeDisplay,
SourcePathFullName
) As Cnts
Left Outer Join #TempCalBySrc Cl
On Cl.SourceId = Cnts.SourceId
And Cl.SourceTypeId = Cnts.SourceTypeId
Order by Cnts.PrcVol Desc
END
QuitError:
IF OBJECT_ID('tempdb..#TmpOpsSrc') IS NOT NULL
BEGIN
Drop Table #TmpOpsSrc
END
IF OBJECT_ID('tempdb..#TmpMPsSrc') IS NOT NULL
BEGIN
Drop Table #TmpMPsSrc
END
IF OBJECT_ID('tempdb..#TmpMGPSrc') IS NOT NULL
BEGIN
Drop Table #TmpMGPSrc
END
IF OBJECT_ID('tempdb..#TempFilterdMpsSrc') IS NOT NULL
BEGIN
Drop Table #TempFilterdMpsSrc
END
IF OBJECT_ID('tempdb..#TmpDataCountsSrc') IS NOT NULL
BEGIN
Drop Table #TmpDataCountsSrc
END
IF OBJECT_ID('tempdb..#TempDaysSrc') IS NOT NULL
BEGIN
Drop Table #TempDaysSrc
END
IF OBJECT_ID('tempdb..#TempTopCalSrc') IS NOT NULL
BEGIN
Drop Table #TempTopCalSrc
END
IF OBJECT_ID('tempdb..#TempBottomCalSrc') IS NOT NULL
BEGIN
Drop Table #TempBottomCalSrc
END
IF OBJECT_ID('tempdb..#TempCalBySrc') IS NOT NULL
BEGIN
Drop Table #TempCalBySrc
END
IF OBJECT_ID('tempdb..#TmpFilCalDtsSrc') IS NOT NULL
BEGIN
Drop Table #TmpFilCalDtsSrc
END
IF OBJECT_ID('tempdb..#TempTopNMpsSrc') IS NOT NULL
BEGIN
drop table #TempTopNMpsSrc
END
RETURN @Error
END
GO
GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_DataWarehouse_Report_DataVolumenDetailsBySrcDataGet] TO OpsMgrReader
GO
</Install> <Uninstall>
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_Report_DataVolumenDetailsBySrcDataGet')
BEGIN
DROP PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_DataVolumenDetailsBySrcDataGet]
END
GO
</Uninstall> <Upgrade>
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_Report_DataVolumenDetailsBySrcDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_DataVolumenDetailsBySrcDataGet] AS RETURN 1')
END
GO
Create Table #TempTopNMpsSrc
(
ManagementPackRowId int
)
If @TopN <= 0
BEGIN
Select 0 As RowNumber,
SourceTypeId,
SourceTypeDisplay,
SourceId,
SourceDisplay,
0.00 As PrcVol,
0.00 As Slope,
0.00 As CountBySource
From #TmpDataCountsSrc
END
ELSE
BEGIN
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Options to Use
Insert into #TmpOpsSrc
Select OptList.OptionId.value('.','int') as OptionId
From @DTXML.nodes('/*/Value') As OptList(OptionId)
Order By OptionId
Select @APMDataExists = Case
When EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[apm].[EVENT]') AND type in (N'U')) Then 1
Else 0
End
Select @ShowDiscs = Case
When Count(OptionId) > 0 Then 1
ELSE 0
END
From #TmpOpsSrc where OptionId = 1
Select @ShowAlerts = Case
When Count(OptionId) > 0 Then 1
ELSE 0
END
From #TmpOpsSrc where OptionId = 2
Select @ShowPerfs = Case
When Count(OptionId) > 0 Then 1
ELSE 0
END
From #TmpOpsSrc where OptionId = 3
Select @ShowEvents = Case
When Count(OptionId) > 0 Then 1
ELSE 0
END
From #TmpOpsSrc where OptionId = 4
Select @ShowStates = Case
When Count(OptionId) > 0 Then 1
ELSE 0
END
From #TmpOpsSrc where OptionId = 5
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Get Mp To Filter
Insert Into #TmpMPsSrc
Select Mp.ManagementPackRowId,
Mp.ManagementPackSystemName
From @MPXML.nodes('/*/Value') As MpList(ManagementPackRowId)
Inner Join vManagementPack (NoLock) Mp
On Mp.ManagementPackRowId = MpList.ManagementPackRowId.value('.','int')
Order By ManagementPackRowId
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Get MANAGEMENT GROUPS To Filter
Insert Into #TmpMGPSrc
Select Mgp.ManagementGroupRowId,
Mgp.ManagementGroupDefaultName
From @MGPXML.nodes('/*/Value') As MgpList(ManagementGroupGuid)
Inner Join vManagementGroup (NoLock) Mgp
On Mgp.ManagementGroupGuid = MgpList.ManagementGroupGuid.value('.','uniqueidentifier')
Order By ManagementGroupRowId
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Get Filtered ManagementPackIds
Insert into #TempFilterdMpsSrc
Select distinct Mpc.ManagementPackRowId
From #TmpMPsSrc Mpc
Inner Join vManagementPackVersion Mpv
On Mpv.ManagementPackRowId = Mpc.ManagementPackRowId
Inner Join vManagementGroupManagementPackVersion Mgmpv
On Mgmpv.ManagementPackVersionRowId = Mpv.ManagementPackVersionRowId
Inner Join #TmpMGPSrc (NoLock) Mgp
On Mgp.ManagementGroupRowId = Mgmpv.ManagementGroupRowId
--Filtered Dates from Calendar
Insert into #TmpFilCalDtsSrc
Select Dt.DateValue
From vDate Dt
Where CONVERT(VARCHAR(8), Dt.DateValue, 112) BETWEEN CONVERT(VARCHAR(8) , @StartDate, 112) AND CONVERT(VARCHAR(8) , @EndDate, 112)
If @ShowDiscs = 1
BEGIN
--Create Temp Table to Store Filtered Data
Create Table #TmpDisFilSrc
(
ManagedEntityRowId int,
ManagedEntityTypeRowId int,
DateValue datetime,
DataCount bigint
)
--Counts By Date of Initial Discoveries base on Managed Entities
Insert into #TmpDisFilSrc
Select Me.ManagedEntityRowId,
Me.ManagedEntityTypeRowId,
DateAdd(hh,@AdjHours,Memg.FromDateTime) as FromDateTime,
Count(Me.ManagedEntityRowId) as DataCount
From vManagedEntity Me
Inner Join vManagedEntityType Met
On Me.ManagedEntityTypeRowId = Met.ManagedEntityTypeRowId
Inner Join vManagedEntityManagementGroup Memg
On Me.ManagedEntityRowId = Memg.ManagedEntityRowId
And Me.ManagementGroupRowId in (Select ManagementGroupRowId From #TmpMGPSrc)
Where DateAdd(hh,@AdjHours,Memg.FromDateTime) BETWEEN @StartDate AND @EndDate
Group By Me.ManagedEntityRowId, Me.ManagedEntityTypeRowId, Memg.FromDateTime
--Counts By Date of Initial Discoveries base on Type Managed Entities
Insert into #TmpDisFilSrc
Select Me.ManagedEntityRowId,
Me.ManagedEntityTypeRowId,
DateAdd(hh,@AdjHours,Tmt.FromDateTime) as FromDateTime,
Count(Me.ManagedEntityRowId) as DataCount
From vManagedEntity Me
Inner Join vTypedManagedEntity Tmt
On Me.ManagedEntityTypeRowId = Tmt.ManagedEntityTypeRowId
And Me.ManagedEntityRowId = Tmt.ManagedEntityRowId
Inner Join vManagedEntityManagementGroup Memg
On Me.ManagedEntityRowId = Memg.ManagedEntityRowId
And Me.ManagementGroupRowId in (Select ManagementGroupRowId From #TmpMGPSrc)
Where DateAdd(hh,@AdjHours,Tmt.FromDateTime) BETWEEN @StartDate AND @EndDate
Group By Me.ManagedEntityRowId, Me.ManagedEntityTypeRowId, Tmt.FromDateTime
--Counts By Date of Properties Changes Submission base on Managed Entities
Insert into #TmpDisFilSrc
Select Me.ManagedEntityRowId,
Me.ManagedEntityTypeRowId,
DateAdd(hh,@AdjHours,Mep.FromDateTime) as FromDateTime,
Count(Mep.ManagedEntityRowId) as DataCount
From vManagedEntityProperty Mep
Inner Join vManagedEntity Me
On Mep.ManagedEntityRowId = Me.ManagedEntityRowId
And Me.ManagementGroupRowId in (Select ManagementGroupRowId From #TmpMGPSrc)
Where DateAdd(hh,@AdjHours,Mep.FromDateTime) BETWEEN @StartDate AND @EndDate
Group By Me.ManagedEntityRowId, Me.ManagedEntityTypeRowId, Mep.FromDateTime
--Discovery Count By Source, Source Type and By Date of Initial Discoveries
Insert into #TmpDataCountsSrc
Select 1 as DataType,
Mpc.ManagementPackRowId,
Dis.ManagedEntityTypeRowId as SourceTypeId,
COALESCE(Ltp.Name,Mtp.ManagedEntityTypeDefaultName) as SourceTypeDisplay,
Dis.ManagedEntityRowId as SourceId,
COALESCE(Loc.Name,Met.DisplayName) as SourceDisplay,
COALESCE(Met.Path,Met.FullName) as SourcePathFullName,
Dt.DateValue as [Date],
IsNull(SUM(Dis.DataCount),0) as DataCount
From #TmpDisFilSrc Dis
Inner Join #TmpFilCalDtsSrc Dt
On CONVERT(VARCHAR(8), Dt.DateValue, 112) = CONVERT(VARCHAR(8) , Dis.DateValue, 112)
Inner Join vManagedEntity Met
On Met.ManagedEntityRowId = Dis.ManagedEntityRowId
Inner Join vManagedEntityType Mtp
On Mtp.ManagedEntityTypeRowId = Dis.ManagedEntityTypeRowId
Inner Join #TempFilterdMpsSrc Mpc
On Mpc.ManagementPackRowId = Mtp.ManagementPackRowId
Left Outer Join vDisplayString (NoLock) Loc
On Loc.ElementGuid = Mtp.ManagedEntityTypeGuid
And Loc.LanguageCode = @LanguageCode
Left Outer Join vDisplayString (NoLock) Ltp
On Ltp.ElementGuid = Mtp.ManagedEntityTypeGuid
And Ltp.LanguageCode = @LanguageCode
Group By Mpc.ManagementPackRowId, Dis.ManagedEntityTypeRowId, Ltp.Name, Mtp.ManagedEntityTypeDefaultName, Dis.ManagedEntityRowId, Loc.Name, Met.DisplayName, Dt.DateValue, Met.Path, Met.FullName
IF OBJECT_ID('tempdb..#TmpDisFilWrk') IS NOT NULL
BEGIN
drop table #TmpDisFilSrc
END
END
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
If @ShowAlerts = 1
BEGIN
--Alerts Count by Source
Insert into #TmpDataCountsSrc
Select 2 as DataType,
Mpc.ManagementPackRowId,
Mtp.ManagedEntityTypeRowId as SourceTypeId,
COALESCE(Ltp.Name,Mtp.ManagedEntityTypeDefaultName) as SourceTypeDisplay,
Met.ManagedEntityRowId as SourceId,
COALESCE(Loc.Name,Met.DisplayName) as SourceDisplay,
COALESCE(Met.Path,Met.FullName) as SourcePathFullName,
Dt.DateValue as Date,
IsNull(Count(Distinct Alr.AlertGuid),0) as DataCount
From Alert.vAlert (NoLock) Alr
LEFT OUTER JOIN vMonitor (NoLock) Mon
ON Mon.MonitorRowId = Alr.WorkflowRowId
AND Alr.MonitorAlertInd = 1
LEFT OUTER JOIN vRule Rul
ON Rul.RuleRowId = Alr.WorkflowRowId
AND Alr.MonitorAlertInd = 0
Inner Join #TmpFilCalDtsSrc Dt
On CONVERT(VARCHAR(8), Dt.DateValue, 112) = CONVERT(VARCHAR(8) , DateAdd(hh,@AdjHours,Alr.RaisedDateTime), 112)
Inner Join #TempFilterdMpsSrc Mpc
On Mon.ManagementPackRowId = Mpc.ManagementPackRowId
Or Rul.ManagementPackRowId = Mpc.ManagementPackRowId
Inner Join vManagedEntity (NoLock) Met
On Met.ManagedEntityRowId = Alr.ManagedEntityRowId
Inner Join #TmpMGPSrc Mg
On Met.ManagementGroupRowId = Mg.ManagementGroupRowId
Inner Join vManagedEntityType (NoLock) Mtp
On Mtp.ManagedEntityTypeRowId = Met.ManagedEntityTypeRowId
Left Outer Join vDisplayString (NoLock) Loc
On Loc.ElementGuid = Met.ManagedEntityGuid
And Loc.LanguageCode = @LanguageCode
Left Outer Join vDisplayString (NoLock) Ltp
On Ltp.ElementGuid = Mtp.ManagedEntityTypeGuid
And Ltp.LanguageCode = @LanguageCode
where DateAdd(hh,@AdjHours,Alr.RaisedDateTime) BETWEEN @StartDate AND @EndDate
Group By Mpc.ManagementPackRowId, Loc.Name,Ltp.Name,Mtp.ManagedEntityTypeRowId, Mtp.ManagedEntityTypeDefaultName, Met.ManagedEntityRowId, Met.DisplayName, Dt.DateValue, Met.Path, Met.FullName
END
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Getting Filtered Perfs Data Count By Dates
Insert Into #TempPrfDtFilSrc
Select PerfCTE.PerformanceRuleInstanceRowId,
PerfCTE.ManagedEntityRowId,
SUM(PerfCTE.SampleCount) As SampleCount,
DateAdd(hh,@AdjHours,PerfCTE.DateTime) as [DateTime]
From Perf.vPerfDaily PerfCTE
Inner Join vManagedEntity Met
On Met.ManagedEntityRowId = PerfCTE.ManagedEntityRowId
Inner Join #TmpMGPSrc Mg
On Met.ManagementGroupRowId = Mg.ManagementGroupRowId
Where CONVERT(VARCHAR(8), DateAdd(hh,@AdjHours,PerfCTE.DateTime), 112) BETWEEN CONVERT(VARCHAR(8) , @StartDate, 112) AND CONVERT(VARCHAR(8) , @EndDate, 112)
Group By PerfCTE.PerformanceRuleInstanceRowId, PerfCTE.ManagedEntityRowId, PerfCTE.DateTime
Insert into #TmpDataCountsSrc
Select Distinct 3 as DataType,
Mpc.ManagementPackRowId,
Mtp.ManagedEntityTypeRowId as SourceTypeId,
COALESCE(Ltp.Name,Mtp.ManagedEntityTypeDefaultName) as SourceTypeDisplay,
Prf.ManagedEntityRowId as SourceId,
COALESCE(Loc.Name,Met.DisplayName) as SourceDisplay,
COALESCE(Met.Path,Met.FullName) as SourcePathFullName,
Dt.DateValue as Date,
IsNull(SUM(Prf.SampleCount),0) as CountByDate
From #TempPrfDtFilSrc Prf
Inner Join vPerformanceRuleInstance (NoLock) Pri
On Prf.PerformanceRuleInstanceRowId = Pri.PerformanceRuleInstanceRowId
Inner Join vRule (NoLock) Rul
ON Pri.RuleRowId = Rul.RuleRowId
Inner Join #TmpFilCalDtsSrc Dt
On CONVERT(VARCHAR(8), Dt.DateValue, 112) = CONVERT(VARCHAR(8) , Prf.[DateTime], 112)
Inner Join #TempFilterdMpsSrc (NoLock) Mpc
On Mpc.ManagementPackRowId = Rul.ManagementPackRowId
Inner Join vManagedEntity (NoLock) Met
On Met.ManagedEntityRowId = Prf.ManagedEntityRowId
Inner Join vManagedEntityType (NoLock) Mtp
On Mtp.ManagedEntityTypeRowId = Met.ManagedEntityTypeRowId
Left Outer Join vDisplayString (NoLock) Loc
On Loc.ElementGuid = Met.ManagedEntityGuid
And Loc.LanguageCode = @LanguageCode
Left Outer Join vDisplayString (NoLock) Ltp
On Ltp.ElementGuid = Mtp.ManagedEntityTypeGuid
And Ltp.LanguageCode = @LanguageCode
Group By Mpc.ManagementPackRowId, Mtp.ManagedEntityTypeRowId, Ltp.Name, Mtp.ManagedEntityTypeDefaultName, Prf.ManagedEntityRowId, Loc.Name, Met.DisplayName, Dt.DateValue, Met.Path, Met.FullName
IF OBJECT_ID('tempdb..#TempPrfDtFilSrc') IS NOT NULL
BEGIN
drop table #TempPrfDtFilSrc
END
END
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Getting APM Events If Exists Table
If @APMDataExists = 1
BEGIN
Create Table #TempAPMEventMachines
(
ManagedEntityRowId int,
MachineID int
)
INSERT INTO #TempAPMEventMachines
SELECT
DISTINCT MEN.ManagedEntityRowId,
DP.MACHINEID
FROM
vManagedEntity AS MEN
INNER JOIN (
SELECT
SUBSTRING(machine, CHARINDEX('\', machine)+1, LEN(machine) ) As DisplayName ,
MACHINEID
FROM apm.MACHINE
) AS DP ON CHARINDEX(DP.DisplayName, MEN.DisplayName,0) > 0
WHERE
MEN.ManagedEntityTypeRowId in (select ManagedEntityTypeRowId from vManagedEntityType where ManagedEntityTypeSystemName = 'Microsoft.SystemCenter.Apm.ApmAgent')
Insert Into #TempEvtDtFilSrc
SELECT RL.RuleRowId,
APMMC.ManagedEntityRowId,
COUNT(EvtAPM.EventId),
DateAdd(hh,@AdjHours,EvtAPM.UTCEVENTDATE) as DateValue
FROM vRule RL
Inner Join vManagementPack MP
On RL.ManagementPackRowId = MP.ManagementPackRowId
And MP.ManagementPackSystemName = 'Microsoft.SystemCenter.Apm.Infrastructure'
And RL.RuleSystemName = 'Microsoft.SystemCenter.Apm.CollectApplicationDiagnosticsEvents'
Inner Join apm.EVENT EvtAPM
On RL.RuleRowId IS NOT NULL
Inner Join #TempAPMEventMachines APMMC
On APMMC.MACHINEID = EvtAPM.MACHINEID
Where DateAdd(hh,@AdjHours,EvtAPM.UTCEVENTDATE) BETWEEN @StartDate AND @EndDate
Group By RL.RuleRowId, APMMC.ManagedEntityRowId, EvtAPM.UTCEVENTDATE
Insert Into #TempEvtDtFilSrc
SELECT RL.RuleRowId,
APMMC.ManagedEntityRowId,
COUNT(EvtAPM.CSEVENTID),
DateAdd(hh,@AdjHours,EvtAPM.UTCDATE) as DateValue
FROM vRule RL
Inner Join vManagementPack MP
On RL.ManagementPackRowId = MP.ManagementPackRowId
And MP.ManagementPackSystemName = 'Microsoft.SystemCenter.Apm.Infrastructure'
And RL.RuleSystemName = 'Microsoft.SystemCenter.Apm.CollectApplicationDiagnosticsEvents'
Inner Join apm.CSEVENT EvtAPM
On RL.RuleRowId IS NOT NULL
Inner Join #TempAPMEventMachines APMMC
On APMMC.MACHINEID = EvtAPM.MACHINEID
Where DateAdd(hh,@AdjHours,EvtAPM.UTCDATE) BETWEEN @StartDate AND @EndDate
Group By RL.RuleRowId, APMMC.ManagedEntityRowId, EvtAPM.UTCDATE
END
--Getting Filtered Events Data Count By Dates
Insert Into #TempEvtDtFilSrc
SELECT ErlCTE.RuleRowId,
ErlCTE.ManagedEntityRowId,
COUNT(ErlCTE.EventOriginId),
DateAdd(hh,@AdjHours,EvtCTE.DateTime) as DateValue
FROM Event.vEvent EvtCTE
Inner Join Event.vEventRule ErlCTE
On EvtCTE.EventOriginId = ErlCTE.EventOriginId
Inner Join vManagedEntity Met
On Met.ManagedEntityRowId = ErlCTE.ManagedEntityRowId
Inner Join #TmpMGPSrc Mg
On Met.ManagementGroupRowId = Mg.ManagementGroupRowId
Where DateAdd(hh,@AdjHours,EvtCTE.DateTime) BETWEEN @StartDate AND @EndDate
Group By ErlCTE.RuleRowId, ErlCTE.ManagedEntityRowId, EvtCTE.DateTime
Insert into #TmpDataCountsSrc
Select 4 as DataType,
Mpc.ManagementPackRowId,
Mtp.ManagedEntityTypeRowId as SourceTypeId,
COALESCE(Ltp.Name,Mtp.ManagedEntityTypeDefaultName) as SourceTypeDisplay,
Evt.ManagedEntityRowId as SourceId,
COALESCE(Loc.Name,Met.DisplayName) as SourceDisplay,
COALESCE(Met.Path,Met.FullName) as SourcePathFullName,
Dt.DateValue as Date,
IsNull(SUM(Evt.EventCount),0) as CountByDate
From #TempEvtDtFilSrc Evt
Inner Join vRule Rul
ON Evt.RuleRowId = Rul.RuleRowId
Inner Join #TmpFilCalDtsSrc Dt
On CONVERT(VARCHAR(8), Dt.DateValue, 112) = CONVERT(VARCHAR(8) , Evt.[DateTime], 112)
Inner Join #TempFilterdMpsSrc Mpc
On Mpc.ManagementPackRowId = Rul.ManagementPackRowId
Inner Join vManagedEntity (NoLock) Met
On Met.ManagedEntityRowId = Evt.ManagedEntityRowId
Inner Join vManagedEntityType (NoLock) Mtp
On Mtp.ManagedEntityTypeRowId = Met.ManagedEntityTypeRowId
Left Outer Join vDisplayString (NoLock) Loc
On Loc.ElementGuid = Met.ManagedEntityGuid
And Loc.LanguageCode = @LanguageCode
Left Outer Join vDisplayString (NoLock) Ltp
On Ltp.ElementGuid = Mtp.ManagedEntityTypeGuid
And Ltp.LanguageCode = @LanguageCode
Group By Mpc.ManagementPackRowId, Mtp.ManagedEntityTypeRowId, Ltp.Name, Mtp.ManagedEntityTypeDefaultName, Evt.ManagedEntityRowId, Loc.Name, Met.DisplayName, Dt.DateValue, Met.Path, Met.FullName
IF OBJECT_ID('tempdb..#TempEvtDtFilSrc') IS NOT NULL
BEGIN
drop table #TempEvtDtFilSrc
END
IF OBJECT_ID('tempdb..#TempAPMEventMachines') IS NOT NULL
BEGIN
drop table #TempAPMEventMachines
END
END
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
If @ShowStates = 1
BEGIN
--Create Temp Filtered State Table
Create Table #TempSteDtFilSrc
(
MonitorRowId int,
ManagedEntityRowId int,
[DateTime] datetime,
DataCount bigint
)
--Getting Filtered State changes Count By Dates
Insert Into #TempSteDtFilSrc
Select Mn.MonitorRowId,
Mn.ManagedEntityRowId,
DateAdd(hh,@AdjHours,SteCTE.DateTime) as DateValue,
Count(SteCTE.ManagedEntityMonitorRowId) as DataCount
From State.vStateRaw SteCTE
Inner Join vManagedEntityMonitor Mn
On Mn.ManagedEntityMonitorRowId = SteCTE.ManagedEntityMonitorRowId
Inner Join vManagedEntity Met
On Met.ManagedEntityRowId = Mn.ManagedEntityRowId
Inner Join #TmpMGPSrc Mg
On Met.ManagementGroupRowId = Mg.ManagementGroupRowId
Where DateAdd(hh,@AdjHours,SteCTE.DateTime) BETWEEN @StartDate AND @EndDate
Group By Mn.MonitorRowId, Mn.ManagedEntityRowId, SteCTE.DateTime
--States Count By Source
Insert into #TmpDataCountsSrc
Select 5 as DataType,
Mpc.ManagementPackRowId,
Mtp.ManagedEntityTypeRowId as SourceTypeId,
COALESCE(Ltp.Name,Mtp.ManagedEntityTypeDefaultName) as SourceTypeDisplay,
Ste.ManagedEntityRowId as SourceId,
COALESCE(Loc.Name,Met.DisplayName) as SourceDisplay,
COALESCE(Met.Path,Met.FullName) as SourcePathFullName,
Dt.DateValue as Date,
IsNull(SUM(Ste.DataCount),0) as CountByDate
From #TempSteDtFilSrc Ste
Inner Join vMonitor Mon
On Mon.MonitorRowId = Ste.MonitorRowId
Inner Join #TmpFilCalDtsSrc Dt
On CONVERT(VARCHAR(8), Dt.DateValue, 112) = CONVERT(VARCHAR(8) , Ste.[DateTime], 112)
Inner Join #TempFilterdMpsSrc Mpc
On Mpc.ManagementPackRowId = Mon.ManagementPackRowId
Inner Join vManagedEntity (NoLock) Met
On Met.ManagedEntityRowId = Ste.ManagedEntityRowId
Inner Join vManagedEntityType (NoLock) Mtp
On Mtp.ManagedEntityTypeRowId = Met.ManagedEntityTypeRowId
Left Outer Join vDisplayString (NoLock) Loc
On Loc.ElementGuid = Met.ManagedEntityGuid
And Loc.LanguageCode = @LanguageCode
Left Outer Join vDisplayString (NoLock) Ltp
On Ltp.ElementGuid = Mtp.ManagedEntityTypeGuid
And Ltp.LanguageCode = @LanguageCode
Group By Mpc.ManagementPackRowId, Mtp.ManagedEntityTypeRowId, Ltp.Name, Mtp.ManagedEntityTypeDefaultName, Ste.ManagedEntityRowId, Loc.Name, Met.DisplayName, Dt.DateValue, Met.Path, Met.FullName
IF OBJECT_ID('tempdb..#TempSteDtFilSrc') IS NOT NULL
BEGIN
drop table #TempSteDtFilSrc
END
END
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Calculate Total Volume
Select @TotVol = IsNull(Sum(DataCount),0) From #TmpDataCountsSrc
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Calculate SumX
Insert into #TempDaysSrc
Select ROW_NUMBER() OVER(ORDER BY DateValue) AS DayNumber,
DateValue as Date
From #TmpFilCalDtsSrc (NoLock) Dt
Select @SumX = SUM(DayNumber) from #TempDaysSrc
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Calculate SumX2
Select @SumX2 = Sum(x0.DayNumber*x1.DayNumber)
From #TempDaysSrc x0
Inner Join #TempDaysSrc x1
On x0.DayNumber = x1.DayNumber
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Calculate Top Values for Equation by Source and SourceType
Insert into #TempTopCalSrc
Select Tot.SourceId,
Tot.SourceTypeId,
@DaysNumber as CntDys,
Sum(Tot.SXY) as SumXY,
Sum(Tot.DataCount) as SumY,
@SumX as SumX
From
(
Select CnSrc.SourceId,
CnSrc.SourceTypeId,
CnSrc.Date,
Sum(CnDy.DayNumber*CnSrc.DataCount) as SXY,
Sum(CnSrc.DataCount) as DataCount
From #TmpDataCountsSrc CnSrc
Inner Join #TempDaysSrc CnDy
On CnSrc.Date = CnDy.Date
Group by CnSrc.SourceId, CnSrc.SourceTypeId, CnSrc.Date
) as Tot
Group by Tot.SourceId, Tot.SourceTypeId
Order by Tot.SourceId, Tot.SourceTypeId
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Calculate Bottom Values for Equation by Source and SourceType
Insert into #TempBottomCalSrc
Select SourceId,
SourceTypeId,
@DaysNumber as CntDys,
@SumX2 as SumX2,
@SumX * @SumX as SumXxSumX
From #TmpDataCountsSrc CnSrc
Group by CnSrc.SourceId, CnSrc.SourceTypeId
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Calculate Final Result of Equation by WorkFlow
Insert into #TempCalBySrc
Select Tp.SourceId,
Tp.SourceTypeId,
((Tp.CntDys * Tp.SumXY)-(Tp.SumY * Tp.SumX)) as TopVal,
((Bt.CntDys * Bt.SumX2)-SumXxSumX) as BottomVal,
Case
When ((Bt.CntDys * Bt.SumX2)-SumXxSumX) > 0 Then (CAST(((Tp.CntDys * Tp.SumXY)-(Tp.SumY * Tp.SumX)) as Decimal) / CAST(((Bt.CntDys * Bt.SumX2)-SumXxSumX) as Decimal))
Else 0.00
End As Slope
From #TempTopCalSrc Tp
Inner Join #TempBottomCalSrc Bt
On Tp.SourceId = Bt.SourceId
And Tp.SourceTypeId = Bt.SourceTypeId
Order By Tp.SourceId, Tp.SourceTypeId
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
Insert into #TempTopNMpsSrc
Select Top (@TopN) Mbv.ManagementPackRowId
From
(
Select
Mpc.ManagementPackRowId,
Case
When @TotVol = 0 Then 0
Else ( ( CAST( ( IsNull(Sum(Cnts.DataCount),0) ) AS decimal)/@TotVol ) * 100 )
End AS PrcVol
From #TempFilterdMpsSrc Mpc
Left Outer Join #TmpDataCountsSrc Cnts
On Mpc.ManagementPackRowId = Cnts.ManagementPackRowId
Group By Mpc.ManagementPackRowId
) As Mbv
Order By Mbv.PrcVol desc
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Final Results
Select Top (@TopN)
ROW_NUMBER() OVER(ORDER BY Cnts.PrcVol Desc) AS 'RowNumber',
Cnts.SourceId,
Cnts.SourceDisplay,
Cnts.SourceTypeId,
Cnts.SourceTypeDisplay,
Cnts.SourcePathFullName,
Cnts.PrcVol,
IsNull(Cl.Slope,0) as Slope,
Cnts.CountBySource
From
(
Select DataType,
SourceId,
SourceDisplay,
SourceTypeId,
SourceTypeDisplay,
SourcePathFullName,
Case
When @TotVol = 0 Then 0
Else ((CAST(Sum(IsNull(DataCount,0)) AS decimal)/@TotVol)*100)
End AS PrcVol,
Sum(IsNull(DataCount,0)) as CountBySource
From #TmpDataCountsSrc CntSrc
Inner Join #TempTopNMpsSrc Mpv
On Mpv.ManagementPackRowId = IsNull(CntSrc.ManagementPackRowId,Mpv.ManagementPackRowId)
Group By DataType,
SourceId,
SourceDisplay,
SourceTypeId,
SourceTypeDisplay,
SourcePathFullName
) As Cnts
Left Outer Join #TempCalBySrc Cl
On Cl.SourceId = Cnts.SourceId
And Cl.SourceTypeId = Cnts.SourceTypeId
Order by Cnts.PrcVol Desc
END
QuitError:
IF OBJECT_ID('tempdb..#TmpOpsSrc') IS NOT NULL
BEGIN
Drop Table #TmpOpsSrc
END
IF OBJECT_ID('tempdb..#TmpMPsSrc') IS NOT NULL
BEGIN
Drop Table #TmpMPsSrc
END
IF OBJECT_ID('tempdb..#TmpMGPSrc') IS NOT NULL
BEGIN
Drop Table #TmpMGPSrc
END
IF OBJECT_ID('tempdb..#TempFilterdMpsSrc') IS NOT NULL
BEGIN
Drop Table #TempFilterdMpsSrc
END
IF OBJECT_ID('tempdb..#TmpDataCountsSrc') IS NOT NULL
BEGIN
Drop Table #TmpDataCountsSrc
END
IF OBJECT_ID('tempdb..#TempDaysSrc') IS NOT NULL
BEGIN
Drop Table #TempDaysSrc
END
IF OBJECT_ID('tempdb..#TempTopCalSrc') IS NOT NULL
BEGIN
Drop Table #TempTopCalSrc
END
IF OBJECT_ID('tempdb..#TempBottomCalSrc') IS NOT NULL
BEGIN
Drop Table #TempBottomCalSrc
END
IF OBJECT_ID('tempdb..#TempCalBySrc') IS NOT NULL
BEGIN
Drop Table #TempCalBySrc
END
IF OBJECT_ID('tempdb..#TmpFilCalDtsSrc') IS NOT NULL
BEGIN
Drop Table #TmpFilCalDtsSrc
END
IF OBJECT_ID('tempdb..#TempTopNMpsSrc') IS NOT NULL
BEGIN
drop table #TempTopNMpsSrc
END
RETURN @Error
END
GO
GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_DataWarehouse_Report_DataVolumenDetailsBySrcDataGet] TO OpsMgrReader
GO
Home
