Res.Comtrade.Citrix.XenDesktop.Reports.ReportScript.DGAvailabilityStoredProcedure.Install (Resource)

Element properties:

File Content: Comtrade.Citrix.XenDesktop.Reports.ReportScript.DGAvailability.Install.sql

IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'ComtradeMPXAXD_DGAvailability_DataGet')

BEGIN

EXECUTE ('CREATE PROCEDURE dbo.[ComtradeMPXAXD_DGAvailability_DataGet] AS RETURN 1')

END

GO





ALTER PROCEDURE [dbo].[ComtradeMPXAXD_DGAvailability_DataGet] 

@StartDate_BaseValue  datetime,

@EndDate_BaseValue  datetime,

@ObjectList xml,

@AggregationType int, -- 0-hourly, 1-daily, 2-monthly, 3-yearly, -1 raw

@BusinessTimeType int, -- 0-Regular, 1-Business

@BusinessDay1 int, --monday

@BusinessDay2 int, --tuesday

@BusinessDay3 int, --wednesday

@BusinessDay4 int, --thursday

@BusinessDay5 int, --friday

@BusinessDay6 int, --saturday

@BusinessDay7 int, --sunday

@StartDate_BaseValueGMT datetime

AS   



DECLARE @ExecError int



DECLARE @DeliveryGroupMonitorSystemName nvarchar(256)

SET @DeliveryGroupMonitorSystemName = 'ComTrade.Citrix.XenDesktop.DeliveryGroup.DGAvailability.AggregateMonitor'



SET DATEFIRST 1 --monday = 1

DECLARE @GMTDiff int

SELECT @GMTDiff = DATEDIFF(minute, @StartDate_BaseValueGMT, @StartDate_BaseValue)

DECLARE @BH_start datetime --business hour start time (LOCAL TIME)

DECLARE @BH_end datetime   --business hour end time   (LOCAL TIME)

SELECT @BH_start = (@StartDate_BaseValue - CAST(FLOOR(CAST(@StartDate_BaseValue AS float)) AS datetime))

SELECT @BH_end = (@EndDate_BaseValue - CAST(FLOOR(CAST(@EndDate_BaseValue AS float)) AS datetime))	



DECLARE @StartDate_BaseValue_UTC datetime

DECLARE @EndDate_BaseValue_UTC datetime

SELECT @StartDate_BaseValue_UTC = DATEADD(minute, -@GMTDiff, @StartDate_BaseValue) --convert to UTC

SELECT @EndDate_BaseValue_UTC = DATEADD(minute, -@GMTDiff, @EndDate_BaseValue)     --convert to UTC



CREATE TABLE #ManagedEntityDG (ManagedEntityRowId int)

INSERT INTO #ManagedEntityDG



EXECUTE @ExecError = [Microsoft_SystemCenter_DataWarehouse_Report_Library_ReportObjectListParse]

@ObjectList = @ObjectList,

@StartDate = @StartDate_BaseValue_UTC ,

@EndDate = @EndDate_BaseValue_UTC



-- DG -> SITE relation!

create table #DGs (DG_RowId int, DG_Name nvarchar(max) COLLATE database_default, Site_RowId int, Site_Name nvarchar(max) COLLATE database_default)

insert into #DGs

Select  meDG.ManagedEntityRowId DG_RowId, meDG.DisplayName DG_Name,

		meSite.ManagedEntityRowId Site_RowId, meSite.DisplayName Site_Name

