Infrastructure

QuickTricks – How to find out what groups a computer is a member of in OpsMgr

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

Leave a Reply

x

We use cookies to ensure the best possible experience on our website. Detailed information on the use of cookies on this site is provided in our Privacy and Cookie Policy. Further instruction on how to disable our cookies can be found there.