[SQL & SCOM] List SCOM groups for a list of servers

SELECT TargetMonitoringObjectDisplayName, SourceMonitoringObjectDisplayName AS 'Group'
FROM RelationshipGenericView
WHERE TargetMonitoringObjectDisplayName in ('MyServerFullName',
'MyServerFullName1')
AND (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'

Result will be like :

TargetMonitoringObjectDisplayName	Group
MyServerFullName1	agent group
MyServerFullName	agent group
MyServerFullName1	Agent Managed Computer Group
MyServerFullName	Agent Managed Computer Group
MyServerFullName1	All Windows Computers
MyServerFullName	All Windows Computers
MyServerFullName1	IIS 2003 Computer Group
MyServerFullName	IIS 2003 Computer Group
MyServerFullName1	IIS Computer Group
MyServerFullName	IIS Computer Group
MyServerFullName1	Windows Server 2003 Computer Group
MyServerFullName	Windows Server 2003 Computer Group
MyServerFullName1	Windows Server Computer Group
MyServerFullName	Windows Server Computer Group
MyServerFullName1	Windows Server Instances Group
MyServerFullName	Windows Server Instances Group

This posting is provided "AS IS" with no warranties.