from			vManagedEntity meSite

	inner join  vManagedEntityManagementGroup mg on mg.ManagedEntityRowId = meSite.ManagedEntityRowId and mg.ToDateTime is null

	inner join	vManagedEntityType metSite on metSite.ManagedEntityTypeRowId = meSite.ManagedEntityTypeRowId AND metSite.ManagedEntityTypeSystemName = 'ComTrade.Citrix.XenDesktop.Site'

	inner join	vRelationship re1 on re1.SourceManagedEntityRowId = meSite.ManagedEntityRowId

	inner join  vRelationshipType ret1 on ret1.RelationshipTypeRowId = re1.RelationshipTypeRowId AND ret1.RelationshipTypeSystemName = 'ComTrade.Citrix.XenDesktop.Site.Contains.SiteProxy'

	inner join	vManagedEntity me1 on me1.ManagedEntityRowId = re1.TargetManagedEntityRowId

	inner join	vManagedEntityType met1 on met1.ManagedEntityTypeRowId = me1.ManagedEntityTypeRowId AND met1.ManagedEntityTypeSystemName = 'ComTrade.Citrix.XenDesktop.SiteDataCollector'



	inner join	vRelationship re2 on re2.SourceManagedEntityRowId = me1.ManagedEntityRowId

	inner join  vRelationshipType ret2 on ret2.RelationshipTypeRowId = re2.RelationshipTypeRowId AND ret2.RelationshipTypeSystemName = 'ComTrade.Citrix.XenDesktop.SiteDataCollector.Hosts.DeliveryGroupContainer'

	inner join	vManagedEntity me2 on me2.ManagedEntityRowId = re2.TargetManagedEntityRowId

	inner join	vManagedEntityType met2 on met2.ManagedEntityTypeRowId = me2.ManagedEntityTypeRowId AND met2.ManagedEntityTypeSystemName = 'ComTrade.Citrix.XenDesktop.DeliveryGroups.Container'



	inner join	vRelationship re3 on re3.SourceManagedEntityRowId = me2.ManagedEntityRowId

	inner join  vRelationshipType ret3 on ret3.RelationshipTypeRowId = re3.RelationshipTypeRowId AND ret3.RelationshipTypeSystemName = 'ComTrade.Citrix.XenDesktop.DeliveryGroupContainer.Hosts.DeliveryGroup'

	inner join	vManagedEntity meDG on meDG.ManagedEntityRowId = re3.TargetManagedEntityRowId

	inner join	vManagedEntityType metDG on metDG.ManagedEntityTypeRowId = meDG.ManagedEntityTypeRowId AND metDG.ManagedEntityTypeSystemName IN 

				('ComTrade.Citrix.XenDesktop.DeliveryGroup.Private',

				 'ComTrade.Citrix.XenDesktop.DeliveryGroup.Private.DesktopOS',

				 'ComTrade.Citrix.XenDesktop.DeliveryGroup.Shared',

				 'ComTrade.Citrix.XenDesktop.DeliveryGroup.Shared.DesktopOS',

				 'ComTrade.Citrix.XenDesktop.DeliveryGroup.Shared.ServerOS')

    inner join vManagementPack mp on metDG.ManagementPackRowId = mp.ManagementPackRowId and mp.ManagementPackSystemName IN ('Comtrade.Citrix.XenApp.And.XenDesktop')

	inner join #ManagedEntityDG selectedDG on selectedDG.ManagedEntityRowId = meDG.ManagedEntityRowId

 

 --get all monitor IDs from DGs (availability)

CREATE TABLE #ManagedEntityMonitorsDGs (MEMonitorRowId int, MEDGRowId int, MEDGName nvarchar(max) COLLATE database_default, MEDGDefaultName nvarchar(max) COLLATE database_default, meSiteName nvarchar(max) COLLATE database_default)--, FromDateTime datetime, ToDateTime datetime)

INSERT INTO #ManagedEntityMonitorsDGs

SELECT     VMeMon.ManagedEntityMonitorRowId ,MES.DG_RowId, MESRV.Name, MESRV.ManagedEntityDefaultName,MES.Site_Name

		   --,MES.FromDateTime, MES.ToDateTime

FROM         vMonitor as VMon INNER JOIN

                      vManagedEntityMonitor as VMeMon ON VMon.MonitorRowId = VMeMon.MonitorRowId INNER JOIN

                      #DGs as MES ON MES.DG_RowId = VMeMon.ManagedEntityRowId INNER JOIN

                      vManagedEntity as MESRV ON MES.DG_RowId = MESRV.ManagedEntityRowId 

