Res.ComTrade.Citrix.XenDesktop.Reports.ReportScript.ApplicationAvailabilityStoredProcedure.Install (Resource)

Element properties:

Type	Resource
File Name	ComTrade.Citrix.XenDesktop.Reports.ReportScript.ApplicationAvailabilityStoredProcedure.Install.sql
Accessibility	Internal
File Content: ComTrade.Citrix.XenDesktop.Reports.ReportScript.ApplicationAvailabilityStoredProcedure.Install.sql

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

BEGIN

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

END

GO



ALTER PROCEDURE [dbo].[ComtradeMPXAXD_ApplicationAvailability_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)

DECLARE @ApplicationMonitorSystemName nvarchar(256)

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

SET @ApplicationMonitorSystemName = 'System.Health.AvailabilityState'



--calculate time

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 #ManagedEntityApplications (ManagedEntityRowId int)

INSERT INTO #ManagedEntityApplications



EXECUTE @ExecError = [Microsoft_SystemCenter_DataWarehouse_Report_Library_ReportObjectListParse]

@ObjectList = @ObjectList,

@StartDate = @StartDate_BaseValue_UTC ,

@EndDate = @EndDate_BaseValue_UTC 





----------------------------------------------------------------------------------------

-- #ApplicationSiteNamesFinal - get site name property for selected applications

----------------------------------------------------------------------------------------

CREATE TABLE #ApplicationSiteNamesFinalTmp ("ManagedEntityRowId" int, "PropertyDefaultName" nvarchar(256) COLLATE database_default, "PropertyValue" nvarchar(max) COLLATE database_default, "FromDateTime" datetime)

CREATE TABLE #ApplicationSiteNamesFinal ("ManagedEntityRowId" int, "ApplicationName" nvarchar(max) COLLATE database_default, "ApplicationSiteName" nvarchar(max) COLLATE database_default, "FromDateTime" datetime)

CREATE TABLE #ApplicationSiteNames ("ManagedEntityRowId" int, "PropertyDefaultName" nvarchar(256) COLLATE database_default, "PropertyValue" nvarchar(max) COLLATE database_default, "FromDateTime" datetime)

INSERT INTO #ApplicationSiteNames	 

SELECT     vManagedEntity.ManagedEntityRowId, vManagedEntityTypeProperty.PropertyDefaultName, vManagedEntityPropertySet.PropertyValue, 

           vManagedEntityPropertySet.FromDateTime

FROM     vManagedEntityTypeProperty INNER JOIN

         vManagedEntityType ON vManagedEntityTypeProperty.ManagedEntityTypeRowId = vManagedEntityType.ManagedEntityTypeRowId INNER JOIN

         vManagedEntity ON vManagedEntityType.ManagedEntityTypeRowId = vManagedEntity.ManagedEntityTypeRowId INNER JOIN

         vManagedEntityPropertySet ON vManagedEntityTypeProperty.PropertyGuid = vManagedEntityPropertySet.PropertyGuid AND 

         vManagedEntity.ManagedEntityRowId = vManagedEntityPropertySet.ManagedEntityRowId 

		 INNER JOIN #ManagedEntityApplications selectedApps ON selectedApps.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId

WHERE       

		(vManagedEntityType.ManagedEntityTypeSystemName IN ('ComTrade.Citrix.XenDesktop.Application')) AND 

		(vManagedEntityTypeProperty.PropertySystemName IN ('Name', 'SiteName')) 

             



INSERT INTO #ApplicationSiteNamesFinalTmp                                            

SELECT adn.ManagedEntityRowId, adn.PropertyDefaultName, adn.PropertyValue, adn.FromDateTime

FROM (

	SELECT PropertyDefaultName, ManagedEntityRowId, MAX(FromDateTime) as MaxFromDateTime

	FROM #ApplicationSiteNames GROUP BY PropertyDefaultName, ManagedEntityRowId

) AS x INNER JOIN #ApplicationSiteNames AS adn ON adn.ManagedEntityRowId = x.ManagedEntityRowId AND adn.PropertyDefaultName = x.PropertyDefaultName AND adn.FromDateTime = x.MaxFromDateTime



INSERT INTO #ApplicationSiteNamesFinal

SELECT distinct(AppNames.ManagedEntityRowId), AppNames.PropertyValue, SiteNames.PropertyValue, AppNames.FromDateTime

