Res.Comtrade.Citrix.XenDesktop.EndUser.Reports.ReportScirpt.LogonDuration.Upgrade (Resource)

Element properties:

File Content: Comtrade.Citrix.XenDesktop.EndUser.Reports.ReportScirpt.LogonDuration.Upgrade.sql

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

BEGIN

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

END

GO







ALTER PROCEDURE [dbo].[ComtradeMPXAXD_LogonDuration_DataGet]

	@StartDate_BaseValue datetime,

	@EndDate_BaseValue datetime,

	@StartDate_BaseValueGMT datetime,

	@DataAggregation int, -- 0-hourly, 1-daily

	@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

	@ObjectList xml,

	@ObjectType varchar(max),

    @LanguageCode varchar(3) = 'ENU'

AS

BEGIN



  SET NOCOUNT ON



  DECLARE @Error int

  DECLARE @ExecError int

  DECLARE @xmldoc int

  DECLARE @RowCount int

  

CREATE TABLE #ManagedEntity (ManagedEntityRowId int)

CREATE TABLE #PerfRules ("RuleSystemName" nvarchar(max) COLLATE database_default)



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)



SET DATEFIRST 1 --monday = 1

DECLARE @GMTDiff int

SELECT @GMTDiff = DATEDIFF(minute, @StartDate_BaseValueGMT, @StartDate_BaseValue)

SELECT @StartDate_BaseValue = DATEADD(minute, -@GMTDiff, @StartDate_BaseValue)

SELECT @EndDate_BaseValue = DATEADD(minute, -@GMTDiff, @EndDate_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))



IF @ObjectType = 'ComTrade.Citrix.XenDesktop.Site'

BEGIN



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

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





INSERT INTO #PerfRules(RuleSystemName) VALUES

		('Comtrade.Citrix.XenDesktop.EndUser.DeliveryGroup.Logons.TotalCompleteDuration.PerfDW')

		

END

ELSE

BEGIN



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

	INNER JOIN #ManagedEntity on #ManagedEntity.ManagedEntityRowId = meDg.ManagedEntityRowId







INSERT INTO #PerfRules(RuleSystemName) VALUES

		('Comtrade.Citrix.XenDesktop.EndUser.DeliveryGroup.Logons.TotalVMStartTime.PerfDW'),

		('Comtrade.Citrix.XenDesktop.EndUser.DeliveryGroup.Logons.TotalProfileLoadTime.PerfDW'),

		('Comtrade.Citrix.XenDesktop.EndUser.DeliveryGroup.Logons.TotalLogonScriptsTime.PerfDW'),

		('Comtrade.Citrix.XenDesktop.EndUser.DeliveryGroup.Logons.TotalInteractiveSessionTime.PerfDW'),

		('Comtrade.Citrix.XenDesktop.EndUser.DeliveryGroup.Logons.TotalHDXConnectionTime.PerfDW'),

		('Comtrade.Citrix.XenDesktop.EndUser.DeliveryGroup.Logons.TotalGPOsTime.PerfDW'),

		('Comtrade.Citrix.XenDesktop.EndUser.DeliveryGroup.Logons.TotalCompleteDuration.PerfDW'),

		('Comtrade.Citrix.XenDesktop.EndUser.DeliveryGroup.Logons.TotalBrokeringTime.PerfDW'),

		('Comtrade.Citrix.XenDesktop.EndUser.DeliveryGroup.Logons.TotalAuthenticationTime.PerfDW')

END





CREATE TABLE #PerfCounterNames("CounterName" nvarchar(256) COLLATE database_default, "DisplayName" nvarchar(256) COLLATE database_default, "Color" nvarchar(256) COLLATE database_default)

INSERT INTO #PerfCounterNames(CounterName, DisplayName, Color) VALUES

		('TotalAuthenticationTime','4. Authentication (s)', 'BLUE'),

		('TotalBrokeringTime','1. Brokering (s)', 'FORESTGREEN'),

		('TotalGPOsTime','6. GPOs (s)', 'RED'),

		('TotalHDXConnectionTime','3. HDX connection (s)', 'PURPLE'),

		('TotalInteractiveSessionTime','8. Interactive session (s)', 'AQUA'),

		('TotalLogonDuration','Avg. Logon Duration (s)', 'BLACK'),

		('TotalLogonScriptsTime','7. Logon scripts (s)', 'GOLD'),

		('TotalProfileLoadTime','5. Profile load (s)', 'LIGHTGREEN'),

		('TotalVMStartTime','2. VM start (s)', 'RED')





CREATE TABLE #NumberOfLogons("DateTime" DateTime, "LogonSampleCount" int, "LogonAverage" float, "ManagedEntityRowId" int)



