| IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_Report_Performace_By_Utilization')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_Report_Performace_By_Utilization] AS RETURN 1')
END
GO ALTER PROCEDURE [dbo].[Microsoft_SystemCenter_Report_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 > 0)
BEGIN
INSERT #ContainedManagedEntity(ManagedEntityRowId, [Level])
SELECT 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 <> 0 GOTO QuitError CREATE TABLE #ManagementGroups(ManagementGroupGuid UNIQUEIDENTIFIER)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError EXEC @ExecError = sp_xml_preparedocument @xmlDoc OUTPUT, @PerfObjectCounterListXml
SET @Error = @@ERROR
IF @ExecError <> 0 OR @Error <> 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 > 0
BEGIN
SET @NextPos = CHARINDEX(',', @ManagementGroupsCsv, @Pos + 1)
SET @ValueLen = (CASE WHEN @NextPos > 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 <> 0 GOTO QuitError
CREATE TABLE #ManagementPacks (
ManagementPackSysName VARCHAR(MAX),
ManagementPackId INT
)
EXEC @ExecError = sp_xml_preparedocument @xmlDoc OUTPUT, @MPListXml
SET @Error = @@ERROR
IF @ExecError <> 0 OR @Error <> 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))
BETWEEN
CONVERT(DATETIME, CONVERT(VARCHAR, @StartDate, 101))
AND
CONVERT(DATETIME, CONVERT(VARCHAR, @EndDate, 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 <> '% 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 <> '% 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 <> '% Free Space')
THEN (ROW_NUMBER() OVER(PARTITION BY ObjectName, CounterName ORDER BY AverageValue DESC))
ELSE (ROW_NUMBER() OVER(PARTITION BY ObjectName, CounterName ORDER BY AverageValue ASC)) END
ELSE
CASE WHEN (CounterName <> '% Free Space')
THEN (ROW_NUMBER() OVER(PARTITION BY ObjectName, CounterName ORDER BY AverageValue ASC))
ELSE (ROW_NUMBER() OVER(PARTITION BY ObjectName, 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 <= @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))
BETWEEN
CONVERT(DATETIME, CONVERT(VARCHAR, @StartDate, 101))
AND
CONVERT(DATETIME, CONVERT(VARCHAR, @EndDate, 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 <> '% 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 <> '% 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 <> '% Free Space')
THEN (ROW_NUMBER() OVER(PARTITION BY ObjectName, CounterName ORDER BY AverageValue DESC))
ELSE (ROW_NUMBER() OVER(PARTITION BY ObjectName, CounterName ORDER BY AverageValue ASC)) END
ELSE
CASE WHEN (CounterName <> '% Free Space')
THEN (ROW_NUMBER() OVER(PARTITION BY ObjectName, CounterName ORDER BY AverageValue ASC))
ELSE (ROW_NUMBER() OVER(PARTITION BY ObjectName, 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 <= @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_Performace_By_Utilization] TO OpsMgrReader
GO |
