All groups and their contained instances
SELECT SourceMonitoringObjectDisplayName AS 'Group', TargetMonitoringObjectDisplayName AS 'Member'
FROM RelationshipGenericView
WHERE (SourceMonitoringObjectDisplayName IN
(SELECT ManagedEntityGenericView.DisplayName
FROM ManagedEntityGenericView INNER JOIN
(SELECT BaseManagedEntityId
FROM BaseManagedEntity WITH (NOLOCK)
WHERE (BaseManagedEntityId = TopLevelHostEntityId) AND (BaseManagedEntityId NOT IN
(SELECT R.TargetEntityId
FROM Relationship AS R WITH (NOLOCK) INNER JOIN
dbo.fn_ContainmentRelationshipTypes() AS CRT ON R.RelationshipTypeId = CRT.RelationshipTypeId
WHERE (R.IsDeleted = 0)))) AS GetTopLevelEntities ON
GetTopLevelEntities.BaseManagedEntityId = ManagedEntityGenericView.Id INNER JOIN
(SELECT DISTINCT BaseManagedEntityId
FROM TypedManagedEntity WITH (NOLOCK)
WHERE (ManagedTypeId IN
(SELECT DerivedManagedTypeId
FROM dbo.fn_DerivedManagedTypes(dbo.fn_ManagedTypeId_Group()) AS fn_DerivedManagedTypes_1))) AS GetOnlyGroups ON
GetOnlyGroups.BaseManagedEntityId = ManagedEntityGenericView.Id))
ORDER BY 'Group'
Comments
Anonymous
January 01, 2003
PingBack from http://www.netdeluxo.com/blog/blogs/jonathan-almquist-on-operations-manager-all-groups-and-their/Anonymous
September 07, 2011
Merci Jonathan pour cette requête bien utile :) Microsoft me surprendra toujours dans la façon de concevoir ses modèles de données. Stéphane