IF @DataAggregation = 0 --HOURLY

  BEGIN

	IF @BusinessTimeType = 1  --business hours

	  BEGIN

		INSERT INTO #NumberOfLogons(DateTime, LogonSampleCount, LogonAverage, ManagedEntityRowId)

		SELECT   vPerf.DateTime, vPerf.SampleCount, vPerf.AverageValue,

				   vManagedEntity.ManagedEntityRowId



		FROM     Perf.vPerfHourly as vPerf INNER JOIN

				   vPerformanceRuleInstance ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = vPerf.PerformanceRuleInstanceRowId INNER JOIN

				   vManagedEntity ON vPerf.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId INNER JOIN

				   vRule ON vPerformanceRuleInstance.RuleRowId = vRule.RuleRowId 

	         

		WHERE	  vPerf.DateTime >= @StartDate_BaseValue AND

				  vPerf.DateTime < @EndDate_BaseValue AND

				  vRule.RuleSystemName = 'Comtrade.Citrix.XenDesktop.EndUser.DeliveryGroup.Logons.NumberOfLogons.PerfDBDW'

				  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

	  END

	ELSE

	  BEGIN

		INSERT INTO #NumberOfLogons(DateTime, LogonSampleCount, LogonAverage, ManagedEntityRowId)

		SELECT   vPerf.DateTime, vPerf.SampleCount, vPerf.AverageValue,

				   vManagedEntity.ManagedEntityRowId



		FROM     Perf.vPerfHourly as vPerf INNER JOIN

				   vPerformanceRuleInstance ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = vPerf.PerformanceRuleInstanceRowId INNER JOIN

				   vManagedEntity ON vPerf.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId INNER JOIN

				   vRule ON vPerformanceRuleInstance.RuleRowId = vRule.RuleRowId 

	         

		WHERE	  vPerf.DateTime >= @StartDate_BaseValue AND

				  vPerf.DateTime < @EndDate_BaseValue AND

				  vRule.RuleSystemName = 'Comtrade.Citrix.XenDesktop.EndUser.DeliveryGroup.Logons.NumberOfLogons.PerfDBDW'

	  END

  END

ELSE

 BEGIN

	IF @BusinessTimeType = 1  --business hours

	  BEGIN

		INSERT INTO #NumberOfLogons(DateTime, LogonSampleCount, LogonAverage, ManagedEntityRowId)

		SELECT   vPerf.DateTime, vPerf.SampleCount, vPerf.AverageValue,

				   vManagedEntity.ManagedEntityRowId



		FROM     Perf.vPerfDaily as vPerf INNER JOIN

				   vPerformanceRuleInstance ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = vPerf.PerformanceRuleInstanceRowId INNER JOIN

				   vManagedEntity ON vPerf.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId INNER JOIN

				   vRule ON vPerformanceRuleInstance.RuleRowId = vRule.RuleRowId 

	         

		WHERE	  vPerf.DateTime >= @StartDate_BaseValue AND

				  vPerf.DateTime < @EndDate_BaseValue AND

				  vRule.RuleSystemName = 'Comtrade.Citrix.XenDesktop.EndUser.DeliveryGroup.Logons.NumberOfLogons.PerfDBDW'

				  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

	  END

	ELSE

	  BEGIN

		INSERT INTO #NumberOfLogons(DateTime, LogonSampleCount, LogonAverage, ManagedEntityRowId)

		SELECT   vPerf.DateTime, vPerf.SampleCount, vPerf.AverageValue,

				   vManagedEntity.ManagedEntityRowId



		FROM     Perf.vPerfDaily as vPerf INNER JOIN

				   vPerformanceRuleInstance ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = vPerf.PerformanceRuleInstanceRowId INNER JOIN

				   vManagedEntity ON vPerf.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId INNER JOIN

				   vRule ON vPerformanceRuleInstance.RuleRowId = vRule.RuleRowId 

	         

		WHERE	  vPerf.DateTime >= @StartDate_BaseValue AND

				  vPerf.DateTime < @EndDate_BaseValue AND

				  vRule.RuleSystemName = 'Comtrade.Citrix.XenDesktop.EndUser.DeliveryGroup.Logons.NumberOfLogons.PerfDBDW'

	  END

  END





