IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_Visualization_Library_PerformanceCounterListByMultipleManagedEntities' AND UID = SCHEMA_ID('SDK'))
BEGIN
EXECUTE ('CREATE PROCEDURE SDK.Microsoft_SystemCenter_Visualization_Library_PerformanceCounterListByMultipleManagedEntities AS RETURN 1')
END
GO
ALTER PROCEDURE [sdk].[Microsoft_SystemCenter_Visualization_Library_PerformanceCounterListByMultipleManagedEntities]
@ManagementGroup uniqueidentifier,
@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),
RuleRowId int,
AbstractInd bit)
-- Get the row id of the management group
DECLARE @MGRowId int
SELECT @MGRowId = MG.ManagementGroupRowId FROM dbo.ManagementGroup MG
WHERE MG.ManagementGroupGuid = @ManagementGroup
-- Populate this table with the target types of the matching rules.
INSERT INTO #RuleTargetTypesAndTheirBaseTypes
SELECT RMV.TargetManagedEntityTypeRowId, PR.ObjectName, PR.CounterName, PR.RuleRowId, METMPV.AbstractInd
FROM vPerformanceRule PR
JOIN RuleManagementPackVersion RMV ON RMV.RuleRowId = PR.RuleRowId
AND EXISTS (SELECT ManagementPackVersionRowId FROM dbo.ManagementGroupManagementPackVersion M2
WHERE M2.ManagementPackVersionRowId = RMV.ManagementPackVersionRowId
AND M2.LatestVersionInd=1)
JOIN ManagedEntityTypeManagementPackVersion METMPV ON METMPV.ManagedEntityTypeRowId = RMV.TargetManagedEntityTypeRowId
JOIN dbo.ManagementGroupManagementPackVersion MGMPV ON MGMPV.ManagementPackVersionRowId = METMPV.ManagementPackVersionRowId
AND MGMPV.LatestVersionInd=1
AND MGMPV.ManagementGroupRowId=@MGRowId
WHERE ((PR.ObjectName LIKE @ObjectNamePattern)
AND (PR.CounterName LIKE @CounterNamePattern))
-- 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 @currentRuleRowId int
DECLARE @currentAbstractInd bit
SELECT @cntTypes = COUNT(*)
FROM #RuleTargetTypesAndTheirBaseTypes
WHILE (@idx <= @cntTypes)
BEGIN
SELECT @currentTypeRowId = RTTT.TypeRowId,
@currentObjectName = RTTT.ObjectName,
@currentCounterName = RTTT.CounterName,
@currentRuleRowId = RTTT.RuleRowId,
@currentAbstractInd = RTTT.AbstractInd
FROM #RuleTargetTypesAndTheirBaseTypes RTTT
WHERE RTTT.Id = @idx
IF (@currentAbstractInd = 1)
BEGIN
INSERT INTO #RuleTargetTypesAndTheirBaseTypes
SELECT MEDTH.ManagedEntityTypeRowId,
@currentObjectName,
@currentCounterName,
@currentRuleRowId,
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;
-- Gather the entity, rule, and instance info
WITH ContainmentAndTypesCTE AS
(
SELECT
#CT.ContainedManagedEntityRowId,
#CT.ContainerManagedEntityRowId,
TME.ManagedEntityTypeRowId
FROM #ContainedEntitiesTable #CT
JOIN TypedManagedEntity TME ON #CT.ContainedManagedEntityRowId = TME.ManagedEntityRowId AND TME.ToDateTime IS NULL
)
SELECT DISTINCT
CAT.ContainerManagedEntityRowId
,#RTTT.ObjectName
,#RTTT.CounterName
,PRI.InstanceName
,PRI.PerformanceRuleInstanceRowId
,ME.ManagedEntityRowId
FROM ContainmentAndTypesCTE CAT
JOIN vManagedEntity ME ON ME.ManagedEntityRowId = CAT.ContainedManagedEntityRowId
JOIN vManagementGroup AS VMG ON ME.ManagementGroupRowId = VMG.ManagementGroupRowId
JOIN #RuleTargetTypesAndTheirBaseTypes #RTTT ON #RTTT.TypeRowId = CAT.ManagedEntityTypeRowId
JOIN vPerformanceRuleInstance PRI ON #RTTT.RuleRowId = PRI.RuleRowId AND (PRI.InstanceName LIKE @InstanceNamePattern)
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_PerformanceCounterListByMultipleManagedEntities] TO OpsMgrReader
GO
Home