WHERE		VMon.MonitorSystemName = @DeliveryGroupMonitorSystemName





CREATE TABLE #StateTable ("DateTime" datetime, IntervalDurationMilliseconds int, InWhiteStateMilliseconds int,

InGreenStateMilliseconds int, InYellowStateMilliseconds int, InRedStateMilliseconds int, InDisabledStateMilliseconds int, 

InPlannedMaintenanceMilliseconds int, InUnplannedMaintenanceMilliseconds int, HealthServiceUnavailableMilliseconds int,

MEMonitorRowId int, MEDGRowId int, MEDGName nvarchar(max) COLLATE database_default, MEDGDefaultName nvarchar(max) COLLATE database_default, meSiteName nvarchar(max) COLLATE database_default) --StateType: 0 - DG, 1 - application





IF @BusinessTimeType = 1 --business hours

BEGIN

	INSERT INTO #StateTable

	SELECT DATEADD(minute, @GMTDiff, vState.DateTime) as "DateTime", vState.IntervalDurationMilliseconds, vState.InWhiteStateMilliseconds, 

		vState.InGreenStateMilliseconds, vState.InYellowStateMilliseconds,

		vState.InRedStateMilliseconds, vState.InDisabledStateMilliseconds,

		vState.InPlannedMaintenanceMilliseconds, vState.InUnplannedMaintenanceMilliseconds,

		vState.HealthServiceUnavailableMilliseconds,

		MEMS.MEMonitorRowId, MEMS.MEDGRowId, MEMS.MEDGName, MEMS.MEDGDefaultName, MEMS.meSiteName

	FROM   vStateHourlyFull as vState INNER JOIN

		#ManagedEntityMonitorsDGs as MEMS ON MEMS.MEMonitorRowId = vState.ManagedEntityMonitorRowId 

	WHERE  



		(vState.DateTime >= DATEADD(hh, DATEPART(hh, @StartDate_BaseValue_UTC), convert(varchar(8), @StartDate_BaseValue_UTC, 112))) AND 

		(vState.DateTime < DATEADD(hh, DATEPART(hh,  @EndDate_BaseValue_UTC), convert(varchar(8),  @EndDate_BaseValue_UTC, 112))) AND

		(vState.Date BETWEEN DATEADD(day, -1, @StartDate_BaseValue_UTC) AND DATEADD(day, 1,  @EndDate_BaseValue_UTC))

			

			and vState.[DateTime] - CAST(FLOOR(CAST(vState.[DateTime] AS float)) AS datetime) >=  DATEADD(minute, -@GMTDiff, @BH_start) --start hours

			and vState.[DateTime] - CAST(FLOOR(CAST(vState.[DateTime] AS float)) AS datetime) <  DATEADD(minute, -@GMTDiff, @BH_end)   --end hours

			and (      (DATEPART(WEEKDAY,  DATEADD(minute, @GMTDiff, vState.[DateTime])) = 1 and @BusinessDay1 = 1)  --monday

					OR (DATEPART(WEEKDAY,  DATEADD(minute, @GMTDiff, vState.[DateTime])) = 2 and @BusinessDay2 = 1)  --tuesday

					OR (DATEPART(WEEKDAY,  DATEADD(minute, @GMTDiff, vState.[DateTime])) = 3 and @BusinessDay3 = 1)  --wednesday

					OR (DATEPART(WEEKDAY,  DATEADD(minute, @GMTDiff, vState.[DateTime])) = 4 and @BusinessDay4 = 1)  --thursday

					OR (DATEPART(WEEKDAY,  DATEADD(minute, @GMTDiff, vState.[DateTime])) = 5 and @BusinessDay5 = 1)  --friday

					OR (DATEPART(WEEKDAY,  DATEADD(minute, @GMTDiff, vState.[DateTime])) = 6 and @BusinessDay6 = 1)  --saturday

					OR (DATEPART(WEEKDAY,  DATEADD(minute, @GMTDiff, vState.[DateTime])) = 7 and @BusinessDay7 = 1)) --sunday



		AND --SAMO V ODSEKIH

		vState.DateTime BETWEEN DATEADD(minute, -@GMTDiff, @StartDate_BaseValue) AND DATEADD(minute, -@GMTDiff, @EndDate_BaseValue)



