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

Element properties:

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

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

BEGIN

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

END

GO







ALTER PROCEDURE [dbo].[ComtradeMPXAXD_LogonDurationDistribution_DataGet]

@StartDate_BaseValue datetime,

@EndDate_BaseValue datetime,

@ObjectList xml, --= NULL,

@ObjectType nvarchar(64), --= 'Farm', -- Farm, Server

@StartDate_BaseValueGMT datetime,

@ReportType nvarchar(64)



AS



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



--CREATE TABLE #ManagedEntitySite (ManagedEntityRowId int)

CREATE TABLE #ManagedEntityDG (ManagedEntityRowId int, Id nvarchar(255), Name nvarchar(255), SiteId int)





CREATE TABLE #ManagedEntity(ManagedEntityRowId int)

INSERT INTO #ManagedEntity(ManagedEntityRowId)



EXECUTE @ExecError = [Microsoft_SystemCenter_DataWarehouse_Report_Library_ReportObjectListParse]

@ObjectList = @ObjectList,

@StartDate = @StartDate_BaseValue, 

@EndDate = @EndDate_BaseValue,

@ContainmentLevelCount = 0,

@ContainmentStartLevel = 0



	INSERT INTO #ManagedEntityDG

	SELECT  meDG.ManagedEntityRowId DG_RowId, meDG.Name DG_Id, meDG.DisplayName DG_Name, meSite.ManagedEntityRowId

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

		INNER JOIN #ManagedEntity on #ManagedEntity.ManagedEntityRowId = meDG.ManagedEntityRowId OR #ManagedEntity.ManagedEntityRowId = meSite.ManagedEntityRowId





	CREATE TABLE #Temp ("NumOrder" int, "Occurences" int, "LoginTime" nvarchar(255), "DeliveryGroup" nvarchar(255), "SiteId" int, "DGid" int, "SiteName" nvarchar(255))



	DECLARE @execStr nvarchar(max)



	SET @execStr = '

	BEGIN WITH

