<DataWarehouseScript ID="Microsoft.SystemCenter.OperationsManager.Reports.2007.Script.DataVolumesDataGet" Accessibility="Internal"> <Install>
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_Report_DataVolumenDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_DataVolumenDataGet] AS RETURN 1')
END
GO
If @TopN <= 0
BEGIN
Select 0 as RowNumber,
ManagementPackRowId,
'' as ManagementPackSystemName,
0.00 as PrcVol,
0.00 as Slope,
0.00 as DiscoveryData,
0.00 as AlertCount,
0.00 as PerfCount,
0.00 as EventCount,
0.00 as StateChanges
From #TmpDataCounts
END
ELSE
BEGIN
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Options to Use
Insert into #TmpOpts
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 #TmpOpts where OptionId = 1
Select @ShowAlerts = Case
When Count(OptionId) > 0 Then 1
ELSE 0
END
From #TmpOpts where OptionId = 2
Select @ShowPerfs = Case
When Count(OptionId) > 0 Then 1
ELSE 0
END
From #TmpOpts where OptionId = 3
Select @ShowEvents = Case
When Count(OptionId) > 0 Then 1
ELSE 0
END
From #TmpOpts where OptionId = 4
Select @ShowStates = Case
When Count(OptionId) > 0 Then 1
ELSE 0
END
From #TmpOpts where OptionId = 5
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Get Mp To Filter
Insert Into #TmpMP
Select Mp.ManagementPackRowId,
Mp.ManagementPackDefaultName,
Mp.ManagementPackVersionIndependentGuid
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 #TmpMGP
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 #TempFilterdMps
Select distinct Mpc.ManagementPackRowId
From #TmpMP Mpc
Inner Join vManagementPackVersion Mpv
On Mpv.ManagementPackRowId = Mpc.ManagementPackRowId
Inner Join vManagementGroupManagementPackVersion Mgmpv
On Mgmpv.ManagementPackVersionRowId = Mpv.ManagementPackVersionRowId
Inner Join #TmpMGP (NoLock) Mgp
On Mgp.ManagementGroupRowId = Mgmpv.ManagementGroupRowId
--Filtered Dates from Calendar
Insert into #TmpFilCalDts
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 #TmpDisFil
(
ManagedEntityTypeRowId int,
DateValue datetime,
DataCount bigint
)
--Counts By Date of Initial Discoveries base on Managed Entities
Insert into #TmpDisFil
Select 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 #TmpMGP)
Where DateAdd(hh,@AdjHours,Memg.FromDateTime) BETWEEN @StartDate AND @EndDate
Group By Me.ManagedEntityTypeRowId, Memg.FromDateTime
--Counts By Date of Initial Discoveries base on Type Managed Entities
Insert into #TmpDisFil
Select 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 #TmpMGP)
Where DateAdd(hh,@AdjHours,Tmt.FromDateTime) BETWEEN @StartDate AND @EndDate
Group By Me.ManagedEntityTypeRowId, Tmt.FromDateTime
--Counts By Date of Properties Changes Submission base on Managed Entities
Insert into #TmpDisFil
Select 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 #TmpMGP)
where DateAdd(hh,@AdjHours,Mep.FromDateTime) BETWEEN @StartDate AND @EndDate
Group By Me.ManagedEntityTypeRowId, Mep.FromDateTime
--Discovery Count By MP
Insert into #TmpDataCounts
Select 1 as DataType,
Mpc.ManagementPackRowId as ManagementPackRowId,
Dt.DateValue as [Date],
IsNull(SUM(Dis.DataCount),0) as DataCount
From #TmpDisFil Dis
Inner Join #TmpFilCalDts Dt
On CONVERT(VARCHAR(8), Dt.DateValue, 112) = CONVERT(VARCHAR(8) , Dis.DateValue, 112)
Inner Join vManagedEntityType Mtp
On Mtp.ManagedEntityTypeRowId = Dis.ManagedEntityTypeRowId
Inner Join #TempFilterdMps Mpc
On Mpc.ManagementPackRowId = Mtp.ManagementPackRowId
Group By Mpc.ManagementPackRowId, Dt.DateValue
IF OBJECT_ID('tempdb..#TmpDisFil') IS NOT NULL
BEGIN
drop table #TmpDisFil
END
END
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Alerts Count by MP
If @ShowAlerts = 1
BEGIN
--Alerts Count By MP
Insert into #TmpDataCounts
Select 2 as DataType,
IsNull(Mon.ManagementPackRowId, Rul.ManagementPackRowId) as ManagementPackRowId,
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 vManagedEntity Met
On Met.ManagedEntityRowId = Alr.ManagedEntityRowId
Inner Join #TmpMGP Mg
On Met.ManagementGroupRowId = Mg.ManagementGroupRowId
Inner Join #TmpFilCalDts Dt
On CONVERT(VARCHAR(8), Dt.DateValue, 112) = CONVERT(VARCHAR(8) , DateAdd(hh,@AdjHours,Alr.RaisedDateTime), 112)
Inner Join #TempFilterdMps Mpc
On Mon.ManagementPackRowId = Mpc.ManagementPackRowId
Or Rul.ManagementPackRowId = Mpc.ManagementPackRowId
where DateAdd(hh,@AdjHours,Alr.RaisedDateTime) BETWEEN @StartDate AND @EndDate
Group By IsNull(Mon.ManagementPackRowId,Rul.ManagementPackRowId), Dt.DateValue
END
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Getting Filtered Perfs Data Count By Dates
Insert Into #TempPrfFil
Select PerfCTE.PerformanceRuleInstanceRowId,
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 #TmpMGP 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.DateTime
--Perf Count By MP
Insert into #TmpDataCounts
Select Distinct 3 as DataType,
Mpc.ManagementPackRowId,
Dt.DateValue as Date,
SUM(Prf.SampleCount) as DataCount
From #TempPrfFil Prf
Inner Join #TmpFilCalDts Dt
On CONVERT(VARCHAR(8), Dt.DateValue, 112) = CONVERT(VARCHAR(8) , Prf.[DateTime], 112)
Inner Join vPerformanceRuleInstance (NoLock) Pri
On Prf.PerformanceRuleInstanceRowId = Pri.PerformanceRuleInstanceRowId
Inner Join vRule (NoLock) Rul
ON Pri.RuleRowId = Rul.RuleRowId
Inner Join #TempFilterdMps (NoLock) Mpc
On Mpc.ManagementPackRowId = Rul.ManagementPackRowId
Group By Mpc.ManagementPackRowId, Dt.DateValue
IF OBJECT_ID('tempdb..#TempPrfFil') IS NOT NULL
BEGIN
drop table #TempPrfFil
END
END
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Getting APM Events If Exists Table
If @APMDataExists = 1
BEGIN
Insert Into #TempEvtFil
SELECT RL.RuleRowId,
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
Where DateAdd(hh,@AdjHours,EvtAPM.UTCEVENTDATE) BETWEEN @StartDate AND @EndDate
Group By RL.RuleRowId, EvtAPM.UTCEVENTDATE
Insert Into #TempEvtFil
SELECT RL.RuleRowId,
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
Where DateAdd(hh,@AdjHours,EvtAPM.UTCDATE) BETWEEN @StartDate AND @EndDate
Group By RL.RuleRowId, EvtAPM.UTCDATE
END
--Getting Filtered Events Data Count By Dates
Insert Into #TempEvtFil
SELECT ErlCTE.RuleRowId,
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 #TmpMGP Mg
On Met.ManagementGroupRowId = Mg.ManagementGroupRowId
Where DateAdd(hh,@AdjHours,EvtCTE.DateTime) BETWEEN @StartDate AND @EndDate
Group By ErlCTE.RuleRowId, EvtCTE.DateTime
--Events Count By MP
Insert into #TmpDataCounts
Select 4 as DataType,
Mpc.ManagementPackRowId,
Dt.DateValue as Date,
SUM(Evt.EventCount) as DataCount
From #TempEvtFil Evt
Inner Join #TmpFilCalDts Dt
On CONVERT(VARCHAR(8), Dt.DateValue, 112) = CONVERT(VARCHAR(8) , Evt.[DateTime], 112)
Inner Join vRule (NoLock) Rul
ON Evt.RuleRowId = Rul.RuleRowId
Inner Join #TempFilterdMps (NoLock) Mpc
On Mpc.ManagementPackRowId = Rul.ManagementPackRowId
Group By Mpc.ManagementPackRowId, Dt.DateValue
IF OBJECT_ID('tempdb..#TempEvtFil') IS NOT NULL
BEGIN
drop table #TempEvtFil
END
END
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
If @ShowStates = 1
BEGIN
--Create Temp Filtered States Table
Create Table #TempSteFil
(
MonitorRowId int,
[DateTime] datetime,
DataCount bigint
)
--Getting Filtered State changes Count By Dates
Insert Into #TempSteFil
Select Mn.MonitorRowId,
DateAdd(hh,@AdjHours,SteCTE.DateTime) as Date,
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 #TmpMGP Mg
On Met.ManagementGroupRowId = Mg.ManagementGroupRowId
Where DateAdd(hh,@AdjHours,SteCTE.DateTime) BETWEEN @StartDate AND @EndDate
Group By Mn.MonitorRowId, SteCTE.DateTime
--State Count By MP
Insert into #TmpDataCounts
Select 5 as DataType,
Mpc.ManagementPackRowId,
Dt.DateValue as Date,
SUM(Ste.DataCount) as DataCount
From #TempSteFil Ste
Inner Join #TmpFilCalDts Dt
On CONVERT(VARCHAR(8), Dt.DateValue, 112) = CONVERT(VARCHAR(8) , Ste.[DateTime], 112)
Inner Join vMonitor Mon
On Mon.MonitorRowId = Ste.MonitorRowId
Inner Join #TempFilterdMps Mpc
On Mpc.ManagementPackRowId = Mon.ManagementPackRowId
Group By Mpc.ManagementPackRowId, Dt.DateValue
IF OBJECT_ID('tempdb..#TempSteFil') IS NOT NULL
BEGIN
drop table #TempSteFil
END
END
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Calculate Total Volume
Select @TotVol = IsNull(Sum(DataCount),0) From #TmpDataCounts
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Calculate SumX
Insert into #TempDays
Select ROW_NUMBER() OVER(ORDER BY DateValue) AS DayNumber,
DateValue as Date
From #TmpFilCalDts (NoLock) Dt
Select @SumX = SUM(DayNumber) from #TempDays
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Calculate SumX2
Select @SumX2 = Sum(x0.DayNumber*x1.DayNumber)
From #TempDays x0
Inner Join #TempDays x1
On x0.DayNumber = x1.DayNumber
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Calculate Top Values for Equation by Mp
Insert into #TempTopCal
Select Tot.ManagementPackRowId,
@DaysNumber as CntDys,
Sum(Tot.SXY) as SumXY,
Sum(Tot.DataCount) as SumY,
@SumX as SumX
From
(
Select CnMp.ManagementPackRowId,
CnMp.Date,
Sum(CnDy.DayNumber*CnMp.DataCount) as SXY,
Sum(CnMp.DataCount) as DataCount
From #TmpDataCounts CnMp
Inner Join #TempDays CnDy
On CnMp.Date = CnDy.Date
Group by CnMp.ManagementPackRowId, CnMp.Date
) as Tot
Group by Tot.ManagementPackRowId
Order by Tot.ManagementPackRowId
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Calculate Bottom Values for Equation by Mp
Insert into #TempBottomCal
Select ManagementPackRowId,
@DaysNumber as CntDys,
@SumX2 as SumX2,
@SumX * @SumX as SumXxSumX
From #TmpDataCounts CnMp
Group by CnMp.ManagementPackRowId
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Calculate Final Result of Equation by Mp
Insert into #TempCalByMp
Select Tp.ManagementPackRowId,
((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 #TempTopCal Tp
Inner Join #TempBottomCal Bt
On Tp.ManagementPackRowId = Bt.ManagementPackRowId
Order By Tp.ManagementPackRowId
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Final Results
Select Top (@TopN)
ROW_NUMBER() OVER(ORDER BY Cnts.PrcVol Desc) AS 'RowNumber',
Cnts.ManagementPackRowId,
Cnts.ManagementPackSystemName,
Cnts.PrcVol,
IsNull(Cl.Slope,0) as Slope,
Cnts.DiscoveryCount as DiscoveryData,
Cnts.AlertCount,
Cnts.PerfCount,
Cnts.EventCount,
Cnts.StateCount as StateChanges
From
(
Select Mp.ManagementPackRowId,
IsNull(Loc.Name, Mp.ManagementPackDefaultName) as ManagementPackSystemName,
Case
When @TotVol = 0 Then 0
Else ( ( CAST( ( IsNull(DsDc.DataCount,0) + IsNull(AlDc.DataCount,0) + IsNull(PfDc.DataCount,0) + IsNull(EvDc.DataCount,0) + IsNull(StDc.DataCount,0) ) AS decimal)/@TotVol ) * 100 )
End AS PrcVol,
IsNull(DsDc.DataCount,0) as DiscoveryCount,
IsNull(AlDc.DataCount,0) as AlertCount,
IsNull(PfDc.DataCount,0) as PerfCount,
IsNull(EvDc.DataCount,0) as EventCount,
IsNull(StDc.DataCount,0) as StateCount
From #TmpMP (NoLock) Mp
Left Outer Join vDisplayString (NoLock) Loc
On Mp.ManagementPackVersionIndependentGuid = Loc.ElementGuid
And Loc.LanguageCode = @LanguageCode
Left Outer Join
(
Select ManagementPackRowId,
Sum(DataCount) as DataCount
From #TmpDataCounts
Where DataType = 1
Group By ManagementPackRowId, DataType
) As DsDc
On Mp.ManagementPackRowId = DsDc.ManagementPackRowId
Left Outer Join
(
Select ManagementPackRowId,
Sum(DataCount) as DataCount
From #TmpDataCounts
Where DataType = 2
Group By ManagementPackRowId, DataType
) As AlDc
On Mp.ManagementPackRowId = AlDc.ManagementPackRowId
Left Outer Join
(
Select ManagementPackRowId,
Sum(DataCount) as DataCount
From #TmpDataCounts
Where DataType = 3
Group By ManagementPackRowId, DataType
) As PfDc
On Mp.ManagementPackRowId = PfDc.ManagementPackRowId
Left Outer Join
(
Select ManagementPackRowId,
Sum(DataCount) as DataCount
From #TmpDataCounts
Where DataType = 4
Group By ManagementPackRowId, DataType
) As EvDc
On Mp.ManagementPackRowId = EvDc.ManagementPackRowId
Left Outer Join
(
Select ManagementPackRowId,
Sum(DataCount) as DataCount
From #TmpDataCounts
Where DataType = 5
Group By ManagementPackRowId, DataType
) As StDc
On Mp.ManagementPackRowId = StDc.ManagementPackRowId
) As Cnts
Left Outer Join #TempCalByMp Cl
On Cl.ManagementPackRowId = Cnts.ManagementPackRowId
Order by Cnts.PrcVol Desc
END
QuitError:
IF OBJECT_ID('tempdb..#TmpOpts') IS NOT NULL
BEGIN
drop table #TmpOpts
END
IF OBJECT_ID('tempdb..#TmpMP') IS NOT NULL
BEGIN
drop table #TmpMP
END
IF OBJECT_ID('tempdb..#TmpMGP') IS NOT NULL
BEGIN
drop table #TmpMGP
END
IF OBJECT_ID('tempdb..#TempFilterdMps') IS NOT NULL
BEGIN
drop table #TempFilterdMps
END
IF OBJECT_ID('tempdb..#TmpDataCounts') IS NOT NULL
BEGIN
drop table #TmpDataCounts
END
IF OBJECT_ID('tempdb..#TempDays') IS NOT NULL
BEGIN
drop table #TempDays
END
IF OBJECT_ID('tempdb..#TempTopCal') IS NOT NULL
BEGIN
drop table #TempTopCal
END
IF OBJECT_ID('tempdb..#TempBottomCal') IS NOT NULL
BEGIN
drop table #TempBottomCal
END
IF OBJECT_ID('tempdb..#TempCalByMp') IS NOT NULL
BEGIN
drop table #TempCalByMp
END
IF OBJECT_ID('tempdb..#TmpFilCalDts') IS NOT NULL
BEGIN
drop table #TmpFilCalDts
END
RETURN @Error
END
GO
GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_DataWarehouse_Report_DataVolumenDataGet] TO OpsMgrReader
GO
</Install> <Uninstall>
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_Report_DataVolumenDataGet')
BEGIN
DROP PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_DataVolumenDataGet]
END
GO
</Uninstall> <Upgrade>
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_Report_DataVolumenDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_DataVolumenDataGet] AS RETURN 1')
END
GO
If @TopN <= 0
BEGIN
Select 0 as RowNumber,
ManagementPackRowId,
'' as ManagementPackSystemName,
0.00 as PrcVol,
0.00 as Slope,
0.00 as DiscoveryData,
0.00 as AlertCount,
0.00 as PerfCount,
0.00 as EventCount,
0.00 as StateChanges
From #TmpDataCounts
END
ELSE
BEGIN
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Options to Use
Insert into #TmpOpts
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 #TmpOpts where OptionId = 1
Select @ShowAlerts = Case
When Count(OptionId) > 0 Then 1
ELSE 0
END
From #TmpOpts where OptionId = 2
Select @ShowPerfs = Case
When Count(OptionId) > 0 Then 1
ELSE 0
END
From #TmpOpts where OptionId = 3
Select @ShowEvents = Case
When Count(OptionId) > 0 Then 1
ELSE 0
END
From #TmpOpts where OptionId = 4
Select @ShowStates = Case
When Count(OptionId) > 0 Then 1
ELSE 0
END
From #TmpOpts where OptionId = 5
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Get Mp To Filter
Insert Into #TmpMP
Select Mp.ManagementPackRowId,
Mp.ManagementPackDefaultName,
Mp.ManagementPackVersionIndependentGuid
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 #TmpMGP
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 #TempFilterdMps
Select distinct Mpc.ManagementPackRowId
From #TmpMP Mpc
Inner Join vManagementPackVersion Mpv
On Mpv.ManagementPackRowId = Mpc.ManagementPackRowId
Inner Join vManagementGroupManagementPackVersion Mgmpv
On Mgmpv.ManagementPackVersionRowId = Mpv.ManagementPackVersionRowId
Inner Join #TmpMGP (NoLock) Mgp
On Mgp.ManagementGroupRowId = Mgmpv.ManagementGroupRowId
--Filtered Dates from Calendar
Insert into #TmpFilCalDts
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 #TmpDisFil
(
ManagedEntityTypeRowId int,
DateValue datetime,
DataCount bigint
)
--Counts By Date of Initial Discoveries base on Managed Entities
Insert into #TmpDisFil
Select 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 #TmpMGP)
Where DateAdd(hh,@AdjHours,Memg.FromDateTime) BETWEEN @StartDate AND @EndDate
Group By Me.ManagedEntityTypeRowId, Memg.FromDateTime
--Counts By Date of Initial Discoveries base on Type Managed Entities
Insert into #TmpDisFil
Select 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 #TmpMGP)
Where DateAdd(hh,@AdjHours,Tmt.FromDateTime) BETWEEN @StartDate AND @EndDate
Group By Me.ManagedEntityTypeRowId, Tmt.FromDateTime
--Counts By Date of Properties Changes Submission base on Managed Entities
Insert into #TmpDisFil
Select 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 #TmpMGP)
where DateAdd(hh,@AdjHours,Mep.FromDateTime) BETWEEN @StartDate AND @EndDate
Group By Me.ManagedEntityTypeRowId, Mep.FromDateTime
--Discovery Count By MP
Insert into #TmpDataCounts
Select 1 as DataType,
Mpc.ManagementPackRowId as ManagementPackRowId,
Dt.DateValue as [Date],
IsNull(SUM(Dis.DataCount),0) as DataCount
From #TmpDisFil Dis
Inner Join #TmpFilCalDts Dt
On CONVERT(VARCHAR(8), Dt.DateValue, 112) = CONVERT(VARCHAR(8) , Dis.DateValue, 112)
Inner Join vManagedEntityType Mtp
On Mtp.ManagedEntityTypeRowId = Dis.ManagedEntityTypeRowId
Inner Join #TempFilterdMps Mpc
On Mpc.ManagementPackRowId = Mtp.ManagementPackRowId
Group By Mpc.ManagementPackRowId, Dt.DateValue
IF OBJECT_ID('tempdb..#TmpDisFil') IS NOT NULL
BEGIN
drop table #TmpDisFil
END
END
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Alerts Count by MP
If @ShowAlerts = 1
BEGIN
--Alerts Count By MP
Insert into #TmpDataCounts
Select 2 as DataType,
IsNull(Mon.ManagementPackRowId, Rul.ManagementPackRowId) as ManagementPackRowId,
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 vManagedEntity Met
On Met.ManagedEntityRowId = Alr.ManagedEntityRowId
Inner Join #TmpMGP Mg
On Met.ManagementGroupRowId = Mg.ManagementGroupRowId
Inner Join #TmpFilCalDts Dt
On CONVERT(VARCHAR(8), Dt.DateValue, 112) = CONVERT(VARCHAR(8) , DateAdd(hh,@AdjHours,Alr.RaisedDateTime), 112)
Inner Join #TempFilterdMps Mpc
On Mon.ManagementPackRowId = Mpc.ManagementPackRowId
Or Rul.ManagementPackRowId = Mpc.ManagementPackRowId
where DateAdd(hh,@AdjHours,Alr.RaisedDateTime) BETWEEN @StartDate AND @EndDate
Group By IsNull(Mon.ManagementPackRowId,Rul.ManagementPackRowId), Dt.DateValue
END
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Getting Filtered Perfs Data Count By Dates
Insert Into #TempPrfFil
Select PerfCTE.PerformanceRuleInstanceRowId,
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 #TmpMGP 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.DateTime
--Perf Count By MP
Insert into #TmpDataCounts
Select Distinct 3 as DataType,
Mpc.ManagementPackRowId,
Dt.DateValue as Date,
SUM(Prf.SampleCount) as DataCount
From #TempPrfFil Prf
Inner Join #TmpFilCalDts Dt
On CONVERT(VARCHAR(8), Dt.DateValue, 112) = CONVERT(VARCHAR(8) , Prf.[DateTime], 112)
Inner Join vPerformanceRuleInstance (NoLock) Pri
On Prf.PerformanceRuleInstanceRowId = Pri.PerformanceRuleInstanceRowId
Inner Join vRule (NoLock) Rul
ON Pri.RuleRowId = Rul.RuleRowId
Inner Join #TempFilterdMps (NoLock) Mpc
On Mpc.ManagementPackRowId = Rul.ManagementPackRowId
Group By Mpc.ManagementPackRowId, Dt.DateValue
IF OBJECT_ID('tempdb..#TempPrfFil') IS NOT NULL
BEGIN
drop table #TempPrfFil
END
END
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Getting APM Events If Exists Table
If @APMDataExists = 1
BEGIN
Insert Into #TempEvtFil
SELECT RL.RuleRowId,
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
Where DateAdd(hh,@AdjHours,EvtAPM.UTCEVENTDATE) BETWEEN @StartDate AND @EndDate
Group By RL.RuleRowId, EvtAPM.UTCEVENTDATE
Insert Into #TempEvtFil
SELECT RL.RuleRowId,
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
Where DateAdd(hh,@AdjHours,EvtAPM.UTCDATE) BETWEEN @StartDate AND @EndDate
Group By RL.RuleRowId, EvtAPM.UTCDATE
END
--Getting Filtered Events Data Count By Dates
Insert Into #TempEvtFil
SELECT ErlCTE.RuleRowId,
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 #TmpMGP Mg
On Met.ManagementGroupRowId = Mg.ManagementGroupRowId
Where DateAdd(hh,@AdjHours,EvtCTE.DateTime) BETWEEN @StartDate AND @EndDate
Group By ErlCTE.RuleRowId, EvtCTE.DateTime
--Events Count By MP
Insert into #TmpDataCounts
Select 4 as DataType,
Mpc.ManagementPackRowId,
Dt.DateValue as Date,
SUM(Evt.EventCount) as DataCount
From #TempEvtFil Evt
Inner Join #TmpFilCalDts Dt
On CONVERT(VARCHAR(8), Dt.DateValue, 112) = CONVERT(VARCHAR(8) , Evt.[DateTime], 112)
Inner Join vRule (NoLock) Rul
ON Evt.RuleRowId = Rul.RuleRowId
Inner Join #TempFilterdMps (NoLock) Mpc
On Mpc.ManagementPackRowId = Rul.ManagementPackRowId
Group By Mpc.ManagementPackRowId, Dt.DateValue
IF OBJECT_ID('tempdb..#TempEvtFil') IS NOT NULL
BEGIN
drop table #TempEvtFil
END
END
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
If @ShowStates = 1
BEGIN
--Create Temp Filtered States Table
Create Table #TempSteFil
(
MonitorRowId int,
[DateTime] datetime,
DataCount bigint
)
--Getting Filtered State changes Count By Dates
Insert Into #TempSteFil
Select Mn.MonitorRowId,
DateAdd(hh,@AdjHours,SteCTE.DateTime) as Date,
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 #TmpMGP Mg
On Met.ManagementGroupRowId = Mg.ManagementGroupRowId
Where DateAdd(hh,@AdjHours,SteCTE.DateTime) BETWEEN @StartDate AND @EndDate
Group By Mn.MonitorRowId, SteCTE.DateTime
--State Count By MP
Insert into #TmpDataCounts
Select 5 as DataType,
Mpc.ManagementPackRowId,
Dt.DateValue as Date,
SUM(Ste.DataCount) as DataCount
From #TempSteFil Ste
Inner Join #TmpFilCalDts Dt
On CONVERT(VARCHAR(8), Dt.DateValue, 112) = CONVERT(VARCHAR(8) , Ste.[DateTime], 112)
Inner Join vMonitor Mon
On Mon.MonitorRowId = Ste.MonitorRowId
Inner Join #TempFilterdMps Mpc
On Mpc.ManagementPackRowId = Mon.ManagementPackRowId
Group By Mpc.ManagementPackRowId, Dt.DateValue
IF OBJECT_ID('tempdb..#TempSteFil') IS NOT NULL
BEGIN
drop table #TempSteFil
END
END
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Calculate Total Volume
Select @TotVol = IsNull(Sum(DataCount),0) From #TmpDataCounts
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Calculate SumX
Insert into #TempDays
Select ROW_NUMBER() OVER(ORDER BY DateValue) AS DayNumber,
DateValue as Date
From #TmpFilCalDts (NoLock) Dt
Select @SumX = SUM(DayNumber) from #TempDays
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Calculate SumX2
Select @SumX2 = Sum(x0.DayNumber*x1.DayNumber)
From #TempDays x0
Inner Join #TempDays x1
On x0.DayNumber = x1.DayNumber
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Calculate Top Values for Equation by Mp
Insert into #TempTopCal
Select Tot.ManagementPackRowId,
@DaysNumber as CntDys,
Sum(Tot.SXY) as SumXY,
Sum(Tot.DataCount) as SumY,
@SumX as SumX
From
(
Select CnMp.ManagementPackRowId,
CnMp.Date,
Sum(CnDy.DayNumber*CnMp.DataCount) as SXY,
Sum(CnMp.DataCount) as DataCount
From #TmpDataCounts CnMp
Inner Join #TempDays CnDy
On CnMp.Date = CnDy.Date
Group by CnMp.ManagementPackRowId, CnMp.Date
) as Tot
Group by Tot.ManagementPackRowId
Order by Tot.ManagementPackRowId
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Calculate Bottom Values for Equation by Mp
Insert into #TempBottomCal
Select ManagementPackRowId,
@DaysNumber as CntDys,
@SumX2 as SumX2,
@SumX * @SumX as SumXxSumX
From #TmpDataCounts CnMp
Group by CnMp.ManagementPackRowId
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Calculate Final Result of Equation by Mp
Insert into #TempCalByMp
Select Tp.ManagementPackRowId,
((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 #TempTopCal Tp
Inner Join #TempBottomCal Bt
On Tp.ManagementPackRowId = Bt.ManagementPackRowId
Order By Tp.ManagementPackRowId
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Final Results
Select Top (@TopN)
ROW_NUMBER() OVER(ORDER BY Cnts.PrcVol Desc) AS 'RowNumber',
Cnts.ManagementPackRowId,
Cnts.ManagementPackSystemName,
Cnts.PrcVol,
IsNull(Cl.Slope,0) as Slope,
Cnts.DiscoveryCount as DiscoveryData,
Cnts.AlertCount,
Cnts.PerfCount,
Cnts.EventCount,
Cnts.StateCount as StateChanges
From
(
Select Mp.ManagementPackRowId,
IsNull(Loc.Name, Mp.ManagementPackDefaultName) as ManagementPackSystemName,
Case
When @TotVol = 0 Then 0
Else ( ( CAST( ( IsNull(DsDc.DataCount,0) + IsNull(AlDc.DataCount,0) + IsNull(PfDc.DataCount,0) + IsNull(EvDc.DataCount,0) + IsNull(StDc.DataCount,0) ) AS decimal)/@TotVol ) * 100 )
End AS PrcVol,
IsNull(DsDc.DataCount,0) as DiscoveryCount,
IsNull(AlDc.DataCount,0) as AlertCount,
IsNull(PfDc.DataCount,0) as PerfCount,
IsNull(EvDc.DataCount,0) as EventCount,
IsNull(StDc.DataCount,0) as StateCount
From #TmpMP (NoLock) Mp
Left Outer Join vDisplayString (NoLock) Loc
On Mp.ManagementPackVersionIndependentGuid = Loc.ElementGuid
And Loc.LanguageCode = @LanguageCode
Left Outer Join
(
Select ManagementPackRowId,
Sum(DataCount) as DataCount
From #TmpDataCounts
Where DataType = 1
Group By ManagementPackRowId, DataType
) As DsDc
On Mp.ManagementPackRowId = DsDc.ManagementPackRowId
Left Outer Join
(
Select ManagementPackRowId,
Sum(DataCount) as DataCount
From #TmpDataCounts
Where DataType = 2
Group By ManagementPackRowId, DataType
) As AlDc
On Mp.ManagementPackRowId = AlDc.ManagementPackRowId
Left Outer Join
(
Select ManagementPackRowId,
Sum(DataCount) as DataCount
From #TmpDataCounts
Where DataType = 3
Group By ManagementPackRowId, DataType
) As PfDc
On Mp.ManagementPackRowId = PfDc.ManagementPackRowId
Left Outer Join
(
Select ManagementPackRowId,
Sum(DataCount) as DataCount
From #TmpDataCounts
Where DataType = 4
Group By ManagementPackRowId, DataType
) As EvDc
On Mp.ManagementPackRowId = EvDc.ManagementPackRowId
Left Outer Join
(
Select ManagementPackRowId,
Sum(DataCount) as DataCount
From #TmpDataCounts
Where DataType = 5
Group By ManagementPackRowId, DataType
) As StDc
On Mp.ManagementPackRowId = StDc.ManagementPackRowId
) As Cnts
Left Outer Join #TempCalByMp Cl
On Cl.ManagementPackRowId = Cnts.ManagementPackRowId
Order by Cnts.PrcVol Desc
END
QuitError:
IF OBJECT_ID('tempdb..#TmpOpts') IS NOT NULL
BEGIN
drop table #TmpOpts
END
IF OBJECT_ID('tempdb..#TmpMP') IS NOT NULL
BEGIN
drop table #TmpMP
END
IF OBJECT_ID('tempdb..#TmpMGP') IS NOT NULL
BEGIN
drop table #TmpMGP
END
IF OBJECT_ID('tempdb..#TempFilterdMps') IS NOT NULL
BEGIN
drop table #TempFilterdMps
END
IF OBJECT_ID('tempdb..#TmpDataCounts') IS NOT NULL
BEGIN
drop table #TmpDataCounts
END
IF OBJECT_ID('tempdb..#TempDays') IS NOT NULL
BEGIN
drop table #TempDays
END
IF OBJECT_ID('tempdb..#TempTopCal') IS NOT NULL
BEGIN
drop table #TempTopCal
END
IF OBJECT_ID('tempdb..#TempBottomCal') IS NOT NULL
BEGIN
drop table #TempBottomCal
END
IF OBJECT_ID('tempdb..#TempCalByMp') IS NOT NULL
BEGIN
drop table #TempCalByMp
END
IF OBJECT_ID('tempdb..#TmpFilCalDts') IS NOT NULL
BEGIN
drop table #TmpFilCalDts
END
RETURN @Error
END
GO
GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_DataWarehouse_Report_DataVolumenDataGet] TO OpsMgrReader
GO
Home
