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
IF NOT EXISTS (SELECT * FROM sys.types WHERE name = 'Microsoft_SystemCenter_Visualization_Library_ManagedEntityContainmentTableType' AND schema_id = SCHEMA_ID('SDK'))
BEGIN
EXECUTE ('CREATE TYPE SDK.Microsoft_SystemCenter_Visualization_Library_ManagedEntityContainmentTableType AS TABLE ( ContainerManagedEntityRowId int , ContainedManagedEntityRowId int )')
END
GO
IF NOT EXISTS (SELECT * FROM sys.types WHERE name = 'Microsoft_SystemCenter_Visualization_Library_PerfSeriesTableType' AND schema_id = SCHEMA_ID('SDK'))
BEGIN
EXECUTE ('CREATE TYPE SDK.Microsoft_SystemCenter_Visualization_Library_PerfSeriesTableType AS TABLE ( PerfRuleInstanceRowId int, 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(
ContainerEntityRowId int NULL,
ContainedEntityRowId int NULL)
-- Create #ResolvedSeriesTable to store the resolved series for all the containers
-- 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 @idx int = 1
DECLARE @cntContainers int = 0
DECLARE @currentContainerRowId int = 0
DECLARE @ObjectListString NVARCHAR(MAX)
DECLARE @ContainmentTVP
AS SDK.Microsoft_SystemCenter_Visualization_Library_ManagedEntityContainmentTableType;
DECLARE @ContainerEntitiesTVP
AS SDK.Microsoft_SystemCenter_Visualization_Library_ManagedEntityRowIdValuesTableType;
INSERT INTO @ContainerEntitiesTVP
SELECT ContainerEntityRowId
FROM #ScopedContainerEntityListTable
INSERT INTO #ContainedEntitiesTable (ContainerEntityRowId,ContainedEntityRowId)
EXEC [sdk].[Microsoft_SystemCenter_Visualization_Library_MultipleEntityContainmentGet]
@ContainerEntitiesTVP,
100,
0
INSERT INTO @ContainmentTVP (ContainerManagedEntityRowId, ContainedManagedEntityRowId)
SELECT ContainerEntityRowId, ContainedEntityRowId
FROM #ContainedEntitiesTable
INSERT INTO #ResolvedSeriesTable(ManagedEntityRowId, PerformanceRuleInstanceRowId)
SELECT CET.ContainedEntityRowId, PRI.PerformanceRuleInstanceRowId
FROM PerformanceRule PR
JOIN PerformanceRuleInstance PRI ON (PR.RuleRowId = PRI.RuleRowId)
JOIN #ContainedEntitiesTable CET ON (1=1)
WHERE ( (PR.ObjectName = @ObjectNamePattern) AND
(PR.CounterName = @CounterNamePattern) AND
(PRI.InstanceName = @InstanceNamePattern))
SET @idx = 1
DECLARE @cntResolvedSeries int = 0
DECLARE @currentResolvedSeriesMERowId int = 0
DECLARE @currentResolvedSeriesPerfRuleInstanceRowId int = 0
DECLARE @currentResultValue float = 0
SELECT @cntResolvedSeries = COUNT(*)
FROM #ResolvedSeriesTable
declare @PerfSeriesTVP
AS SDK.Microsoft_SystemCenter_Visualization_Library_PerfSeriesTableType;
INSERT INTO @PerfSeriesTVP
SELECT PerformanceRuleInstanceRowId, ManagedEntityRowId
FROM #ResolvedSeriesTable
SELECT @cntTopContainedEntities = COUNT(*)
FROM #TopContainedEntitiesTable
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
GRANT EXECUTE ON TYPE::[SDK].[Microsoft_SystemCenter_Visualization_Library_ManagedEntityContainmentTableType] TO OpsMgrReader
GRANT EXECUTE ON TYPE::[SDK].[Microsoft_SystemCenter_Visualization_Library_PerfSeriesTableType] TO OpsMgrReader
GO
Home