FROM	#ApplicationSiteNamesFinalTmp AS AppNames INNER JOIN

		#ApplicationSiteNamesFinalTmp AS SiteNames ON AppNames.ManagedEntityRowId = SiteNames.ManagedEntityRowId 

		AND SiteNames.PropertyDefaultName = 'Site Name' AND AppNames.PropertyDefaultName = 'Name'

DROP TABLE #ApplicationSiteNamesFinalTmp	

DROP TABLE #ApplicationSiteNames



--select * from #ApplicationSiteNamesFinal order by ManagedEntityRowId





----------------------------------------------------------------------------------------

-- get all the Delivery Groups (#ManagedEntityDGs) that host applications for a certain period of time!

----------------------------------------------------------------------------------------

CREATE TABLE #ManagedEntityDGs (MEDGRowId int, meApplicationRowId int, meApplicationName nvarchar(max) COLLATE database_default, meApplicationSiteName nvarchar(max) COLLATE database_default, FromDateTime datetime, ToDateTime datetime)



		INSERT INTO #ManagedEntityDGs

		SELECT  meDG.ManagedEntityRowId, meSelectedApps.ManagedEntityRowId, meApplications.DisplayName, AppSiteNames.ApplicationSiteName,

		DATEADD(minute, @GMTDiff, rmgApplicationToDG.FromDateTime) as "FromDateTime",               --local time

        ISNULL(DATEADD(minute, @GMTDiff, rmgApplicationToDG.ToDateTime), GETDATE()) as "ToDateTime" --local time

		FROM        vManagedEntityType AS metDG 

					INNER JOIN vManagedEntity AS meDG ON metDG.ManagedEntityTypeRowId = meDG.ManagedEntityTypeRowId 

					INNER JOIN vRelationshipType AS rtApplicationToDG 

					INNER JOIN vRelationship AS rApplicationToDG ON rtApplicationToDG.RelationshipTypeRowId = rApplicationToDG.RelationshipTypeRowId 

					INNER JOIN #ManagedEntityApplications AS meSelectedApps ON rApplicationToDG.SourceManagedEntityRowId = meSelectedApps.ManagedEntityRowId ON 

							  meDG.ManagedEntityRowId = rApplicationToDG.TargetManagedEntityRowId

					INNER JOIN vManagedEntity AS meApplications on meApplications.ManagedEntityRowId = meSelectedApps.ManagedEntityRowId

					INNER JOIN vRelationshipManagementGroup AS rmgApplicationToDG ON rmgApplicationToDG.RelationshipRowId = rApplicationToDG.RelationshipRowId	

					LEFT JOIN #ApplicationSiteNamesFinal AS AppSiteNames ON AppSiteNames.ManagedEntityRowId = meApplications.ManagedEntityRowId

		WHERE		(metDG.ManagedEntityTypeSystemName = 'ComTrade.Citrix.XenDesktop.DeliveryGroup.Shared')

					AND (rtApplicationToDG.RelationshipTypeSystemName in ('ComTrade.Citrix.XenDesktop.Application.Contains.DeliveryGroupSharedDesktopOS','ComTrade.Citrix.XenDesktop.Application.Contains.DeliveryGroupSharedServerOS'))

							  

							 

	

--select * from #ManagedEntityDGs	

--select * from #ManagedEntityApplications



DROP TABLE #ManagedEntityApplications



--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, meApplicationRowId int, meApplicationName nvarchar(max) COLLATE database_default, meApplicationSiteName nvarchar(max) COLLATE database_default, FromDateTime datetime, ToDateTime datetime)

INSERT INTO #ManagedEntityMonitorsDGs

SELECT     VMeMon.ManagedEntityMonitorRowId ,MES.MEDGRowId, MESRV.Name, MESRV.ManagedEntityDefaultName, MES.meApplicationRowId, MES.meApplicationName, MES.meApplicationSiteName,

		   MES.FromDateTime, MES.ToDateTime

