Home
  •  

Res.Comtrade.Citrix.XenDesktop.Reports.ReportScript.ServerActiveSessions.Install (Resource)

Element properties:

TypeResource
File NameComtrade.Citrix.XenDesktop.Reports.ReportScript.ServerActiveSessions.Install.sql
AccessibilityInternal

File Content: Comtrade.Citrix.XenDesktop.Reports.ReportScript.ServerActiveSessions.Install.sql

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

BEGIN

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

END

GO







ALTER PROCEDURE [dbo].[ComtradeMPXAXD_ServerActiveSessions_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  me.ManagedEntityRowId, met.ManagedEntityTypeSystemName, me.ManagedEntityDefaultName, meps.FromDateTime

		FROM    vManagedEntity me             

				INNER JOIN vManagedEntityType met ON met.ManagedEntityTypeRowId = me.ManagedEntityTypeRowId

				--filter only selected objects!

				inner join #ManagedEntityServers mes on mes.ManagedEntityRowId = me.ManagedEntityRowId

				INNER JOIN (select mpnoxml.ManagedEntityRowId, MAX(mpnoxml.ManagedEntityPropertyRowId) as MaxProperyId

							from vManagedEntityProperty mpnoxml

							group by mpnoxml.ManagedEntityRowId) LatestProperties on LatestProperties.ManagedEntityRowId = me.ManagedEntityRowId 

				INNER JOIN vManagedEntityPropertySet meps ON  meps.ManagedEntityRowId = me.ManagedEntityRowId and LatestProperties.MaxProperyId = meps.ManagedEntityPropertyRowId

				inner join vManagedEntityTypeProperty metpDG  ON metpDG.PropertyGuid = meps.PropertyGuid



WHERE      (met.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 #ActiveSessionFinal ("DateTime" DateTime, "MaxValue" int, "MinValue" int, "Average" float, "DNSName" nvarchar(256), "MEServerRowId" int,

"SampleCount" int, "DG_RowId" int, "DG_Name" nvarchar(256), "Site_RowId" int, "Site_Name" nvarchar(256), "StandardDeviation" float)





IF @BusinessTimeType = 1

	BEGIN

		INSERT INTO #ActiveSessionFinal

		SELECT	Distinct(DATEADD(minute, @GMTDiff, vPerf.DateTime)) AS DateTime, vPerf.MaxValue AS MaxValue,

				vPerf.MinValue as MinValue, vPerf.AverageValue as Average, vPerformanceRuleInstance.InstanceName AS DNSName, mesf.MEServerRowId,

				vPerf.SampleCount, dg.DG_RowId, dg.DG_Name, dg.Site_RowId, dg.Site_Name, vPerf.StandardDeviation



		FROM    Perf.vPerfHourly as vPerf 

				INNER JOIN vPerformanceRuleInstance ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = vPerf.PerformanceRuleInstanceRowId

				INNER JOIN vRule on vRule.RuleRowId = vPerformanceRuleInstance.RuleRowId

				INNER JOIN #ManagedEntityServersFinal mesf on mesf.ServerDNS = vPerformanceRuleInstance.InstanceName

				INNER JOIN ComtradeMPXAXD.vCTXAXDMachine m on m.CTXAXDMachineRowId = mesf.MEServerRowId

				INNER JOIN #DGs dg on dg.DG_RowId = m.ME_DeliveryGroupRowId





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

				and DATEADD(minute, @GMTDiff, vPerf.DateTime) - CAST(FLOOR(CAST(vPerf.DateTime AS float)) AS datetime) >= @BH_start --start hours

				and DATEADD(minute, @GMTDiff, vPerf.DateTime) - CAST(FLOOR(CAST(vPerf.DateTime AS float)) AS datetime) < @BH_end   --end hours

				AND vRule.RuleSystemName = 'ComTrade.Citrix.XenDesktop.ServerOSMachine.ActiveSessions.PerfDW'

				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 DateTime

	END

ELSE

	BEGIN

		IF @DataAggregation = 0

			INSERT INTO #ActiveSessionFinal

			SELECT	Distinct(DATEADD(minute, @GMTDiff, vPerf.DateTime)) AS DateTime, vPerf.MaxValue AS MaxValue,

					vPerf.MinValue as MinValue, vPerf.AverageValue as Average, vPerformanceRuleInstance.InstanceName AS DNSName,mesf.MEServerRowId,

					vPerf.SampleCount, dg.DG_RowId, dg.DG_Name, dg.Site_RowId, dg.Site_Name, vPerf.StandardDeviation



			FROM    Perf.vPerfHourly as vPerf 

					INNER JOIN vPerformanceRuleInstance ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = vPerf.PerformanceRuleInstanceRowId

					INNER JOIN vRule on vRule.RuleRowId = vPerformanceRuleInstance.RuleRowId

					INNER JOIN #ManagedEntityServersFinal mesf on mesf.ServerDNS = vPerformanceRuleInstance.InstanceName

					INNER JOIN ComtradeMPXAXD.vCTXAXDMachine m on m.CTXAXDMachineRowId = mesf.MEServerRowId

					INNER JOIN #DGs dg on dg.DG_RowId = m.ME_DeliveryGroupRowId



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

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

					AND vRule.RuleSystemName = 'ComTrade.Citrix.XenDesktop.ServerOSMachine.ActiveSessions.PerfDW'

			ORDER BY DateTime



		ELSE

			INSERT INTO #ActiveSessionFinal

			SELECT	Distinct(DATEADD(minute, @GMTDiff, vPerf.DateTime)) AS DateTime, vPerf.MaxValue AS MaxValue,

					vPerf.MinValue as MinValue, vPerf.AverageValue as Average, vPerformanceRuleInstance.InstanceName AS DNSName,mesf.MEServerRowId,

					vPerf.SampleCount, dg.DG_RowId, dg.DG_Name, dg.Site_RowId, dg.Site_Name, vPerf.StandardDeviation



			FROM    Perf.vPerfDaily as vPerf 

					INNER JOIN vPerformanceRuleInstance ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = vPerf.PerformanceRuleInstanceRowId

					INNER JOIN vRule on vRule.RuleRowId = vPerformanceRuleInstance.RuleRowId

					INNER JOIN #ManagedEntityServersFinal mesf on mesf.ServerDNS = vPerformanceRuleInstance.InstanceName

					INNER JOIN ComtradeMPXAXD.vCTXAXDMachine m on m.CTXAXDMachineRowId = mesf.MEServerRowId

					INNER JOIN #DGs dg on dg.DG_RowId = m.ME_DeliveryGroupRowId



			WHERE   DATEADD(minute, @GMTDiff, vPerf.DateTime) >= CAST(FLOOR(CAST(DATEADD(minute, -@GMTDiff, @StartDate_BaseValue) as float)) AS datetime) 

					AND DATEADD(minute, @GMTDiff, vPerf.DateTime) <= CAST(FLOOR(CAST(DATEADD(minute, -@GMTDiff, @EndDate_BaseValue) as float)) AS datetime) 

					AND vRule.RuleSystemName = 'ComTrade.Citrix.XenDesktop.ServerOSMachine.ActiveSessions.PerfDW'

			ORDER BY DateTime

	END



IF @DataAggregation = 1 and @BusinessTimeType = 1

BEGIN

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

	MAX(MaxValue) as MaxValue, MIN(MinValue) as MinValue, SUM(Average*SampleCount)/SUM(SampleCount) as Average, DNSName, MEServerRowId, SUM(SampleCount) as SampleCount, DG_RowId, DG_Name, Site_RowId, Site_Name, STDEV(StandardDeviation) as StandardDeviation

	FROM #ActiveSessionFinal

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

	DNSName, MEServerRowId, DG_RowId, DG_Name, Site_RowId, Site_Name

END

ELSE

	SELECT * FROM #ActiveSessionFinal





DROP TABLE #ActiveSessionFinal

DROP TABLE #DGs

DROP TABLE #DistinctServerNames

DROP TABLE #ManagedEntityServersFinal



GO



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

	PERMISSIONS 

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

	

GRANT EXECUTE ON dbo.[ComtradeMPXAXD_ServerActiveSessions_DataGet] TO OpsMgrReader 



GO