IF @DataAggregation = 0 --HOURLY

  BEGIN

	SELECT	   DATEADD(minute, @GMTDiff, vPerf.DateTime) AS DateTime, vPerf.SampleCount* vPerf.AverageValue AS AverageTime,

			   vPerformanceRuleInstance.RuleRowId, vPerformanceRuleInstance.InstanceName AS InstanceName, 

			   vRule.RuleSystemName AS RuleSystemName,  #DGs.Site_Name,

			   #NumberOfLogons.LogonAverage*#NumberOfLogons.LogonSampleCount AS LogonCount,

			   vManagedEntity.ManagedEntityRowId, vManagedEntity.ManagedEntityGuid, vManagedEntity.ManagedEntityDefaultName, vManagedEntity.Path,

			   ISNULL(vDisplayString.Name,vManagedEntityType.ManagedEntityTypeDefaultName) AS DisplayName,

			   vManagementGroup.ManagementGroupGuid, vManagementGroup.ManagementGroupDefaultName,

			   #PerfCounterNames.DisplayName AS RuleDisplayName,#PerfCounterNames.Color AS GraphColor,			   

			   DATEPART(wk, vPerf.DateTime) AS WeekNumber

			   

	FROM       Perf.vPerfHourly as vPerf INNER JOIN

			   vPerformanceRuleInstance ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = vPerf.PerformanceRuleInstanceRowId INNER JOIN

			   vManagedEntity ON vPerf.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId INNER JOIN

			   vManagedEntityType ON vManagedEntityType.ManagedEntityTypeRowId = vManagedEntity.ManagedEntityTypeRowId INNER JOIN

			   vManagementGroup ON vManagementGroup.ManagementGroupRowId = vManagedEntity.ManagementGroupRowId INNER JOIN

			   vRule ON vPerformanceRuleInstance.RuleRowId = vRule.RuleRowId INNER JOIN

			   #PerfRules on vRule.RuleSystemName = #PerfRules.RuleSystemName INNER JOIN

			   vPerformanceRule ON vPerformanceRuleInstance.RuleRowId = vPerformanceRule.RuleRowId INNER JOIN

			   #PerfCounterNames ON #PerfCounterNames.CounterName = vPerformanceRule.CounterName INNER JOIN

			   #NumberOfLogons ON #NumberOfLogons.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId AND

			   #NumberOfLogons.DateTime = vPerf.DateTime INNER JOIN

			   #DGs ON #DGs.DG_RowId = vManagedEntity.ManagedEntityRowId INNER JOIN

	          vDisplayString ON vManagedEntityType.ManagedEntityTypeGuid = vDisplayString.ElementGuid AND 

	          vDisplayString.LanguageCode = @LanguageCode LEFT OUTER JOIN

	          vDisplayString vDisplayStringRule ON vRule.RuleGuid = vDisplayStringRule.ElementGuid AND 

	          vDisplayStringRule.LanguageCode = @LanguageCode

   END

ELSE

  BEGIN

	SELECT	   DATEADD(minute, @GMTDiff, vPerf.DateTime) AS DateTime, vPerf.SampleCount* vPerf.AverageValue AS AverageTime,

			   vPerformanceRuleInstance.RuleRowId, vPerformanceRuleInstance.InstanceName AS InstanceName, 

			   vRule.RuleSystemName AS RuleSystemName,  #DGs.Site_Name,

			   #NumberOfLogons.LogonAverage*#NumberOfLogons.LogonSampleCount AS LogonCount,

			   vManagedEntity.ManagedEntityRowId, vManagedEntity.ManagedEntityGuid, vManagedEntity.ManagedEntityDefaultName, vManagedEntity.Path,

			   ISNULL(vDisplayString.Name,vManagedEntityType.ManagedEntityTypeDefaultName) AS DisplayName,

			   vManagementGroup.ManagementGroupGuid, vManagementGroup.ManagementGroupDefaultName,

			   #PerfCounterNames.DisplayName AS RuleDisplayName,#PerfCounterNames.Color AS GraphColor,			   

			   DATEPART(wk, vPerf.DateTime) AS WeekNumber

			   

	FROM       Perf.vPerfDaily as vPerf INNER JOIN

			   vPerformanceRuleInstance ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = vPerf.PerformanceRuleInstanceRowId INNER JOIN

			   vManagedEntity ON vPerf.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId INNER JOIN

			   vManagedEntityType ON vManagedEntityType.ManagedEntityTypeRowId = vManagedEntity.ManagedEntityTypeRowId INNER JOIN

			   vManagementGroup ON vManagementGroup.ManagementGroupRowId = vManagedEntity.ManagementGroupRowId INNER JOIN

			   vRule ON vPerformanceRuleInstance.RuleRowId = vRule.RuleRowId INNER JOIN

			   #PerfRules on vRule.RuleSystemName = #PerfRules.RuleSystemName INNER JOIN

			   vPerformanceRule ON vPerformanceRuleInstance.RuleRowId = vPerformanceRule.RuleRowId INNER JOIN

			   #PerfCounterNames ON #PerfCounterNames.CounterName = vPerformanceRule.CounterName INNER JOIN

			   #NumberOfLogons ON #NumberOfLogons.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId AND

			   #NumberOfLogons.DateTime = vPerf.DateTime INNER JOIN

			   #DGs ON #DGs.DG_RowId = vManagedEntity.ManagedEntityRowId INNER JOIN

	          vDisplayString ON vManagedEntityType.ManagedEntityTypeGuid = vDisplayString.ElementGuid AND 

	          vDisplayString.LanguageCode = @LanguageCode LEFT OUTER JOIN

	          vDisplayString vDisplayStringRule ON vRule.RuleGuid = vDisplayStringRule.ElementGuid AND 

	          vDisplayStringRule.LanguageCode = @LanguageCode

  END





  DROP TABLE #DGs

  DROP TABLE #ManagedEntity

  DROP TABLE #NumberOfLogons

  DROP TABLE #PerfRules 

  DROP TABLE #PerfCounterNames



END



GO



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

	PERMISSIONS 

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

	

GRANT EXECUTE ON dbo.[ComtradeMPXAXD_LogonDuration_DataGet] TO OpsMgrReader 



GO