Res.ComTrade.Citrix.XenDesktop.Reports.ReportScript.ServerPerformanceProcedure.Upgrade (Resource)

Element properties:

Type	Resource
File Name	ComTrade.Citrix.XenDesktop.Reports.ReportScript.ServerPerformanceProcedure.Upgrade.sql
Accessibility	Internal
File Content: ComTrade.Citrix.XenDesktop.Reports.ReportScript.ServerPerformanceProcedure.Upgrade.sql



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

BEGIN

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

END

GO



ALTER PROCEDURE [dbo].[ComtradeMPXAXD_ServerPerformance_DataGet] 

@StartDate_BaseValue  datetime,

@EndDate_BaseValue  datetime,

@ObjectList xml, -- servers

@DataAggregation 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

CREATE TABLE #ManagedEntityServers (ManagedEntityRowId int)

INSERT INTO #ManagedEntityServers

EXECUTE @ExecError = [Microsoft_SystemCenter_DataWarehouse_Report_Library_ReportObjectListParse]

@ObjectList = @ObjectList,

@StartDate = @StartDate_BaseValue ,

@EndDate = @EndDate_BaseValue 





--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

DECLARE @BH_end datetime   --business hour end 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))



--select @BH_start as BH_start, @BH_end as BH_end, @GMTDiff as GMTDiff, @StartDate_BaseValue as StartDate_BaseValue, @EndDate_BaseValue as EndDate_BaseValue



--site and delivery group

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

insert into #DGs

Select  meDG.ManagedEntityRowId DG_RowId, meDG.Name DG_Id, meDG.DisplayName DG_Name,

              meSite.ManagedEntityRowId Site_RowId, meSite.Name Site_Id, meSite.DisplayName Site_Name

from                 vManagedEntity meSite

       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')



CREATE TABLE #DistinctServerNames ("ManagedEntityRowId" int, "ManagedEntityTypeSystemName" nvarchar(256) COLLATE database_default, "ManagedEntityDefaultName" nvarchar(max) COLLATE database_default, "FromDateTime" datetime)

INSERT INTO #DistinctServerNames	 

SELECT   distinct vManagedEntity.ManagedEntityRowId, vManagedEntityType.ManagedEntityTypeSystemName, vManagedEntity.ManagedEntityDefaultName, 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 AND

		 vManagedEntity.ManagedEntityRowId IN (select * from #ManagedEntityServers)

WHERE      (vManagedEntityType.ManagedEntityTypeSystemName IN ('ComTrade.Citrix.XenDesktop.ServerOSMachine'))		   



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

-- get all the servers 

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

CREATE TABLE #ManagedEntityServersFinal (MEServerRowId int, ServerDNS nvarchar(256) COLLATE database_default, FromDateTime datetime, ToDateTime datetime)

INSERT INTO #ManagedEntityServersFinal

SELECT distinct MESERV.CTXAXDMachineRowId, MESERV.DNSName, @StartDate_BaseValue, @EndDate_BaseValue 

FROM #DistinctServerNames as SN INNER JOIN [ComtradeMPXAXD].[vCTXAXDMachine] AS MESERV ON SN.ManagedEntityDefaultName = MESERV.DNSName



DROP TABLE #ManagedEntityServers



CREATE TABLE #PerfTable ("DateTime" datetime, "CTXAXDMachineRowId" int, "SampleCount" int, "CpuUtil" float, "PhysicalMemUtil" float,

 "VirtualMemUtil" float, "PhysicalMem" int, "VirtualMem" int, "PageFaultsPerSec" float, "NetworkRead" float, "NetworkWrite" float,

 "NetworkQueue" float, "DiskTransfersPerSec" float, "DiskRead" int, "DiskWrite" int, "DiskQueue" float, "DiskLatency" int,

 "CitrixLatency" int, "CitrixRTT" int, "SessionCount" int, "Load" float, "DNSName" nvarchar(256) COLLATE database_default, "OSType" nvarchar(256) COLLATE database_default,

 "SiteName" nvarchar(256) COLLATE database_default, "DGName" nvarchar(256) COLLATE database_default)



