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),
RuleRowId 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
-- 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
SELECT DISTINCT
#RTTT.ObjectName
,#RTTT.CounterName
,PRI.InstanceName
, MAX(PRI.PerformanceRuleInstanceRowId)'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 AND TME.ToDateTime Is NULL
JOIN vManagementGroup AS VMG ON ME.ManagementGroupRowId = VMG.ManagementGroupRowId
JOIN #RuleTargetTypesAndTheirBaseTypes #RTTT ON #RTTT.TypeRowId = TME.ManagedEntityTypeRowId
JOIN vPerformanceRuleInstance PRI ON #RTTT.RuleRowId = PRI.RuleRowId AND (PRI.InstanceName LIKE @InstanceNamePattern)
WHERE VMG.ManagementGroupGuid = @ManagementGroup
AND EXISTS
(Select PeR.PerformanceRuleInstanceRowId FROM Perf.vPerfHourly PeR
WHERE PeR.PerformanceRuleInstanceRowId=PRI.PerformanceRuleInstanceRowId
AND PeR.ManagedEntityRowId=#CET.ContainedEntityRowId
)
GROUP BY #RTTT.ObjectName, #RTTT.CounterName, PRI.InstanceName,ME.ManagedEntityGuid, ME.Path, ME.DisplayName
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