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’

 

Back to SQL queries main menu

Comments (2)

  1. Stéphane Gaudé says:

    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