Res.Comtrade.Citrix.XenDesktop.EndUser.Reports.ReportScript.FailedConnection.Install (Resource)

Element properties:

File Content: Comtrade.Citrix.XenDesktop.EndUser.Reports.ReportScript.FailedConnection.Install.sql

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

BEGIN

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

END

GO







ALTER PROCEDURE [dbo].[ComtradeMPXAXD_FailedConnections_DataGet] 

@StartDate_BaseValue datetime,

@EndDate_BaseValue datetime,

@ObjectList xml,

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

@StartDate_BaseValueGMT datetime,

@UserNamesText nvarchar(max) = '',

@FailureType nvarchar(max) = '',

@UserNameId nvarchar(max) = ''



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

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



--SELECT * FROM #ManagedEntityDG

	

CREATE TABLE #FailureTypes("TypeId" int)

DECLARE @P3 int

DECLARE @P4 int

SET @P3 = CharIndex(',',@FailureType,0)

SET @P4 = 1

WHILE @P3 > 0

BEGIN

	INSERT INTO #FailureTypes(TypeId) VALUES(Substring(@FailureType,@P4,@P3-@P4))

	SET @P4 = @P3 + 1

	SET @P3 = CharIndex(',',@FailureType,@P3+1)

END

INSERT INTO #FailureTypes(TypeId) VALUES(Substring(@FailureType,@P4,DataLength(@FailureType)))



--SELECT * FROM #FailureTypes



CREATE TABLE #UserNames("UserNameId" int, "UserName" nvarchar(255) COLLATE database_default)

INSERT INTO #UserNames

EXECUTE @ExecError = [ComtradeMPXAXD_SessionUserNames_DataGet]



--CONVERT csv string to table

CREATE TABLE #UserIds("UserNameId" int)

DECLARE @P1 int

DECLARE @P2 int



IF @UserNameId != ''

BEGIN



SET @P1 = CharIndex(',',@UserNameId,0)

SET @P2 = 1

WHILE @P1 > 0

BEGIN

	INSERT INTO #UserIds(UserNameId) VALUES(Substring(@UserNameId,@P2,@P1-@P2))

	SET @P2 = @P1 + 1

	SET @P1 = CharIndex(',',@UserNameId,@P1+1)

END

INSERT INTO #UserIds(UserNameId) VALUES(Substring(@UserNameId,@P2,DataLength(@UserNameId)))

	

