I was asked recently how to add a new server to the same groups which another server was already of member of in OpsMgr (add Server 5 to the same groups that Server 1 is a member of). At first glance this looks like a simple request – look at each group and see if the server is a member of the group and then add the new server to the same groups as the first server. However, if you have a large number of groups this isn’t as simple as it sounds. I started investigating doing this through the console and did not find an easy way to do this. Next I investigated doing this through powershell but came up blank. Finally a co-worker located this SQL query which can be run with the actual computer name (replace %computername% with the actual computer name to check in each group for):
SELECT SourceMonitoringObjectDisplayName AS ‘Group’
FROM RelationshipGenericView
WHERE TargetMonitoringObjectDisplayName like (‘%computername%‘)
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’
Thanks to Hamid Yusuff for find this and to the original author who’s article is available at: http://www.virmansec.com/forums/p/97/247.aspx