<DataWarehouseScript ID="Microsoft.SystemCenter.OperationsManager.Reports.2007.Script.DataVolumesDetailsByWorkflowDataGet" Accessibility="Internal"> <Install>
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_Report_DataVolumenDetailsByWrkDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_DataVolumenDetailsByWrkDataGet] AS RETURN 1')
END
GO
Create Table #TempTopNMpsWrk
(
ManagementPackRowId int
)
If @TopN <= 0
BEGIN
Select 0 As RowNumber,
WorkFlowId,
WorkflowDisplay,
0.00 As PrcVol,
0.00 As Slope,
0.00 As CountByWorkflow
From #TmpDataCountsWrk
END
ELSE
BEGIN
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Options to Use
Insert into #TmpOpsWrk
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 #TmpOpsWrk where OptionId = 1
Select @ShowAlerts = Case
When Count(OptionId) > 0 Then 1
ELSE 0
END
From #TmpOpsWrk where OptionId = 2
Select @ShowPerfs = Case
When Count(OptionId) > 0 Then 1
ELSE 0
END
From #TmpOpsWrk where OptionId = 3
Select @ShowEvents = Case
When Count(OptionId) > 0 Then 1
ELSE 0
END
From #TmpOpsWrk where OptionId = 4
Select @ShowStates = Case
When Count(OptionId) > 0 Then 1
ELSE 0
END
From #TmpOpsWrk where OptionId = 5
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Get Mp To Filter
Insert Into #TmpMPsWk
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 #TmpMGPWk
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 #TempFilterdMpsWrk
Select distinct Mpc.ManagementPackRowId
From #TmpMPsWk Mpc
Inner Join vManagementPackVersion Mpv
On Mpv.ManagementPackRowId = Mpc.ManagementPackRowId
Inner Join vManagementGroupManagementPackVersion Mgmpv
On Mgmpv.ManagementPackVersionRowId = Mpv.ManagementPackVersionRowId
Inner Join #TmpMGPWk (NoLock) Mgp
On Mgp.ManagementGroupRowId = Mgmpv.ManagementGroupRowId
--Filtered Dates from Calendar
Insert into #TmpFilCalDtsWrk
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 #TmpDisFilWrk
(
ManagedEntityTypeRowId int,
DateValue datetime,
DataCount bigint
)
--Counts By Date of Initial Discoveries base on Managed Entities
Insert into #TmpDisFilWrk
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 #TmpMGPWk)
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 #TmpDisFilWrk
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 #TmpMGPWk)
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 #TmpDisFilWrk
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 #TmpMGPWk)
Where DateAdd(hh,@AdjHours,Mep.FromDateTime) BETWEEN @StartDate AND @EndDate
Group By Me.ManagedEntityTypeRowId, Mep.FromDateTime
--Discovery Count By MP
Insert into #TmpDataCountsWrk
Select 1 as DataType,
1 as [Type],
Mpc.ManagementPackRowId,
Mtp.ManagedEntityTypeRowId as WorkFlowId,
COALESCE(Loc.Name,Mtp.ManagedEntityTypeDefaultName) as WorkFlowDisplay,
Dt.DateValue as [Date],
IsNull(SUM(Dis.DataCount),0) as DataCount
From #TmpDisFilWrk Dis
Inner Join #TmpFilCalDtsWrk 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 #TempFilterdMpsWrk Mpc
On Mpc.ManagementPackRowId = Mtp.ManagementPackRowId
Left Outer Join vDisplayString Loc
On Loc.ElementGuid = Mtp.ManagedEntityTypeGuid
And Loc.LanguageCode = @LanguageCode
Group By Mpc.ManagementPackRowId, Mtp.ManagedEntityTypeRowId, Loc.Name, Mtp.ManagedEntityTypeDefaultName, Dt.DateValue
IF OBJECT_ID('tempdb..#TmpDisFilWrk') IS NOT NULL
BEGIN
drop table #TmpDisFilWrk
END
END
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
If @ShowAlerts = 1
BEGIN
--Alerts Count by WorkFlow
Insert into #TmpDataCountsWrk
Select 2 as DataType,
Case
When Rul.RuleRowId is not null Then 2
When Mon.MonitorRowId is not null Then 3
End as [Type],
Mpc.ManagementPackRowId,
COALESCE(Mon.MonitorRowId,Rul.RuleRowId) as WorkFlowId,
COALESCE(Loc.Name,COALESCE(Mon.MonitorDefaultName,Rul.RuleDefaultName)) as WorkFlowDisplay,
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 #TmpMGPWk Mg
On Met.ManagementGroupRowId = Mg.ManagementGroupRowId
Inner Join #TmpFilCalDtsWrk Dt
On CONVERT(VARCHAR(8), Dt.DateValue, 112) = CONVERT(VARCHAR(8) , DateAdd(hh,@AdjHours,Alr.RaisedDateTime), 112)
Inner Join #TempFilterdMpsWrk Mpc
On Mon.ManagementPackRowId = Mpc.ManagementPackRowId
Or Rul.ManagementPackRowId = Mpc.ManagementPackRowId
Left Outer Join vDisplayString (NoLock) Loc
On Loc.ElementGuid = COALESCE(Mon.MonitorGuid, Rul.RuleGuid)
And Loc.LanguageCode = @LanguageCode
where DateAdd(hh,@AdjHours,Alr.RaisedDateTime) BETWEEN @StartDate AND @EndDate
Group By Mpc.ManagementPackRowId, Mon.MonitorRowId, Rul.RuleRowId, Mon.MonitorDefaultName, Rul.RuleDefaultName, Loc.Name, Dt.DateValue
END
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Getting Filtered Perfs Data Count By Dates
Insert Into #TempPrfDtFilWrk
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 #TmpMGPWk 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 WorkFlow
Insert into #TmpDataCountsWrk
Select Distinct 3 as DataType,
2 as [Type],
Mpc.ManagementPackRowId,
Rul.RuleRowId as WorkFlowId,
COALESCE(Loc.Name,Rul.RuleDefaultName) as WorkFlowDisplay,
Dt.DateValue as Date,
IsNull(SUM(Prf.SampleCount),0) as CountByDate
From #TempPrfDtFilWrk Prf
Inner Join vPerformanceRuleInstance (NoLock) Pri
On Prf.PerformanceRuleInstanceRowId = Pri.PerformanceRuleInstanceRowId
Inner Join vRule (NoLock) Rul
ON Pri.RuleRowId = Rul.RuleRowId
Inner Join #TmpFilCalDtsWrk Dt
On CONVERT(VARCHAR(8), Dt.DateValue, 112) = CONVERT(VARCHAR(8) , Prf.[DateTime], 112)
Inner Join #TempFilterdMpsWrk (NoLock) Mpc
On Mpc.ManagementPackRowId = Rul.ManagementPackRowId
Left Outer Join vDisplayString (NoLock) Loc
On Loc.ElementGuid = Rul.RuleGuid
And Loc.LanguageCode = @LanguageCode
Group By Mpc.ManagementPackRowId, Rul.RuleRowId, Loc.Name, Rul.RuleDefaultName, Dt.DateValue
IF OBJECT_ID('tempdb..#TempPrfDtFilWrk') IS NOT NULL
BEGIN
drop table #TempPrfDtFilWrk
END
END
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Getting APM Events If Exists Table
If @APMDataExists = 1
BEGIN
Insert Into #TempEvtDtFilWrk
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 #TempEvtDtFilWrk
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 #TempEvtDtFilWrk
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 #TmpMGPWk Mg
On Met.ManagementGroupRowId = Mg.ManagementGroupRowId
Where DateAdd(hh,@AdjHours,EvtCTE.DateTime) BETWEEN @StartDate AND @EndDate
Group By ErlCTE.RuleRowId, EvtCTE.DateTime
Insert into #TmpDataCountsWrk
Select 4 as DataType,
2 as [Type],
Mpc.ManagementPackRowId,
Evt.RuleRowId as WorkFlowId,
COALESCE(Loc.Name,Rul.RuleSystemName) as WorkFlowDisplay,
Dt.DateValue as Date,
IsNull(SUM(Evt.EventCount),0) as CountByDate
From #TempEvtDtFilWrk Evt
Inner Join vRule Rul
ON Evt.RuleRowId = Rul.RuleRowId
Inner Join #TmpFilCalDtsWrk Dt
On CONVERT(VARCHAR(8), Dt.DateValue, 112) = CONVERT(VARCHAR(8) , Evt.[DateTime], 112)
Inner Join #TempFilterdMpsWrk Mpc
On Mpc.ManagementPackRowId = Rul.ManagementPackRowId
Left Outer Join vDisplayString (NoLock) Loc
On Loc.ElementGuid = Rul.RuleGuid
And Loc.LanguageCode = @LanguageCode
Group By Mpc.ManagementPackRowId, Evt.RuleRowId, Loc.Name, Rul.RuleSystemName, Dt.DateValue
IF OBJECT_ID('tempdb..#TempEvtDtFilWrk') IS NOT NULL
BEGIN
drop table #TempEvtDtFilWrk
END
END
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
If @ShowStates = 1
BEGIN
--Create Temp Filtered State Table
Create Table #TempSteDtFilWrk
(
MonitorRowId int,
[DateTime] datetime,
DataCount bigint
)
--Getting Filtered State changes Count By Dates
Insert Into #TempSteDtFilWrk
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 #TmpMGPWk 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 WorkFlow
Insert into #TmpDataCountsWrk
Select 5 as DataType,
3 as [Type],
Mpc.ManagementPackRowId,
Ste.MonitorRowId as WorkFlowId,
COALESCE(Loc.Name,Mon.MonitorDefaultName) as WorkFlowDisplay,
Dt.DateValue as Date,
IsNull(SUM(Ste.DataCount),0) as CountByDate
From #TempSteDtFilWrk Ste
Inner Join vMonitor Mon
On Mon.MonitorRowId = Ste.MonitorRowId
Inner Join #TmpFilCalDtsWrk Dt
On CONVERT(VARCHAR(8), Dt.DateValue, 112) = CONVERT(VARCHAR(8) , Ste.[DateTime], 112)
Inner Join #TempFilterdMpsWrk Mpc
On Mpc.ManagementPackRowId = Mon.ManagementPackRowId
Left Outer Join vDisplayString (NoLock) Loc
On Loc.ElementGuid = Mon.MonitorGuid
And Loc.LanguageCode = @LanguageCode
Group By Mpc.ManagementPackRowId, Ste.MonitorRowId, Loc.Name, Mon.MonitorDefaultName, 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(DataCount),0) From #TmpDataCountsWrk
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Calculate SumX
Insert into #TempDaysWrk
Select ROW_NUMBER() OVER(ORDER BY DateValue) AS DayNumber,
DateValue as Date
From #TmpFilCalDtsWrk (NoLock) Dt
Select @SumX = SUM(DayNumber) from #TempDaysWrk
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Calculate SumX2
Select @SumX2 = Sum(x0.DayNumber*x1.DayNumber)
From #TempDaysWrk x0
Inner Join #TempDaysWrk x1
On x0.DayNumber = x1.DayNumber
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Calculate Top Values for Equation by WorkFlow
Insert into #TempTopCalWrk
Select Tot.WorkFlowId,
@DaysNumber as CntDys,
Sum(Tot.SXY) as SumXY,
Sum(Tot.DataCount) as SumY,
@SumX as SumX
From
(
Select CnWk.WorkFlowId,
CnWk.Date,
Sum(CnDy.DayNumber*CnWk.DataCount) as SXY,
Sum(CnWk.DataCount) as DataCount
From #TmpDataCountsWrk CnWk
Inner Join #TempDaysWrk CnDy
On CnWk.Date = CnDy.Date
Group by CnWk.WorkFlowId, CnWk.Date
) as Tot
Group by Tot.WorkFlowId
Order by Tot.WorkFlowId
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Calculate Bottom Values for Equation by WorkFlow
Insert into #TempBottomCalWrk
Select WorkFlowId,
@DaysNumber as CntDys,
@SumX2 as SumX2,
@SumX * @SumX as SumXxSumX
From #TmpDataCountsWrk CnWk
Group by CnWk.WorkFlowId
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Calculate Final Result of Equation by WorkFlow
Insert into #TempCalByWrk
Select Tp.WorkFlowId,
((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 #TempTopCalWrk Tp
Inner Join #TempBottomCalWrk Bt
On Tp.WorkFlowId = Bt.WorkFlowId
Order By Tp.WorkFlowId
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
Insert into #TempTopNMpsWrk
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 #TempFilterdMpsWrk Mpc
Left Outer Join #TmpDataCountsWrk 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.[Type],
Cnts.WorkFlowId,
Cnts.WorkFlowDisplay,
Cnts.PrcVol,
IsNull(Cl.Slope,0) as Slope,
Cnts.CountByWorkflow
From
(
Select
[Type],
WorkFlowId,
WorkFlowDisplay,
Case
When @TotVol = 0 Then 0
Else ((CAST(Sum(IsNull(DataCount,0)) AS decimal)/@TotVol)*100)
End AS PrcVol,
Sum(IsNull(DataCount,0)) as CountByWorkflow
From #TmpDataCountsWrk CntWk
Inner Join #TempTopNMpsWrk Mpv
On Mpv.ManagementPackRowId = IsNull(CntWk.ManagementPackRowId,Mpv.ManagementPackRowId)
Group By
[Type],
WorkFlowId,
WorkFlowDisplay
) As Cnts
Left Outer Join #TempCalByWrk Cl
On Cl.WorkFlowId = Cnts.WorkFlowId
Order by Cnts.PrcVol Desc
END
QuitError:
IF OBJECT_ID('tempdb..#TmpOpsWrk') IS NOT NULL
BEGIN
Drop Table #TmpOpsWrk
END
IF OBJECT_ID('tempdb..#TmpMPsWk') IS NOT NULL
BEGIN
Drop Table #TmpMPsWk
END
IF OBJECT_ID('tempdb..#TmpMGPWk') IS NOT NULL
BEGIN
Drop Table #TmpMGPWk
END
IF OBJECT_ID('tempdb..#TempFilterdMpsWrk') IS NOT NULL
BEGIN
Drop Table #TempFilterdMpsWrk
END
IF OBJECT_ID('tempdb..#TmpDataCountsWrk') IS NOT NULL
BEGIN
Drop Table #TmpDataCountsWrk
END
IF OBJECT_ID('tempdb..#TempDaysWrk') IS NOT NULL
BEGIN
Drop Table #TempDaysWrk
END
IF OBJECT_ID('tempdb..#TempTopCalWrk') IS NOT NULL
BEGIN
Drop Table #TempTopCalWrk
END
IF OBJECT_ID('tempdb..#TempBottomCalWrk') IS NOT NULL
BEGIN
Drop Table #TempBottomCalWrk
END
IF OBJECT_ID('tempdb..#TempCalByWrk') IS NOT NULL
BEGIN
Drop Table #TempCalByWrk
END
IF OBJECT_ID('tempdb..#TmpFilCalDtsWrk') IS NOT NULL
BEGIN
Drop Table #TmpFilCalDtsWrk
END
IF OBJECT_ID('tempdb..#TempTopNMpsWrk') IS NOT NULL
BEGIN
drop table #TempTopNMpsWrk
END
RETURN @Error
END
GO
GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_DataWarehouse_Report_DataVolumenDetailsByWrkDataGet] TO OpsMgrReader
GO
</Install> <Uninstall>
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_Report_DataVolumenDetailsByWrkDataGet')
BEGIN
DROP PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_DataVolumenDetailsByWrkDataGet]
END
GO
</Uninstall> <Upgrade>
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_Report_DataVolumenDetailsByWrkDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_DataVolumenDetailsByWrkDataGet] AS RETURN 1')
END
GO
Create Table #TempTopNMpsWrk
(
ManagementPackRowId int
)
If @TopN <= 0
BEGIN
Select 0 As RowNumber,
WorkFlowId,
WorkflowDisplay,
0.00 As PrcVol,
0.00 As Slope,
0.00 As CountByWorkflow
From #TmpDataCountsWrk
END
ELSE
BEGIN
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Options to Use
Insert into #TmpOpsWrk
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 #TmpOpsWrk where OptionId = 1
Select @ShowAlerts = Case
When Count(OptionId) > 0 Then 1
ELSE 0
END
From #TmpOpsWrk where OptionId = 2
Select @ShowPerfs = Case
When Count(OptionId) > 0 Then 1
ELSE 0
END
From #TmpOpsWrk where OptionId = 3
Select @ShowEvents = Case
When Count(OptionId) > 0 Then 1
ELSE 0
END
From #TmpOpsWrk where OptionId = 4
Select @ShowStates = Case
When Count(OptionId) > 0 Then 1
ELSE 0
END
From #TmpOpsWrk where OptionId = 5
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Get Mp To Filter
Insert Into #TmpMPsWk
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 #TmpMGPWk
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 #TempFilterdMpsWrk
Select distinct Mpc.ManagementPackRowId
From #TmpMPsWk Mpc
Inner Join vManagementPackVersion Mpv
On Mpv.ManagementPackRowId = Mpc.ManagementPackRowId
Inner Join vManagementGroupManagementPackVersion Mgmpv
On Mgmpv.ManagementPackVersionRowId = Mpv.ManagementPackVersionRowId
Inner Join #TmpMGPWk (NoLock) Mgp
On Mgp.ManagementGroupRowId = Mgmpv.ManagementGroupRowId
--Filtered Dates from Calendar
Insert into #TmpFilCalDtsWrk
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 #TmpDisFilWrk
(
ManagedEntityTypeRowId int,
DateValue datetime,
DataCount bigint
)
--Counts By Date of Initial Discoveries base on Managed Entities
Insert into #TmpDisFilWrk
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 #TmpMGPWk)
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 #TmpDisFilWrk
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 #TmpMGPWk)
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 #TmpDisFilWrk
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 #TmpMGPWk)
Where DateAdd(hh,@AdjHours,Mep.FromDateTime) BETWEEN @StartDate AND @EndDate
Group By Me.ManagedEntityTypeRowId, Mep.FromDateTime
--Discovery Count By MP
Insert into #TmpDataCountsWrk
Select 1 as DataType,
1 as [Type],
Mpc.ManagementPackRowId,
Mtp.ManagedEntityTypeRowId as WorkFlowId,
COALESCE(Loc.Name,Mtp.ManagedEntityTypeDefaultName) as WorkFlowDisplay,
Dt.DateValue as [Date],
IsNull(SUM(Dis.DataCount),0) as DataCount
From #TmpDisFilWrk Dis
Inner Join #TmpFilCalDtsWrk 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 #TempFilterdMpsWrk Mpc
On Mpc.ManagementPackRowId = Mtp.ManagementPackRowId
Left Outer Join vDisplayString Loc
On Loc.ElementGuid = Mtp.ManagedEntityTypeGuid
And Loc.LanguageCode = @LanguageCode
Group By Mpc.ManagementPackRowId, Mtp.ManagedEntityTypeRowId, Loc.Name, Mtp.ManagedEntityTypeDefaultName, Dt.DateValue
IF OBJECT_ID('tempdb..#TmpDisFilWrk') IS NOT NULL
BEGIN
drop table #TmpDisFilWrk
END
END
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
If @ShowAlerts = 1
BEGIN
--Alerts Count by WorkFlow
Insert into #TmpDataCountsWrk
Select 2 as DataType,
Case
When Rul.RuleRowId is not null Then 2
When Mon.MonitorRowId is not null Then 3
End as [Type],
Mpc.ManagementPackRowId,
COALESCE(Mon.MonitorRowId,Rul.RuleRowId) as WorkFlowId,
COALESCE(Loc.Name,COALESCE(Mon.MonitorDefaultName,Rul.RuleDefaultName)) as WorkFlowDisplay,
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 #TmpMGPWk Mg
On Met.ManagementGroupRowId = Mg.ManagementGroupRowId
Inner Join #TmpFilCalDtsWrk Dt
On CONVERT(VARCHAR(8), Dt.DateValue, 112) = CONVERT(VARCHAR(8) , DateAdd(hh,@AdjHours,Alr.RaisedDateTime), 112)
Inner Join #TempFilterdMpsWrk Mpc
On Mon.ManagementPackRowId = Mpc.ManagementPackRowId
Or Rul.ManagementPackRowId = Mpc.ManagementPackRowId
Left Outer Join vDisplayString (NoLock) Loc
On Loc.ElementGuid = COALESCE(Mon.MonitorGuid, Rul.RuleGuid)
And Loc.LanguageCode = @LanguageCode
where DateAdd(hh,@AdjHours,Alr.RaisedDateTime) BETWEEN @StartDate AND @EndDate
Group By Mpc.ManagementPackRowId, Mon.MonitorRowId, Rul.RuleRowId, Mon.MonitorDefaultName, Rul.RuleDefaultName, Loc.Name, Dt.DateValue
END
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Getting Filtered Perfs Data Count By Dates
Insert Into #TempPrfDtFilWrk
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 #TmpMGPWk 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 WorkFlow
Insert into #TmpDataCountsWrk
Select Distinct 3 as DataType,
2 as [Type],
Mpc.ManagementPackRowId,
Rul.RuleRowId as WorkFlowId,
COALESCE(Loc.Name,Rul.RuleDefaultName) as WorkFlowDisplay,
Dt.DateValue as Date,
IsNull(SUM(Prf.SampleCount),0) as CountByDate
From #TempPrfDtFilWrk Prf
Inner Join vPerformanceRuleInstance (NoLock) Pri
On Prf.PerformanceRuleInstanceRowId = Pri.PerformanceRuleInstanceRowId
Inner Join vRule (NoLock) Rul
ON Pri.RuleRowId = Rul.RuleRowId
Inner Join #TmpFilCalDtsWrk Dt
On CONVERT(VARCHAR(8), Dt.DateValue, 112) = CONVERT(VARCHAR(8) , Prf.[DateTime], 112)
Inner Join #TempFilterdMpsWrk (NoLock) Mpc
On Mpc.ManagementPackRowId = Rul.ManagementPackRowId
Left Outer Join vDisplayString (NoLock) Loc
On Loc.ElementGuid = Rul.RuleGuid
And Loc.LanguageCode = @LanguageCode
Group By Mpc.ManagementPackRowId, Rul.RuleRowId, Loc.Name, Rul.RuleDefaultName, Dt.DateValue
IF OBJECT_ID('tempdb..#TempPrfDtFilWrk') IS NOT NULL
BEGIN
drop table #TempPrfDtFilWrk
END
END
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Getting APM Events If Exists Table
If @APMDataExists = 1
BEGIN
Insert Into #TempEvtDtFilWrk
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 #TempEvtDtFilWrk
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 #TempEvtDtFilWrk
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 #TmpMGPWk Mg
On Met.ManagementGroupRowId = Mg.ManagementGroupRowId
Where DateAdd(hh,@AdjHours,EvtCTE.DateTime) BETWEEN @StartDate AND @EndDate
Group By ErlCTE.RuleRowId, EvtCTE.DateTime
Insert into #TmpDataCountsWrk
Select 4 as DataType,
2 as [Type],
Mpc.ManagementPackRowId,
Evt.RuleRowId as WorkFlowId,
COALESCE(Loc.Name,Rul.RuleSystemName) as WorkFlowDisplay,
Dt.DateValue as Date,
IsNull(SUM(Evt.EventCount),0) as CountByDate
From #TempEvtDtFilWrk Evt
Inner Join vRule Rul
ON Evt.RuleRowId = Rul.RuleRowId
Inner Join #TmpFilCalDtsWrk Dt
On CONVERT(VARCHAR(8), Dt.DateValue, 112) = CONVERT(VARCHAR(8) , Evt.[DateTime], 112)
Inner Join #TempFilterdMpsWrk Mpc
On Mpc.ManagementPackRowId = Rul.ManagementPackRowId
Left Outer Join vDisplayString (NoLock) Loc
On Loc.ElementGuid = Rul.RuleGuid
And Loc.LanguageCode = @LanguageCode
Group By Mpc.ManagementPackRowId, Evt.RuleRowId, Loc.Name, Rul.RuleSystemName, Dt.DateValue
IF OBJECT_ID('tempdb..#TempEvtDtFilWrk') IS NOT NULL
BEGIN
drop table #TempEvtDtFilWrk
END
END
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
If @ShowStates = 1
BEGIN
--Create Temp Filtered State Table
Create Table #TempSteDtFilWrk
(
MonitorRowId int,
[DateTime] datetime,
DataCount bigint
)
--Getting Filtered State changes Count By Dates
Insert Into #TempSteDtFilWrk
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 #TmpMGPWk 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 WorkFlow
Insert into #TmpDataCountsWrk
Select 5 as DataType,
3 as [Type],
Mpc.ManagementPackRowId,
Ste.MonitorRowId as WorkFlowId,
COALESCE(Loc.Name,Mon.MonitorDefaultName) as WorkFlowDisplay,
Dt.DateValue as Date,
IsNull(SUM(Ste.DataCount),0) as CountByDate
From #TempSteDtFilWrk Ste
Inner Join vMonitor Mon
On Mon.MonitorRowId = Ste.MonitorRowId
Inner Join #TmpFilCalDtsWrk Dt
On CONVERT(VARCHAR(8), Dt.DateValue, 112) = CONVERT(VARCHAR(8) , Ste.[DateTime], 112)
Inner Join #TempFilterdMpsWrk Mpc
On Mpc.ManagementPackRowId = Mon.ManagementPackRowId
Left Outer Join vDisplayString (NoLock) Loc
On Loc.ElementGuid = Mon.MonitorGuid
And Loc.LanguageCode = @LanguageCode
Group By Mpc.ManagementPackRowId, Ste.MonitorRowId, Loc.Name, Mon.MonitorDefaultName, 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(DataCount),0) From #TmpDataCountsWrk
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Calculate SumX
Insert into #TempDaysWrk
Select ROW_NUMBER() OVER(ORDER BY DateValue) AS DayNumber,
DateValue as Date
From #TmpFilCalDtsWrk (NoLock) Dt
Select @SumX = SUM(DayNumber) from #TempDaysWrk
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Calculate SumX2
Select @SumX2 = Sum(x0.DayNumber*x1.DayNumber)
From #TempDaysWrk x0
Inner Join #TempDaysWrk x1
On x0.DayNumber = x1.DayNumber
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Calculate Top Values for Equation by WorkFlow
Insert into #TempTopCalWrk
Select Tot.WorkFlowId,
@DaysNumber as CntDys,
Sum(Tot.SXY) as SumXY,
Sum(Tot.DataCount) as SumY,
@SumX as SumX
From
(
Select CnWk.WorkFlowId,
CnWk.Date,
Sum(CnDy.DayNumber*CnWk.DataCount) as SXY,
Sum(CnWk.DataCount) as DataCount
From #TmpDataCountsWrk CnWk
Inner Join #TempDaysWrk CnDy
On CnWk.Date = CnDy.Date
Group by CnWk.WorkFlowId, CnWk.Date
) as Tot
Group by Tot.WorkFlowId
Order by Tot.WorkFlowId
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Calculate Bottom Values for Equation by WorkFlow
Insert into #TempBottomCalWrk
Select WorkFlowId,
@DaysNumber as CntDys,
@SumX2 as SumX2,
@SumX * @SumX as SumXxSumX
From #TmpDataCountsWrk CnWk
Group by CnWk.WorkFlowId
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Calculate Final Result of Equation by WorkFlow
Insert into #TempCalByWrk
Select Tp.WorkFlowId,
((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 #TempTopCalWrk Tp
Inner Join #TempBottomCalWrk Bt
On Tp.WorkFlowId = Bt.WorkFlowId
Order By Tp.WorkFlowId
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
Insert into #TempTopNMpsWrk
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 #TempFilterdMpsWrk Mpc
Left Outer Join #TmpDataCountsWrk 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.[Type],
Cnts.WorkFlowId,
Cnts.WorkFlowDisplay,
Cnts.PrcVol,
IsNull(Cl.Slope,0) as Slope,
Cnts.CountByWorkflow
From
(
Select
[Type],
WorkFlowId,
WorkFlowDisplay,
Case
When @TotVol = 0 Then 0
Else ((CAST(Sum(IsNull(DataCount,0)) AS decimal)/@TotVol)*100)
End AS PrcVol,
Sum(IsNull(DataCount,0)) as CountByWorkflow
From #TmpDataCountsWrk CntWk
Inner Join #TempTopNMpsWrk Mpv
On Mpv.ManagementPackRowId = IsNull(CntWk.ManagementPackRowId,Mpv.ManagementPackRowId)
Group By
[Type],
WorkFlowId,
WorkFlowDisplay
) As Cnts
Left Outer Join #TempCalByWrk Cl
On Cl.WorkFlowId = Cnts.WorkFlowId
Order by Cnts.PrcVol Desc
END
QuitError:
IF OBJECT_ID('tempdb..#TmpOpsWrk') IS NOT NULL
BEGIN
Drop Table #TmpOpsWrk
END
IF OBJECT_ID('tempdb..#TmpMPsWk') IS NOT NULL
BEGIN
Drop Table #TmpMPsWk
END
IF OBJECT_ID('tempdb..#TmpMGPWk') IS NOT NULL
BEGIN
Drop Table #TmpMGPWk
END
IF OBJECT_ID('tempdb..#TempFilterdMpsWrk') IS NOT NULL
BEGIN
Drop Table #TempFilterdMpsWrk
END
IF OBJECT_ID('tempdb..#TmpDataCountsWrk') IS NOT NULL
BEGIN
Drop Table #TmpDataCountsWrk
END
IF OBJECT_ID('tempdb..#TempDaysWrk') IS NOT NULL
BEGIN
Drop Table #TempDaysWrk
END
IF OBJECT_ID('tempdb..#TempTopCalWrk') IS NOT NULL
BEGIN
Drop Table #TempTopCalWrk
END
IF OBJECT_ID('tempdb..#TempBottomCalWrk') IS NOT NULL
BEGIN
Drop Table #TempBottomCalWrk
END
IF OBJECT_ID('tempdb..#TempCalByWrk') IS NOT NULL
BEGIN
Drop Table #TempCalByWrk
END
IF OBJECT_ID('tempdb..#TmpFilCalDtsWrk') IS NOT NULL
BEGIN
Drop Table #TmpFilCalDtsWrk
END
IF OBJECT_ID('tempdb..#TempTopNMpsWrk') IS NOT NULL
BEGIN
drop table #TempTopNMpsWrk
END
RETURN @Error
END
GO
GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_DataWarehouse_Report_DataVolumenDetailsByWrkDataGet] TO OpsMgrReader
GO
Home