-- add manually inserted users (selected user = -1)

	IF EXISTS ( SELECT 1 FROM #UserIds WHERE UserNameId = -1 ) AND @UserNamesText != ''

	BEGIN

		--parse usernames

		SET @UserNamesText = REPLACE(@UserNamesText, ' ', '')

		SET @UserNamesText = REPLACE(@UserNamesText, ';', ',')

		

		SET @P1 = CharIndex(',',@UserNamesText,0)

		SET @P2 = 1

		WHILE @P1 > 0

		BEGIN

			INSERT INTO #UserIds(UserNameId)

				SELECT AN.UserNameId FROM #UserNames AN WHERE AN.UserName = SUBSTRING(@UserNamesText,@P2,@P1-@P2)

			SET @P2 = @P1 + 1

			SET @P1 = CharIndex(',',@UserNamesText,@P1+1)

		END

		INSERT INTO #UserIds(UserNameId)

			SELECT AN.UserNameId FROM #UserNames AN WHERE AN.UserName = SUBSTRING(@UserNamesText,@P2,DataLength(@UserNamesText))

	END



	--SELECT * FROM #UserIds



	SELECT DATEADD(minute, @GMTDiff, c.StartDate) as LaunchTime, c.MachineDNSNameRowId, c.Failure as Failure, datediff(second, c.StartDate, c.FailureTime) as FailureTime, FailureReason.FailureReasonName, FailureType.FailureTypeName , c.ME_DeliveryGroupRowId, m.MachineDNSName as DNSName, meDG.Name as Name, meDG.SiteId, Site.DisplayName, #UserNames.UserName, ComtradeMPXAXD.LTClientIp.ClientIp, ComtradeMPXAXD.LTClientHostname.ClientHostname,  ComtradeMPXAXD.LTClientVersion.ClientVersion, FailureReason.FailureReasonCode--, #FailureTypes.TypeId 

		FROM ComtradeMPXAXD.vCTXAXDConnection as c



		inner join ComtradeMPXAXD.LTMachineDNSName as m on m.LTMachineDNSNameRowId = c.MachineDNSNameRowId

		left join #ManagedEntityDG as meDG on meDG.ManagedEntityRowId = c.ME_DeliveryGroupRowId

		left join vManagedEntity as Site on Site.ManagedEntityRowId = meDG.SiteId

		inner join #UserNames on #UserNames.UserNameId = c.UserRowId -- TO DELA TAK???????

		inner join #UserIds on #UserIds.UserNameId = #UserNames.UserNameId

		inner join ComtradeMPXAXD.LTClientIp on ComtradeMPXAXD.LTClientIp.LTClientIpRowId = c.ClientIpRowId

		inner join ComtradeMPXAXD.LTClientHostname on ComtradeMPXAXD.LTClientHostname.LTClientHostnameRowId = c.ClientHostnameRowId

		inner join ComtradeMPXAXD.LTClientVersion on ComtradeMPXAXD.LTClientVersion.LTClientVersionRowId = c.ClientVersionRowId

		inner join ComtradeMPXAXD.LTFailureReason as FailureReason on FailureReason.FailureReasonCode = c.FailureReasonCode

		inner join ComtradeMPXAXD.LTFailureType as FailureType on FailureType.FailureTypeCode = c.FailureTypeCode



	WHERE    DATEADD(minute, @GMTDiff, c.StartDate) >= @StartDate_BaseValue

			AND DATEADD(minute, @GMTDiff, c.StartDate) < @EndDate_BaseValue 

			AND Failure = 1

	ORDER BY DATEADD(minute, @GMTDiff, c.StartDate)



END

ELSE

	BEGIN



		SELECT DATEADD(minute, @GMTDiff, c.StartDate) as LaunchTime, c.MachineDNSNameRowId, c.Failure as Failure, datediff(second, c.StartDate, c.FailureTime) as FailureTime, FailureReason.FailureReasonName, FailureType.FailureTypeName , c.ME_DeliveryGroupRowId, m.MachineDNSName as DNSName, meDG.Name, meDG.SiteId, Site.DisplayName, #UserNames.UserName, ComtradeMPXAXD.LTClientIp.ClientIp, ComtradeMPXAXD.LTClientHostname.ClientHostname,  ComtradeMPXAXD.LTClientVersion.ClientVersion, FailureReason.FailureReasonCode--, #FailureTypes.TypeId 

		FROM ComtradeMPXAXD.vCTXAXDConnection as c



		inner join ComtradeMPXAXD.LTMachineDNSName as m on m.LTMachineDNSNameRowId = c.MachineDNSNameRowId

		inner join #ManagedEntity as me on me.ManagedEntityRowId = c.ME_DeliveryGroupRowId

		left join #ManagedEntityDG as meDG on meDG.ManagedEntityRowId = me.ManagedEntityRowId

		left join vManagedEntity as Site on Site.ManagedEntityRowId = meDG.SiteId

		inner join #UserNames on #UserNames.UserNameId = c.UserRowId -- TO DELA TAK???????

		inner join ComtradeMPXAXD.LTClientIp on ComtradeMPXAXD.LTClientIp.LTClientIpRowId = c.ClientIpRowId

		inner join ComtradeMPXAXD.LTClientHostname on ComtradeMPXAXD.LTClientHostname.LTClientHostnameRowId = c.ClientHostnameRowId

		--inner join ComtradeMPXAXD.LTLaunchedViaIp on ComtradeMPXAXD.LTLaunchedViaIp.LTLaunchedViaIpRowId = c.LaunchedViaIpRowId

		inner join ComtradeMPXAXD.LTClientVersion on ComtradeMPXAXD.LTClientVersion.LTClientVersionRowId = c.ClientVersionRowId

		inner join ComtradeMPXAXD.LTFailureReason as FailureReason on FailureReason.FailureReasonCode = c.FailureReasonCode

		inner join ComtradeMPXAXD.LTFailureType as FailureType on FailureType.FailureTypeCode = c.FailureTypeCode

		inner join #FailureTypes on #FailureTypes.TypeId = FailureType.FailureTypeCode

	WHERE DATEADD(minute, @GMTDiff, c.StartDate) >= @StartDate_BaseValue

			AND DATEADD(minute, @GMTDiff, c.StartDate) < @EndDate_BaseValue 

			AND Failure = 1

	ORDER BY DATEADD(minute, @GMTDiff, c.StartDate)

END







DROP TABLE #ManagedEntity

DROP TABLE #ManagedEntityDG

--DROP TABLE #Users

DROP TABLE #UserNames

DROP TABLE #UserIds

DROP TABLE #FailureTypes



GO



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

	PERMISSIONS 

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

	

GRANT EXECUTE ON dbo.[ComtradeMPXAXD_FailedConnections_DataGet] TO OpsMgrReader 



GO