IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_Visualization_Library_PerformanceCounterListByManagedEntityTypeUsingContainerME' AND UID = SCHEMA_ID('SDK'))
BEGIN
EXECUTE ('CREATE PROCEDURE SDK.Microsoft_SystemCenter_Visualization_Library_PerformanceCounterListByManagedEntityTypeUsingContainerME AS RETURN 1')
END
GO
ALTER PROCEDURE [sdk].[Microsoft_SystemCenter_Visualization_Library_PerformanceCounterListByManagedEntityTypeUsingContainerME]
@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
-- Create #ContainedEntitiesTable to store the container entities and the contained entities.
CREATE TABLE #ContainedEntitiesTable(
ContainerEntityRowId int NULL,
ContainedEntityRowId int NULL)
CREATE INDEX IDX1 ON #ContainedEntitiesTable(ContainedEntityRowId)
-- Create #ContainerEntityTable to contain the container id and the container row id.
-- Create #ManagedEntityTypesTable to contain the types of the managed entities
CREATE TABLE #ManagedEntityTypeTable(
Id int IDENTITY(1,1),
TypeRowId int)
-- This table will be used to store the base types of the given ME, plus the types of the ME
-- as stored in the TypedManagedEntity table.
CREATE TABLE #BaseAndTMEManagedEntityTypeTable(
TypeRowId int)
-- This table stores the unique types that are stored in the #BaseAndDerivedManagedEntityTypesTable
-- table.
CREATE TABLE #UniqueBaseAndTMEManagedEntityTypeTable(
TypeRowId int)
-- Parse the ContainerManagedEntityXml to get the entities into the #ContainerEntityTable
DECLARE @xmlhandle int
IF NOT @ExecError = 0
RAISERROR(777971000, 16, 1
,'ManagedEntityXml'
,@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
INSERT INTO #ManagedEntityTypeTable (TypeRowId)
SELECT MET.ManagedEntityTypeRowId
FROM vManagedEntity ME
JOIN vManagedEntityType MET ON ME.ManagedEntityTypeRowId = MET.ManagedEntityTypeRowId
JOIN #ContainedEntitiesTable #CET ON #CET.ContainedEntityRowId = ME.ManagedEntityRowId
JOIN vManagementGroup AS VMG ON ME.ManagementGroupRowId = VMG.ManagementGroupRowId
WHERE VMG.ManagementGroupGuid = @ManagementGroup
DECLARE @METypeRowIdListTVP
AS SDK.Microsoft_SystemCenter_Visualization_Library_ManagedEntityTypeRowIdValuesTableType;
INSERT INTO @METypeRowIdListTVP
SELECT TypeRowId
FROM #ManagedEntityTypeTable
INSERT INTO #BaseAndTMEManagedEntityTypeTable
SELECT ManagedEntityTypeRowId
FROM dbo.ManagedEntityBaseTypeHierarchyUsingTypeList(@METypeRowIdListTVP, 0)
INSERT INTO #BaseAndTMEManagedEntityTypeTable
SELECT TME.ManagedEntityTypeRowId
FROM #ContainedEntitiesTable #CET
JOIN vManagedEntity ME ON ME.ManagedEntityRowId = #CET.ContainedEntityRowId
JOIN vTypedManagedEntity TME ON TME.ManagedEntityRowId = ME.ManagedEntityRowId
JOIN vManagementGroup AS VMG ON ME.ManagementGroupRowId = VMG.ManagementGroupRowId
WHERE VMG.ManagementGroupGuid = @ManagementGroup
INSERT INTO #UniqueBaseAndTMEManagedEntityTypeTable
SELECT DISTINCT TypeRowId
FROM #BaseAndTMEManagedEntityTypeTable
-- The caller is asking for unique object name, counter and instance names. (such as in the perf counter selection control)
SELECT DISTINCT PR.ObjectName, PR.CounterName, PRI.InstanceName
FROM vPerformanceRule PR
JOIN vPerformanceRuleInstance PRI ON PR.RuleRowId = PRI.RuleRowId
JOIN RuleManagementPackVersion RMV ON RMV.RuleRowId = PR.RuleRowId
JOIN #UniqueBaseAndTMEManagedEntityTypeTable UBDMET ON UBDMET.TypeRowId = RMV.TargetManagedEntityTypeRowId
WHERE ( (PR.ObjectName LIKE @ObjectNamePattern) AND
(PR.CounterName LIKE @CounterNamePattern) AND
(PRI.InstanceName LIKE @InstanceNamePattern))
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_PerformanceCounterListByManagedEntityTypeUsingContainerME TO OpsMgrReader
GO
Home