SCOM 2012: SQL query to find the destination management pack for a group

Question

I'm trying to find out the destination management pack for an existing group by querying the OperationsManager database. I was hoping someone on stack has already solved this issue.

Answer 1

There are two different situations. If a group is created manually in Console, then it's a singleton class, and the class is the group itself. So, say, deleting its MP you delete the group.

Another situation is when a group class is defined, but not marked as singleton. In this case, there must be a discovery which finds zero or more instances of this group. In this case, you cannot delete a single group instance (only discovery can do that). But if you delete the MP, where group class is defined, then you delete all group instances.

Use the following query to find MP where group/group class is defined. The query also shows if the group class is singleton or not.

select bme.DisplayName, mp.MPName, mp.MPFriendlyName, mt.IsSingleton
  from BaseManagedEntity bme
  join TypedManagedEntity tme on tme.BaseManagedEntityId = bme.BaseManagedEntityId
  join ManagedType mt on mt.ManagedTypeId = tme.ManagedTypeId
  join ManagementPack mp on mp.ManagementPackId = mt.ManagementPackId
  where DisplayName = '<group display name>'

Please note. Groups with multiple instances in Console are shown as "Class Discplay Name/Group Instance Name". For querying purpose use only "Group Instance Name".