IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_Visualization_Library_PerformanceCounterListByManagedEntityUsingContainerME' AND UID = SCHEMA_ID('SDK'))
BEGIN
EXECUTE ('CREATE PROCEDURE SDK.Microsoft_SystemCenter_Visualization_Library_PerformanceCounterListByManagedEntityUsingContainerME AS RETURN 1')
END
GO
ALTER PROCEDURE [sdk].[Microsoft_SystemCenter_Visualization_Library_PerformanceCounterListByManagedEntityUsingContainerME]
@ManagementGroup uniqueidentifier,
@ContainerManagedEntityXml nvarchar(max),
@ObjectNamePattern nvarchar(256) = '%',
@CounterNamePattern nvarchar(256) = '%',
@InstanceNamePattern nvarchar(256) = '%'
AS
BEGIN
SET NOCOUNT ON
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
-- This table will be used to store the rules' target types and their base types.
CREATE TABLE #RuleTargetTypesAndTheirBaseTypes(
Id int IDENTITY(1,1),
TypeRowId int,
ObjectName nvarchar(256),
CounterName nvarchar(256),
InstanceName nvarchar(256),
PerformanceRuleInstanceRowId int,
AbstractInd bit
)
-- Create #ContainedEntitiesTable to store the container entities and the contained entities.
CREATE TABLE #ContainedEntitiesTable(
ContainerEntityRowId int NULL,
ContainedEntityRowId int NULL)
-- Create #ContainerEntityTable to contain the container id and the container row id.
IF NOT @ExecError = 0
RAISERROR(777971000, 16, 1
,'ContainerManagedEntityXml'
,@ExecError)
INSERT INTO #ContainerEntityTable (ContainerId, ContainerEntityRowId)
SELECT ManagedEntityId, ME.ManagedEntityRowId
FROM
OPENXML(@xmlhandle, '/ManagedEntityIds/ManagedEntityId', 2)
WITH
(ManagedEntityId nvarchar(max) '.') CX
JOIN ManagedEntity ME ON CX.ManagedEntityId = ME.ManagedEntityGuid
DECLARE @ContainerEntitiesTVP
AS SDK.Microsoft_SystemCenter_Visualization_Library_ManagedEntityRowIdValuesTableType;
INSERT INTO @ContainerEntitiesTVP
SELECT ContainerEntityRowId
FROM #ContainerEntityTable
INSERT INTO #ContainedEntitiesTable (ContainerEntityRowId,ContainedEntityRowId)
EXEC [sdk].[Microsoft_SystemCenter_Visualization_Library_MultipleEntityContainmentGet]
@ContainerEntitiesTVP,
100,
0
-- Populate this table with the target types of the matching rules.
INSERT INTO #RuleTargetTypesAndTheirBaseTypes
SELECT RMV.TargetManagedEntityTypeRowId, PR.ObjectName, PR.CounterName, PRI.InstanceName,
PRI.PerformanceRuleInstanceRowId, METMPV.AbstractInd
FROM vPerformanceRule PR
JOIN vPerformanceRuleInstance PRI ON PR.RuleRowId = PRI.RuleRowId
JOIN RuleManagementPackVersion RMV ON RMV.RuleRowId = PR.RuleRowId
JOIN ManagedEntityTypeManagementPackVersion METMPV ON METMPV.ManagedEntityTypeRowId = RMV.TargetManagedEntityTypeRowId
WHERE ( (PR.ObjectName LIKE @ObjectNamePattern) AND
(PR.CounterName LIKE @CounterNamePattern) AND
(PRI.InstanceName LIKE @InstanceNamePattern))
-- Iterate through the table of rule target types and for each abstract type, find the non-abstract types
DECLARE @idx int = 1
DECLARE @cntTypes int = 0
DECLARE @currentTypeRowId int
DECLARE @currentObjectName nvarchar(256)
DECLARE @currentCounterName nvarchar(256)
DECLARE @currentInstanceName nvarchar(256)
DECLARE @currentPerformanceRuleInstanceRowId int
DECLARE @currentAbstractInd bit
SELECT @cntTypes = COUNT(*)
FROM #RuleTargetTypesAndTheirBaseTypes
WHILE (@idx <= @cntTypes)
BEGIN
SELECT @currentTypeRowId = RTTT.TypeRowId,
@currentObjectName = RTTT.ObjectName,
@currentCounterName = RTTT.CounterName,
@currentInstanceName = RTTT.InstanceName,
@currentPerformanceRuleInstanceRowId = RTTT.PerformanceRuleInstanceRowId,
@currentAbstractInd = RTTT.AbstractInd
FROM #RuleTargetTypesAndTheirBaseTypes RTTT
WHERE RTTT.Id = @idx
IF (@currentAbstractInd = 1)
BEGIN
INSERT INTO #RuleTargetTypesAndTheirBaseTypes
SELECT MEDTH.ManagedEntityTypeRowId, @currentObjectName,
@currentCounterName, @currentInstanceName,
@currentPerformanceRuleInstanceRowId,
METMPV.AbstractInd
FROM dbo.ManagedEntityDerivedTypeHierarchy(@currentTypeRowId, 0) MEDTH
JOIN ManagedEntityTypeManagementPackVersion METMPV ON METMPV.ManagedEntityTypeRowId = MEDTH.ManagedEntityTypeRowId
WHERE MEDTH.Level > 0 AND METMPV.AbstractInd = 0
END
SET @idx = @idx + 1
END
-- Select the appropriate columns from the below tables.
SELECT DISTINCT #RTTT.ObjectName, #RTTT.CounterName, #RTTT.InstanceName, #RTTT.PerformanceRuleInstanceRowId ,
ME.ManagedEntityGuid, ME.Path, ME.DisplayName
FROM vManagedEntity ME
JOIN #ContainedEntitiesTable #CET ON #CET.ContainedEntityRowId = ME.ManagedEntityRowId
JOIN TypedManagedEntity TME ON ME.ManagedEntityRowId = TME.ManagedEntityRowId
JOIN vManagementGroup AS VMG ON ME.ManagementGroupRowId = VMG.ManagementGroupRowId
JOIN #RuleTargetTypesAndTheirBaseTypes #RTTT ON #RTTT.TypeRowId = TME.ManagedEntityTypeRowId
WHERE VMG.ManagementGroupGuid = @ManagementGroup
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_PerformanceCounterListByManagedEntityUsingContainerME TO OpsMgrReader
GO
Home