IF @BusinessTimeType = 1  --business hours

	BEGIN

		IF (@DataAggregation = 5) --RAW DATA

		BEGIN 

			INSERT INTO #PerfTable 

			SELECT     

				DATEADD(minute, @GMTDiff, vPerf.DateTime)

				,vPerf.[CTXAXDMachineRowId]

				,1 as [SampleCount]

				,vPerf.[CpuUtil]

				,vPerf.[PhysicalMemUtil]

				,vPerf.[VirtualMemUtil]

				,vPerf.[PhysicalMem]

				,vPerf.[VirtualMem]

				,vPerf.[PageFaultsPerSec]

				,vPerf.[NetworkRead]

				,vPerf.[NetworkWrite]

				--,vPerf.[NetworkQueue]

				,CASE WHEN vPerf.[NetworkQueue] IS NULL THEN 0 ELSE vPerf.[NetworkQueue] END

				,vPerf.[DiskTransfersPerSec]

				,vPerf.[DiskRead]

				,vPerf.[DiskWrite]

				,vPerf.[DiskQueue]

				,vPerf.[DiskLatency]

				--,vPerf.[CitrixLatency]

				,CASE WHEN vPerf.[CitrixLatency] IS NULL THEN 0 ELSE vPerf.[CitrixLatency] END

				--,vPerf.[CitrixRTT]

				,CASE WHEN vPerf.[CitrixRTT] IS NULL THEN 0 ELSE vPerf.[CitrixRTT] END

				,vPerf.[SessionCount]

				,vPerf.[Load]

				,MESERV.DNSName, 

				MESERV.OSType,

				DG.Site_Name,

				DG.DG_Name

				--FROM [ComtradeMPXAXD].[vCTXAXDMachinePerformanceRaw] AS vPerf INNER JOIN

				--[ComtradeMPXAXD].[vCTXAXDMachine] AS MESERV ON vPerf.[CTXAXDMachineRowId] = MESERV.[CTXAXDMachineRowId]

				--WHERE vPerf.DateTime >= DATEADD(minute, -@GMTDiff, @StartDate_BaseValue)

				--AND vPerf.DateTime < DATEADD(minute, -@GMTDiff, @EndDate_BaseValue)

				--AND vPerf.CTXAXDMachineRowId IN (select #ManagedEntityServersFinal.MEServerRowId from #ManagedEntityServersFinal)

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

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

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

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

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

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

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

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

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

				--ORDER BY  vPerf.DateTime

				FROM [ComtradeMPXAXD].[vCTXAXDMachinePerformanceRaw] AS vPerf INNER JOIN

					 [ComtradeMPXAXD].[vCTXAXDMachine] AS MESERV ON vPerf.[CTXAXDMachineRowId] = MESERV.[CTXAXDMachineRowId] INNER JOIN

					 #DGs as DG on MESERV.ME_DeliveryGroupRowId = DG.DG_RowId

				WHERE vPerf.DateTime >= DATEADD(minute, -@GMTDiff, @StartDate_BaseValue)

					AND vPerf.DateTime < DATEADD(minute, -@GMTDiff, @EndDate_BaseValue)

					AND vPerf.CTXAXDMachineRowId IN (select #ManagedEntityServersFinal.MEServerRowId from #ManagedEntityServersFinal)

				ORDER BY  vPerf.DateTime

		END

		ELSE

		BEGIN

			--HOURLY DATA

			INSERT INTO #PerfTable 

			SELECT     

			DATEADD(minute, @GMTDiff, vPerf.DateTime)

			,vPerf.[CTXAXDMachineRowId]

			,vPerf.[SampleCount]

			,vPerf.[CpuUtil]

			,vPerf.[PhysicalMemUtil]

			,vPerf.[VirtualMemUtil]

			,vPerf.[PhysicalMem]

			,vPerf.[VirtualMem]

			,vPerf.[PageFaultsPerSec]

			,vPerf.[NetworkRead]

			,vPerf.[NetworkWrite]

			--,vPerf.[NetworkQueue]

			,CASE WHEN vPerf.[NetworkQueue] IS NULL THEN 0 ELSE vPerf.[NetworkQueue] END

			,vPerf.[DiskTransfersPerSec]

			,vPerf.[DiskRead]

			,vPerf.[DiskWrite]

			,vPerf.[DiskQueue]

			,vPerf.[DiskLatency]

			--,vPerf.[CitrixLatency]

			,CASE WHEN vPerf.[CitrixLatency] IS NULL THEN 0 ELSE vPerf.[CitrixLatency] END

			--,vPerf.[CitrixRTT]

			,CASE WHEN vPerf.[CitrixRTT] IS NULL THEN 0 ELSE vPerf.[CitrixRTT] END

			,vPerf.[SessionCount]

			,vPerf.[Load]

			,MESERV.DNSName, 

			MESERV.OSType,

			DG.Site_Name,

			DG.DG_Name

			--FROM [ComtradeMPXAXD].[vCTXAXDMachinePerformanceHourly] AS vPerf INNER JOIN

			--	 [ComtradeMPXAXD].[vCTXAXDMachine] AS MESERV ON vPerf.[CTXAXDMachineRowId] = MESERV.[CTXAXDMachineRowId]

			--WHERE vPerf.DateTime >= DATEADD(minute, -@GMTDiff, @StartDate_BaseValue)

			--	AND vPerf.DateTime < DATEADD(minute, -@GMTDiff, @EndDate_BaseValue)

			--	AND vPerf.CTXAXDMachineRowId IN (select #ManagedEntityServersFinal.MEServerRowId from #ManagedEntityServersFinal)

			--	--AND --SAMO V ODSEKIH

			--	--vPerf.DateTime BETWEEN DATEADD(minute, -@GMTDiff, MESERV.FromDateTime)

			--	--AND DATEADD(minute, -@GMTDiff, MESERV.ToDateTime)

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

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

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

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

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

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

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

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

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

			--ORDER BY  vPerf.DateTime

			FROM [ComtradeMPXAXD].[vCTXAXDMachinePerformanceHourly] AS vPerf INNER JOIN

				 [ComtradeMPXAXD].[vCTXAXDMachine] AS MESERV ON vPerf.[CTXAXDMachineRowId] = MESERV.[CTXAXDMachineRowId] INNER JOIN

				 #DGs as DG on MESERV.ME_DeliveryGroupRowId = DG.DG_RowId

			WHERE vPerf.DateTime >= DATEADD(minute, -@GMTDiff, @StartDate_BaseValue)

				AND vPerf.DateTime < DATEADD(minute, -@GMTDiff, @EndDate_BaseValue)

				AND vPerf.CTXAXDMachineRowId IN (select #ManagedEntityServersFinal.MEServerRowId from #ManagedEntityServersFinal)

			ORDER BY  vPerf.DateTime

		END		

	END

ELSE

	BEGIN

		IF (@DataAggregation = 5) --RAW DATA

		BEGIN

			INSERT INTO #PerfTable

			SELECT     

			DATEADD(minute, @GMTDiff, vPerf.DateTime)

			,vPerf.[CTXAXDMachineRowId]

			,1 as [SampleCount]

			,vPerf.[CpuUtil]

			,vPerf.[PhysicalMemUtil]

			,vPerf.[VirtualMemUtil]

			,vPerf.[PhysicalMem]

			,vPerf.[VirtualMem]

			,vPerf.[PageFaultsPerSec]

			,vPerf.[NetworkRead]

			,vPerf.[NetworkWrite]

			--,vPerf.[NetworkQueue]

			,CASE WHEN vPerf.[NetworkQueue] IS NULL THEN 0 ELSE vPerf.[NetworkQueue] END

			,vPerf.[DiskTransfersPerSec]

			,vPerf.[DiskRead]

			,vPerf.[DiskWrite]

			,vPerf.[DiskQueue]

			,vPerf.[DiskLatency]

			--,vPerf.[CitrixLatency]

			,CASE WHEN vPerf.[CitrixLatency] IS NULL THEN 0 ELSE vPerf.[CitrixLatency] END

			--,vPerf.[CitrixRTT]

			,CASE WHEN vPerf.[CitrixRTT] IS NULL THEN 0 ELSE vPerf.[CitrixRTT] END

			,vPerf.[SessionCount]

			,vPerf.[Load]

			,MESERV.DNSName, 

			MESERV.OSType,

			DG.Site_Name,

			DG.DG_Name

			--[ComtradeMPXAXD].[vCTXAXDMachinePerformanceRaw]

			FROM [ComtradeMPXAXD].[vCTXAXDMachinePerformanceRaw] AS vPerf INNER JOIN

				 [ComtradeMPXAXD].[vCTXAXDMachine] AS MESERV ON vPerf.[CTXAXDMachineRowId] = MESERV.[CTXAXDMachineRowId] INNER JOIN

				 #DGs as DG on MESERV.ME_DeliveryGroupRowId = DG.DG_RowId

			WHERE vPerf.DateTime >= DATEADD(minute, -@GMTDiff, @StartDate_BaseValue)

				AND vPerf.DateTime < DATEADD(minute, -@GMTDiff, @EndDate_BaseValue)

				AND vPerf.CTXAXDMachineRowId IN (select #ManagedEntityServersFinal.MEServerRowId from #ManagedEntityServersFinal)

			ORDER BY  vPerf.DateTime

		END

		ELSE

		BEGIN

			INSERT INTO #PerfTable

			SELECT     

			DATEADD(minute, @GMTDiff, vPerf.DateTime)

			,vPerf.[CTXAXDMachineRowId]

			,vPerf.[SampleCount]

			,vPerf.[CpuUtil]

			,vPerf.[PhysicalMemUtil]

			,vPerf.[VirtualMemUtil]

			,vPerf.[PhysicalMem]

			,vPerf.[VirtualMem]

			,vPerf.[PageFaultsPerSec]

			,vPerf.[NetworkRead]

			,vPerf.[NetworkWrite]

			--,vPerf.[NetworkQueue]

			,CASE WHEN vPerf.[NetworkQueue] IS NULL THEN 0 ELSE vPerf.[NetworkQueue] END

			,vPerf.[DiskTransfersPerSec]

			,vPerf.[DiskRead]

			,vPerf.[DiskWrite]

			,vPerf.[DiskQueue]

			,vPerf.[DiskLatency]

			--,vPerf.[CitrixLatency]

			,CASE WHEN vPerf.[CitrixLatency] IS NULL THEN 0 ELSE vPerf.[CitrixLatency] END

			--,vPerf.[CitrixRTT]

			,CASE WHEN vPerf.[CitrixRTT] IS NULL THEN 0 ELSE vPerf.[CitrixRTT] END

			,vPerf.[SessionCount]

			,vPerf.[Load]

			,MESERV.DNSName, 

			MESERV.OSType,

			DG.Site_Name,

			DG.DG_Name

			--[ComtradeMPXAXD].[vCTXAXDMachinePerformanceRaw]

			FROM [ComtradeMPXAXD].[vCTXAXDMachinePerformanceHourly] AS vPerf INNER JOIN

				 [ComtradeMPXAXD].[vCTXAXDMachine] AS MESERV ON vPerf.[CTXAXDMachineRowId] = MESERV.[CTXAXDMachineRowId] INNER JOIN

				 #DGs as DG on MESERV.ME_DeliveryGroupRowId = DG.DG_RowId

			WHERE vPerf.DateTime >= DATEADD(minute, -@GMTDiff, @StartDate_BaseValue)

				AND vPerf.DateTime < DATEADD(minute, -@GMTDiff, @EndDate_BaseValue)

				AND vPerf.CTXAXDMachineRowId IN (select #ManagedEntityServersFinal.MEServerRowId from #ManagedEntityServersFinal)

			ORDER BY  vPerf.DateTime

		END

	END



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

-- CALCULATE DATA AGGREGATION

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

CREATE TABLE #PerformanceTableFinal ("DateTime" datetime, "CTXAXDMachineRowId" int, "SampleCount" int, "CpuUtil" float, "PhysicalMemUtil" float,

 "VirtualMemUtil" float, "PhysicalMem" int, "VirtualMem" int, "PageFaultsPerSec" float, "NetworkRead" float, "NetworkWrite" float,

 "NetworkQueue" float, "DiskTransfersPerSec" float, "DiskRead" int, "DiskWrite" int, "DiskQueue" float, "DiskLatency" int,

 "CitrixLatency" int, "CitrixRTT" int, "SessionCount" float, "Load" float, "DNSName" nvarchar(256) COLLATE database_default, "OSType" nvarchar(256) COLLATE database_default, 

 "SiteName" nvarchar(256) COLLATE database_default, "DGName" nvarchar(256) COLLATE database_default)



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

	BEGIN 

		IF (@DataAggregation = 0 OR @DataAggregation = 5) --HOURLY OR RAW

		BEGIN 

			INSERT INTO #PerformanceTableFinal

			SELECT *

			FROM #PerfTable A 

		END



		IF @DataAggregation = 1 --DAILY

		BEGIN

			INSERT INTO #PerformanceTableFinal

			SELECT  

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

					--DATEADD(minute, @GMTDiff, A.[DateTime]),

					A.CTXAXDMachineRowId,

					AVG(A.[SampleCount]), 

					AVG(A.[CpuUtil]), 

					AVG(A.[PhysicalMemUtil]), 

					AVG(A.[VirtualMemUtil]), 

					AVG(A.[PhysicalMem]), 

					AVG(A.[VirtualMem]), 

					AVG(A.[PageFaultsPerSec]), 

					AVG(A.[NetworkRead]),

					AVG(A.[NetworkWrite]),

					AVG(A.[NetworkQueue]),

					AVG(A.[DiskTransfersPerSec]),

					AVG(A.[DiskRead]),

					AVG(A.[DiskWrite]),

					AVG(A.[DiskQueue]),

					AVG(A.[DiskLatency]),

					AVG(A.[CitrixLatency]),

					AVG(A.[CitrixRTT]),

					ROUND(AVG(CAST(A.[SessionCount] AS FLOAT)), 2),

					--A.[SessionCount],

					AVG(A.[Load]),

					A.[DNSName],

					A.[OSType],

					A.SiteName, 

					A.DGName

			FROM #PerfTable A 

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

			--A.[DateTime],

			A.CTXAXDMachineRowId, 

			--A.[SessionCount], 

			--A.[SampleCount], 

			A.[DNSName], A.[OSType], A.SiteName, A.DGName

		END



		IF @DataAggregation = 2 --MONTHLY

		BEGIN

			INSERT INTO #PerformanceTableFinal

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

					A.CTXAXDMachineRowId,

					SUM(A.[SampleCount]), 

					SUM(A.[CpuUtil])/A.[SampleCount], 

					SUM(A.[PhysicalMemUtil])/A.[SampleCount], 

					SUM(A.[VirtualMemUtil])/A.[SampleCount], 

					SUM(A.[PhysicalMem])/A.[SampleCount], 

					SUM(A.[VirtualMem])/A.[SampleCount], 

					SUM(A.[PageFaultsPerSec])/A.[SampleCount], 

					SUM(A.[NetworkRead])/A.[SampleCount],

					SUM(A.[NetworkWrite])/A.[SampleCount],

					SUM(A.[NetworkQueue])/A.[SampleCount],

					SUM(A.[DiskTransfersPerSec])/A.[SampleCount],

					SUM(A.[DiskRead])/A.[SampleCount],

					SUM(A.[DiskWrite])/A.[SampleCount],

					SUM(A.[DiskQueue])/A.[SampleCount],

					SUM(A.[DiskLatency])/A.[SampleCount],

					SUM(A.[CitrixLatency])/A.[SampleCount],

					SUM(A.[CitrixRTT])/A.[SampleCount],

					A.[SessionCount],

					SUM(A.[Load])/A.[SampleCount],

					A.[DNSName],

					A.[OSType],

					A.SiteName, 

					A.DGName

			FROM #PerfTable A 

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

			A.CTXAXDMachineRowId, A.[SessionCount], A.[SampleCount], A.[DNSName], A.[OSType], A.SiteName, A.DGName

		END

		

		IF @DataAggregation = 4 --QUARTERLY

		BEGIN

			INSERT INTO #PerformanceTableFinal

			SELECT  convert(datetime,CONVERT(varchar,(((DATEPART(q, A.[DateTime]))*3)-2))+'/1/'+CONVERT(varchar,DATEPART(yyyy, A.[DateTime]))), 

					A.CTXAXDMachineRowId,

					SUM(A.[SampleCount]), 

					SUM(A.[CpuUtil])/A.[SampleCount], 

					SUM(A.[PhysicalMemUtil])/A.[SampleCount], 

					SUM(A.[VirtualMemUtil])/A.[SampleCount], 

					SUM(A.[PhysicalMem])/A.[SampleCount], 

					SUM(A.[VirtualMem])/A.[SampleCount], 

					SUM(A.[PageFaultsPerSec])/A.[SampleCount], 

					SUM(A.[NetworkRead])/A.[SampleCount],

					SUM(A.[NetworkWrite])/A.[SampleCount],

					SUM(A.[NetworkQueue])/A.[SampleCount],

					SUM(A.[DiskTransfersPerSec])/A.[SampleCount],

					SUM(A.[DiskRead])/A.[SampleCount],

					SUM(A.[DiskWrite])/A.[SampleCount],

					SUM(A.[DiskQueue])/A.[SampleCount],

					SUM(A.[DiskLatency])/A.[SampleCount],

					SUM(A.[CitrixLatency])/A.[SampleCount],

					SUM(A.[CitrixRTT])/A.[SampleCount],

					A.[SessionCount],

					SUM(A.[Load])/A.[SampleCount],

					A.[DNSName],

					A.[OSType], 

					A.SiteName, 

					A.DGName

			FROM #PerfTable A 

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

			A.CTXAXDMachineRowId, A.[SessionCount], A.[SampleCount], A.[DNSName], A.[OSType], A.SiteName, A.DGName

		END



		IF @DataAggregation = 3 --YEARLY

		BEGIN

			INSERT INTO #PerformanceTableFinal

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

					A.CTXAXDMachineRowId,

					SUM(A.[SampleCount]), 

					SUM(A.[CpuUtil])/A.[SampleCount], 

					SUM(A.[PhysicalMemUtil])/A.[SampleCount], 

					SUM(A.[VirtualMemUtil])/A.[SampleCount], 

					SUM(A.[PhysicalMem])/A.[SampleCount], 

					SUM(A.[VirtualMem])/A.[SampleCount], 

					SUM(A.[PageFaultsPerSec])/A.[SampleCount], 

					SUM(A.[NetworkRead])/A.[SampleCount],

					SUM(A.[NetworkWrite])/A.[SampleCount],

					SUM(A.[NetworkQueue])/A.[SampleCount],

					SUM(A.[DiskTransfersPerSec])/A.[SampleCount],

					SUM(A.[DiskRead])/A.[SampleCount],

					SUM(A.[DiskWrite])/A.[SampleCount],

					SUM(A.[DiskQueue])/A.[SampleCount],

					SUM(A.[DiskLatency])/A.[SampleCount],

					SUM(A.[CitrixLatency])/A.[SampleCount],

					SUM(A.[CitrixRTT])/A.[SampleCount],

					A.[SessionCount],

					SUM(A.[Load])/A.[SampleCount],

					A.[DNSName],

					A.[OSType], 

					A.SiteName, 

					A.DGName

			FROM #PerfTable A 

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

			A.CTXAXDMachineRowId, A.[SessionCount], A.[SampleCount], A.[DNSName], A.[OSType], A.SiteName, A.DGName

		END

	END



select pf.[DateTime] as [DateTime],

pf.CTXAXDMachineRowId as CTXAXDMachineRowId,

pf.SampleCount as SampleCount,

pf.CpuUtil as CpuUtil,

pf.PhysicalMemUtil as PhysicalMemUtil,

pf.VirtualMemUtil as VirtualMemUtil,

pf.PhysicalMem as PhysicalMem,

pf.VirtualMem as VirtualMem,

pf.PageFaultsPerSec as PageFaults,

pf.NetworkRead as NetworkRead,

pf.NetworkWrite as NetworkWrite,

pf.NetworkQueue as NetworkQueue,

pf.DiskTransfersPerSec as DiscTransferPerSec,

pf.DiskRead as DiskRead,

pf.DiskWrite as DiskWrite,

pf.DiskQueue as DiskQueue,

pf.DiskLatency as AvgDiskSecPerTransfer,

pf.CitrixLatency as ICALatency,

pf.CitrixRTT as CitrixRTT,

pf.SessionCount as SessionCount,

pf.[Load] as [Load],

pf.DNSName as DNSName,

pf.OSType as OSType,

pf.SiteName as SiteName,

pf.DGName as DGName

from #PerformanceTableFinal as pf

order by pf.DateTime



DROP table #DGs

DROP TABLE #PerformanceTableFinal

DROP TABLE #PerfTable

DROP TABLE #ManagedEntityServersFinal 

DROP TABLE #DistinctServerNames



GO



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

	PERMISSIONS 

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

	

GRANT EXECUTE ON dbo.[ComtradeMPXAXD_ServerPerformance_DataGet] TO OpsMgrReader 



GO