Res.Comtrade.Citrix.XenDesktop.EndUser.Reports.ReportScript.UserDesktopUsage_SessionPerformance.Upgrade (Resource)

Element properties:

File Content: Comtrade.Citrix.XenDesktop.EndUser.Reports.ReportScript.UserDesktopUsage_SessionPerformance.Upgrade.sql

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

BEGIN

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

END

GO



ALTER PROCEDURE [dbo].[ComtradeMPXAXD_UserDesktopUsage_SessionPerformance_DataGet] 

@StartDate_BaseValue  datetime,

@EndDate_BaseValue  datetime,

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

@CTXAXDSessionRowId int



AS





/*

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

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

--- VARIABLE INIT

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

DECLARE @StartDate_BaseValue  datetime

DECLARE @EndDate_BaseValue  datetime

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

DECLARE @BusinessDay1 int --monday

DECLARE @BusinessDay2 int --tuesday

DECLARE @BusinessDay3 int --wednesday

DECLARE @BusinessDay4 int --thursday

DECLARE @BusinessDay5 int --friday

DECLARE @BusinessDay6 int --saturday

DECLARE @BusinessDay7 int --sunday

DECLARE @StartDate_BaseValueGMT datetime

DECLARE @CTXAXDSessionRowId int



SET @StartDate_BaseValue =    '2014-01-04 08:00:00.000'

SET @EndDate_BaseValue =      '2014-10-07 08:00:00.000'

SET @StartDate_BaseValueGMT = '2014-01-04 07:00:00.000'

SET @BusinessDay1 = 0

SET @BusinessDay2 = 0

SET @BusinessDay3 = 0

SET @BusinessDay4 = 0

SET @BusinessDay5 = 0

SET @BusinessDay6 = 0

SET @BusinessDay7 = 0

SET @CTXAXDSessionRowId = 2

--SET @ObjectList = '<Data><Objects><Object Use="Self">363</Object></Objects></Data>'



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

--- END VARIABLE INIT

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

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

*/





DECLARE @ExecError int



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





--get session performance!



CREATE TABLE #SessionPerformance (CTXAXDSessionRowId int, [DateTime] datetime, CpuUtil real, PhysicalMem int, PhysicalMemUtil real,

VirtualMem int, VirtualMemUtil real, PageFaultsPerSec real, NetworkRead real, NetworkWrite real, CitrixNetworkInput real, CitrixNetworkOutput real, 

CitrixLatency int, CitrixRTT int, DiskTransfersPerSec real, DiskRead int, DiskWrite int, DiskLatency int)



INSERT INTO #SessionPerformance

SELECT sPerf.CTXAXDSessionRowId, 

	DATEADD(minute, @GMTDiff, sPerf.[DateTime]) as [DateTime],

	sPerf.CpuUtil  as CpuUtil,

	sPerf.PhysicalMem,

	sPerf.PhysicalMemUtil, 

	sPerf.VirtualMem,

	sPerf.VirtualMemUtil,

	sPerf.PageFaultsPerSec,

	sPerf.NetworkRead,

	sPerf.NetworkWrite,

	sPerf.CitrixNetworkInput,

	sPerf.CitrixNetworkOutput,

	sPerf.CitrixLatency,

	sPerf.CitrixRTT,

	sPerf.DiskTransfersPerSec, 

	sPerf.DiskRead, 

	sPerf.DiskWrite, 

	sPerf.DiskLatency

FROM ComtradeMPXAXD.vCTXAXDSessionPerformanceRaw/*Hourly*/ sPerf

WHERE	sPerf.[DateTime] >= DATEADD(minute, -@GMTDiff, @StartDate_BaseValue)

		and sPerf.[DateTime] < DATEADD(minute, -@GMTDiff, @EndDate_BaseValue)

		and sPerf.CTXAXDSessionRowId = @CTXAXDSessionRowId 

ORDER BY sPerf.[DateTime] asc



	IF @BusinessTimeType = 1  --business hours

		BEGIN

			

			SELECT * FROM #SessionPerformance pt WHERE

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

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

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

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

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

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

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

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

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

		END

	ELSE

		BEGIN

			SELECT * FROM #SessionPerformance

		END	



DROP TABLE #SessionPerformance





GO



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

	PERMISSIONS 

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

	

GRANT EXECUTE ON dbo.[ComtradeMPXAXD_UserDesktopUsage_SessionPerformance_DataGet] TO OpsMgrReader 



GO