IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_Report_AgentCountsByVerMGDateDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_AgentCountsByVerMGDateDataGet] AS RETURN 1')
END
GO
--Check if StartDate < EndDate
If @StartDate > @EndDate
BEGIN
Select * From #TempAgentCountsData
END
Else
BEGIN
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Get MANAGEMENT GROUPS To Filter
Insert Into #TmpMGPAgs
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
--Start Dates By Aggregation Section
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
If @AggregationType = '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
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Calculate Time Zone Dates according to Aggregation 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 @AggregationType = '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 @AggregationType = '1' Then RANK() OVER(PARTITION BY Dt.[Year], Dt.DateValue ORDER BY Dt.[Year], Dt.DateValue)
When @AggregationType = '2' Then RANK() OVER(PARTITION BY Dt.[Year], Dt.WeekOfYear ORDER BY Dt.[Year], Dt.WeekOfYear, Dt.DayOfWeek, Dt.DateValue)
When @AggregationType = '3' Then RANK() OVER(PARTITION BY Dt.[Year], Dt.[Month] ORDER BY Dt.[Year], Dt.[Month], Dt.DayOfMonth, Dt.DateValue)
When @AggregationType = '4' Then RANK() OVER(PARTITION BY Dt.[Year], Dt.Quarter ORDER BY Dt.[Year], Dt.Quarter, Dt.DayOfMonth, Dt.DateValue)
When @AggregationType = '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 @AggregationType = '1' Then Dx.DateValue
When @AggregationType = '2' Then Dx.WeekOfYear
When @AggregationType = '3' Then Dx.[Month]
When @AggregationType = '4' Then Dx.Quarter
When @AggregationType = '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 @AggregationType = '1' Then Dx.DateValue
When @AggregationType = '2' Then Dx.WeekOfYear
When @AggregationType = '3' Then Dx.[Month]
When @AggregationType = '4' Then Dx.Quarter
When @AggregationType = '5' Then Dx.[Year]
End
) As TFT
On Dt.[Year] = TFT.[Year]
And (
(@AggregationType = '1' And Dt.DateValue = TFT.GroupId ) Or (@AggregationType = '2' And Dt.WeekOfYear = TFT.GroupId ) Or
(@AggregationType = '3' And Dt.[Month] = TFT.GroupId ) Or (@AggregationType = '4' And Dt.Quarter = TFT.GroupId ) Or
(@AggregationType = '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)
--End Dates By Aggregation Section
--Start Agent List Section
--Get Agents List
Insert into #TmpAgLst
Select distinct ManagedEntityRowId From vManagedEntityPropertySet (NoLock) Mps
Inner Join vManagedEntityTypeProperty (NoLock) Ptp On Mps.PropertyGuid = Ptp.PropertyGuid And Ptp.PropertySystemName = 'IsAgent'
Where PropertyValue = '1'
--Get Agents in Time Frame
Insert into #TmpAgents
Select distinct Men.ManagementGroupRowId As ManagementGroupRowId, Pst1.PropertyValue As Version, IsNull(Men.DisplayName,' ') As AgentName, CONVERT(VARCHAR(20), Pst1.FromDateTime, 101) as FromDate, CONVERT(VARCHAR(20), IsNull(Pst1.ToDateTime,@EndDate), 101) as ToDate From vManagedEntity (NoLock) Men
Inner Join vManagedEntityType (NoLock) Mty On Mty.ManagedentityTypeRowId = Men.ManagedentityTypeRowId
Inner Join vManagedEntityTypeProperty (NoLock) Mtp1 On Men.ManagedentityTypeRowId = Mtp1.ManagedentityTypeRowId And Mtp1.PropertySystemName = 'Version'
Inner Join vManagedEntityPropertySet (NoLock) Pst1 On Mtp1.PropertyGuid = Pst1.PropertyGuid And Men.ManagedentityRowId = Pst1.ManagedentityRowId
Inner Join vManagedEntityTypeProperty (NoLock) Mtp2 On Men.ManagedentityTypeRowId = Mtp2.ManagedentityTypeRowId And Mtp2.PropertySystemName = 'InstallTime'
Inner Join vManagedEntityPropertySet (NoLock) Pst2 On Mtp2.PropertyGuid = Pst2.PropertyGuid And Men.ManagedentityRowId = Pst2.ManagedentityRowId
Inner Join #TmpAgLst FilAgn On Men.ManagedentityRowId = FilAgn.ManagedentityRowId
Where Mty.ManagedEntityTypeSystemName = 'Microsoft.SystemCenter.HealthService' And Men.ManagementGroupRowId in (Select ManagementGroupRowId From #TmpMGPAgs)
And ( DateAdd(hh,@AdjHours, Pst1.FromDateTime) BETWEEN @StartDate AND @EndDate ) Or ( IsNull(DateAdd(hh,@AdjHours, Pst1.ToDateTime),@EndDate) BETWEEN @StartDate AND @EndDate )
Group By Men.ManagementGroupRowId, Men.DisplayName, Pst1.PropertyValue, Pst2.PropertyValue, Pst1.FromDateTime, Pst1.ToDateTime
--End Agent List Section
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Start Agent Versions List Section
Insert into #TmpAgentVersions
Select Distinct Version From #TmpAgents Group By Version
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Get Duplicate Agents Records
insert into #TmpDuplicateAgentCounts
Select distinct AG.ManagementGroupRowId,
AG.Version,
AG.AgentName,
AG.FromDate,
AG.ToDate,
Case
When AgVr.VerCheck = 1 Then '1'
Else '0'
End As SameVersion,
Case
When AgVr.VerCheck > 1 Then '1'
Else '0'
End As DifferentVersion,
AgCn.DupCnt As DuplicateCounts,
ROW_NUMBER()
OVER(PARTITION BY AG.ManagementGroupRowId, AG.AgentName
ORDER BY AG.Version ) AS 'RowNumber'
From #TmpAgents AG
Inner Join
(
Select AG.ManagementGroupRowId, AG.AgentName, AG.Version, AG.FromDate, AG.ToDate,
RANK() OVER (PARTITION BY AG.ManagementGroupRowId, AG.AgentName ORDER BY AG.Version) AS 'VerCheck'
From #TmpAgents AG
) AgVr
On AgVr.ManagementGroupRowId = AG.ManagementGroupRowId
And AgVr.AgentName = AG.AgentName
And AgVr.Version = AG.Version
And AgVr.FromDate = AG.FromDate
And AgVr.ToDate = AG.ToDate
Inner Join
(
Select ManagementGroupRowId, AgentName, Count(AgentName) as DupCnt
From #TmpAgents
Group By ManagementGroupRowId, AgentName
Having Count(AgentName) > 1
) AgCn
On AgCn.ManagementGroupRowId = AG.ManagementGroupRowId
And AgCn.AgentName = AG.AgentName
Where AG.AgentName in
(
Select AgentName
From #TmpAgents
Group by AgentName
Having count(AgentName) > 1
)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Delete Duplicates Agents records in same day
Delete #TmpAgents
from
(
Select DP.ManagementGroupRowId, DP.Version, DP.AgentName, DP.FromDate, DP.ToDate
From #TmpDuplicateAgentCounts DP
Where ( DP.SameVersion = '1' Or DP.DifferentVersion = '1' )
And DP.FromDate = DP.ToDate
And DP.RowNumber < DP.DuplicateCounts
) DelRec
Where #TmpAgents.ManagementGroupRowId = DelRec.ManagementGroupRowId
And #TmpAgents.Version = DelRec.Version
And #TmpAgents.AgentName = DelRec.AgentName
And #TmpAgents.FromDate = DelRec.FromDate
And #TmpAgents.ToDate = DelRec.ToDate
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
Delete #TmpDuplicateAgentCounts
From
(
Select DP.ManagementGroupRowId, DP.Version, DP.AgentName, DP.FromDate, DP.ToDate
From #TmpDuplicateAgentCounts DP
Where ( DP.SameVersion = '1' Or DP.DifferentVersion = '1' )
And DP.FromDate = DP.ToDate
And DP.RowNumber < DP.DuplicateCounts
) DelRec
Where #TmpDuplicateAgentCounts.ManagementGroupRowId = DelRec.ManagementGroupRowId
And #TmpDuplicateAgentCounts.Version = DelRec.Version
And #TmpDuplicateAgentCounts.AgentName = DelRec.AgentName
And #TmpDuplicateAgentCounts.FromDate = DelRec.FromDate
And #TmpDuplicateAgentCounts.ToDate = DelRec.ToDate
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Update Agents With Adjusted Dates
Update #TmpAgents
Set #TmpAgents.ToDate = UpdRec.ToDate
From
(
Select DP.ManagementGroupRowId, DP.Version, DP.AgentName, DP.FromDate, DP.ToDate, DP.CheckUpd
From
(
Select DuCnIn.ManagementGroupRowId,
DuCnIn.Version,
DuCnIn.AgentName,
DuCnIn.FromDate,
dateadd(dd, -1, DuCnIn.ToDate) As ToDate,
Case
When DuCnIn.ToDate = DuCnFn.FromDate Then '1'
Else '0'
End CheckUpd
From #TmpDuplicateAgentCounts DuCnIn
Inner Join
(
Select ManagementGroupRowId, AgentName, FromDate, ToDate, RowNumber
From #TmpDuplicateAgentCounts
Where RowNumber > 1
) DuCnFn
On DuCnIn.ManagementGroupRowId = DuCnFn.ManagementGroupRowId
And DuCnIn.AgentName = DuCnFn.AgentName
Where DuCnIn.RowNumber = 1
And DuCnIn.SameVersion = '1'
) DP
Where DP.CheckUpd = '1'
) UpdRec
Where #TmpAgents.ManagementGroupRowId = UpdRec.ManagementGroupRowId
And #TmpAgents.Version = UpdRec.Version
And #TmpAgents.AgentName = UpdRec.AgentName
And #TmpAgents.FromDate = UpdRec.FromDate
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
Insert into #TempCalDatesVer
Select Dtc.DateValue, Dtc.FromDateValue, Dtc.ToDateValue, AgVr.Version, NULL, '0', '0'
From #TempCalDates Dtc Cross Join #TmpAgentVersions AgVr Order by DtC.DateValue, AgVr.Version
--End Agent List Section
--Start MG List Section
Insert into #TempCalDatesVer
Select Dtc.DateValue, Dtc.FromDateValue, Dtc.ToDateValue, NULL, Mg.ManagementGroupRowId, '0', '0'
From #TempCalDates Dtc Cross Join #TmpMGPAgs Mg Order by DtC.DateValue, Mg.ManagementGroupRowId
Update #TempCalDatesVer
Set InitDate = '1' Where DateValue = (Select top 1 DateValue from #TempCalDatesVer order by DateValue asc)
Update #TempCalDatesVer
Set LastDate = '1' Where DateValue = (Select top 1 DateValue from #TempCalDatesVer order by DateValue desc)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--End Agent List Section
--Start Final Query Section
Insert into #TmpAgentCountsByDate
Select Ag.ManagementGroupRowId, Ag.Version As Version, Ag.AgentName As AgentName, Ag.FromDate, Ag.ToDate, Count(Ag.AgentName) as Counts
From #TmpAgents Ag Group By Ag.ManagementGroupRowId, Ag.Version, AgentName, Ag.FromDate, Ag.ToDate
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Get Last Date
Select @LastDate = LsDt.DateValue
From
(
Select Top 1 DateValue
From #TmpFilCalDts Order By DateValue Desc
) LsDt
--Get Last Date per Version
Insert into #TempLastCalDatesVer
Select Dtc.DateValue, Dtc.DateValue As FromDateValue, Dtc.DateValue As ToDateValue, AgVr.Version, NULL As ManagementGroupRowId, '0' As InitDate , '0' As LastDate
From #TmpFilCalDts Dtc Cross Join #TmpAgentVersions AgVr
Where Dtc.DateValue = @LastDate
Order by DtC.DateValue, AgVr.Version
--Get Last Date per Management Group
Insert into #TempLastCalDatesVer
Select Dtc.DateValue, Dtc.DateValue As FromDateValue, Dtc.DateValue As ToDateValue, NULL As Version, Mg.ManagementGroupRowId, '0' As InitDate , '0' As LastDate
From #TmpFilCalDts Dtc Cross Join #TmpMGPAgs Mg
Where Dtc.DateValue = @LastDate
Order by DtC.DateValue, Mg.ManagementGroupRowId
--Header Data Set
Insert into #TempAgentCountsData
Select CnDt.ChartDataSet,
CnDt.InitDate,
CnDt.LastDate,
CnDt.HeaderDataSet,
CnDt.VersionDataSet,
CnDt.ManagementGroupDataSet,
CnDt.DateValue,
CnDt.Version,
CnDt.ManagementGroupRowId,
CnDt.ManagementGroupDefaultName,
SUM(CnDt.CountByDate) As CountByDate
From
(
Select '0' As ChartDataSet,
DtCl.InitDate As InitDate,
DtCl.LastDate As LastDate,
'1' As HeaderDataSet,
'0' As VersionDataSet,
'0' As ManagementGroupDataSet,
DtCl.DateValue As DateValue,
NULL As Version,
NULL As ManagementGroupRowId,
NULL As ManagementGroupDefaultName,
CntAgMG.AgentName As AgentName,
IsNull(CntAgMG.CountByDate,0) As CountByDate
From #TempCalDatesVer DtCl
Left Outer Join #TmpAgentCountsByDate CntAgMG
On DtCl.ManagementGroupRowId = CntAgMG.ManagementGroupRowId
And DtCl.FromDateValue between CntAgMG.FromDate and CntAgMG.ToDate
Where DtCl.ManagementGroupRowId IS NOT NULL
And DtCl.Version IS NULL
And ( DtCl.InitDate = '1' Or DtCl.LastDate = '1' )
Group By DtCl.InitDate, DtCl.LastDate, DtCl.DateValue, CntAgMG.AgentName, CntAgMG.CountByDate
)CnDt
Group By CnDt.ChartDataSet, CnDt.InitDate, CnDt.LastDate, CnDt.HeaderDataSet, CnDt.VersionDataSet, CnDt.ManagementGroupDataSet, CnDt.DateValue, CnDt.Version, CnDt.ManagementGroupRowId, CnDt.ManagementGroupDefaultName
--Version Data Set
Insert into #TempAgentCountsData
Select CnDt.ChartDataSet,
CnDt.InitDate,
CnDt.LastDate,
CnDt.HeaderDataSet,
CnDt.VersionDataSet,
CnDt.ManagementGroupDataSet,
CnDt.DateValue,
CnDt.Version,
CnDt.ManagementGroupRowId,
CnDt.ManagementGroupDefaultName,
SUM(CnDt.CountByDate) As CountByDate
From
(
Select '0' As ChartDataSet,
'0' As InitDate,
'0' As LastDate,
'0' As HeaderDataSet,
'1' As VersionDataSet,
'0' As ManagementGroupDataSet,
DtCl.DateValue As DateValue,
DtCl.Version As Version,
NULL As ManagementGroupRowId,
NULL As ManagementGroupDefaultName,
CntAg.AgentName As AgentName,
IsNull(CntAg.CountByDate,0) As CountByDate
From #TempCalDatesVer DtCl
Left Outer Join #TmpAgentCountsByDate CntAg
On DtCl.Version = CntAg.Version
And DtCl.FromDateValue between CntAg.FromDate and CntAg.ToDate
Where DtCl.Version IS NOT NULL
And DtCl.ManagementGroupRowId IS NULL
Group By DtCl.DateValue, DtCl.Version, CntAg.AgentName, CntAg.CountByDate
)CnDt
Group By CnDt.ChartDataSet, CnDt.InitDate, CnDt.LastDate, CnDt.HeaderDataSet, CnDt.VersionDataSet, CnDt.ManagementGroupDataSet, CnDt.DateValue, CnDt.Version, CnDt.ManagementGroupRowId, CnDt.ManagementGroupDefaultName
--Version Data Set And Last Date
Insert into #TempAgentCountsData
Select CnDt.ChartDataSet,
CnDt.InitDate,
CnDt.LastDate,
CnDt.HeaderDataSet,
CnDt.VersionDataSet,
CnDt.ManagementGroupDataSet,
CnDt.DateValue,
CnDt.Version,
CnDt.ManagementGroupRowId,
CnDt.ManagementGroupDefaultName,
SUM(CnDt.CountByDate) As CountByDate
From
(
Select '1' As ChartDataSet,
'0' As InitDate,
'1' As LastDate,
'0' As HeaderDataSet,
'1' As VersionDataSet,
'0' As ManagementGroupDataSet,
DtCl.DateValue As DateValue,
DtCl.Version As Version,
NULL As ManagementGroupRowId,
NULL As ManagementGroupDefaultName,
CntAg.AgentName As AgentName,
IsNull(CntAg.CountByDate,0) As CountByDate
From #TempLastCalDatesVer DtCl
Left Outer Join #TmpAgentCountsByDate CntAg
On DtCl.Version = CntAg.Version
And DtCl.FromDateValue between CntAg.FromDate and CntAg.ToDate
Where DtCl.Version IS NOT NULL
And DtCl.ManagementGroupRowId IS NULL
Group By DtCl.DateValue, DtCl.Version, CntAg.AgentName, CntAg.CountByDate
)CnDt
Group By CnDt.ChartDataSet, CnDt.InitDate, CnDt.LastDate, CnDt.HeaderDataSet, CnDt.VersionDataSet, CnDt.ManagementGroupDataSet, CnDt.DateValue, CnDt.Version, CnDt.ManagementGroupRowId, CnDt.ManagementGroupDefaultName
--Management Group Data Set
Insert into #TempAgentCountsData
Select CnDt.ChartDataSet, CnDt.InitDate, CnDt.LastDate, CnDt.HeaderDataSet, CnDt.VersionDataSet, CnDt.ManagementGroupDataSet, CnDt.DateValue, CnDt.Version, CnDt.ManagementGroupRowId, CnDt.ManagementGroupDefaultName, Sum(CnDt.CountByDate) as CountByDate
From
(
Select '0' As ChartDataSet,
'0' As InitDate,
'0' As LastDate,
'0' As HeaderDataSet,
'0' As VersionDataSet,
'1' As ManagementGroupDataSet,
DtCl.DateValue As DateValue,
NULL As Version,
DtCl.ManagementGroupRowId As ManagementGroupRowId,
MG.ManagementGroupDefaultName As ManagementGroupDefaultName,
CntAgMG.AgentName As AgentName,
IsNull(CntAgMG.CountByDate,0) As CountByDate
From #TempCalDatesVer DtCl
Left Outer Join #TmpAgentCountsByDate CntAgMG
On DtCl.ManagementGroupRowId = CntAgMG.ManagementGroupRowId
And DtCl.FromDateValue between CntAgMG.FromDate and CntAgMG.ToDate
Left Outer Join #TmpMGPAgs MG
On DtCl.ManagementGroupRowId = MG.ManagementGroupRowId
Where DtCl.Version IS NULL
And DtCl.ManagementGroupRowId IS NOT NULL
Group By DtCl.DateValue, DtCl.ManagementGroupRowId, MG.ManagementGroupDefaultName, CntAgMG.AgentName, CntAgMG.CountByDate
) CnDt
Group By CnDt.ChartDataSet, CnDt.InitDate, CnDt.LastDate, CnDt.HeaderDataSet, CnDt.VersionDataSet, CnDt.ManagementGroupDataSet, CnDt.DateValue, CnDt.Version, CnDt.ManagementGroupRowId, CnDt.ManagementGroupDefaultName
--Management Group Data Set And Last Date
Insert into #TempAgentCountsData
Select CnDt.ChartDataSet, CnDt.InitDate, CnDt.LastDate, CnDt.HeaderDataSet, CnDt.VersionDataSet, CnDt.ManagementGroupDataSet, CnDt.DateValue, CnDt.Version, CnDt.ManagementGroupRowId, CnDt.ManagementGroupDefaultName, Sum(CnDt.CountByDate) as CountByDate
From
(
Select '1' As ChartDataSet,
'0' As InitDate,
'1' As LastDate,
'0' As HeaderDataSet,
'0' As VersionDataSet,
'1' As ManagementGroupDataSet,
DtCl.DateValue As DateValue,
NULL As Version,
DtCl.ManagementGroupRowId As ManagementGroupRowId,
MG.ManagementGroupDefaultName As ManagementGroupDefaultName,
CntAgMG.AgentName As AgentName,
IsNull(CntAgMG.CountByDate,0) As CountByDate
From #TempLastCalDatesVer DtCl
Left Outer Join #TmpAgentCountsByDate CntAgMG
On DtCl.ManagementGroupRowId = CntAgMG.ManagementGroupRowId
And DtCl.FromDateValue between CntAgMG.FromDate and CntAgMG.ToDate
Left Outer Join #TmpMGPAgs MG
On DtCl.ManagementGroupRowId = MG.ManagementGroupRowId
Where DtCl.Version IS NULL
And DtCl.ManagementGroupRowId IS NOT NULL
Group By DtCl.DateValue, DtCl.ManagementGroupRowId, MG.ManagementGroupDefaultName, CntAgMG.AgentName, CntAgMG.CountByDate
) CnDt
Group By CnDt.ChartDataSet, CnDt.InitDate, CnDt.LastDate, CnDt.HeaderDataSet, CnDt.VersionDataSet, CnDt.ManagementGroupDataSet, CnDt.DateValue, CnDt.Version, CnDt.ManagementGroupRowId, CnDt.ManagementGroupDefaultName
Select * From #TempAgentCountsData Order By ChartDataSet, HeaderDataSet, VersionDataSet, ManagementGroupDataSet, DateValue, Version, ManagementGroupRowId
--End Final Query Section
END
QuitError:
IF OBJECT_ID('tempdb..#TmpMGPAgs') IS NOT NULL
BEGIN
drop table #TmpMGPAgs
END
IF OBJECT_ID('tempdb..#TmpAgLst') IS NOT NULL
BEGIN
drop table #TmpAgLst
END
IF OBJECT_ID('tempdb..#TempTotCntByDates') IS NOT NULL
BEGIN
drop table #TempTotCntByDates
END
IF OBJECT_ID('tempdb..#TmpAgentVersions') IS NOT NULL
BEGIN
drop table #TmpAgents
END
IF OBJECT_ID('tempdb..#TmpDuplicateAgentCounts') IS NOT NULL
BEGIN
drop table #TmpDuplicateAgentCounts
END
IF OBJECT_ID('tempdb..#TmpAgentVersions') IS NOT NULL
BEGIN
drop table #TmpAgentVersions
END
IF OBJECT_ID('tempdb..#TmpAgentCountsByDate') IS NOT NULL
BEGIN
drop table #TmpAgentCountsByDate
END
IF OBJECT_ID('tempdb..#TempCalDatesVer') IS NOT NULL
BEGIN
drop table #TempCalDatesVer
END
IF OBJECT_ID('tempdb..#TempLastCalDatesVer') IS NOT NULL
BEGIN
drop table #TempLastCalDatesVer
END
IF OBJECT_ID('tempdb..#TempCalDates') IS NOT NULL
BEGIN
drop table #TempCalDates
END
IF OBJECT_ID('tempdb..#TempWeeksToAdj') IS NOT NULL
BEGIN
drop table #TempWeeksToAdj
END
IF OBJECT_ID('tempdb..#TempAgentCountsData') IS NOT NULL
BEGIN
drop table #TempAgentCountsData
END
RETURN @Error
END
GO
GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_DataWarehouse_Report_AgentCountsByVerMGDateDataGet] TO OpsMgrReader
GO
</Install> <Uninstall>
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_Report_AgentCountsByVerMGDateDataGet')
BEGIN
DROP PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_AgentCountsByVerMGDateDataGet]
END
GO
</Uninstall> <Upgrade>
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_Report_AgentCountsByVerMGDateDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_AgentCountsByVerMGDateDataGet] AS RETURN 1')
END
GO
--Check if StartDate < EndDate
If @StartDate > @EndDate
BEGIN
Select * From #TempAgentCountsData
END
Else
BEGIN
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Get MANAGEMENT GROUPS To Filter
Insert Into #TmpMGPAgs
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
--Start Dates By Aggregation Section
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
If @AggregationType = '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
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Calculate Time Zone Dates according to Aggregation 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 @AggregationType = '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 @AggregationType = '1' Then RANK() OVER(PARTITION BY Dt.[Year], Dt.DateValue ORDER BY Dt.[Year], Dt.DateValue)
When @AggregationType = '2' Then RANK() OVER(PARTITION BY Dt.[Year], Dt.WeekOfYear ORDER BY Dt.[Year], Dt.WeekOfYear, Dt.DayOfWeek, Dt.DateValue)
When @AggregationType = '3' Then RANK() OVER(PARTITION BY Dt.[Year], Dt.[Month] ORDER BY Dt.[Year], Dt.[Month], Dt.DayOfMonth, Dt.DateValue)
When @AggregationType = '4' Then RANK() OVER(PARTITION BY Dt.[Year], Dt.Quarter ORDER BY Dt.[Year], Dt.Quarter, Dt.DayOfMonth, Dt.DateValue)
When @AggregationType = '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 @AggregationType = '1' Then Dx.DateValue
When @AggregationType = '2' Then Dx.WeekOfYear
When @AggregationType = '3' Then Dx.[Month]
When @AggregationType = '4' Then Dx.Quarter
When @AggregationType = '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 @AggregationType = '1' Then Dx.DateValue
When @AggregationType = '2' Then Dx.WeekOfYear
When @AggregationType = '3' Then Dx.[Month]
When @AggregationType = '4' Then Dx.Quarter
When @AggregationType = '5' Then Dx.[Year]
End
) As TFT
On Dt.[Year] = TFT.[Year]
And (
(@AggregationType = '1' And Dt.DateValue = TFT.GroupId ) Or (@AggregationType = '2' And Dt.WeekOfYear = TFT.GroupId ) Or
(@AggregationType = '3' And Dt.[Month] = TFT.GroupId ) Or (@AggregationType = '4' And Dt.Quarter = TFT.GroupId ) Or
(@AggregationType = '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)
--End Dates By Aggregation Section
--Start Agent List Section
--Get Agents List
Insert into #TmpAgLst
Select distinct ManagedEntityRowId From vManagedEntityPropertySet (NoLock) Mps
Inner Join vManagedEntityTypeProperty (NoLock) Ptp On Mps.PropertyGuid = Ptp.PropertyGuid And Ptp.PropertySystemName = 'IsAgent'
Where PropertyValue = '1'
--Get Agents in Time Frame
Insert into #TmpAgents
Select distinct Men.ManagementGroupRowId As ManagementGroupRowId, Pst1.PropertyValue As Version, IsNull(Men.DisplayName,' ') As AgentName, CONVERT(VARCHAR(20), Pst1.FromDateTime, 101) as FromDate, CONVERT(VARCHAR(20), IsNull(Pst1.ToDateTime,@EndDate), 101) as ToDate From vManagedEntity (NoLock) Men
Inner Join vManagedEntityType (NoLock) Mty On Mty.ManagedentityTypeRowId = Men.ManagedentityTypeRowId
Inner Join vManagedEntityTypeProperty (NoLock) Mtp1 On Men.ManagedentityTypeRowId = Mtp1.ManagedentityTypeRowId And Mtp1.PropertySystemName = 'Version'
Inner Join vManagedEntityPropertySet (NoLock) Pst1 On Mtp1.PropertyGuid = Pst1.PropertyGuid And Men.ManagedentityRowId = Pst1.ManagedentityRowId
Inner Join vManagedEntityTypeProperty (NoLock) Mtp2 On Men.ManagedentityTypeRowId = Mtp2.ManagedentityTypeRowId And Mtp2.PropertySystemName = 'InstallTime'
Inner Join vManagedEntityPropertySet (NoLock) Pst2 On Mtp2.PropertyGuid = Pst2.PropertyGuid And Men.ManagedentityRowId = Pst2.ManagedentityRowId
Inner Join #TmpAgLst FilAgn On Men.ManagedentityRowId = FilAgn.ManagedentityRowId
Where Mty.ManagedEntityTypeSystemName = 'Microsoft.SystemCenter.HealthService' And Men.ManagementGroupRowId in (Select ManagementGroupRowId From #TmpMGPAgs)
And ( DateAdd(hh,@AdjHours, Pst1.FromDateTime) BETWEEN @StartDate AND @EndDate ) Or ( IsNull(DateAdd(hh,@AdjHours, Pst1.ToDateTime),@EndDate) BETWEEN @StartDate AND @EndDate )
Group By Men.ManagementGroupRowId, Men.DisplayName, Pst1.PropertyValue, Pst2.PropertyValue, Pst1.FromDateTime, Pst1.ToDateTime
--End Agent List Section
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Start Agent Versions List Section
Insert into #TmpAgentVersions
Select Distinct Version From #TmpAgents Group By Version
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Get Duplicate Agents Records
insert into #TmpDuplicateAgentCounts
Select distinct AG.ManagementGroupRowId,
AG.Version,
AG.AgentName,
AG.FromDate,
AG.ToDate,
Case
When AgVr.VerCheck = 1 Then '1'
Else '0'
End As SameVersion,
Case
When AgVr.VerCheck > 1 Then '1'
Else '0'
End As DifferentVersion,
AgCn.DupCnt As DuplicateCounts,
ROW_NUMBER()
OVER(PARTITION BY AG.ManagementGroupRowId, AG.AgentName
ORDER BY AG.Version ) AS 'RowNumber'
From #TmpAgents AG
Inner Join
(
Select AG.ManagementGroupRowId, AG.AgentName, AG.Version, AG.FromDate, AG.ToDate,
RANK() OVER (PARTITION BY AG.ManagementGroupRowId, AG.AgentName ORDER BY AG.Version) AS 'VerCheck'
From #TmpAgents AG
) AgVr
On AgVr.ManagementGroupRowId = AG.ManagementGroupRowId
And AgVr.AgentName = AG.AgentName
And AgVr.Version = AG.Version
And AgVr.FromDate = AG.FromDate
And AgVr.ToDate = AG.ToDate
Inner Join
(
Select ManagementGroupRowId, AgentName, Count(AgentName) as DupCnt
From #TmpAgents
Group By ManagementGroupRowId, AgentName
Having Count(AgentName) > 1
) AgCn
On AgCn.ManagementGroupRowId = AG.ManagementGroupRowId
And AgCn.AgentName = AG.AgentName
Where AG.AgentName in
(
Select AgentName
From #TmpAgents
Group by AgentName
Having count(AgentName) > 1
)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Delete Duplicates Agents records in same day
Delete #TmpAgents
from
(
Select DP.ManagementGroupRowId, DP.Version, DP.AgentName, DP.FromDate, DP.ToDate
From #TmpDuplicateAgentCounts DP
Where ( DP.SameVersion = '1' Or DP.DifferentVersion = '1' )
And DP.FromDate = DP.ToDate
And DP.RowNumber < DP.DuplicateCounts
) DelRec
Where #TmpAgents.ManagementGroupRowId = DelRec.ManagementGroupRowId
And #TmpAgents.Version = DelRec.Version
And #TmpAgents.AgentName = DelRec.AgentName
And #TmpAgents.FromDate = DelRec.FromDate
And #TmpAgents.ToDate = DelRec.ToDate
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
Delete #TmpDuplicateAgentCounts
From
(
Select DP.ManagementGroupRowId, DP.Version, DP.AgentName, DP.FromDate, DP.ToDate
From #TmpDuplicateAgentCounts DP
Where ( DP.SameVersion = '1' Or DP.DifferentVersion = '1' )
And DP.FromDate = DP.ToDate
And DP.RowNumber < DP.DuplicateCounts
) DelRec
Where #TmpDuplicateAgentCounts.ManagementGroupRowId = DelRec.ManagementGroupRowId
And #TmpDuplicateAgentCounts.Version = DelRec.Version
And #TmpDuplicateAgentCounts.AgentName = DelRec.AgentName
And #TmpDuplicateAgentCounts.FromDate = DelRec.FromDate
And #TmpDuplicateAgentCounts.ToDate = DelRec.ToDate
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Update Agents With Adjusted Dates
Update #TmpAgents
Set #TmpAgents.ToDate = UpdRec.ToDate
From
(
Select DP.ManagementGroupRowId, DP.Version, DP.AgentName, DP.FromDate, DP.ToDate, DP.CheckUpd
From
(
Select DuCnIn.ManagementGroupRowId,
DuCnIn.Version,
DuCnIn.AgentName,
DuCnIn.FromDate,
dateadd(dd, -1, DuCnIn.ToDate) As ToDate,
Case
When DuCnIn.ToDate = DuCnFn.FromDate Then '1'
Else '0'
End CheckUpd
From #TmpDuplicateAgentCounts DuCnIn
Inner Join
(
Select ManagementGroupRowId, AgentName, FromDate, ToDate, RowNumber
From #TmpDuplicateAgentCounts
Where RowNumber > 1
) DuCnFn
On DuCnIn.ManagementGroupRowId = DuCnFn.ManagementGroupRowId
And DuCnIn.AgentName = DuCnFn.AgentName
Where DuCnIn.RowNumber = 1
And DuCnIn.SameVersion = '1'
) DP
Where DP.CheckUpd = '1'
) UpdRec
Where #TmpAgents.ManagementGroupRowId = UpdRec.ManagementGroupRowId
And #TmpAgents.Version = UpdRec.Version
And #TmpAgents.AgentName = UpdRec.AgentName
And #TmpAgents.FromDate = UpdRec.FromDate
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
Insert into #TempCalDatesVer
Select Dtc.DateValue, Dtc.FromDateValue, Dtc.ToDateValue, AgVr.Version, NULL, '0', '0'
From #TempCalDates Dtc Cross Join #TmpAgentVersions AgVr Order by DtC.DateValue, AgVr.Version
--End Agent List Section
--Start MG List Section
Insert into #TempCalDatesVer
Select Dtc.DateValue, Dtc.FromDateValue, Dtc.ToDateValue, NULL, Mg.ManagementGroupRowId, '0', '0'
From #TempCalDates Dtc Cross Join #TmpMGPAgs Mg Order by DtC.DateValue, Mg.ManagementGroupRowId
Update #TempCalDatesVer
Set InitDate = '1' Where DateValue = (Select top 1 DateValue from #TempCalDatesVer order by DateValue asc)
Update #TempCalDatesVer
Set LastDate = '1' Where DateValue = (Select top 1 DateValue from #TempCalDatesVer order by DateValue desc)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--End Agent List Section
--Start Final Query Section
Insert into #TmpAgentCountsByDate
Select Ag.ManagementGroupRowId, Ag.Version As Version, Ag.AgentName As AgentName, Ag.FromDate, Ag.ToDate, Count(Ag.AgentName) as Counts
From #TmpAgents Ag Group By Ag.ManagementGroupRowId, Ag.Version, AgentName, Ag.FromDate, Ag.ToDate
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
--Get Last Date
Select @LastDate = LsDt.DateValue
From
(
Select Top 1 DateValue
From #TmpFilCalDts Order By DateValue Desc
) LsDt
--Get Last Date per Version
Insert into #TempLastCalDatesVer
Select Dtc.DateValue, Dtc.DateValue As FromDateValue, Dtc.DateValue As ToDateValue, AgVr.Version, NULL As ManagementGroupRowId, '0' As InitDate , '0' As LastDate
From #TmpFilCalDts Dtc Cross Join #TmpAgentVersions AgVr
Where Dtc.DateValue = @LastDate
Order by DtC.DateValue, AgVr.Version
--Get Last Date per Management Group
Insert into #TempLastCalDatesVer
Select Dtc.DateValue, Dtc.DateValue As FromDateValue, Dtc.DateValue As ToDateValue, NULL As Version, Mg.ManagementGroupRowId, '0' As InitDate , '0' As LastDate
From #TmpFilCalDts Dtc Cross Join #TmpMGPAgs Mg
Where Dtc.DateValue = @LastDate
Order by DtC.DateValue, Mg.ManagementGroupRowId
--Header Data Set
Insert into #TempAgentCountsData
Select CnDt.ChartDataSet,
CnDt.InitDate,
CnDt.LastDate,
CnDt.HeaderDataSet,
CnDt.VersionDataSet,
CnDt.ManagementGroupDataSet,
CnDt.DateValue,
CnDt.Version,
CnDt.ManagementGroupRowId,
CnDt.ManagementGroupDefaultName,
SUM(CnDt.CountByDate) As CountByDate
From
(
Select '0' As ChartDataSet,
DtCl.InitDate As InitDate,
DtCl.LastDate As LastDate,
'1' As HeaderDataSet,
'0' As VersionDataSet,
'0' As ManagementGroupDataSet,
DtCl.DateValue As DateValue,
NULL As Version,
NULL As ManagementGroupRowId,
NULL As ManagementGroupDefaultName,
CntAgMG.AgentName As AgentName,
IsNull(CntAgMG.CountByDate,0) As CountByDate
From #TempCalDatesVer DtCl
Left Outer Join #TmpAgentCountsByDate CntAgMG
On DtCl.ManagementGroupRowId = CntAgMG.ManagementGroupRowId
And DtCl.FromDateValue between CntAgMG.FromDate and CntAgMG.ToDate
Where DtCl.ManagementGroupRowId IS NOT NULL
And DtCl.Version IS NULL
And ( DtCl.InitDate = '1' Or DtCl.LastDate = '1' )
Group By DtCl.InitDate, DtCl.LastDate, DtCl.DateValue, CntAgMG.AgentName, CntAgMG.CountByDate
)CnDt
Group By CnDt.ChartDataSet, CnDt.InitDate, CnDt.LastDate, CnDt.HeaderDataSet, CnDt.VersionDataSet, CnDt.ManagementGroupDataSet, CnDt.DateValue, CnDt.Version, CnDt.ManagementGroupRowId, CnDt.ManagementGroupDefaultName
--Version Data Set
Insert into #TempAgentCountsData
Select CnDt.ChartDataSet,
CnDt.InitDate,
CnDt.LastDate,
CnDt.HeaderDataSet,
CnDt.VersionDataSet,
CnDt.ManagementGroupDataSet,
CnDt.DateValue,
CnDt.Version,
CnDt.ManagementGroupRowId,
CnDt.ManagementGroupDefaultName,
SUM(CnDt.CountByDate) As CountByDate
From
(
Select '0' As ChartDataSet,
'0' As InitDate,
'0' As LastDate,
'0' As HeaderDataSet,
'1' As VersionDataSet,
'0' As ManagementGroupDataSet,
DtCl.DateValue As DateValue,
DtCl.Version As Version,
NULL As ManagementGroupRowId,
NULL As ManagementGroupDefaultName,
CntAg.AgentName As AgentName,
IsNull(CntAg.CountByDate,0) As CountByDate
From #TempCalDatesVer DtCl
Left Outer Join #TmpAgentCountsByDate CntAg
On DtCl.Version = CntAg.Version
And DtCl.FromDateValue between CntAg.FromDate and CntAg.ToDate
Where DtCl.Version IS NOT NULL
And DtCl.ManagementGroupRowId IS NULL
Group By DtCl.DateValue, DtCl.Version, CntAg.AgentName, CntAg.CountByDate
)CnDt
Group By CnDt.ChartDataSet, CnDt.InitDate, CnDt.LastDate, CnDt.HeaderDataSet, CnDt.VersionDataSet, CnDt.ManagementGroupDataSet, CnDt.DateValue, CnDt.Version, CnDt.ManagementGroupRowId, CnDt.ManagementGroupDefaultName
--Version Data Set And Last Date
Insert into #TempAgentCountsData
Select CnDt.ChartDataSet,
CnDt.InitDate,
CnDt.LastDate,
CnDt.HeaderDataSet,
CnDt.VersionDataSet,
CnDt.ManagementGroupDataSet,
CnDt.DateValue,
CnDt.Version,
CnDt.ManagementGroupRowId,
CnDt.ManagementGroupDefaultName,
SUM(CnDt.CountByDate) As CountByDate
From
(
Select '1' As ChartDataSet,
'0' As InitDate,
'1' As LastDate,
'0' As HeaderDataSet,
'1' As VersionDataSet,
'0' As ManagementGroupDataSet,
DtCl.DateValue As DateValue,
DtCl.Version As Version,
NULL As ManagementGroupRowId,
NULL As ManagementGroupDefaultName,
CntAg.AgentName As AgentName,
IsNull(CntAg.CountByDate,0) As CountByDate
From #TempLastCalDatesVer DtCl
Left Outer Join #TmpAgentCountsByDate CntAg
On DtCl.Version = CntAg.Version
And DtCl.FromDateValue between CntAg.FromDate and CntAg.ToDate
Where DtCl.Version IS NOT NULL
And DtCl.ManagementGroupRowId IS NULL
Group By DtCl.DateValue, DtCl.Version, CntAg.AgentName, CntAg.CountByDate
)CnDt
Group By CnDt.ChartDataSet, CnDt.InitDate, CnDt.LastDate, CnDt.HeaderDataSet, CnDt.VersionDataSet, CnDt.ManagementGroupDataSet, CnDt.DateValue, CnDt.Version, CnDt.ManagementGroupRowId, CnDt.ManagementGroupDefaultName
--Management Group Data Set
Insert into #TempAgentCountsData
Select CnDt.ChartDataSet, CnDt.InitDate, CnDt.LastDate, CnDt.HeaderDataSet, CnDt.VersionDataSet, CnDt.ManagementGroupDataSet, CnDt.DateValue, CnDt.Version, CnDt.ManagementGroupRowId, CnDt.ManagementGroupDefaultName, Sum(CnDt.CountByDate) as CountByDate
From
(
Select '0' As ChartDataSet,
'0' As InitDate,
'0' As LastDate,
'0' As HeaderDataSet,
'0' As VersionDataSet,
'1' As ManagementGroupDataSet,
DtCl.DateValue As DateValue,
NULL As Version,
DtCl.ManagementGroupRowId As ManagementGroupRowId,
MG.ManagementGroupDefaultName As ManagementGroupDefaultName,
CntAgMG.AgentName As AgentName,
IsNull(CntAgMG.CountByDate,0) As CountByDate
From #TempCalDatesVer DtCl
Left Outer Join #TmpAgentCountsByDate CntAgMG
On DtCl.ManagementGroupRowId = CntAgMG.ManagementGroupRowId
And DtCl.FromDateValue between CntAgMG.FromDate and CntAgMG.ToDate
Left Outer Join #TmpMGPAgs MG
On DtCl.ManagementGroupRowId = MG.ManagementGroupRowId
Where DtCl.Version IS NULL
And DtCl.ManagementGroupRowId IS NOT NULL
Group By DtCl.DateValue, DtCl.ManagementGroupRowId, MG.ManagementGroupDefaultName, CntAgMG.AgentName, CntAgMG.CountByDate
) CnDt
Group By CnDt.ChartDataSet, CnDt.InitDate, CnDt.LastDate, CnDt.HeaderDataSet, CnDt.VersionDataSet, CnDt.ManagementGroupDataSet, CnDt.DateValue, CnDt.Version, CnDt.ManagementGroupRowId, CnDt.ManagementGroupDefaultName
--Management Group Data Set And Last Date
Insert into #TempAgentCountsData
Select CnDt.ChartDataSet, CnDt.InitDate, CnDt.LastDate, CnDt.HeaderDataSet, CnDt.VersionDataSet, CnDt.ManagementGroupDataSet, CnDt.DateValue, CnDt.Version, CnDt.ManagementGroupRowId, CnDt.ManagementGroupDefaultName, Sum(CnDt.CountByDate) as CountByDate
From
(
Select '1' As ChartDataSet,
'0' As InitDate,
'1' As LastDate,
'0' As HeaderDataSet,
'0' As VersionDataSet,
'1' As ManagementGroupDataSet,
DtCl.DateValue As DateValue,
NULL As Version,
DtCl.ManagementGroupRowId As ManagementGroupRowId,
MG.ManagementGroupDefaultName As ManagementGroupDefaultName,
CntAgMG.AgentName As AgentName,
IsNull(CntAgMG.CountByDate,0) As CountByDate
From #TempLastCalDatesVer DtCl
Left Outer Join #TmpAgentCountsByDate CntAgMG
On DtCl.ManagementGroupRowId = CntAgMG.ManagementGroupRowId
And DtCl.FromDateValue between CntAgMG.FromDate and CntAgMG.ToDate
Left Outer Join #TmpMGPAgs MG
On DtCl.ManagementGroupRowId = MG.ManagementGroupRowId
Where DtCl.Version IS NULL
And DtCl.ManagementGroupRowId IS NOT NULL
Group By DtCl.DateValue, DtCl.ManagementGroupRowId, MG.ManagementGroupDefaultName, CntAgMG.AgentName, CntAgMG.CountByDate
) CnDt
Group By CnDt.ChartDataSet, CnDt.InitDate, CnDt.LastDate, CnDt.HeaderDataSet, CnDt.VersionDataSet, CnDt.ManagementGroupDataSet, CnDt.DateValue, CnDt.Version, CnDt.ManagementGroupRowId, CnDt.ManagementGroupDefaultName
Select * From #TempAgentCountsData Order By ChartDataSet, HeaderDataSet, VersionDataSet, ManagementGroupDataSet, DateValue, Version, ManagementGroupRowId
--End Final Query Section
END
QuitError:
IF OBJECT_ID('tempdb..#TmpMGPAgs') IS NOT NULL
BEGIN
drop table #TmpMGPAgs
END
IF OBJECT_ID('tempdb..#TmpAgLst') IS NOT NULL
BEGIN
drop table #TmpAgLst
END
IF OBJECT_ID('tempdb..#TempTotCntByDates') IS NOT NULL
BEGIN
drop table #TempTotCntByDates
END
IF OBJECT_ID('tempdb..#TmpAgentVersions') IS NOT NULL
BEGIN
drop table #TmpAgents
END
IF OBJECT_ID('tempdb..#TmpDuplicateAgentCounts') IS NOT NULL
BEGIN
drop table #TmpDuplicateAgentCounts
END
IF OBJECT_ID('tempdb..#TmpAgentVersions') IS NOT NULL
BEGIN
drop table #TmpAgentVersions
END
IF OBJECT_ID('tempdb..#TmpAgentCountsByDate') IS NOT NULL
BEGIN
drop table #TmpAgentCountsByDate
END
IF OBJECT_ID('tempdb..#TempCalDatesVer') IS NOT NULL
BEGIN
drop table #TempCalDatesVer
END
IF OBJECT_ID('tempdb..#TempLastCalDatesVer') IS NOT NULL
BEGIN
drop table #TempLastCalDatesVer
END
IF OBJECT_ID('tempdb..#TempCalDates') IS NOT NULL
BEGIN
drop table #TempCalDates
END
IF OBJECT_ID('tempdb..#TempWeeksToAdj') IS NOT NULL
BEGIN
drop table #TempWeeksToAdj
END
IF OBJECT_ID('tempdb..#TempAgentCountsData') IS NOT NULL
BEGIN
drop table #TempAgentCountsData
END
RETURN @Error
END
GO
GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_DataWarehouse_Report_AgentCountsByVerMGDateDataGet] TO OpsMgrReader
GO
Home