FROM         vMonitor as VMon INNER JOIN

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

                      [#ManagedEntityDGs] as MES ON MES.MEDGRowId = VMeMon.ManagedEntityRowId INNER JOIN

                      vManagedEntity as MESRV ON MES.MEDGRowId = MESRV.ManagedEntityRowId 

WHERE		VMon.MonitorSystemName = @DeliveryGroupMonitorSystemName





--get all monitor IDs from applications

CREATE TABLE #ManagedEntityMonitorsApplications (MEMonitorRowId int, meApplicationRowId int, meApplicationName nvarchar(max) COLLATE database_default, meApplicationSiteName nvarchar(max) COLLATE database_default)

INSERT INTO #ManagedEntityMonitorsApplications

SELECT     DISTINCT VMeMon.ManagedEntityMonitorRowId, MES.meApplicationRowId, MES.meApplicationName, MES.meApplicationSiteName

FROM         vMonitor as VMon INNER JOIN

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

                      [#ManagedEntityDGs] as MES ON MES.meApplicationRowId = VMeMon.ManagedEntityRowId 

WHERE		VMon.MonitorSystemName = @ApplicationMonitorSystemName





--select * from #ManagedEntityMonitorsDGs --input table for the DG monitor data!

--select * from #ManagedEntityMonitorsApplications --input table for the application monitor data!



----------------------------------------------------------------------------------------------------------------  

-- GET ALL AVAILABILITY DATA HOURLY FROM THE    DELIVERY GROUPS !  

----------------------------------------------------------------------------------------------------------------

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, meApplicationRowId int, meApplicationName nvarchar(max) COLLATE database_default, meApplicationSiteName nvarchar(max) COLLATE database_default, StateType int) --StateType: 0 - DG, 1 - application

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.meApplicationRowId, MEMS.meApplicationName, MEMS.meApplicationSiteName, 0

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,MEMS.FromDateTime) AND DATEADD(minute, -@GMTDiff, MEMS.ToDateTime)

    

order by "DateTime" asc



--select * from #StateTable



----------------------------------------------------------------------------------------------------------------  

-- GET ALL AVAILABILITY DATA HOURLY FROM THE    A P P L I C A T I O N S !  

----------------------------------------------------------------------------------------------------------------

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,-1, 'N/A', 'N/A', MEMS.meApplicationRowId, MEMS.meApplicationName, MEMS.meApplicationSiteName, 1

FROM   vStateHourlyFull as vState INNER JOIN

	#ManagedEntityMonitorsApplications 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))

	

order by "DateTime" asc





----------------------------------------------------------------

-- CALCULATE BUSSINES HOURS AND DATA AGGREGATION !!!

----------------------------------------------------------------



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

InGreenStateMilliseconds bigint, InYellowStateMilliseconds bigint, InRedStateMilliseconds bigint, InDisabledStateMilliseconds bigint, 

InPlannedMaintenanceMilliseconds bigint, InUnplannedMaintenanceMilliseconds bigint, HealthServiceUnavailableMilliseconds bigint,

MEMonitorRowId int, MEDGRowId int, MEDGName nvarchar(max) COLLATE database_default, MEDGDefaultName nvarchar(max) COLLATE database_default, meApplicationRowId int, meApplicationName nvarchar(max) COLLATE database_default, meApplicationSiteName nvarchar(max) COLLATE database_default, StateType int)



DECLARE @m_monID integer

DECLARE @m_DGID integer

DECLARE @m_appID integer

DECLARE @m_stateType integer

DECLARE cur INSENSITIVE CURSOR FOR	--group by monitor [MEMonitorRowId]

									--and delivery group [MEDGRowId]

									--and application  [meApplicationRowId]

									--and statetype [StateType] availability for app or DG

select [MEMonitorRowId], [MEDGRowId], [meApplicationRowId], [StateType] from #StateTable GROUP BY [MEMonitorRowId], [MEDGRowId], [meApplicationRowId], [StateType]



OPEN cur



WHILE 1 = 1

BEGIN

FETCH cur INTO @m_monID, @m_DGID, @m_appID, @m_stateType

IF @@fetch_status <0

BREAK



	CREATE TABLE #StateTablePart ("DateTime" datetime, IntervalDurationMilliseconds bigint, InWhiteStateMilliseconds bigint,

InGreenStateMilliseconds bigint, InYellowStateMilliseconds bigint, InRedStateMilliseconds bigint, InDisabledStateMilliseconds bigint, 

InPlannedMaintenanceMilliseconds bigint, InUnplannedMaintenanceMilliseconds bigint, HealthServiceUnavailableMilliseconds bigint,