END

ELSE

BEGIN

	INSERT INTO #StateTable

	SELECT DATEADD(minute, @GMTDiff, vState.DateTime) as "DateTime", vState.IntervalDurationMilliseconds, vState.InWhiteStateMilliseconds, 

		vState.InGreenStateMilliseconds, vState.InYellowStateMilliseconds,

		vState.InRedStateMilliseconds, vState.InDisabledStateMilliseconds,

		vState.InPlannedMaintenanceMilliseconds, vState.InUnplannedMaintenanceMilliseconds,

		vState.HealthServiceUnavailableMilliseconds,

		MEMS.MEMonitorRowId, MEMS.MEDGRowId, MEMS.MEDGName, MEMS.MEDGDefaultName, MEMS.meSiteName

	FROM   vStateHourlyFull as vState INNER JOIN

		#ManagedEntityMonitorsDGs as MEMS ON MEMS.MEMonitorRowId = vState.ManagedEntityMonitorRowId 

	WHERE  



		(vState.DateTime >= DATEADD(hh, DATEPART(hh, @StartDate_BaseValue_UTC), convert(varchar(8), @StartDate_BaseValue_UTC, 112))) AND 

		(vState.DateTime < DATEADD(hh, DATEPART(hh,  @EndDate_BaseValue_UTC), convert(varchar(8),  @EndDate_BaseValue_UTC, 112))) AND

		(vState.Date BETWEEN DATEADD(day, -1, @StartDate_BaseValue_UTC) AND DATEADD(day, 1,  @EndDate_BaseValue_UTC))

			

		AND --SAMO V ODSEKIH

		vState.DateTime BETWEEN DATEADD(minute, -@GMTDiff, @StartDate_BaseValue) AND DATEADD(minute, -@GMTDiff, @EndDate_BaseValue)

END 



CREATE TABLE #StateTableFinal ("DateTime" datetime, IntervalDurationMilliseconds int, InWhiteStateMilliseconds int,

InGreenStateMilliseconds int, InYellowStateMilliseconds int, InRedStateMilliseconds int, InDisabledStateMilliseconds int, 

InPlannedMaintenanceMilliseconds int, InUnplannedMaintenanceMilliseconds int, HealthServiceUnavailableMilliseconds int,

MEMonitorRowId int, MEDGRowId int, MEDGName nvarchar(max) COLLATE database_default, MEDGDefaultName nvarchar(max) COLLATE database_default, meSiteName nvarchar(max) COLLATE database_default) --StateType: 0 - DG, 1 - application



-- DATA AGGREGATION

IF (@AggregationType = 0 OR @AggregationType = -1) --HOURLY or RAW

BEGIN 

	INSERT INTO #StateTableFinal

	SELECT A."DateTime", A.IntervalDurationMilliseconds, A.InWhiteStateMilliseconds, A.InGreenStateMilliseconds, A.InYellowStateMilliseconds, A.InRedStateMilliseconds,

		A.InDisabledStateMilliseconds, A.InPlannedMaintenanceMilliseconds, A.InUnplannedMaintenanceMilliseconds, A.HealthServiceUnavailableMilliseconds,

		A.MEMonitorRowId, A.MEDGRowId, A.MEDGName, A.MEDGDefaultName, A.meSiteName

	FROM #StateTable A 

END



IF @AggregationType = 1 --DAILY

