[OpsMgr 2007][OpsMgr 2012] Usefull SQL Query / Report to retrieve what is targeting the RMS

Jean-Sébastien DUCHÊNE has just published a SQL query and a report that will be very helpfull for the one that is preparing a migration to SCOM 2012.

Retrieving rules, monitors, tasks and discoveries that target the RMS will be usefull to decide what to do know :

• if you have to modify your own management packs to target MS instead of RMS,
• what provided MP is targeting the RMS (Microsoft Exchange Server 2010 Management Pack, Citrix EdgeSight Management Pack...) and also help you to decide if you have to install the RMS emulator in your new SCOM 2012 environment.

Read more on Jean-Sébastien DUCHENE Blog's

Please find his SQL query that must be launched on the Operations Manager DB.

DECLARE @ManagedType varchar(200)
SET @ManagedType = 'Root Management Server'
SELECT     ManagedTypeView.DisplayName, ManagementPackView.DisplayName AS 'Management Pack', 'Monitors' AS 'Type', ManagedEntityGenericView.DisplayName AS 'Target',
                      MonitorView.DisplayName AS 'Name', MonitorView.Category, MonitorView.Description,
                      CASE MonitorView.Enabled
                      WHEN '0' THEN 'Disabled'
                      WHEN '2' THEN 'Enabled'
                      WHEN '3' THEN 'Enabled'
                      WHEN '4' THEN 'Enabled'
                      End As 'Enabled', CONVERT(VARCHAR(20),
                      MonitorView.TimeAdded, 102) AS 'TimeAdded'
FROM         ManagementPackView INNER JOIN
                      MonitorView WITH(NOLOCK) ON ManagementPackView.Id = MonitorView.ManagementPackId INNER JOIN
                      ManagedEntityGenericView WITH(NOLOCK) ON MonitorView.TargetMonitoringClassId = ManagedEntityGenericView.MonitoringClassId INNER JOIN
                      TypedManagedEntity  WITH(NOLOCK) ON ManagedEntityGenericView.TypedManagedEntityId = TypedManagedEntity.TypedManagedEntityId INNER JOIN
                      ManagedTypeView  WITH(NOLOCK) ON TypedManagedEntity.ManagedTypeId = ManagedTypeView.Id
WHERE     (ManagedTypeView.DisplayName like '%' + @ManagedType + '%')
UNION
SELECT      ManagedTypeView_1.DisplayName, ManagementPackView_1.DisplayName AS 'Management Pack', 'Rules' AS 'Type', ManagedEntityGenericView_1.DisplayName AS 'Target',
                      RuleView.DisplayName AS 'Name', RuleView.Category, RuleView.Description, CASE RuleView.Enabled
                      WHEN '0' THEN 'Disabled'
                      WHEN '2' THEN 'Enabled'
                      WHEN '3' THEN 'Enabled'
                      WHEN '4' THEN 'Enabled'
                      End As 'Enabled', CONVERT(VARCHAR(20), RuleView.TimeAdded, 102)
                      AS 'TimeAdded'
FROM         ManagementPackView AS ManagementPackView_1 INNER JOIN
                      RuleView WITH(NOLOCK) ON ManagementPackView_1.Id = RuleView.ManagementPackId INNER JOIN
                      ManagedEntityGenericView AS ManagedEntityGenericView_1 WITH(NOLOCK) ON
                      RuleView.TargetMonitoringClassId = ManagedEntityGenericView_1.MonitoringClassId INNER JOIN
                      TypedManagedEntity AS TypedManagedEntity_1 WITH(NOLOCK) ON ManagedEntityGenericView_1.TypedManagedEntityId = TypedManagedEntity_1.TypedManagedEntityId INNER JOIN
                      ManagedTypeView As ManagedTypeView_1 WITH(NOLOCK) ON TypedManagedEntity_1.ManagedTypeId = ManagedTypeView_1.Id
WHERE     (ManagedTypeView_1.DisplayName like '%' + @ManagedType + '%')
UNION
SELECT      ManagedTypeView_2.DisplayName, ManagementPackView_2.DisplayName AS 'Management Pack', 'Discoveries' AS 'Type', ManagedEntityGenericView_2.DisplayName AS 'Target',
                      DiscoveryView.DisplayName AS 'Name', case DiscoveryView.Category when 12 THEN 'Discovery' END, DiscoveryView.Description, CASE DiscoveryView.Enabled
                      WHEN '0' THEN 'Disabled'
                      WHEN '2' THEN 'Enabled'
                      WHEN '3' THEN 'Enabled'
                      WHEN '4' THEN 'Enabled'
                      End As 'Enabled', CONVERT(VARCHAR(20), DiscoveryView.TimeAdded, 102)
                      AS 'TimeAdded'
FROM         ManagementPackView AS ManagementPackView_2 INNER JOIN
                      DiscoveryView WITH(NOLOCK) ON ManagementPackView_2.Id = DiscoveryView.ManagementPackId INNER JOIN
                      ManagedEntityGenericView AS ManagedEntityGenericView_2 WITH(NOLOCK) ON
                      DiscoveryView.TargetMonitoringClassId = ManagedEntityGenericView_2.MonitoringClassId INNER JOIN
                      TypedManagedEntity AS TypedManagedEntity_2 WITH(NOLOCK) ON ManagedEntityGenericView_2.TypedManagedEntityId = TypedManagedEntity_2.TypedManagedEntityId INNER JOIN
                      ManagedTypeView As ManagedTypeView_2 WITH(NOLOCK) ON TypedManagedEntity_2.ManagedTypeId = ManagedTypeView_2.Id
WHERE     (ManagedTypeView_2.DisplayName like '%' + @ManagedType + '%')
UNION
SELECT      ManagedTypeView_3.DisplayName, ManagementPackView_3.DisplayName AS 'Management Pack', 'Tasks' AS 'Type', ManagedEntityGenericView_3.DisplayName AS 'Target',
                      RecoveryView.DisplayName AS 'Name', case RecoveryView.Category  when 17 THEN 'Recovery' END, RecoveryView.Description, CASE RecoveryView.Enabled
                      WHEN '0' THEN 'Disabled'
                      WHEN '2' THEN 'Enabled'
                      WHEN '3' THEN 'Enabled'
                      WHEN '4' THEN 'Enabled'
                      End As 'Enabled', CONVERT(VARCHAR(20), RecoveryView.TimeAdded, 102)
                      AS 'TimeAdded'
FROM         ManagementPackView AS ManagementPackView_3 INNER JOIN
                      RecoveryView WITH(NOLOCK) ON ManagementPackView_3.Id = RecoveryView.ManagementPackId INNER JOIN
                      ManagedEntityGenericView AS ManagedEntityGenericView_3 WITH(NOLOCK) ON
                      RecoveryView.TargetMonitoringClassId = ManagedEntityGenericView_3.MonitoringClassId INNER JOIN
                      TypedManagedEntity AS TypedManagedEntity_3 WITH(NOLOCK) ON ManagedEntityGenericView_3.TypedManagedEntityId = TypedManagedEntity_3.TypedManagedEntityId INNER JOIN
                      ManagedTypeView As ManagedTypeView_3 WITH(NOLOCK) ON TypedManagedEntity_3.ManagedTypeId = ManagedTypeView_3.Id
WHERE     (ManagedTypeView_3.DisplayName like '%' + @ManagedType + '%')
ORDER BY 'Management Pack'

Download the report - Recovery Tasks targeting RMS

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