<DataWarehouseScript ID="Microsoft.SystemCenter.OperationsManager.Reports.2007.Script.DataVolumesTotalsByDateDataGet" Accessibility="Internal"> <Install>
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_Report_DataVolumenTotalsByDateDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_DataVolumenTotalsByDateDataGet] AS RETURN 1')
END
GO
Create Table #TempTopNMps
(
ManagementPackRowId int
)
Create Table #TempTopNWrks
(
WorkFlowId int
)
If @TopN <= 0
BEGIN
Select DataType,
Date,
CountByDate,
1 as ShowDataPoint
From #TmpCountsByDate
END
ELSE
BEGIN
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Options to Use
Insert into #TmpOps
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 #TmpOps where OptionId = 1
Select @ShowAlerts = Case
When Count(OptionId) > 0 Then 1
ELSE 0
END
From #TmpOps where OptionId = 2
Select @ShowPerfs = Case
When Count(OptionId) > 0 Then 1
ELSE 0
END
From #TmpOps where OptionId = 3
Select @ShowEvents = Case
When Count(OptionId) > 0 Then 1
ELSE 0
END
From #TmpOps where OptionId = 4
Select @ShowStates = Case
When Count(OptionId) > 0 Then 1
ELSE 0
END
From #TmpOps where OptionId = 5
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Get Mp To Filter
Insert Into #TmpMPs
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 #TmpMGPs
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 #TempFilterdDtMps
Select distinct Mpc.ManagementPackRowId
From #TmpMPs Mpc
Inner Join vManagementPackVersion Mpv
On Mpv.ManagementPackRowId = Mpc.ManagementPackRowId
Inner Join vManagementGroupManagementPackVersion Mgmpv
On Mgmpv.ManagementPackVersionRowId = Mpv.ManagementPackVersionRowId
Inner Join #TmpMGPs (NoLock) Mgp
On Mgp.ManagementGroupRowId = Mgmpv.ManagementGroupRowId
If @GroupingType = '2'
BEGIN
Insert into #TempWeeksToAdj
Select Dy.[Year],
Dy.WeekOfYear,
Count(Dy.WeekOfYear) as CntDysByWeek,
(7-Count(Dy.WeekOfYear)) as DaysToAdd
From vDate Dy
Inner Join
(
Select DC.[Year],
DC.WeekOfYear
From vDate DC
Where CONVERT(VARCHAR(8), DC.DateValue, 112) BETWEEN CONVERT(VARCHAR(8) , @StartDate, 112) AND CONVERT(VARCHAR(8) , @EndDate, 112)
Group By DC.[Year], DC.WeekOfYear
) As DC
On DC.[Year] = Dy.[Year]
And DC.[WeekOfYear] = Dy.[WeekOfYear]
Group By Dy.[Year], Dy.WeekOfYear
Having Count(Dy.WeekOfYear) < 7
Order By Dy.[Year], Dy.WeekOfYear
Select @RawWeeksToAdjust = Count(*) from #TempWeeksToAdj
END
ELSE
BEGIN
Select @RawWeeksToAdjust = 0
END
--Calculate Time Zone Dates according to grouping Type
Insert into #TempCalDates
Select DtCl.DateValue,
DtCl.FromDateValue,
DtCl.ToDateValue
From
(
Select Dt.[Year],
Dt.Quarter,
Dt.[Month],
Dt.WeekOfYear,
Dt.DayOfWeek,
Dt.DayOfMonth,
TFT.FromDateValue As DateValue,
DyWkCnts.CntDysByWeek,
LWY.LastWeek,
TFT.FromDateValue,
Case
When @GroupingType = '2' And WTA.[Year] = Dt.[Year] And Dt.[WeekOfYear] = WTA.[WeekOfYear] Then DATEADD(DAY, WTA.DaysToAdd ,TFT.ToDateValue)
Else TFT.ToDateValue
End As ToDateValue,
Case
When @GroupingType = '1' Then RANK() OVER(PARTITION BY Dt.[Year], Dt.DateValue ORDER BY Dt.[Year], Dt.DateValue)
When @GroupingType = '2' Then RANK() OVER(PARTITION BY Dt.[Year], Dt.WeekOfYear ORDER BY Dt.[Year], Dt.WeekOfYear, Dt.DayOfWeek, Dt.DateValue)
When @GroupingType = '3' Then RANK() OVER(PARTITION BY Dt.[Year], Dt.[Month] ORDER BY Dt.[Year], Dt.[Month], Dt.DayOfMonth, Dt.DateValue)
When @GroupingType = '4' Then RANK() OVER(PARTITION BY Dt.[Year], Dt.Quarter ORDER BY Dt.[Year], Dt.Quarter, Dt.DayOfMonth, Dt.DateValue)
When @GroupingType = '5' Then RANK() OVER(PARTITION BY Dt.[Year] ORDER BY Dt.[Year], Dt.DayOfMonth, Dt.DateValue)
End AS 'RowNumber',
IsNull(WDA.DelWeek,'0') As DelWeek
From vDate Dt
Inner Join
(
Select Dy.[Year],
Dy.WeekOfYear,
Count(Dy.WeekOfYear) as CntDysByWeek
From vDate Dy
Where CONVERT(VARCHAR(8), Dy.DateValue, 112) BETWEEN CONVERT(VARCHAR(8) , @StartDate, 112) AND CONVERT(VARCHAR(8) , @EndDate, 112)
Group By Dy.[Year], Dy.WeekOfYear
) As DyWkCnts
On Dt.[Year] = DyWkCnts.[Year]
And Dt.WeekOfYear = DyWkCnts.WeekOfYear
Inner Join
(
Select Dx.[Year],
Case
When @GroupingType = '1' Then Dx.DateValue
When @GroupingType = '2' Then Dx.WeekOfYear
When @GroupingType = '3' Then Dx.[Month]
When @GroupingType = '4' Then Dx.Quarter
When @GroupingType = '5' Then Dx.[Year]
End As GroupId,
Min(Dx.DateValue) as FromDateValue,
Max(Dx.DateValue) as ToDateValue
From vDate Dx
Where CONVERT(VARCHAR(8), Dx.DateValue, 112) BETWEEN CONVERT(VARCHAR(8) , @StartDate, 112) AND CONVERT(VARCHAR(8) , @EndDate, 112)
Group By Dx.[Year],
Case
When @GroupingType = '1' Then Dx.DateValue
When @GroupingType = '2' Then Dx.WeekOfYear
When @GroupingType = '3' Then Dx.[Month]
When @GroupingType = '4' Then Dx.Quarter
When @GroupingType = '5' Then Dx.[Year]
End
) As TFT
On Dt.[Year] = TFT.[Year]
And (
(@GroupingType = '1' And Dt.DateValue = TFT.GroupId ) Or
(@GroupingType = '2' And Dt.WeekOfYear = TFT.GroupId ) Or
(@GroupingType = '3' And Dt.[Month] = TFT.GroupId ) Or
(@GroupingType = '4' And Dt.Quarter = TFT.GroupId ) Or
(@GroupingType = '5' And Dt.[Year] = TFT.GroupId )
)
Inner Join
(
Select Dt.[Year],
Max(Dt.WeekOfYear) as LastWeek
From vDate Dt
Where CONVERT(VARCHAR(8), DateValue, 112) Between CONVERT(VARCHAR(8), @StartDate, 112) And CONVERT(VARCHAR(8), @EndDate, 112)
Group By Dt.[Year]
) As LWY
On Dt.[Year] = LWY.[Year]
Left Outer Join
(
Select Wkd.[Year],
Wkd.[WeekOfYear],
Wkd.DaysToAdd
From #TempWeeksToAdj Wkd
Where Wkd.WeekOfYear > 1 And @RawWeeksToAdjust > 1
) As WTA
On Dt.[Year] = WTA.[Year]
And Dt.[WeekOfYear] = WTA.[WeekOfYear]
Left Outer Join
(
Select Wdl.[Year],
Wdl.[WeekOfYear] ,
'1' As DelWeek
From #TempWeeksToAdj Wdl
Where Wdl.WeekOfYear = 1
And @RawWeeksToAdjust > 1
) As WDA
On Dt.[Year] = WDA.[Year]
And Dt.[WeekOfYear] = WDA.[WeekOfYear]
Where CONVERT(VARCHAR(8), Dt.DateValue, 112) BETWEEN CONVERT(VARCHAR(8) , @StartDate, 112) AND CONVERT(VARCHAR(8) , @EndDate, 112)
Group By Dt.[Year], DyWkCnts.[Year], Dt.Quarter, Dt.[Month], Dt.WeekOfYear, DyWkCnts.WeekOfYear, Dt.DayOfMonth, Dt.DayOfWeek, Dt.DateValue, DyWkCnts.CntDysByWeek, TFT.FromDateValue, TFT.ToDateValue, LWY.LastWeek, WTA.[Year], WTA.[WeekOfYear], WTA.DaysToAdd , WDA.[Year], WDA.[WeekOfYear], WDA.DelWeek
) as DtCl
Where DtCl.RowNumber = 1
And DtCl.DelWeek = '0'
Order by DtCl.[Year],
DtCl.Quarter,
DtCl.[Month],
DtCl.WeekOfYear,
DtCl.DayOfWeek,
DtCl.DayOfMonth,
DtCl.DateValue
--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)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
If @ShowDiscs = 1
BEGIN
--Create Temp Table to Store Filtered Data
Create Table #TmpDisDtFil
(
ManagedEntityTypeRowId int,
DateValue datetime,
DataCount bigint
)
--Counts By Date of Initial Discoveries base on Managed Entities
Insert into #TmpDisDtFil
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 #TmpMGPs)
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 #TmpDisDtFil
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 #TmpMGPs)
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 #TmpDisDtFil
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 #TmpMGPs)
Where DateAdd(hh,@AdjHours,Mep.FromDateTime) BETWEEN @StartDate AND @EndDate
Group By Me.ManagedEntityTypeRowId, Mep.FromDateTime
--Discovery Count By MP
Insert into #TmpCountsByDate
Select 1 as DataType,
Mpc.ManagementPackRowId,
Mtp.ManagedEntityTypeRowId as WorkFlowId,
Dt.DateValue as [Date],
IsNull(SUM(Dis.DataCount),0) as CountByDate
From #TmpDisDtFil Dis
Inner Join vManagedEntityType Mtp
On Mtp.ManagedEntityTypeRowId = Dis.ManagedEntityTypeRowId
Inner Join #TempFilterdDtMps Mpc
On Mpc.ManagementPackRowId = Mtp.ManagementPackRowId
Right Outer Join #TmpFilCalDts Dt
On CONVERT(VARCHAR(8), Dt.DateValue, 112) = CONVERT(VARCHAR(8) , Dis.DateValue, 112)
Group By Mpc.ManagementPackRowId, Mtp.ManagedEntityTypeRowId, Dt.DateValue
IF OBJECT_ID('tempdb..#TmpDisDtFil') IS NOT NULL
BEGIN
drop table #TmpDisDtFil
END
END
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
If @ShowAlerts = 1
BEGIN
Create Table #TmpAltDtFil
(
ManagementPackRowId int,
WorkFlowId int,
DateValue datetime,
DataCount bigint
)
--Alerts Count By MP
Insert into #TmpAltDtFil
Select IsNull(Mon.ManagementPackRowId, Rul.ManagementPackRowId) as ManagementPackRowId,
COALESCE(Mon.MonitorRowId,Rul.RuleRowId) as WorkFlowId,
DateAdd(hh,@AdjHours,Alr.RaisedDateTime) as DateValue,
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 #TmpMGPs Mg
On Met.ManagementGroupRowId = Mg.ManagementGroupRowId
Inner Join #TempFilterdDtMps 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), Mon.MonitorRowId, Rul.RuleRowId, Alr.RaisedDateTime
Insert into #TmpCountsByDate
Select 2 as DataType,
Alr.ManagementPackRowId as ManagementPackRowId,
Alr.WorkFlowId,
Dt.DateValue as [Date],
IsNull(SUM(Alr.DataCount),0) as CountByDate
From #TmpFilCalDts Dt
Left Outer Join #TmpAltDtFil Alr
On CONVERT(VARCHAR(8), Dt.DateValue, 112) = CONVERT(VARCHAR(8) , Alr.DateValue, 112)
Group By Alr.ManagementPackRowId, Alr.WorkFlowId, Dt.DateValue
IF OBJECT_ID('tempdb..#TmpAltDtFil') IS NOT NULL
BEGIN
drop table #TmpAltDtFil
END
END
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Getting Filtered Perfs Data Count By Dates
Insert Into #TempPrfDtFil
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 #TmpMGPs 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 Day
Insert into #TmpCountsByDate
Select Distinct 3 as DataType,
Mpc.ManagementPackRowId,
Rul.RuleRowId as WorkFlowId,
Dt.DateValue as Date,
IsNull(SUM(Prf.SampleCount),0) as CountByDate
From #TempPrfDtFil Prf
Inner Join vPerformanceRuleInstance (NoLock) Pri
On Prf.PerformanceRuleInstanceRowId = Pri.PerformanceRuleInstanceRowId
Inner Join vRule (NoLock) Rul
ON Pri.RuleRowId = Rul.RuleRowId
Inner Join #TempFilterdDtMps (NoLock) Mpc
On Mpc.ManagementPackRowId = Rul.ManagementPackRowId
Right Outer Join #TmpFilCalDts Dt
On CONVERT(VARCHAR(8), Dt.DateValue, 112) = CONVERT(VARCHAR(8) , Prf.[DateTime], 112)
Group By Mpc.ManagementPackRowId, Rul.RuleRowId, Dt.DateValue
IF OBJECT_ID('tempdb..#TempPrfDtFil') IS NOT NULL
BEGIN
drop table #TempPrfDtFil
END
END
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Getting APM Events If Exists Table
If @APMDataExists = 1
BEGIN
Insert Into #TempEvtDtFil
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 #TempEvtDtFil
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 #TempEvtDtFil
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 #TmpMGPs 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 Date
Insert into #TmpCountsByDate
Select 4 as DataType,
Mpc.ManagementPackRowId,
Evt.RuleRowId as WorkFlowId,
Dt.DateValue as Date,
IsNull(SUM(Evt.EventCount),0) as CountByDate
From #TempEvtDtFil Evt
Inner Join vRule Rul
ON Evt.RuleRowId = Rul.RuleRowId
Inner Join #TempFilterdDtMps Mpc
On Mpc.ManagementPackRowId = Rul.ManagementPackRowId
Right Outer Join #TmpFilCalDts Dt
On CONVERT(VARCHAR(8), Dt.DateValue, 112) = CONVERT(VARCHAR(8) , Evt.[DateTime], 112)
Group By Mpc.ManagementPackRowId, Evt.RuleRowId, Dt.DateValue
IF OBJECT_ID('tempdb..#TempEvtDtFil') IS NOT NULL
BEGIN
drop table #TempEvtDtFil
END
END
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
If @ShowStates = 1
BEGIN
--Create Temp Filtered State Table
Create Table #TempSteDtFil
(
MonitorRowId int,
[DateTime] datetime,
DataCount bigint
)
--Getting Filtered State changes Count By Dates
Insert Into #TempSteDtFil
Select Mn.MonitorRowId,
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 #TmpMGPs Mg
On Met.ManagementGroupRowId = Mg.ManagementGroupRowId
Where DateAdd(hh,@AdjHours,SteCTE.DateTime) BETWEEN @StartDate AND @EndDate
Group By Mn.MonitorRowId, SteCTE.DateTime
--States Count By Date
Insert into #TmpCountsByDate
Select 5 as DataType,
Mpc.ManagementPackRowId,
Ste.MonitorRowId as WorkFlowId,
Dt.DateValue as Date,
IsNull(SUM(Ste.DataCount),0) as CountByDate
From #TempSteDtFil Ste
Inner Join vMonitor Mon
On Mon.MonitorRowId = Ste.MonitorRowId
Inner Join #TempFilterdDtMps Mpc
On Mpc.ManagementPackRowId = Mon.ManagementPackRowId
Right Outer Join #TmpFilCalDts Dt
On CONVERT(VARCHAR(8), Dt.DateValue, 112) = CONVERT(VARCHAR(8) , Ste.[DateTime], 112)
Group By Mpc.ManagementPackRowId, Ste.MonitorRowId, Dt.DateValue
IF OBJECT_ID('tempdb..#TempSteDtFil') IS NOT NULL
BEGIN
drop table #TempSteDtFil
END
END
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Calculate Total Volume
Select @TotVol = IsNull(Sum(CountByDate),0) From #TmpCountsByDate
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
IF @TopNType = '1'
BEGIN
Insert into #TempTopNMps
Select Top (@TopN) Mb.ManagementPackRowId
From
(
Select
Cnts.ManagementPackRowId,
Case
When @TotVol = 0 Then 0
Else ( ( CAST( ( IsNull(Sum(Cnts.CountByDate),0) ) AS decimal)/@TotVol ) * 100 )
End AS PrcVol
From #TmpCountsByDate Cnts
Group By Cnts.ManagementPackRowId
) As Mb
Order By Mb.PrcVol desc
Select Cnts.DataType As DataType,
Cal.DateValue As Date,
Sum(Cnts.CountByDate) As CountByDate,
1 As ShowDataPoint
From #TmpCountsByDate Cnts
Inner Join #TempCalDates Cal
On Cnts.Date Between Cal.FromDateValue and Cal.ToDateValue
Inner Join #TempTopNMps Mpv
On Mpv.ManagementPackRowId = IsNull(Cnts.ManagementPackRowId,Mpv.ManagementPackRowId)
Group By Cnts.DataType, Cal.DateValue
Order By DataType, Date asc
END
ELSE
BEGIN
Insert into #TempTopNWrks
Select Top (@TopN) Mbv.WorkFlowId
From
(
Select
Cnts.WorkFlowId,
Case
When @TotVol = 0 Then 0
Else ( ( CAST( ( IsNull(Sum(Cnts.CountByDate),0) ) AS decimal)/@TotVol ) * 100 )
End AS PrcVol
From #TmpCountsByDate Cnts
Group By Cnts.WorkFlowId
) As Mbv
Order By Mbv.PrcVol desc
Select Cnts.DataType As DataType,
Cal.DateValue As Date,
Sum(Cnts.CountByDate) As CountByDate,
1 As ShowDataPoint
From #TmpCountsByDate Cnts
Inner Join #TempCalDates Cal
On Cnts.Date Between Cal.FromDateValue and Cal.ToDateValue
Inner Join #TempTopNWrks Mpv
On Mpv.WorkFlowId = IsNull(Cnts.WorkFlowId,Mpv.WorkFlowId)
Group By Cnts.DataType, Cal.DateValue
Order By DataType, Date asc
END
END
QuitError:
IF OBJECT_ID('tempdb..#TmpOps') IS NOT NULL
BEGIN
drop table #TmpOps
END
IF OBJECT_ID('tempdb..#TmpMGPs') IS NOT NULL
BEGIN
drop table #TmpMGPs
END
IF OBJECT_ID('tempdb..#TmpMPs') IS NOT NULL
BEGIN
drop table #TmpMPs
END
IF OBJECT_ID('tempdb..#TempFilterdDtMps') IS NOT NULL
BEGIN
drop table #TempFilterdDtMps
END
IF OBJECT_ID('tempdb..#TmpCountsByDate') IS NOT NULL
BEGIN
drop table #TmpCountsByDate
END
IF OBJECT_ID('tempdb..#TempCalDates') IS NOT NULL
BEGIN
drop table #TempCalDates
END
IF OBJECT_ID('tempdb..#TmpFilCalDts') IS NOT NULL
BEGIN
drop table #TmpFilCalDts
END
IF OBJECT_ID('tempdb..#TempWeeksToAdj') IS NOT NULL
BEGIN
drop table #TempWeeksToAdj
END
IF OBJECT_ID('tempdb..#TempTopNMps') IS NOT NULL
BEGIN
drop table #TempTopNMps
END
IF OBJECT_ID('tempdb..#TempTopNWrks') IS NOT NULL
BEGIN
drop table #TempTopNWrks
END
RETURN @Error
END
GO
GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_DataWarehouse_Report_DataVolumenTotalsByDateDataGet] TO OpsMgrReader
GO
</Install> <Uninstall>
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_Report_DataVolumenTotalsByDateDataGet')
BEGIN
DROP PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_DataVolumenTotalsByDateDataGet]
END
GO
</Uninstall> <Upgrade>
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_Report_DataVolumenTotalsByDateDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_DataVolumenTotalsByDateDataGet] AS RETURN 1')
END
GO
Create Table #TempTopNMps
(
ManagementPackRowId int
)
Create Table #TempTopNWrks
(
WorkFlowId int
)
If @TopN <= 0
BEGIN
Select DataType,
Date,
CountByDate,
1 as ShowDataPoint
From #TmpCountsByDate
END
ELSE
BEGIN
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Options to Use
Insert into #TmpOps
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 #TmpOps where OptionId = 1
Select @ShowAlerts = Case
When Count(OptionId) > 0 Then 1
ELSE 0
END
From #TmpOps where OptionId = 2
Select @ShowPerfs = Case
When Count(OptionId) > 0 Then 1
ELSE 0
END
From #TmpOps where OptionId = 3
Select @ShowEvents = Case
When Count(OptionId) > 0 Then 1
ELSE 0
END
From #TmpOps where OptionId = 4
Select @ShowStates = Case
When Count(OptionId) > 0 Then 1
ELSE 0
END
From #TmpOps where OptionId = 5
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Get Mp To Filter
Insert Into #TmpMPs
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 #TmpMGPs
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 #TempFilterdDtMps
Select distinct Mpc.ManagementPackRowId
From #TmpMPs Mpc
Inner Join vManagementPackVersion Mpv
On Mpv.ManagementPackRowId = Mpc.ManagementPackRowId
Inner Join vManagementGroupManagementPackVersion Mgmpv
On Mgmpv.ManagementPackVersionRowId = Mpv.ManagementPackVersionRowId
Inner Join #TmpMGPs (NoLock) Mgp
On Mgp.ManagementGroupRowId = Mgmpv.ManagementGroupRowId
If @GroupingType = '2'
BEGIN
Insert into #TempWeeksToAdj
Select Dy.[Year],
Dy.WeekOfYear,
Count(Dy.WeekOfYear) as CntDysByWeek,
(7-Count(Dy.WeekOfYear)) as DaysToAdd
From vDate Dy
Inner Join
(
Select DC.[Year],
DC.WeekOfYear
From vDate DC
Where CONVERT(VARCHAR(8), DC.DateValue, 112) BETWEEN CONVERT(VARCHAR(8) , @StartDate, 112) AND CONVERT(VARCHAR(8) , @EndDate, 112)
Group By DC.[Year], DC.WeekOfYear
) As DC
On DC.[Year] = Dy.[Year]
And DC.[WeekOfYear] = Dy.[WeekOfYear]
Group By Dy.[Year], Dy.WeekOfYear
Having Count(Dy.WeekOfYear) < 7
Order By Dy.[Year], Dy.WeekOfYear
Select @RawWeeksToAdjust = Count(*) from #TempWeeksToAdj
END
ELSE
BEGIN
Select @RawWeeksToAdjust = 0
END
--Calculate Time Zone Dates according to grouping Type
Insert into #TempCalDates
Select DtCl.DateValue,
DtCl.FromDateValue,
DtCl.ToDateValue
From
(
Select Dt.[Year],
Dt.Quarter,
Dt.[Month],
Dt.WeekOfYear,
Dt.DayOfWeek,
Dt.DayOfMonth,
TFT.FromDateValue As DateValue,
DyWkCnts.CntDysByWeek,
LWY.LastWeek,
TFT.FromDateValue,
Case
When @GroupingType = '2' And WTA.[Year] = Dt.[Year] And Dt.[WeekOfYear] = WTA.[WeekOfYear] Then DATEADD(DAY, WTA.DaysToAdd ,TFT.ToDateValue)
Else TFT.ToDateValue
End As ToDateValue,
Case
When @GroupingType = '1' Then RANK() OVER(PARTITION BY Dt.[Year], Dt.DateValue ORDER BY Dt.[Year], Dt.DateValue)
When @GroupingType = '2' Then RANK() OVER(PARTITION BY Dt.[Year], Dt.WeekOfYear ORDER BY Dt.[Year], Dt.WeekOfYear, Dt.DayOfWeek, Dt.DateValue)
When @GroupingType = '3' Then RANK() OVER(PARTITION BY Dt.[Year], Dt.[Month] ORDER BY Dt.[Year], Dt.[Month], Dt.DayOfMonth, Dt.DateValue)
When @GroupingType = '4' Then RANK() OVER(PARTITION BY Dt.[Year], Dt.Quarter ORDER BY Dt.[Year], Dt.Quarter, Dt.DayOfMonth, Dt.DateValue)
When @GroupingType = '5' Then RANK() OVER(PARTITION BY Dt.[Year] ORDER BY Dt.[Year], Dt.DayOfMonth, Dt.DateValue)
End AS 'RowNumber',
IsNull(WDA.DelWeek,'0') As DelWeek
From vDate Dt
Inner Join
(
Select Dy.[Year],
Dy.WeekOfYear,
Count(Dy.WeekOfYear) as CntDysByWeek
From vDate Dy
Where CONVERT(VARCHAR(8), Dy.DateValue, 112) BETWEEN CONVERT(VARCHAR(8) , @StartDate, 112) AND CONVERT(VARCHAR(8) , @EndDate, 112)
Group By Dy.[Year], Dy.WeekOfYear
) As DyWkCnts
On Dt.[Year] = DyWkCnts.[Year]
And Dt.WeekOfYear = DyWkCnts.WeekOfYear
Inner Join
(
Select Dx.[Year],
Case
When @GroupingType = '1' Then Dx.DateValue
When @GroupingType = '2' Then Dx.WeekOfYear
When @GroupingType = '3' Then Dx.[Month]
When @GroupingType = '4' Then Dx.Quarter
When @GroupingType = '5' Then Dx.[Year]
End As GroupId,
Min(Dx.DateValue) as FromDateValue,
Max(Dx.DateValue) as ToDateValue
From vDate Dx
Where CONVERT(VARCHAR(8), Dx.DateValue, 112) BETWEEN CONVERT(VARCHAR(8) , @StartDate, 112) AND CONVERT(VARCHAR(8) , @EndDate, 112)
Group By Dx.[Year],
Case
When @GroupingType = '1' Then Dx.DateValue
When @GroupingType = '2' Then Dx.WeekOfYear
When @GroupingType = '3' Then Dx.[Month]
When @GroupingType = '4' Then Dx.Quarter
When @GroupingType = '5' Then Dx.[Year]
End
) As TFT
On Dt.[Year] = TFT.[Year]
And (
(@GroupingType = '1' And Dt.DateValue = TFT.GroupId ) Or
(@GroupingType = '2' And Dt.WeekOfYear = TFT.GroupId ) Or
(@GroupingType = '3' And Dt.[Month] = TFT.GroupId ) Or
(@GroupingType = '4' And Dt.Quarter = TFT.GroupId ) Or
(@GroupingType = '5' And Dt.[Year] = TFT.GroupId )
)
Inner Join
(
Select Dt.[Year],
Max(Dt.WeekOfYear) as LastWeek
From vDate Dt
Where CONVERT(VARCHAR(8), DateValue, 112) Between CONVERT(VARCHAR(8), @StartDate, 112) And CONVERT(VARCHAR(8), @EndDate, 112)
Group By Dt.[Year]
) As LWY
On Dt.[Year] = LWY.[Year]
Left Outer Join
(
Select Wkd.[Year],
Wkd.[WeekOfYear],
Wkd.DaysToAdd
From #TempWeeksToAdj Wkd
Where Wkd.WeekOfYear > 1 And @RawWeeksToAdjust > 1
) As WTA
On Dt.[Year] = WTA.[Year]
And Dt.[WeekOfYear] = WTA.[WeekOfYear]
Left Outer Join
(
Select Wdl.[Year],
Wdl.[WeekOfYear] ,
'1' As DelWeek
From #TempWeeksToAdj Wdl
Where Wdl.WeekOfYear = 1
And @RawWeeksToAdjust > 1
) As WDA
On Dt.[Year] = WDA.[Year]
And Dt.[WeekOfYear] = WDA.[WeekOfYear]
Where CONVERT(VARCHAR(8), Dt.DateValue, 112) BETWEEN CONVERT(VARCHAR(8) , @StartDate, 112) AND CONVERT(VARCHAR(8) , @EndDate, 112)
Group By Dt.[Year], DyWkCnts.[Year], Dt.Quarter, Dt.[Month], Dt.WeekOfYear, DyWkCnts.WeekOfYear, Dt.DayOfMonth, Dt.DayOfWeek, Dt.DateValue, DyWkCnts.CntDysByWeek, TFT.FromDateValue, TFT.ToDateValue, LWY.LastWeek, WTA.[Year], WTA.[WeekOfYear], WTA.DaysToAdd , WDA.[Year], WDA.[WeekOfYear], WDA.DelWeek
) as DtCl
Where DtCl.RowNumber = 1
And DtCl.DelWeek = '0'
Order by DtCl.[Year],
DtCl.Quarter,
DtCl.[Month],
DtCl.WeekOfYear,
DtCl.DayOfWeek,
DtCl.DayOfMonth,
DtCl.DateValue
--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)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
If @ShowDiscs = 1
BEGIN
--Create Temp Table to Store Filtered Data
Create Table #TmpDisDtFil
(
ManagedEntityTypeRowId int,
DateValue datetime,
DataCount bigint
)
--Counts By Date of Initial Discoveries base on Managed Entities
Insert into #TmpDisDtFil
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 #TmpMGPs)
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 #TmpDisDtFil
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 #TmpMGPs)
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 #TmpDisDtFil
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 #TmpMGPs)
Where DateAdd(hh,@AdjHours,Mep.FromDateTime) BETWEEN @StartDate AND @EndDate
Group By Me.ManagedEntityTypeRowId, Mep.FromDateTime
--Discovery Count By MP
Insert into #TmpCountsByDate
Select 1 as DataType,
Mpc.ManagementPackRowId,
Mtp.ManagedEntityTypeRowId as WorkFlowId,
Dt.DateValue as [Date],
IsNull(SUM(Dis.DataCount),0) as CountByDate
From #TmpDisDtFil Dis
Inner Join vManagedEntityType Mtp
On Mtp.ManagedEntityTypeRowId = Dis.ManagedEntityTypeRowId
Inner Join #TempFilterdDtMps Mpc
On Mpc.ManagementPackRowId = Mtp.ManagementPackRowId
Right Outer Join #TmpFilCalDts Dt
On CONVERT(VARCHAR(8), Dt.DateValue, 112) = CONVERT(VARCHAR(8) , Dis.DateValue, 112)
Group By Mpc.ManagementPackRowId, Mtp.ManagedEntityTypeRowId, Dt.DateValue
IF OBJECT_ID('tempdb..#TmpDisDtFil') IS NOT NULL
BEGIN
drop table #TmpDisDtFil
END
END
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
If @ShowAlerts = 1
BEGIN
Create Table #TmpAltDtFil
(
ManagementPackRowId int,
WorkFlowId int,
DateValue datetime,
DataCount bigint
)
--Alerts Count By MP
Insert into #TmpAltDtFil
Select IsNull(Mon.ManagementPackRowId, Rul.ManagementPackRowId) as ManagementPackRowId,
COALESCE(Mon.MonitorRowId,Rul.RuleRowId) as WorkFlowId,
DateAdd(hh,@AdjHours,Alr.RaisedDateTime) as DateValue,
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 #TmpMGPs Mg
On Met.ManagementGroupRowId = Mg.ManagementGroupRowId
Inner Join #TempFilterdDtMps 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), Mon.MonitorRowId, Rul.RuleRowId, Alr.RaisedDateTime
Insert into #TmpCountsByDate
Select 2 as DataType,
Alr.ManagementPackRowId as ManagementPackRowId,
Alr.WorkFlowId,
Dt.DateValue as [Date],
IsNull(SUM(Alr.DataCount),0) as CountByDate
From #TmpFilCalDts Dt
Left Outer Join #TmpAltDtFil Alr
On CONVERT(VARCHAR(8), Dt.DateValue, 112) = CONVERT(VARCHAR(8) , Alr.DateValue, 112)
Group By Alr.ManagementPackRowId, Alr.WorkFlowId, Dt.DateValue
IF OBJECT_ID('tempdb..#TmpAltDtFil') IS NOT NULL
BEGIN
drop table #TmpAltDtFil
END
END
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Getting Filtered Perfs Data Count By Dates
Insert Into #TempPrfDtFil
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 #TmpMGPs 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 Day
Insert into #TmpCountsByDate
Select Distinct 3 as DataType,
Mpc.ManagementPackRowId,
Rul.RuleRowId as WorkFlowId,
Dt.DateValue as Date,
IsNull(SUM(Prf.SampleCount),0) as CountByDate
From #TempPrfDtFil Prf
Inner Join vPerformanceRuleInstance (NoLock) Pri
On Prf.PerformanceRuleInstanceRowId = Pri.PerformanceRuleInstanceRowId
Inner Join vRule (NoLock) Rul
ON Pri.RuleRowId = Rul.RuleRowId
Inner Join #TempFilterdDtMps (NoLock) Mpc
On Mpc.ManagementPackRowId = Rul.ManagementPackRowId
Right Outer Join #TmpFilCalDts Dt
On CONVERT(VARCHAR(8), Dt.DateValue, 112) = CONVERT(VARCHAR(8) , Prf.[DateTime], 112)
Group By Mpc.ManagementPackRowId, Rul.RuleRowId, Dt.DateValue
IF OBJECT_ID('tempdb..#TempPrfDtFil') IS NOT NULL
BEGIN
drop table #TempPrfDtFil
END
END
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Getting APM Events If Exists Table
If @APMDataExists = 1
BEGIN
Insert Into #TempEvtDtFil
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 #TempEvtDtFil
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 #TempEvtDtFil
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 #TmpMGPs 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 Date
Insert into #TmpCountsByDate
Select 4 as DataType,
Mpc.ManagementPackRowId,
Evt.RuleRowId as WorkFlowId,
Dt.DateValue as Date,
IsNull(SUM(Evt.EventCount),0) as CountByDate
From #TempEvtDtFil Evt
Inner Join vRule Rul
ON Evt.RuleRowId = Rul.RuleRowId
Inner Join #TempFilterdDtMps Mpc
On Mpc.ManagementPackRowId = Rul.ManagementPackRowId
Right Outer Join #TmpFilCalDts Dt
On CONVERT(VARCHAR(8), Dt.DateValue, 112) = CONVERT(VARCHAR(8) , Evt.[DateTime], 112)
Group By Mpc.ManagementPackRowId, Evt.RuleRowId, Dt.DateValue
IF OBJECT_ID('tempdb..#TempEvtDtFil') IS NOT NULL
BEGIN
drop table #TempEvtDtFil
END
END
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
If @ShowStates = 1
BEGIN
--Create Temp Filtered State Table
Create Table #TempSteDtFil
(
MonitorRowId int,
[DateTime] datetime,
DataCount bigint
)
--Getting Filtered State changes Count By Dates
Insert Into #TempSteDtFil
Select Mn.MonitorRowId,
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 #TmpMGPs Mg
On Met.ManagementGroupRowId = Mg.ManagementGroupRowId
Where DateAdd(hh,@AdjHours,SteCTE.DateTime) BETWEEN @StartDate AND @EndDate
Group By Mn.MonitorRowId, SteCTE.DateTime
--States Count By Date
Insert into #TmpCountsByDate
Select 5 as DataType,
Mpc.ManagementPackRowId,
Ste.MonitorRowId as WorkFlowId,
Dt.DateValue as Date,
IsNull(SUM(Ste.DataCount),0) as CountByDate
From #TempSteDtFil Ste
Inner Join vMonitor Mon
On Mon.MonitorRowId = Ste.MonitorRowId
Inner Join #TempFilterdDtMps Mpc
On Mpc.ManagementPackRowId = Mon.ManagementPackRowId
Right Outer Join #TmpFilCalDts Dt
On CONVERT(VARCHAR(8), Dt.DateValue, 112) = CONVERT(VARCHAR(8) , Ste.[DateTime], 112)
Group By Mpc.ManagementPackRowId, Ste.MonitorRowId, Dt.DateValue
IF OBJECT_ID('tempdb..#TempSteDtFil') IS NOT NULL
BEGIN
drop table #TempSteDtFil
END
END
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Calculate Total Volume
Select @TotVol = IsNull(Sum(CountByDate),0) From #TmpCountsByDate
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
IF @TopNType = '1'
BEGIN
Insert into #TempTopNMps
Select Top (@TopN) Mb.ManagementPackRowId
From
(
Select
Cnts.ManagementPackRowId,
Case
When @TotVol = 0 Then 0
Else ( ( CAST( ( IsNull(Sum(Cnts.CountByDate),0) ) AS decimal)/@TotVol ) * 100 )
End AS PrcVol
From #TmpCountsByDate Cnts
Group By Cnts.ManagementPackRowId
) As Mb
Order By Mb.PrcVol desc
Select Cnts.DataType As DataType,
Cal.DateValue As Date,
Sum(Cnts.CountByDate) As CountByDate,
1 As ShowDataPoint
From #TmpCountsByDate Cnts
Inner Join #TempCalDates Cal
On Cnts.Date Between Cal.FromDateValue and Cal.ToDateValue
Inner Join #TempTopNMps Mpv
On Mpv.ManagementPackRowId = IsNull(Cnts.ManagementPackRowId,Mpv.ManagementPackRowId)
Group By Cnts.DataType, Cal.DateValue
Order By DataType, Date asc
END
ELSE
BEGIN
Insert into #TempTopNWrks
Select Top (@TopN) Mbv.WorkFlowId
From
(
Select
Cnts.WorkFlowId,
Case
When @TotVol = 0 Then 0
Else ( ( CAST( ( IsNull(Sum(Cnts.CountByDate),0) ) AS decimal)/@TotVol ) * 100 )
End AS PrcVol
From #TmpCountsByDate Cnts
Group By Cnts.WorkFlowId
) As Mbv
Order By Mbv.PrcVol desc
Select Cnts.DataType As DataType,
Cal.DateValue As Date,
Sum(Cnts.CountByDate) As CountByDate,
1 As ShowDataPoint
From #TmpCountsByDate Cnts
Inner Join #TempCalDates Cal
On Cnts.Date Between Cal.FromDateValue and Cal.ToDateValue
Inner Join #TempTopNWrks Mpv
On Mpv.WorkFlowId = IsNull(Cnts.WorkFlowId,Mpv.WorkFlowId)
Group By Cnts.DataType, Cal.DateValue
Order By DataType, Date asc
END
END
QuitError:
IF OBJECT_ID('tempdb..#TmpOps') IS NOT NULL
BEGIN
drop table #TmpOps
END
IF OBJECT_ID('tempdb..#TmpMGPs') IS NOT NULL
BEGIN
drop table #TmpMGPs
END
IF OBJECT_ID('tempdb..#TmpMPs') IS NOT NULL
BEGIN
drop table #TmpMPs
END
IF OBJECT_ID('tempdb..#TempFilterdDtMps') IS NOT NULL
BEGIN
drop table #TempFilterdDtMps
END
IF OBJECT_ID('tempdb..#TmpCountsByDate') IS NOT NULL
BEGIN
drop table #TmpCountsByDate
END
IF OBJECT_ID('tempdb..#TempCalDates') IS NOT NULL
BEGIN
drop table #TempCalDates
END
IF OBJECT_ID('tempdb..#TmpFilCalDts') IS NOT NULL
BEGIN
drop table #TmpFilCalDts
END
IF OBJECT_ID('tempdb..#TempWeeksToAdj') IS NOT NULL
BEGIN
drop table #TempWeeksToAdj
END
IF OBJECT_ID('tempdb..#TempTopNMps') IS NOT NULL
BEGIN
drop table #TempTopNMps
END
IF OBJECT_ID('tempdb..#TempTopNWrks') IS NOT NULL
BEGIN
drop table #TempTopNWrks
END
RETURN @Error
END
GO
GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_DataWarehouse_Report_DataVolumenTotalsByDateDataGet] TO OpsMgrReader
GO
Home
