Group members (DW)
/*
Return Health Service instances hosting one or more instances contained in system.group
*/
USE OperationsManagerDW
SELECT vManagedEntity.DisplayName AS Computer
FROM vManagedEntity INNER JOIN
vManagedEntityType ON vManagedEntity.ManagedEntityTypeRowId = vManagedEntityType.ManagedEntityTypeRowId
WHERE (vManagedEntity.TopLevelHostManagedEntityRowId IN
(SELECT DISTINCT ME1.TopLevelHostManagedEntityRowId
FROM vManagedEntity AS ME2 INNER JOIN
vRelationship ON ME2.ManagedEntityRowId = vRelationship.SourceManagedEntityRowId INNER JOIN
vManagedEntity AS ME1 ON vRelationship.TargetManagedEntityRowId = ME1.ManagedEntityRowId
WHERE (ME2.DisplayName = 'group'))) AND (vManagedEntityType.ManagedEntityTypeSystemName = 'Microsoft.SystemCenter.HealthService')
ORDER BY Computer