IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_Visualization_Library_TopNEntitiesByPerfGet' AND UID = SCHEMA_ID('SDK'))
BEGIN
EXECUTE ('CREATE PROCEDURE SDK.Microsoft_SystemCenter_Visualization_Library_TopNEntitiesByPerfGet AS RETURN 1')
END
GO
IF NOT EXISTS (SELECT * FROM sys.types WHERE name = 'Microsoft_SystemCenter_Visualization_Library_ManagedEntityRowIdValuesTableType' AND schema_id = SCHEMA_ID('SDK'))
BEGIN
EXECUTE ('CREATE TYPE SDK.Microsoft_SystemCenter_Visualization_Library_ManagedEntityRowIdValuesTableType AS TABLE ( ManagedEntityRowId int )')
END
GO
DECLARE
@ErrorInd bit
,@ErrorMessage nvarchar(max)
,@ErrorNumber int
,@ErrorSeverity int
,@ErrorState int
,@ErrorLine int
,@ErrorProcedure nvarchar(256)
,@ErrorMessageText nvarchar(max)
SET @ErrorInd = 0
/* ------------------------------ */
BEGIN TRY
DECLARE @ExecError int
-- Create #ScopedContainerEntityListTable to contain the GUIDs for all of the container class types that we care about
CREATE TABLE #ScopedContainerEntityListTable(
RowId int IDENTITY(1,1),
ContainerId uniqueidentifier,
ContainerEntityRowId int)
-- Create #ContainedEntitiesTable to store the container entities and the contained entities.
CREATE TABLE #ContainedEntitiesTable(
RowId int NOT NULL IDENTITY(1,1),
ContainerManagedEntityRowId int NOT NULL,
ContainedManagedEntityRowId int NOT NULL,
PRIMARY KEY (ContainedManagedEntityRowId, RowId))
-- Create #ResolvedPerfInstancesTable to contain all the valid perf counter instances for the contained entities
CREATE TABLE #ResolvedPerfInstancesTable(
ContainerManagedEntityRowId int,
ObjectName nvarchar(256),
CounterName nvarchar(256),
InstanceName nvarchar(256),
PerformanceRuleInstanceRowId int,
ManagedEntityRowId int)
-- Create the #PerfSeriesResultTable table that stores the values for each series.
CREATE TABLE #PerfSeriesResultTable(
PerformanceRuleInstanceRowId int,
ManagedEntityRowId int,
Result float)
-- Create ##TopContainedEntitiesTable to store the top resolved series for all the contained entities
CREATE TABLE #TopContainedEntitiesTable(
RowId int IDENTITY(1,1),
ContainedEntityId uniqueidentifier NULL,
ContainedEntityRowId int NULL,
Value float NULL,
ObjectName nvarchar(256) NULL,
CounterName nvarchar(256) NULL,
InstanceName nvarchar(256) NULL,
ContainedEntityPath nvarchar(max) NULL,
ContainedEntityDisplayName nvarchar(max) NULL)
-- Parse the #ScopedContainerEntityListTable to get the GUIDs into the #ScopedContainerEntityListTable
DECLARE @xmlhandleScopedMEs int
IF NOT @ExecError = 0
RAISERROR(777971000, 16, 1
,'ScopedEntityListXml'
,@ExecError)
INSERT INTO #ScopedContainerEntityListTable (ContainerId, ContainerEntityRowId)
SELECT Id, ME.ManagedEntityRowId
FROM
OPENXML(@xmlhandleScopedMEs, '/ManagedEntityIds/Id', 2)
WITH
(Id nvarchar(max) '.') CX
JOIN ManagedEntity ME ON CX.Id = ME.ManagedEntityGuid
-- #ScopedContainerEntityListTable is now set up
DECLARE @ContainerEntitiesTVP
AS SDK.Microsoft_SystemCenter_Visualization_Library_ManagedEntityRowIdValuesTableType;
INSERT INTO @ContainerEntitiesTVP
SELECT ContainerEntityRowId
FROM #ScopedContainerEntityListTable
-- Get the entities that are contained in the entities passed into the sproc (to a certain depth)
INSERT INTO #ContainedEntitiesTable (ContainerManagedEntityRowId,ContainedManagedEntityRowId)
EXEC [sdk].[Microsoft_SystemCenter_Visualization_Library_MultipleEntityContainmentGet]
@ContainerEntitiesTVP,
100,
0
-- Use existing sprocs to narrow down the perf counter instances that are valid for the contained types. Avoids duplicating that logic here.
INSERT INTO #ResolvedPerfInstancesTable (ContainerManagedEntityRowId, ObjectName, CounterName, InstanceName, PerformanceRuleInstanceRowId, ManagedEntityRowId)
EXEC [sdk].[Microsoft_SystemCenter_Visualization_Library_PerformanceCounterListByMultipleManagedEntities]
@ManagementGroup = @ManagementGroup,
@ObjectNamePattern= @ObjectNamePattern,
@CounterNamePattern = @CounterNamePattern,
@InstanceNamePattern = @InstanceNamePattern
SELECT @cntTopContainedEntities = COUNT(*)
FROM #TopContainedEntitiesTable
-- Populate the entity IDs and display names in the result table
WHILE (@RowId <= @cntTopContainedEntities)
BEGIN
SELECT @currentTopContainedRowId = ContainedEntityRowId,
@currentTopContainedValue = Value
FROM #TopContainedEntitiesTable
WHERE RowId = @RowId
SELECT @currentTopContainedGuid = ME.ManagedEntityGuid,
@currentTopContainedDisplayName = ME.DisplayName,
@currentTopContainedPath = ME.Path
FROM ManagedEntity ME
WHERE ME.ManagedEntityRowId = @currentTopContainedRowId
UPDATE #TopContainedEntitiesTable
SET
ContainedEntityId = @currentTopContainedGuid,
ContainedEntityDisplayName = @currentTopContainedDisplayName
WHERE RowId = @RowId
SET @RowId = @RowId + 1
END
IF (@OrderBy = 0)
SELECT TCT.ContainedEntityId as Id, TCT.ContainedEntityDisplayName, TCT.Value, TCT.ObjectName, TCT.CounterName, TCT.InstanceName
FROM #TopContainedEntitiesTable AS TCT
ORDER BY TCT.Value ASC
ELSE
SELECT TCT.ContainedEntityId as Id, TCT.ContainedEntityDisplayName, TCT.Value, TCT.ObjectName, TCT.CounterName, TCT.InstanceName
FROM #TopContainedEntitiesTable AS TCT
ORDER BY Value DESC
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRAN
-- report error if any
IF (@ErrorInd = 1)
BEGIN
DECLARE @AdjustedErrorSeverity int
SET @AdjustedErrorSeverity = CASE
WHEN @ErrorSeverity > 18 THEN 18
ELSE @ErrorSeverity
END
RAISERROR (777971002, @AdjustedErrorSeverity, 1
,@ErrorNumber
,@ErrorSeverity
,@ErrorState
,@ErrorProcedure
,@ErrorLine
,@ErrorMessageText
)
END
END
GO
GRANT EXECUTE ON [SDK].[Microsoft_SystemCenter_Visualization_Library_TopNEntitiesByPerfGet] TO OpsMgrReader
GRANT EXECUTE ON TYPE::[SDK].[Microsoft_SystemCenter_Visualization_Library_ManagedEntityRowIdValuesTableType] TO OpsMgrReader
GO
Home