Microsoft Windows Server Reports Performance By Utilization Script

Microsoft.Windows.Server.Reports.PerformancebyUtilization.Script (DataWarehouseScript)

SQL Script that deploys necessary for the "Performance By Utilization" report database objects.

Element properties:

Source Code:

<DataWarehouseScript ID="Microsoft.Windows.Server.Reports.PerformancebyUtilization.Script" Accessibility="Internal">

  <Install>

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

BEGIN

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

END

GO



ALTER PROCEDURE [dbo].[Microsoft_SystemCenter_Report_WindowsServer_Performace_By_Utilization]

	@DataAggregation INT,

	@StartDate DATETIME,

	@EndDate DATETIME,

	@GroupManagedEntityID INT,

	@Top INT,

	@OrderType BIT,

	@ManagementGroupsCsv NVARCHAR(MAX),

	@PerfObjectCounterListXml XML,

	@MPListXml XML

AS

BEGIN



	SET NOCOUNT ON



	DECLARE @ExecError INT

	DECLARE @Error INT

	DECLARE @xmlDoc INT	

	

	-- build a list of all relationship

-- types derived from "System.Containment"

-- get row id of the "containment" relationship type

DECLARE @ContainmentRelationshipTypeRowId int



SELECT @ContainmentRelationshipTypeRowId = RelationshipTypeRowId

FROM vRelationshipType

WHERE (RelationshipTypeSystemName = 'System.Containment')



-- create table to hold all relationship types found

IF (OBJECT_ID('tempdb..#RelationshipType') IS NOT NULL)

  DROP TABLE #RelationshipType



CREATE TABLE #RelationshipType (

   RelationshipTypeRowId int

)



-- use table-valued function to build relationship list

INSERT #RelationshipType (RelationshipTypeRowId)

SELECT RelationshipTypeRowId

FROM dbo.RelationshipDerivedTypeHierarchy(@ContainmentRelationshipTypeRowId, 0)



-- *********************************************

-- *  STEP I: Get member computers

--

--     In this step we're going to figure out

--     which objects are currently members

--     of the group specified for report

-- *********************************************



-- create table to hold all contained objects

IF (OBJECT_ID('tempdb..#ContainedManagedEntity') IS NOT NULL)

  DROP TABLE #ContainedManagedEntity

  

CREATE TABLE #ContainedManagedEntity (

   ManagedEntityRowId	int

  ,[Level]				int

)



-- prepare recursion: put group into contained objects

INSERT #ContainedManagedEntity(ManagedEntityRowId, [Level])

  VALUES (@GroupManagedEntityID, 0)

  

DECLARE @CurrentLevel int

SET @CurrentLevel = 1



DECLARE @RowCount int

SET @RowCount = 1



-- recursively walk down containment hierarchy

WHILE (@RowCount &gt; 0)

BEGIN

  INSERT #ContainedManagedEntity(ManagedEntityRowId, [Level])

  SELECT DISTINCT r.TargetManagedEntityRowId, @CurrentLevel

  FROM vRelationship r

          JOIN #RelationshipType rt ON (r.RelationshipTypeRowId = rt.RelationshipTypeRowId)

          JOIN #ContainedManagedEntity c ON (r.SourceManagedEntityRowId = c.ManagedEntityRowId) AND (c.[Level] = @CurrentLevel - 1)

  WHERE EXISTS (SELECT *

				FROM vRelationshipManagementGroup rmg

				WHERE (rmg.RelationshipRowId = r.RelationshipRowId)

				  AND (GETUTCDATE() BETWEEN rmg.FromDateTime AND ISNULL(rmg.ToDateTime, '99991231'))

			   ) -- membership relationship exists as of NOW

          

  SELECT @RowCount = @@ROWCOUNT

  SET @CurrentLevel = @CurrentLevel + 1

END





	CREATE TABLE #PerfObjectCounters (

		PerfObject VARCHAR(MAX), 

		PerfCounter VARCHAR(MAX),

		PerInstance BIT

		)



	SET @Error = @@ERROR

	IF @Error &lt;&gt; 0 GOTO QuitError



	

	CREATE TABLE #ManagementGroups(ManagementGroupGuid UNIQUEIDENTIFIER)

		

	SET @Error = @@ERROR

	IF @Error &lt;&gt; 0 GOTO QuitError



	EXEC @ExecError = sp_xml_preparedocument @xmlDoc OUTPUT, @PerfObjectCounterListXml

	SET @Error = @@ERROR

	IF @ExecError &lt;&gt; 0 OR @Error &lt;&gt; 0 GOTO QuitError



	INSERT INTO #PerfObjectCounters (PerfObject, PerfCounter, PerInstance)

	SELECT PerfObject, PerfCounter, PerInstance

	FROM OPENXML(@xmlDoc,'/Data/Objects/Object',2) WITH

		(PerfObject VARCHAR(MAX) '@Name',

		 PerfCounter VARCHAR(MAX) '@Counter',

		 PerInstance BIT '@PerInstance'

		 )





	DECLARE @Pos        INT,

			@NextPos    INT,

			@ValueLen   INT,

			@MGList		NVARCHAR(MAX)



			

	



	SET @Pos = 0

	SET @NextPos = 1



	WHILE @NextPos &gt; 0

		BEGIN

			SET @NextPos = CHARINDEX(',', @ManagementGroupsCsv, @Pos + 1)

			SET @ValueLen = (CASE WHEN @NextPos &gt; 0

								  THEN @NextPos

								  ELSE LEN(@ManagementGroupsCsv) + 1

							 END) - @Pos - 1

			INSERT #ManagementGroups (ManagementGroupGuid)

			 VALUES (CONVERT(UNIQUEIDENTIFIER, SUBSTRING(REPLACE(@ManagementGroupsCsv,' ',''), @Pos + 1, @ValueLen)))

			SET @Pos = @NextPos

		END



	IF @ExecError &lt;&gt; 0 GOTO QuitError

	

	CREATE TABLE #ManagementPacks (

	ManagementPackSysName VARCHAR(MAX),

	ManagementPackId INT

	)

	

	EXEC @ExecError = sp_xml_preparedocument @xmlDoc OUTPUT, @MPListXml

		SET @Error = @@ERROR

		IF @ExecError &lt;&gt; 0 OR @Error &lt;&gt; 0 GOTO QuitError



	INSERT INTO #ManagementPacks(ManagementPackSysName, ManagementPackId)

		SELECT ManagementPackSysName, MP.ManagementPackRowId

		FROM OPENXML(@xmlDoc,'/ManagementPacks/ManagementPack',2) WITH

			(ManagementPackSysName VARCHAR(MAX) '@Name')

		INNER JOIN 

			ManagementPack MP ON MP.ManagementPackSystemName = ManagementPackSysName



