Command Shell Examples
Useful SQL Queries
All groups and their contained instances - Jonathan Almquist on Operations Manager - Site Home - TechNet Blogs

All groups and their contained instances

All groups and their contained instances

  • Comments 2
  • Likes

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'

 

Back to SQL queries main menu

I do not moderate this blog anymore. If you have a question regarding this post, send me a message.

Comments