IntervalCount AS (

		SELECT 

		CASE

		WHEN [CompleteLogonTime] <= 5000 THEN ''0-5''

		WHEN [CompleteLogonTime] > 5000 AND [CompleteLogonTime] <= 10000 THEN ''5-10''

		WHEN [CompleteLogonTime] > 10000 AND [CompleteLogonTime] <= 15000 THEN ''10-15''

		WHEN [CompleteLogonTime] > 15000 AND [CompleteLogonTime] <= 20000 THEN ''15-20''

		WHEN [CompleteLogonTime] > 20000 AND [CompleteLogonTime] <= 25000 THEN ''20-25''

		WHEN [CompleteLogonTime] > 25000 AND [CompleteLogonTime] <= 30000 THEN ''25-30''

		WHEN [CompleteLogonTime] > 30000 AND [CompleteLogonTime] <= 35000 THEN ''30-35''

		WHEN [CompleteLogonTime] > 35000 AND [CompleteLogonTime] <= 40000 THEN ''35-40''

		WHEN [CompleteLogonTime] > 40000 AND [CompleteLogonTime] <= 45000 THEN ''40-45''

		WHEN [CompleteLogonTime] > 45000 AND [CompleteLogonTime]<= 50000 THEN ''45-50''

		WHEN [CompleteLogonTime] > 50000 AND [CompleteLogonTime] <= 55000 THEN ''50-55''

		WHEN [CompleteLogonTime] > 55000 AND [CompleteLogonTime] <= 60000 THEN ''55-60''

		WHEN [CompleteLogonTime] > 60000 THEN ''60+'' 

		END AS Label, 1 AS Occurences, logonP.StartDate, logonP.EndDate, (CompleteLogonTime/1000) AS LogonDuration, connection.ME_DeliveryGroupRowId, DG.DisplayName as DeliveryGroup, DG.Name as Id,#ManagedEntityDG.SiteId as SiteId, Site.DisplayName as SiteName

		FROM ComtradeMPXAXD.vCTXAXDLogonPerformance AS logonP

			INNER JOIN ComtradeMPXAXD.vCTXAXDConnection as connection 

		on connection.CTXAXDConnectionRowId = logonP.CTXAXDConnectionRowId



	INNER JOIN vManagedEntity as DG on connection.ME_DeliveryGroupRowId = DG.ManagedEntityRowId

	INNER JOIN #ManagedEntityDG on #ManagedEntityDG.ManagedEntityRowId = DG.ManagedEntityRowId

	INNER JOIN vManagedEntity as Site on Site.ManagedEntityRowId = #ManagedEntityDG.SiteId       

		WHERE (connection.Failure = 0 OR connection.Failure IS NULL)

		AND logonP.[StartDate] BETWEEN DATEADD(minute, ' + cast (-@GMTDiff as nvarchar(10)) + ', ''' + CONVERT(VARCHAR, @StartDate_BaseValue, 126) + ''') AND  DATEADD(minute, ' + cast (-@GMTDiff as nvarchar(10)) + ', ''' + CONVERT(VARCHAR, @EndDate_BaseValue, 126) + ''')

 

	),ManagementEntities AS (

		SELECT  

		vMEServer.DisplayName AS DeliveryGroup,

		vMEServer.FullName    AS FullName,

		vMEFarm.ManagedEntityRowId   AS SiteRowId,

		vMEFarm.DisplayName AS SiteName,

		vMEServer.ManagedEntityRowId  AS ManagedEntityRowId

		FROM #ManagedEntityDG AS baseTable

		-- Farms

		LEFT JOIN vManagedEntity  AS vMEFarm

			ON vMEFarm.ManagedEntityRowId = baseTable.SiteId

		-- Server

		LEFT JOIN vManagedEntity  AS vMEServer

			ON vMEServer.ManagedEntityRowId = baseTable.ManagedEntityRowId

	),Intervals AS (

		SELECT 0 as NumOrder, 0 as Occurences, ''0-5'' as Label, NULL AS FullName, NULL AS DisplayName

		UNION ALL SELECT 1, 0, ''5-10'',   NULL, NULL

		UNION ALL SELECT 2, 0, ''10-15'',  NULL, NULL

		UNION ALL SELECT 3, 0, ''15-20'',  NULL, NULL

		UNION ALL SELECT 4, 0, ''20-25'',  NULL, NULL

		UNION ALL SELECT 5, 0, ''25-30'',  NULL, NULL

		UNION ALL SELECT 6, 0, ''30-35'',  NULL, NULL

		UNION ALL SELECT 7, 0, ''35-40'',  NULL, NULL

		UNION ALL SELECT 8, 0, ''40-45'',  NULL, NULL

		UNION ALL SELECT 9, 0, ''45-50'',  NULL, NULL

		UNION ALL SELECT 10, 0, ''50-55'',  NULL, NULL

		UNION ALL SELECT 11, 0, ''55-60'',  NULL, NULL

		UNION ALL SELECT 12, 0, ''60+'',    NULL, NULL

	)	INSERT INTO #Temp SELECT

		ivl.NumOrder,

		ISNULL(cnt.Occurences, 0) AS Occurences,  

		cnt.Label as LoginTime,

		cnt.DeliveryGroup,

		cnt.SiteId,

		cnt.ME_DeliveryGroupRowId,

		cnt.SiteName

	FROM Intervals AS ivl

	INNER JOIN IntervalCount cnt ON cnt.label = ivl.Label

	UNION ALL 

	SELECT ivls.NumOrder,

		ivls.Occurences,  

		ivls.Label as LoginTime,

		me.DeliveryGroup,

		me.SiteRowId,

		me.ManagedEntityRowId,

		me.SiteName

	FROM ManagementEntities as me, Intervals as ivls

	ORDER BY ivl.NumOrder



	END

	'

	EXECUTE (@execStr)

--SELECT * FROM #Temp



SELECT NumOrder,LoginTime,  COUNT(DGid) -1 AS NumberOfOccurences, DGid, DeliveryGroup, SiteName, SiteId 

FROM #Temp

GROUP BY DGid, LoginTime, DeliveryGroup, SiteName, SiteId, NumOrder

ORDER BY NumOrder





DROP TABLE #Temp

DROP TABLE #ManagedEntity

DROP TABLE #ManagedEntityDG





GO



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

	PERMISSIONS 

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

	

GRANT EXECUTE ON dbo.[ComtradeMPXAXD_LogonDurationDistribution_DataGet] TO OpsMgrReader 



GO