IF @DataAggregation = 1



	WITH ComputerId AS

	(

		SELECT

			MgEntity.ManagedEntityRowId,

			MgEntity.Name

		FROM

			vManagedEntity MgEntity

		INNER JOIN

			vManagedEntityType MgEntityType ON MgEntity.ManagedEntityTypeRowId = MgEntityType.ManagedEntityTypeRowId

			JOIN #ContainedManagedEntity cme ON MgEntity.ManagedEntityRowId = cme.ManagedEntityRowId

		WHERE

			MgEntityType.ManagedEntityTypeSystemName = 'Microsoft.Windows.Computer'

	),

	PerfTable AS

	(

	SELECT

		MgEntity.[Path] ComputerName,

		SUM(PerfData.SampleCount * PerfData.AverageValue)/(SUM(SUM(PerfData.SampleCount)) OVER(PARTITION BY MgEntity.ManagedEntityRowId, PerfRule.ObjectName, PerfRule.CounterName)) AverageValue,

		PerfRule.ObjectName ObjectName,

		PerfRule.CounterName CounterName,

		PerfRuleInst.InstanceName InstanceName,

		MAX(CAST(#PerfObjectCounters.PerInstance AS INT)) PerInstance,

		ComputerId.ManagedEntityRowId ManagedEntityRowId,

		MAX(PerfRule.RuleRowId) RuleId,

		(SUM(SUM(PerfData.SampleCount)) OVER(PARTITION BY MgEntity.ManagedEntityRowId, PerfRule.ObjectName, PerfRule.CounterName)) RuleSampleCount

	FROM     

		[Perf].vPerfDaily PerfData

		INNER JOIN

			[dbo].vManagedEntity MgEntity ON PerfData.ManagedEntityRowId =  

			MgEntity.ManagedEntityRowId 

		INNER JOIN

			[dbo].vManagedEntityType MgEntityType ON MgEntity.ManagedEntityTypeRowId = 

			MgEntityType.ManagedEntityTypeRowId 

		INNER JOIN

			[dbo].vPerformanceRuleInstance PerfRuleInst ON   

			PerfRuleInst.PerformanceRuleInstanceRowId =  

			PerfData.PerformanceRuleInstanceRowId 

		INNER JOIN

			[dbo].vPerformanceRule PerfRule ON PerfRuleInst.RuleRowId =    

			PerfRule.RuleRowId

		INNER JOIN

			[dbo].vManagementGroup MG ON MgEntity.ManagementGroupRowId = MG.ManagementGroupRowId

		INNER JOIN

			#ManagementGroups ON MG.ManagementGroupGuid = #ManagementGroups.ManagementGroupGuid

		INNER JOIN 

			#PerfObjectCounters ON PerfRule.ObjectName = PerfObject AND PerfRule.CounterName = PerfCounter

		INNER JOIN

			#ManagementPacks ON MgEntityType.ManagementPackRowId = #ManagementPacks.ManagementPackId

		RIGHT JOIN

			ComputerId ON ComputerId.Name = MgEntity.Path

	WHERE     

		  (

			CONVERT(DATETIME, CONVERT(VARCHAR, PerfData.[DateTime], 101),101)

			BETWEEN 

				CONVERT(DATETIME, CONVERT(VARCHAR, @StartDate, 101),101)

			AND 

				CONVERT(DATETIME, CONVERT(VARCHAR, @EndDate, 101),101)

		  ) 

		 AND 

		  (

			MgEntity.[Path] IS NOT NULL

		  )

	GROUP BY

		MgEntity.ManagedEntityRowId, MgEntity.[Path], PerfRule.ObjectName, PerfRule.CounterName, PerfRuleInst.InstanceName, MgEntityType.ManagedEntityTypeSystemName, ComputerId.ManagedEntityRowId

	),

	InstanceFilteredTable AS

	(

	SELECT 

		ComputerName,

		SUM(AverageValue*RuleSampleCount)/ SUM(SUM(RuleSampleCount)) OVER(PARTITION BY ComputerName,ObjectName,CounterName,InstanceName,ManagedEntityRowId)  AverageValue,

		ObjectName,

		CounterName,

		InstanceName,

		PerInstance,

		CASE WHEN (@OrderType = 1) THEN

			(CASE WHEN ((PerInstance = 1) AND (AVG(AverageValue) = MAX(AVG(AverageValue)) OVER(PARTITION BY ComputerName,ObjectName,CounterName)) AND (CounterName &lt;&gt; '% Free Space')) 

					OR (PerInstance = 0) 

					OR ((PerInstance = 1) AND (AVG(AverageValue) = MIN(AVG(AverageValue)) OVER(PARTITION BY ComputerName,ObjectName,CounterName)) AND (CounterName = '% Free Space'))

				THEN 1 ELSE 0 END)

		ELSE

			(CASE WHEN ((PerInstance = 1) AND (AVG(AverageValue) = MIN(AVG(AverageValue)) OVER(PARTITION BY ComputerName,ObjectName,CounterName)) AND (CounterName &lt;&gt; '% Free Space')) 

					OR (PerInstance = 0) 

					OR ((PerInstance = 1) AND (AVG(AverageValue) = MAX(AVG(AverageValue)) OVER(PARTITION BY ComputerName,ObjectName,CounterName)) AND (CounterName = '% Free Space'))

				THEN 1 ELSE 0 END) 

		END MaxFlag,

		ManagedEntityRowId

	FROM

		PerfTable

	GROUP BY

		ComputerName,

		ObjectName,

		CounterName,

		InstanceName,

		PerInstance,

		ManagedEntityRowId

	),

	InstanceRankedTable AS

	(

		SELECT 

			ComputerName,

			AverageValue,

			ObjectName,

			CounterName,

			InstanceName,

			PerInstance,

			ManagedEntityRowId,

			ROW_NUMBER() OVER(PARTITION BY ComputerName,ObjectName,CounterName ORDER BY MaxFlag DESC) MaxFlag

		FROM

			InstanceFilteredTable

	),

	OrderedPerfTable AS

	(

	SELECT

		CASE WHEN (@OrderType = 1)THEN 

			CASE WHEN (CounterName &lt;&gt; '% Free Space') 

			THEN (ROW_NUMBER() OVER(PARTITION BY CounterName ORDER BY AverageValue DESC))

			ELSE (ROW_NUMBER() OVER(PARTITION BY CounterName ORDER BY AverageValue ASC)) END

		ELSE 

			CASE WHEN (CounterName &lt;&gt; '% Free Space') 

			THEN (ROW_NUMBER() OVER(PARTITION BY CounterName ORDER BY AverageValue ASC))

			ELSE (ROW_NUMBER() OVER(PARTITION BY CounterName ORDER BY AverageValue DESC))END  

		END OrderNum,

		ComputerName,

		AverageValue,

		ObjectName,

		CounterName,

		InstanceName,

		ManagedEntityRowId

	FROM 

		InstanceRankedTable

	WHERE

		MaxFlag = 1

	)

	SELECT

		OrderNum,

		ComputerName,

		AverageValue,

		ObjectName,

		CounterName,

		InstanceName,

		ManagedEntityRowId

	FROM

		OrderedPerfTable

	WHERE 

		OrderNum &lt;= @Top



ELSE

			

	WITH ComputerId AS

	(

		SELECT

			MgEntity.ManagedEntityRowId,

			MgEntity.Name

		FROM

			vManagedEntity MgEntity

		INNER JOIN

			vManagedEntityType MgEntityType ON MgEntity.ManagedEntityTypeRowId = MgEntityType.ManagedEntityTypeRowId

			JOIN #ContainedManagedEntity cme ON MgEntity.ManagedEntityRowId = cme.ManagedEntityRowId

		WHERE

			MgEntityType.ManagedEntityTypeSystemName = 'Microsoft.Windows.Computer'

	),

	PerfTable AS

	(

	SELECT

		MgEntity.[Path] ComputerName,

		SUM(PerfData.SampleCount * PerfData.AverageValue)/(SUM(SUM(PerfData.SampleCount)) OVER(PARTITION BY MgEntity.ManagedEntityRowId, PerfRule.ObjectName, PerfRule.CounterName)) AverageValue,

		PerfRule.ObjectName ObjectName,

		PerfRule.CounterName CounterName,

		PerfRuleInst.InstanceName InstanceName,

		MAX(CAST(#PerfObjectCounters.PerInstance AS INT)) PerInstance,

		ComputerId.ManagedEntityRowId ManagedEntityRowId,

		MAX(PerfRule.RuleRowId) RuleId,

		(SUM(SUM(PerfData.SampleCount)) OVER(PARTITION BY MgEntity.ManagedEntityRowId, PerfRule.ObjectName, PerfRule.CounterName)) RuleSampleCount

	FROM     

		[Perf].vPerfHourly PerfData

		INNER JOIN

			[dbo].vManagedEntity MgEntity ON PerfData.ManagedEntityRowId =  

			MgEntity.ManagedEntityRowId 

		INNER JOIN

			[dbo].vManagedEntityType MgEntityType ON MgEntity.ManagedEntityTypeRowId = 

			MgEntityType.ManagedEntityTypeRowId 

		INNER JOIN

			[dbo].vPerformanceRuleInstance PerfRuleInst ON   

			PerfRuleInst.PerformanceRuleInstanceRowId =  

			PerfData.PerformanceRuleInstanceRowId 

		INNER JOIN

			[dbo].vPerformanceRule PerfRule ON PerfRuleInst.RuleRowId =    

			PerfRule.RuleRowId

		INNER JOIN

			[dbo].vManagementGroup MG ON MgEntity.ManagementGroupRowId = MG.ManagementGroupRowId

		INNER JOIN

			#ManagementGroups ON MG.ManagementGroupGuid = #ManagementGroups.ManagementGroupGuid

		INNER JOIN 

			#PerfObjectCounters ON PerfRule.ObjectName = PerfObject AND PerfRule.CounterName = PerfCounter

		INNER JOIN

			#ManagementPacks ON MgEntityType.ManagementPackRowId = #ManagementPacks.ManagementPackId

		RIGHT JOIN

			ComputerId ON ComputerId.Name = MgEntity.Path

	WHERE     

		  (

			CONVERT(DATETIME, CONVERT(VARCHAR, PerfData.[DateTime], 101),101)

			BETWEEN 

				CONVERT(DATETIME, CONVERT(VARCHAR, @StartDate, 101),101)

			AND 

				CONVERT(DATETIME, CONVERT(VARCHAR, @EndDate, 101),101)

		  ) 

		 AND 

		  (

			MgEntity.[Path] IS NOT NULL

		  )

	GROUP BY

		MgEntity.ManagedEntityRowId, MgEntity.[Path], PerfRule.ObjectName, PerfRule.CounterName, PerfRuleInst.InstanceName, MgEntityType.ManagedEntityTypeSystemName, ComputerId.ManagedEntityRowId

	),

	InstanceFilteredTable AS

	(

	SELECT 

		ComputerName,

		SUM(AverageValue*RuleSampleCount)/ SUM(SUM(RuleSampleCount)) OVER(PARTITION BY ComputerName,ObjectName,CounterName,InstanceName,ManagedEntityRowId)  AverageValue,

		ObjectName,

		CounterName,

		InstanceName,

		PerInstance,

		CASE WHEN (@OrderType = 1) THEN

			(CASE WHEN ((PerInstance = 1) AND (AVG(AverageValue) = MAX(AVG(AverageValue)) OVER(PARTITION BY ComputerName,ObjectName,CounterName)) AND (CounterName &lt;&gt; '% Free Space')) 

					OR (PerInstance = 0) 

					OR ((PerInstance = 1) AND (AVG(AverageValue) = MIN(AVG(AverageValue)) OVER(PARTITION BY ComputerName,ObjectName,CounterName)) AND (CounterName = '% Free Space'))

				THEN 1 ELSE 0 END)

		ELSE

			(CASE WHEN ((PerInstance = 1) AND (AVG(AverageValue) = MIN(AVG(AverageValue)) OVER(PARTITION BY ComputerName,ObjectName,CounterName)) AND (CounterName &lt;&gt; '% Free Space')) 

					OR (PerInstance = 0) 

					OR ((PerInstance = 1) AND (AVG(AverageValue) = MAX(AVG(AverageValue)) OVER(PARTITION BY ComputerName,ObjectName,CounterName)) AND (CounterName = '% Free Space'))

				THEN 1 ELSE 0 END) 

		END MaxFlag,

		ManagedEntityRowId

	FROM

		PerfTable

	GROUP BY

		ComputerName,

		ObjectName,

		CounterName,

		InstanceName,

		PerInstance,

		ManagedEntityRowId

	),

	InstanceRankedTable AS

	(

		SELECT 

			ComputerName,

			AverageValue,

			ObjectName,

			CounterName,

			InstanceName,

			PerInstance,

			ManagedEntityRowId,

			ROW_NUMBER() OVER(PARTITION BY ComputerName,ObjectName,CounterName ORDER BY MaxFlag DESC) MaxFlag

		FROM

			InstanceFilteredTable

	),

	OrderedPerfTable AS

	(

	SELECT

		CASE WHEN (@OrderType = 1)THEN 

			CASE WHEN (CounterName &lt;&gt; '% Free Space') 

			THEN (ROW_NUMBER() OVER(PARTITION BY CounterName ORDER BY AverageValue DESC))

			ELSE (ROW_NUMBER() OVER(PARTITION BY CounterName ORDER BY AverageValue ASC)) END

		ELSE 

			CASE WHEN (CounterName &lt;&gt; '% Free Space') 

			THEN (ROW_NUMBER() OVER(PARTITION BY CounterName ORDER BY AverageValue ASC))

			ELSE (ROW_NUMBER() OVER(PARTITION BY CounterName ORDER BY AverageValue DESC))END  

		END OrderNum,

		ComputerName,

		AverageValue,

		ObjectName,

		CounterName,

		InstanceName,

		ManagedEntityRowId

	FROM 

		InstanceRankedTable

	WHERE

		MaxFlag = 1

	)

	SELECT

		OrderNum,

		ComputerName,

		AverageValue,

		ObjectName,

		CounterName,

		InstanceName,

		ManagedEntityRowId

	FROM

		OrderedPerfTable

	WHERE 

		OrderNum &lt;= @Top



	QuitError:

	DROP TABLE #PerfObjectCounters

	DROP TABLE #ManagementGroups

	DROP TABLE #ManagementPacks

  

	

	IF (@xmlDoc IS NOT NULL)

    EXEC sp_xml_removedocument @xmlDoc

    SET @xmlDoc = NULL



	RETURN @Error



END



GO



GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_Report_WindowsServer_Performace_By_Utilization] TO OpsMgrReader

GO

</Install>

  <Uninstall>

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

BEGIN

        DROP PROCEDURE dbo.[Microsoft_SystemCenter_Report_WindowsServer_Performace_By_Utilization]

END

GO

</Uninstall>

  <Upgrade>		

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

BEGIN

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

END

GO





ALTER PROCEDURE [dbo].[Microsoft_SystemCenter_Report_WindowsServer_Performace_By_Utilization]

	@DataAggregation INT,

	@StartDate DATETIME,

	@EndDate DATETIME,

	@GroupManagedEntityID INT,

	@Top INT,

	@OrderType BIT,

	@ManagementGroupsCsv NVARCHAR(MAX),

	@PerfObjectCounterListXml XML,

	@MPListXml XML

AS

BEGIN



	SET NOCOUNT ON



	DECLARE @ExecError INT

	DECLARE @Error INT

	DECLARE @xmlDoc INT	

	

	-- build a list of all relationship

-- types derived from "System.Containment"

-- get row id of the "containment" relationship type

DECLARE @ContainmentRelationshipTypeRowId int



SELECT @ContainmentRelationshipTypeRowId = RelationshipTypeRowId

FROM vRelationshipType

WHERE (RelationshipTypeSystemName = 'System.Containment')



-- create table to hold all relationship types found

IF (OBJECT_ID('tempdb..#RelationshipType') IS NOT NULL)

  DROP TABLE #RelationshipType



CREATE TABLE #RelationshipType (

   RelationshipTypeRowId int

)



-- use table-valued function to build relationship list

INSERT #RelationshipType (RelationshipTypeRowId)

SELECT RelationshipTypeRowId

FROM dbo.RelationshipDerivedTypeHierarchy(@ContainmentRelationshipTypeRowId, 0)



-- *********************************************

-- *  STEP I: Get member computers

--

--     In this step we're going to figure out

--     which objects are currently members

--     of the group specified for report

-- *********************************************



-- create table to hold all contained objects

IF (OBJECT_ID('tempdb..#ContainedManagedEntity') IS NOT NULL)

  DROP TABLE #ContainedManagedEntity

  

CREATE TABLE #ContainedManagedEntity (

   ManagedEntityRowId	int

  ,[Level]				int

)



-- prepare recursion: put group into contained objects

INSERT #ContainedManagedEntity(ManagedEntityRowId, [Level])

  VALUES (@GroupManagedEntityID, 0)

  

DECLARE @CurrentLevel int

SET @CurrentLevel = 1



DECLARE @RowCount int

SET @RowCount = 1



-- recursively walk down containment hierarchy

WHILE (@RowCount &gt; 0)

BEGIN

  INSERT #ContainedManagedEntity(ManagedEntityRowId, [Level])

  SELECT DISTINCT r.TargetManagedEntityRowId, @CurrentLevel

  FROM vRelationship r

          JOIN #RelationshipType rt ON (r.RelationshipTypeRowId = rt.RelationshipTypeRowId)

          JOIN #ContainedManagedEntity c ON (r.SourceManagedEntityRowId = c.ManagedEntityRowId) AND (c.[Level] = @CurrentLevel - 1)

  WHERE EXISTS (SELECT *

				FROM vRelationshipManagementGroup rmg

				WHERE (rmg.RelationshipRowId = r.RelationshipRowId)

				  AND (GETUTCDATE() BETWEEN rmg.FromDateTime AND ISNULL(rmg.ToDateTime, '99991231'))

			   ) -- membership relationship exists as of NOW

          

  SELECT @RowCount = @@ROWCOUNT

  SET @CurrentLevel = @CurrentLevel + 1

END





	CREATE TABLE #PerfObjectCounters (

		PerfObject VARCHAR(MAX), 

		PerfCounter VARCHAR(MAX),

		PerInstance BIT

		)



	SET @Error = @@ERROR

	IF @Error &lt;&gt; 0 GOTO QuitError



	

	CREATE TABLE #ManagementGroups(ManagementGroupGuid UNIQUEIDENTIFIER)

		

	SET @Error = @@ERROR

	IF @Error &lt;&gt; 0 GOTO QuitError



	EXEC @ExecError = sp_xml_preparedocument @xmlDoc OUTPUT, @PerfObjectCounterListXml

	SET @Error = @@ERROR

	IF @ExecError &lt;&gt; 0 OR @Error &lt;&gt; 0 GOTO QuitError



	INSERT INTO #PerfObjectCounters (PerfObject, PerfCounter, PerInstance)

	SELECT PerfObject, PerfCounter, PerInstance

	FROM OPENXML(@xmlDoc,'/Data/Objects/Object',2) WITH

		(PerfObject VARCHAR(MAX) '@Name',

		 PerfCounter VARCHAR(MAX) '@Counter',

		 PerInstance BIT '@PerInstance'

		 )





	DECLARE @Pos        INT,

			@NextPos    INT,

			@ValueLen   INT,

			@MGList		NVARCHAR(MAX)



			

	



	SET @Pos = 0

	SET @NextPos = 1



	WHILE @NextPos &gt; 0

		BEGIN

			SET @NextPos = CHARINDEX(',', @ManagementGroupsCsv, @Pos + 1)

			SET @ValueLen = (CASE WHEN @NextPos &gt; 0

								  THEN @NextPos

								  ELSE LEN(@ManagementGroupsCsv) + 1

							 END) - @Pos - 1

			INSERT #ManagementGroups (ManagementGroupGuid)

			 VALUES (CONVERT(UNIQUEIDENTIFIER, SUBSTRING(REPLACE(@ManagementGroupsCsv,' ',''), @Pos + 1, @ValueLen)))

			SET @Pos = @NextPos

		END



	IF @ExecError &lt;&gt; 0 GOTO QuitError

	

	CREATE TABLE #ManagementPacks (

	ManagementPackSysName VARCHAR(MAX),

	ManagementPackId INT

	)

	

	EXEC @ExecError = sp_xml_preparedocument @xmlDoc OUTPUT, @MPListXml

		SET @Error = @@ERROR

		IF @ExecError &lt;&gt; 0 OR @Error &lt;&gt; 0 GOTO QuitError



	INSERT INTO #ManagementPacks(ManagementPackSysName, ManagementPackId)

		SELECT ManagementPackSysName, MP.ManagementPackRowId

		FROM OPENXML(@xmlDoc,'/ManagementPacks/ManagementPack',2) WITH

			(ManagementPackSysName VARCHAR(MAX) '@Name')

		INNER JOIN 

			ManagementPack MP ON MP.ManagementPackSystemName = ManagementPackSysName



