Sometimes it is necessary to not only return a list of objects contained in a group, but also return all or specific objects nested in a group at different levels. For this we can leverage a built-in store procedure that is in your Operations Manager Data Warehouse. This procedure is used under the hood for the report group object picker. I’m simply leveraging it in this example to demonstrate how to get current group membership.

This is a good example group, since it contains several nested group each containing different types of objects.

There are three parameter you can change in this example:

GroupDisplayName = name of group you want to check

LevelCount = how far to look into a nested group scenario (this can also be used to find all hosted objects of group members)

StartLevel = at which level do you want to begin returning group/object membership (e.g.; do not return membership of root group, but start at second level)

This should be executed on your Operations Manager Data Warehouse, not the operational database.

 

 

DECLARE @GroupDisplayName as varchar(max) = 'All Operations Manager Objects Group',
        @LevelCount as int = 

1

,
        @StartLevel as int = 

1

,
        @ObjectListDate as datetime = getutcdate(),
        @ObjectList as xml

SET @ObjectList = '<Data><Objects><Object Use="Containment">' + 
    convert(varchar,(
        SELECT ManagedEntityRowId 
        FROM vManagedEntity 
        WHERE displayName = @GroupDisplayName)) + 
        '</Object></Objects></Data>'

CREATE TABLE #ObjectList (ManagedEntityRowId int)
INSERT INTO #ObjectList (ManagedEntityRowId)
EXEC [Microsoft_SystemCenter_DataWarehouse_Report_Library_ReportObjectListParse]
    @ObjectList = @ObjectList,
    @StartDate = @ObjectListDate,
    @EndDate = @ObjectListDate,
    @ContainmentLevelCount = @LevelCount,
    @ContainmentStartLevel = @StartLevel

SELECT ISNULL(vme.Path,vme.DisplayName) AS DisplayName, ManagedEntityDefaultName
FROM vManagedEntity as vme inner join
    #ObjectList on #ObjectList.ManagedEntityRowId = vme.ManagedEntityRowId

DROP TABLE #ObjectList

 

 

 

If you wanted to return a history of group membership, you could replace StartDate and EndDate parameters with your desired range.

 

 

 

_