BEGIN

	INSERT INTO #StateTableFinal

	SELECT  convert(datetime,CONVERT(varchar,DATEPART(month, A.[DateTime]))+'/'+CONVERT(varchar,DATEPART(day, A.[DateTime]))+'/'+CONVERT(varchar,DATEPART(yyyy, A.[DateTime]))), 

		SUM(A.IntervalDurationMilliseconds), SUM(A.InWhiteStateMilliseconds), SUM(A.InGreenStateMilliseconds), SUM(A.InYellowStateMilliseconds), SUM(A.InRedStateMilliseconds),

		SUM(A.InDisabledStateMilliseconds), SUM(A.InPlannedMaintenanceMilliseconds), SUM(A.InUnplannedMaintenanceMilliseconds), SUM(A.HealthServiceUnavailableMilliseconds),

		A.MEMonitorRowId, A.MEDGRowId, A.MEDGName, A.MEDGDefaultName, A.meSiteName

	FROM #StateTable A 

	GROUP BY DATEPART(yyyy, A.[DateTime]), DATEPART(month, A.[DateTime]), DATEPART(day, A.[DateTime]),

		A.MEMonitorRowId, A.MEDGRowId, A.MEDGName, A.MEDGDefaultName, A.meSiteName

END



IF @AggregationType = 2 --MONTHLY

BEGIN

	INSERT INTO #StateTableFinal

	SELECT  convert(datetime,CONVERT(varchar,DATEPART(month, A.[DateTime]))+'/1/'+CONVERT(varchar,DATEPART(yyyy, A.[DateTime]))), 

		SUM(A.IntervalDurationMilliseconds), SUM(A.InWhiteStateMilliseconds), SUM(A.InGreenStateMilliseconds), SUM(A.InYellowStateMilliseconds), SUM(A.InRedStateMilliseconds),

		SUM(A.InDisabledStateMilliseconds), SUM(A.InPlannedMaintenanceMilliseconds), SUM(A.InUnplannedMaintenanceMilliseconds), SUM(A.HealthServiceUnavailableMilliseconds),

		A.MEMonitorRowId, A.MEDGRowId, A.MEDGName, A.MEDGDefaultName, A.meSiteName

	FROM #StateTable A 

	GROUP BY DATEPART(yyyy, A.[DateTime]), DATEPART(month, A.[DateTime]),

	A.MEMonitorRowId, A.MEDGRowId, A.MEDGName, A.MEDGDefaultName, A.meSiteName

END



IF @AggregationType = 3 --YEARLY

BEGIN

	INSERT INTO #StateTableFinal

	SELECT  convert(datetime,'1/1/'+CONVERT(varchar,DATEPART(yyyy, A.[DateTime]))), 

		SUM(A.IntervalDurationMilliseconds), SUM(A.InWhiteStateMilliseconds), SUM(A.InGreenStateMilliseconds), SUM(A.InYellowStateMilliseconds), SUM(A.InRedStateMilliseconds),

		SUM(A.InDisabledStateMilliseconds), SUM(A.InPlannedMaintenanceMilliseconds), SUM(A.InUnplannedMaintenanceMilliseconds), SUM(A.HealthServiceUnavailableMilliseconds),

		A.MEMonitorRowId, A.MEDGRowId, A.MEDGName, A.MEDGDefaultName, A.meSiteName

	FROM #StateTable A 

	GROUP BY DATEPART(yyyy, A.[DateTime]),

	A.MEMonitorRowId, A.MEDGRowId, A.MEDGName, A.MEDGDefaultName, A.meSiteName

END



select * from #StateTableFinal

order by #StateTableFinal.DateTime asc



DROP TABLE #ManagedEntityDG

DROP TABLE #DGs

DROP TABLE #StateTable

DROP TABLE #StateTableFinal

DROP TABLE #ManagedEntityMonitorsDGs





GO



/* 	=============================================================== 

	PERMISSIONS 

	===============================================================  */

	

GRANT EXECUTE ON dbo.[ComtradeMPXAXD_DGAvailability_DataGet] TO OpsMgrReader 



GO