IF @DataAggregation = 1



	WITH ComputerId AS

	(

		SELECT

			MgEntity.ManagedEntityRowId,

			MgEntity.Name

		FROM

			vManagedEntity MgEntity

		INNER JOIN

			vManagedEntityType MgEntityType ON MgEntity.ManagedEntityTypeRowId = MgEntityType.ManagedEntityTypeRowId

			JOIN #ContainedManagedEntity cme ON MgEntity.ManagedEntityRowId = cme.ManagedEntityRowId

		WHERE

			MgEntityType.ManagedEntityTypeSystemName = 'Microsoft.Windows.Computer'

	),

	PerfTable AS

	(

	SELECT

		MgEntity.[Path] ComputerName,

		SUM(PerfData.SampleCount * PerfData.AverageValue)/(SUM(SUM(PerfData.SampleCount)) OVER(PARTITION BY MgEntity.ManagedEntityRowId, PerfRule.ObjectName, PerfRule.CounterName)) AverageValue,

		PerfRule.ObjectName ObjectName,

		PerfRule.CounterName CounterName,

		PerfRuleInst.InstanceName InstanceName,

		MAX(CAST(#PerfObjectCounters.PerInstance AS INT)) PerInstance,

		ComputerId.ManagedEntityRowId ManagedEntityRowId,

		MAX(PerfRule.RuleRowId) RuleId,

		(SUM(SUM(PerfData.SampleCount)) OVER(PARTITION BY MgEntity.ManagedEntityRowId, PerfRule.ObjectName, PerfRule.CounterName)) RuleSampleCount

	FROM     

		[Perf].vPerfDaily PerfData

		INNER JOIN

			[dbo].vManagedEntity MgEntity ON PerfData.ManagedEntityRowId =  

			MgEntity.ManagedEntityRowId 

		INNER JOIN

			[dbo].vManagedEntityType MgEntityType ON MgEntity.ManagedEntityTypeRowId = 

			MgEntityType.ManagedEntityTypeRowId 

		INNER JOIN

			[dbo].vPerformanceRuleInstance PerfRuleInst ON   

			PerfRuleInst.PerformanceRuleInstanceRowId =  

			PerfData.PerformanceRuleInstanceRowId 

		INNER JOIN

			[dbo].vPerformanceRule PerfRule ON PerfRuleInst.RuleRowId =    

			PerfRule.RuleRowId

		INNER JOIN

			[dbo].vManagementGroup MG ON MgEntity.ManagementGroupRowId = MG.ManagementGroupRowId

		INNER JOIN

			#ManagementGroups ON MG.ManagementGroupGuid = #ManagementGroups.ManagementGroupGuid

		INNER JOIN 

			#PerfObjectCounters ON PerfRule.ObjectName = PerfObject AND PerfRule.CounterName = PerfCounter

		INNER JOIN

			#ManagementPacks ON MgEntityType.ManagementPackRowId = #ManagementPacks.ManagementPackId

		RIGHT JOIN

			ComputerId ON ComputerId.Name = MgEntity.Path

	WHERE     

		  (

			CONVERT(DATETIME, CONVERT(VARCHAR, PerfData.[DateTime], 101),101)

			BETWEEN 

				CONVERT(DATETIME, CONVERT(VARCHAR, @StartDate, 101),101)

			AND 

				CONVERT(DATETIME, CONVERT(VARCHAR, @EndDate, 101),101)

		  ) 

		 AND 

		  (

			MgEntity.[Path] IS NOT NULL

		  )

	GROUP BY

		MgEntity.ManagedEntityRowId, MgEntity.[Path], PerfRule.ObjectName, PerfRule.CounterName, PerfRuleInst.InstanceName, MgEntityType.ManagedEntityTypeSystemName, ComputerId.ManagedEntityRowId

	),

	InstanceFilteredTable AS

	(

	SELECT 

		ComputerName,

		SUM(AverageValue*RuleSampleCount)/ SUM(SUM(RuleSampleCount)) OVER(PARTITION BY ComputerName,ObjectName,CounterName,InstanceName,ManagedEntityRowId)  AverageValue,

		ObjectName,

		CounterName,

		InstanceName,

		PerInstance,

		CASE WHEN (@OrderType = 1) THEN

			(CASE WHEN ((PerInstance = 1) AND (AVG(AverageValue) = MAX(AVG(AverageValue)) OVER(PARTITION BY ComputerName,ObjectName,CounterName)) AND (CounterName &lt;&gt; '% Free Space')) 

					OR (PerInstance = 0) 

					OR ((PerInstance = 1) AND (AVG(AverageValue) = MIN(AVG(AverageValue)) OVER(PARTITION BY ComputerName,ObjectName,CounterName)) AND (CounterName = '% Free Space'))

				THEN 1 ELSE 0 END)

		ELSE

			(CASE WHEN ((PerInstance = 1) AND (AVG(AverageValue) = MIN(AVG(AverageValue)) OVER(PARTITION BY ComputerName,ObjectName,CounterName)) AND (CounterName &lt;&gt; '% Free Space')) 

					OR (PerInstance = 0) 

					OR ((PerInstance = 1) AND (AVG(AverageValue) = MAX(AVG(AverageValue)) OVER(PARTITION BY ComputerName,ObjectName,CounterName)) AND (CounterName = '% Free Space'))

				THEN 1 ELSE 0 END) 

		END MaxFlag,

		ManagedEntityRowId

	FROM

		PerfTable

	GROUP BY

		ComputerName,

		ObjectName,

		CounterName,

		InstanceName,

		PerInstance,

		ManagedEntityRowId

	),

	InstanceRankedTable AS

	(

		SELECT 

			ComputerName,

			AverageValue,

			ObjectName,

			CounterName,

			InstanceName,

			PerInstance,

			ManagedEntityRowId,

			ROW_NUMBER() OVER(PARTITION BY ComputerName,ObjectName,CounterName ORDER BY MaxFlag DESC) MaxFlag

		FROM

			InstanceFilteredTable

	),

	OrderedPerfTable AS

	(

	SELECT

		CASE WHEN (@OrderType = 1)THEN 

			CASE WHEN (CounterName &lt;&gt; '% Free Space') 

			THEN (ROW_NUMBER() OVER(PARTITION BY CounterName ORDER BY AverageValue DESC))

			ELSE (ROW_NUMBER() OVER(PARTITION BY CounterName ORDER BY AverageValue ASC)) END

		ELSE 

			CASE WHEN (CounterName &lt;&gt; '% Free Space') 

			THEN (ROW_NUMBER() OVER(PARTITION BY CounterName ORDER BY AverageValue ASC))

			ELSE (ROW_NUMBER() OVER(PARTITION BY CounterName ORDER BY AverageValue DESC))END  

		END OrderNum,

		ComputerName,

		AverageValue,

		ObjectName,

		CounterName,

		InstanceName,

		ManagedEntityRowId

	FROM 

		InstanceRankedTable

	WHERE

		MaxFlag = 1

	)

	SELECT

		OrderNum,

		ComputerName,

		AverageValue,

		ObjectName,

		CounterName,

		InstanceName,

		ManagedEntityRowId

	FROM

		OrderedPerfTable

	WHERE 

		OrderNum &lt;= @Top



ELSE

			

	WITH ComputerId AS

	(

		SELECT

			MgEntity.ManagedEntityRowId,

			MgEntity.Name

		FROM

			vManagedEntity MgEntity

		INNER JOIN

			vManagedEntityType MgEntityType ON MgEntity.ManagedEntityTypeRowId = MgEntityType.ManagedEntityTypeRowId

			JOIN #ContainedManagedEntity cme ON MgEntity.ManagedEntityRowId = cme.ManagedEntityRowId

		WHERE

			MgEntityType.ManagedEntityTypeSystemName = 'Microsoft.Windows.Computer'

	),

	PerfTable AS

	(

	SELECT

		MgEntity.[Path] ComputerName,

		SUM(PerfData.SampleCount * PerfData.AverageValue)/(SUM(SUM(PerfData.SampleCount)) OVER(PARTITION BY MgEntity.ManagedEntityRowId, PerfRule.ObjectName, PerfRule.CounterName)) AverageValue,

		PerfRule.ObjectName ObjectName,

		PerfRule.CounterName CounterName,

		PerfRuleInst.InstanceName InstanceName,

		MAX(CAST(#PerfObjectCounters.PerInstance AS INT)) PerInstance,

		ComputerId.ManagedEntityRowId ManagedEntityRowId,

		MAX(PerfRule.RuleRowId) RuleId,

		(SUM(SUM(PerfData.SampleCount)) OVER(PARTITION BY MgEntity.ManagedEntityRowId, PerfRule.ObjectName, PerfRule.CounterName)) RuleSampleCount

	FROM     

		[Perf].vPerfHourly PerfData

		INNER JOIN

			[dbo].vManagedEntity MgEntity ON PerfData.ManagedEntityRowId =  

			MgEntity.ManagedEntityRowId 

		INNER JOIN

			[dbo].vManagedEntityType MgEntityType ON MgEntity.ManagedEntityTypeRowId = 

			MgEntityType.ManagedEntityTypeRowId 

		INNER JOIN

			[dbo].vPerformanceRuleInstance PerfRuleInst ON   

			PerfRuleInst.PerformanceRuleInstanceRowId =  

			PerfData.PerformanceRuleInstanceRowId 

		INNER JOIN

			[dbo].vPerformanceRule PerfRule ON PerfRuleInst.RuleRowId =    

			PerfRule.RuleRowId

		INNER JOIN

			[dbo].vManagementGroup MG ON MgEntity.ManagementGroupRowId = MG.ManagementGroupRowId

		INNER JOIN

			#ManagementGroups ON MG.ManagementGroupGuid = #ManagementGroups.ManagementGroupGuid

		INNER JOIN 

			#PerfObjectCounters ON PerfRule.ObjectName = PerfObject AND PerfRule.CounterName = PerfCounter

		INNER JOIN

			#ManagementPacks ON MgEntityType.ManagementPackRowId = #ManagementPacks.ManagementPackId

		RIGHT JOIN

			ComputerId ON ComputerId.Name = MgEntity.Path

	WHERE     

		  (

			CONVERT(DATETIME, CONVERT(VARCHAR, PerfData.[DateTime], 101),101)

			BETWEEN 

				CONVERT(DATETIME, CONVERT(VARCHAR, @StartDate, 101),101)

			AND 

				CONVERT(DATETIME, CONVERT(VARCHAR, @EndDate, 101),101)

		  ) 

		 AND 

		  (

			MgEntity.[Path] IS NOT NULL

		  )

	GROUP BY

		MgEntity.ManagedEntityRowId, MgEntity.[Path], PerfRule.ObjectName, PerfRule.CounterName, PerfRuleInst.InstanceName, MgEntityType.ManagedEntityTypeSystemName, ComputerId.ManagedEntityRowId

	),

	InstanceFilteredTable AS

	(

	SELECT 

		ComputerName,

		SUM(AverageValue*RuleSampleCount)/ SUM(SUM(RuleSampleCount)) OVER(PARTITION BY ComputerName,ObjectName,CounterName,InstanceName,ManagedEntityRowId)  AverageValue,

		ObjectName,

		CounterName,

		InstanceName,

		PerInstance,

		CASE WHEN (@OrderType = 1) THEN

			(CASE WHEN ((PerInstance = 1) AND (AVG(AverageValue) = MAX(AVG(AverageValue)) OVER(PARTITION BY ComputerName,ObjectName,CounterName)) AND (CounterName &lt;&gt; '% Free Space')) 

					OR (PerInstance = 0) 

					OR ((PerInstance = 1) AND (AVG(AverageValue) = MIN(AVG(AverageValue)) OVER(PARTITION BY ComputerName,ObjectName,CounterName)) AND (CounterName = '% Free Space'))

				THEN 1 ELSE 0 END)

		ELSE

			(CASE WHEN ((PerInstance = 1) AND (AVG(AverageValue) = MIN(AVG(AverageValue)) OVER(PARTITION BY ComputerName,ObjectName,CounterName)) AND (CounterName &lt;&gt; '% Free Space')) 

					OR (PerInstance = 0) 

					OR ((PerInstance = 1) AND (AVG(AverageValue) = MAX(AVG(AverageValue)) OVER(PARTITION BY ComputerName,ObjectName,CounterName)) AND (CounterName = '% Free Space'))

				THEN 1 ELSE 0 END) 

		END MaxFlag,

		ManagedEntityRowId

	FROM

		PerfTable

	GROUP BY

		ComputerName,

		ObjectName,

		CounterName,

		InstanceName,

		PerInstance,

		ManagedEntityRowId

	),

	InstanceRankedTable AS

	(

		SELECT 

			ComputerName,

			AverageValue,

			ObjectName,

			CounterName,

			InstanceName,

			PerInstance,

			ManagedEntityRowId,

			ROW_NUMBER() OVER(PARTITION BY ComputerName,ObjectName,CounterName ORDER BY MaxFlag DESC) MaxFlag

		FROM

			InstanceFilteredTable

	),

	OrderedPerfTable AS

	(

	SELECT

		CASE WHEN (@OrderType = 1)THEN 

			CASE WHEN (CounterName &lt;&gt; '% Free Space') 

			THEN (ROW_NUMBER() OVER(PARTITION BY CounterName ORDER BY AverageValue DESC))

			ELSE (ROW_NUMBER() OVER(PARTITION BY CounterName ORDER BY AverageValue ASC)) END

		ELSE 

			CASE WHEN (CounterName &lt;&gt; '% Free Space') 

			THEN (ROW_NUMBER() OVER(PARTITION BY CounterName ORDER BY AverageValue ASC))

			ELSE (ROW_NUMBER() OVER(PARTITION BY CounterName ORDER BY AverageValue DESC))END  

		END OrderNum,

		ComputerName,

		AverageValue,

		ObjectName,

		CounterName,

		InstanceName,

		ManagedEntityRowId

	FROM 

		InstanceRankedTable

	WHERE

		MaxFlag = 1

	)

	SELECT

		OrderNum,

		ComputerName,

		AverageValue,

		ObjectName,

		CounterName,

		InstanceName,

		ManagedEntityRowId

	FROM

		OrderedPerfTable

	WHERE 

		OrderNum &lt;= @Top



	QuitError:

	DROP TABLE #PerfObjectCounters

	DROP TABLE #ManagementGroups

	DROP TABLE #ManagementPacks

  

	

	IF (@xmlDoc IS NOT NULL)

    EXEC sp_xml_removedocument @xmlDoc

    SET @xmlDoc = NULL



	RETURN @Error



END





GO



GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_Report_WindowsServer_Performace_By_Utilization] TO OpsMgrReader

GO

</Upgrade>

</DataWarehouseScript>