MEMonitorRowId int, MEDGRowId int, MEDGName nvarchar(max) COLLATE database_default, MEDGDefaultName nvarchar(max) COLLATE database_default, meApplicationRowId int, meApplicationName nvarchar(max) COLLATE database_default,  meApplicationSiteName nvarchar(max) COLLATE database_default, StateType int)

	

	----------------------------------------------------------------

	-- BUSSINES HOURS!!!

	IF @BusinessTimeType = 1  --business hours

		BEGIN

			INSERT INTO #StateTablePart 

			SELECT * FROM #StateTable st WHERE st.MEMonitorRowId = @m_monID AND st.MEDGRowId = @m_DGID AND st.meApplicationRowId = @m_appID AND st.StateType =  @m_stateType

				and st.[DateTime] - CAST(FLOOR(CAST(st.[DateTime] AS float)) AS datetime) >= @BH_start --start hours

				and st.[DateTime] - CAST(FLOOR(CAST(st.[DateTime] AS float)) AS datetime) < @BH_end   --end hours

				and ((DATEPART(WEEKDAY,  st.[DateTime]) = 1 and @BusinessDay1 = 1)        --monday

						OR (DATEPART(WEEKDAY,  st.[DateTime]) = 2 and @BusinessDay2 = 1)  --tuesday

						OR (DATEPART(WEEKDAY,  st.[DateTime]) = 3 and @BusinessDay3 = 1)  --wednesday

						OR (DATEPART(WEEKDAY,  st.[DateTime]) = 4 and @BusinessDay4 = 1)  --thursday

						OR (DATEPART(WEEKDAY,  st.[DateTime]) = 5 and @BusinessDay5 = 1)  --friday

						OR (DATEPART(WEEKDAY,  st.[DateTime]) = 6 and @BusinessDay6 = 1)  --saturday

						OR (DATEPART(WEEKDAY,  st.[DateTime]) = 7 and @BusinessDay7 = 1)) --sunday

		END

	ELSE

		BEGIN

			INSERT INTO #StateTablePart 

			SELECT * FROM #StateTable st WHERE st.MEMonitorRowId = @m_monID AND st.MEDGRowId = @m_DGID AND st.meApplicationRowId = @m_appID AND st.StateType =  @m_stateType

		END	



	----------------------------------------------------------------

	-- DATA AGGREGATION !!!

	

	--at least 1 rows are needed to show graph

	IF cast((select COUNT(*) from #StateTablePart) as int) > 0 

	BEGIN 

		  

		-- 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.meApplicationRowId ,A.meApplicationName, A.meApplicationSiteName, A.StateType

			FROM #StateTablePart 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.meApplicationRowId ,A.meApplicationName, A.meApplicationSiteName, A.StateType

			FROM #StateTablePart A 

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

				A.MEMonitorRowId, A.MEDGRowId, A.MEDGName, A.MEDGDefaultName, A.meApplicationRowId ,A.meApplicationName, A.meApplicationSiteName, A.StateType

		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.meApplicationRowId ,A.meApplicationName, A.meApplicationSiteName, A.StateType

			FROM #StateTablePart A 

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

			A.MEMonitorRowId, A.MEDGRowId, A.MEDGName, A.MEDGDefaultName, A.meApplicationRowId ,A.meApplicationName, A.meApplicationSiteName, A.StateType

		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.meApplicationRowId ,A.meApplicationName, A.meApplicationSiteName, A.StateType

			FROM #StateTablePart A 

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

			A.MEMonitorRowId, A.MEDGRowId, A.MEDGName, A.MEDGDefaultName, A.meApplicationRowId ,A.meApplicationName, A.meApplicationSiteName, A.StateType

		END



	END

	

	DROP TABLE #StateTablePart

END



DEALLOCATE cur





SELECT * FROM #StateTableFinal





DROP TABLE #ApplicationSiteNamesFinal

DROP TABLE #StateTableFinal

DROP TABLE #StateTable

DROP TABLE #ManagedEntityMonitorsDGs

DROP TABLE #ManagedEntityMonitorsApplications

DROP TABLE #ManagedEntityDGs 







GO



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

	PERMISSIONS 

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

	

GRANT EXECUTE ON dbo.[ComtradeMPXAXD_ApplicationAvailability_DataGet] TO OpsMgrReader 



GO