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