[OpsMgr 2007 R2][OpsMgr 2012] Usefull Query / report to list all powershell sripts in your MPs

If you are working on an OpsMgr 2012 migration, this posting should be very interesting.
Once again Jean-Sebastien DUCHENE as worked on a very usefull report / SQL Query to help us to manage our migration. This time, we are able to list all discoveries, monitors, rules, tasks and recovery tasks that contain PowerShell scripts in your OpsMgr 2007 R2 environment. You'll also be able to retrieve MPs to modify to use the news SCOM 2012 cmdlet.

Here is the query provided by Jean-Sebastien DUCHENE to be executed on your Operations Manager DB :

SELECT ManagementPackView.FriendlyName As 'Management Pack'
   ,'Monitor' As 'Type'
   ,MonitorView.Name As 'Name'
      ,ManagedTypeView.Name As 'Class Targeted'
      ,Case Enabled When '4' Then 'Enabled'
      When '0' Then 'Disabled' END As 'Enabled'
      ,Category
      ,Priority
      ,'Null' As 'Module Name'
   ,SUBSTRING(SubString(SubString(ConfigurationXml,0,CharIndex('</ScriptName>',ConfigurationXml)), CharIndex('<ScriptName>',SubString(ConfigurationXml,0,CharIndex('</ScriptName>',ConfigurationXml))),LEN(SubString(ConfigurationXml,0,CharIndex('</ScriptName>',ConfigurationXml)))), CharIndex('>',SubString(SubString(ConfigurationXml,0,CharIndex('</ScriptName>',ConfigurationXml)), CharIndex('<ScriptName>',SubString(ConfigurationXml,0,CharIndex('</ScriptName>',ConfigurationXml))),LEN(SubString(ConfigurationXml,0,CharIndex('</ScriptName>',ConfigurationXml))))) + 1,LEN(SubString(SubString(ConfigurationXml,0,CharIndex('</ScriptName>',ConfigurationXml)), CharIndex('<ScriptName>',SubString(ConfigurationXml,0,CharIndex('</ScriptName>',ConfigurationXml))),LEN(SubString(ConfigurationXml,0,CharIndex('</ScriptName>',ConfigurationXml))))))  As 'Script Name'
   ,ConfigurationXml As 'Module Configuration'
      ,MonitorView.LastModified
 FROM MonitorView
  INNER JOIN ManagementPackView WITH(NOLOCK) ON MonitorView.ManagementPackId = ManagementPackView.Id
  INNER JOIN ManagedTypeView WITH(NOLOCK) ON MonitorView.TargetMonitoringClassId = ManagedTypeView.Id
  WHERE  MonitorView.ConfigurationXml like '%.PS1%'
UNION
SELECT ManagementPackView.FriendlyName As 'Management Pack'
   ,'Rule' As 'Type'
   ,RuleView.Name As 'Name'
      ,ManagedTypeView.Name As 'Class Targeted'
      ,Case RuleView.Enabled When '4' Then 'Enabled'
      When '0' Then 'Disabled' END As 'Enabled'
      ,Category
      ,Priority
      ,RuleModuleName As 'Module Name'
   ,SUBSTRING(SubString(SubString(RuleModuleConfiguration,0,CharIndex('</ScriptName>',RuleModuleConfiguration)), CharIndex('<ScriptName>',SubString(RuleModuleConfiguration,0,CharIndex('</ScriptName>',RuleModuleConfiguration))),LEN(SubString(RuleModuleConfiguration,0,CharIndex('</ScriptName>',RuleModuleConfiguration)))), CharIndex('>',SubString(SubString(RuleModuleConfiguration,0,CharIndex('</ScriptName>',RuleModuleConfiguration)), CharIndex('<ScriptName>',SubString(RuleModuleConfiguration,0,CharIndex('</ScriptName>',RuleModuleConfiguration))),LEN(SubString(RuleModuleConfiguration,0,CharIndex('</ScriptName>',RuleModuleConfiguration))))) + 1,LEN(SubString(SubString(RuleModuleConfiguration,0,CharIndex('</ScriptName>',RuleModuleConfiguration)), CharIndex('<ScriptName>',SubString(RuleModuleConfiguration,0,CharIndex('</ScriptName>',RuleModuleConfiguration))),LEN(SubString(RuleModuleConfiguration,0,CharIndex('</ScriptName>',RuleModuleConfiguration)))))) + SUBSTRING(SubString(SubString(RuleModuleConfiguration,0,CharIndex('</CommandLine>',RuleModuleConfiguration)), CharIndex('<CommandLine>',SubString(RuleModuleConfiguration,0,CharIndex('</CommandLine>',RuleModuleConfiguration))),LEN(SubString(RuleModuleConfiguration,0,CharIndex('</CommandLine>',RuleModuleConfiguration)))), CharIndex('>',SubString(SubString(RuleModuleConfiguration,0,CharIndex('</CommandLine>',RuleModuleConfiguration)), CharIndex('<CommandLine>',SubString(RuleModuleConfiguration,0,CharIndex('</CommandLine>',RuleModuleConfiguration))),LEN(SubString(RuleModuleConfiguration,0,CharIndex('</CommandLine>',RuleModuleConfiguration))))) + 1,LEN(SubString(SubString(RuleModuleConfiguration,0,CharIndex('</CommandLine>',RuleModuleConfiguration)), CharIndex('<CommandLine>',SubString(RuleModuleConfiguration,0,CharIndex('</CommandLine>',RuleModuleConfiguration))),LEN(SubString(RuleModuleConfiguration,0,CharIndex('</CommandLine>',RuleModuleConfiguration)))))) As 'Script Name'
   ,RuleModuleConfiguration As 'Module Configuration'
      ,RuleView.LastModified
  FROM RuleModule
  INNER JOIN RuleView WITH(NOLOCK) ON RuleModule.RuleId = RuleView.Id
  INNER JOIN ManagedTypeView WITH(NOLOCK) ON RuleView.TargetMonitoringClassId = ManagedTypeView.Id
  INNER JOIN ManagementPackView WITH(NOLOCK) ON RuleView.ManagementPackId = ManagementPackView.Id
  WHERE RuleModuleConfiguration like '%.PS1%'
  UNION
  SELECT ManagementPackView.FriendlyName As 'Management Pack'
   ,'Discovery' As 'Type'
   ,DiscoveryView.Name As 'Name'
      ,ManagedTypeView.Name As 'Class Targeted'
      ,Case Enabled When '4' Then 'Enabled'
      When '0' Then 'Disabled' END As 'Enabled'
      ,Case Category When '12' Then 'Discovery' Else 'Unknown' END 'Category'
      ,Priority
      ,Module.ModuleName As 'Module Name'
   ,SUBSTRING(SubString(SubString(Module.ModuleConfiguration,0,CharIndex('</ScriptName>',Module.ModuleConfiguration)), CharIndex('<ScriptName>',SubString(Module.ModuleConfiguration,0,CharIndex('</ScriptName>',Module.ModuleConfiguration))),LEN(SubString(Module.ModuleConfiguration,0,CharIndex('</ScriptName>',Module.ModuleConfiguration)))), CharIndex('>',SubString(SubString(Module.ModuleConfiguration,0,CharIndex('</ScriptName>',Module.ModuleConfiguration)), CharIndex('<ScriptName>',SubString(Module.ModuleConfiguration,0,CharIndex('</ScriptName>',Module.ModuleConfiguration))),LEN(SubString(Module.ModuleConfiguration,0,CharIndex('</ScriptName>',Module.ModuleConfiguration))))) + 1,LEN(SubString(SubString(Module.ModuleConfiguration,0,CharIndex('</ScriptName>',Module.ModuleConfiguration)), CharIndex('<ScriptName>',SubString(Module.ModuleConfiguration,0,CharIndex('</ScriptName>',Module.ModuleConfiguration))),LEN(SubString(Module.ModuleConfiguration,0,CharIndex('</ScriptName>',Module.ModuleConfiguration)))))) + SUBSTRING(SubString(SubString(Module.ModuleConfiguration,0,CharIndex('</CommandLine>',Module.ModuleConfiguration)), CharIndex('<CommandLine>',SubString(Module.ModuleConfiguration,0,CharIndex('</CommandLine>',Module.ModuleConfiguration))),LEN(SubString(Module.ModuleConfiguration,0,CharIndex('</CommandLine>',Module.ModuleConfiguration)))), CharIndex('>',SubString(SubString(Module.ModuleConfiguration,0,CharIndex('</CommandLine>',Module.ModuleConfiguration)), CharIndex('<CommandLine>',SubString(Module.ModuleConfiguration,0,CharIndex('</CommandLine>',Module.ModuleConfiguration))),LEN(SubString(Module.ModuleConfiguration,0,CharIndex('</CommandLine>',Module.ModuleConfiguration))))) + 1,LEN(SubString(SubString(Module.ModuleConfiguration,0,CharIndex('</CommandLine>',Module.ModuleConfiguration)), CharIndex('<CommandLine>',SubString(Module.ModuleConfiguration,0,CharIndex('</CommandLine>',Module.ModuleConfiguration))),LEN(SubString(Module.ModuleConfiguration,0,CharIndex('</CommandLine>',Module.ModuleConfiguration)))))) As 'Script Name'
      ,Module.ModuleConfiguration As 'Module Configuration'
      ,DiscoveryView.LastModified
  FROM DiscoveryView
  INNER JOIN Module WITH(NOLOCK) ON DiscoveryView.Id = Module.ParentId
  INNER JOIN ManagementPackView WITH(NOLOCK) ON DiscoveryView.ManagementPackId = ManagementPackView.Id
  INNER JOIN ManagedTypeView WITH(NOLOCK) ON DiscoveryView.TargetMonitoringClassId = ManagedTypeView.Id
  WHERE Module.ModuleConfiguration like '%.PS1%'
  UNION
  SELECT ManagementPackView.FriendlyName As 'Management Pack'
   ,'Recovery Task' As 'Type'
   ,RecoveryView.Name As 'Name'
      ,ManagedTypeView.Name As 'Class Targeted'
      ,Case Enabled When '4' Then 'Enabled'
      When '0' Then 'Disabled' END As 'Enabled'
      ,Case Category When '7' Then 'Custom' Else 'Unknown' END AS 'Category'
      ,'0' As 'Priority'
      ,Module.ModuleName As 'Module Name'
   ,SUBSTRING(SubString(SubString(Module.ModuleConfiguration,0,CharIndex('</ScriptName>',Module.ModuleConfiguration)), CharIndex('<ScriptName>',SubString(Module.ModuleConfiguration,0,CharIndex('</ScriptName>',Module.ModuleConfiguration))),LEN(SubString(Module.ModuleConfiguration,0,CharIndex('</ScriptName>',Module.ModuleConfiguration)))), CharIndex('>',SubString(SubString(Module.ModuleConfiguration,0,CharIndex('</ScriptName>',Module.ModuleConfiguration)), CharIndex('<ScriptName>',SubString(Module.ModuleConfiguration,0,CharIndex('</ScriptName>',Module.ModuleConfiguration))),LEN(SubString(Module.ModuleConfiguration,0,CharIndex('</ScriptName>',Module.ModuleConfiguration))))) + 1,LEN(SubString(SubString(Module.ModuleConfiguration,0,CharIndex('</ScriptName>',Module.ModuleConfiguration)), CharIndex('<ScriptName>',SubString(Module.ModuleConfiguration,0,CharIndex('</ScriptName>',Module.ModuleConfiguration))),LEN(SubString(Module.ModuleConfiguration,0,CharIndex('</ScriptName>',Module.ModuleConfiguration))))))  As 'Script Name'
      ,Module.ModuleConfiguration As 'Module Configuration'
      ,RecoveryView.LastModified
  FROM RecoveryView
  INNER JOIN Module WITH(NOLOCK) ON RecoveryView.Id = Module.ParentId
  INNER JOIN ManagementPackView WITH(NOLOCK) ON RecoveryView.ManagementPackId = ManagementPackView.Id
  INNER JOIN ManagedTypeView WITH(NOLOCK) ON RecoveryView.TargetMonitoringClassId = ManagedTypeView.Id
  WHERE Module.ModuleConfiguration like '%.PS1%'
  UNION
  SELECT ManagementPackView.FriendlyName As 'Management Pack'
   ,'Task' As 'Type'
   ,TaskView.Name As 'Name'
      ,ManagedTypeView.Name As 'Class Targeted'
      ,Case Enabled When '4' Then 'Enabled'
      When '0' Then 'Disabled' END As 'Enabled'
      ,Case Category When '7' Then 'Custom' Else 'Unknown' END AS 'Category'
      ,'0' As 'Priority'
      ,Module.ModuleName As 'Module Name'
   ,SUBSTRING(SubString(SubString(Module.ModuleConfiguration,0,CharIndex('</ScriptName>',Module.ModuleConfiguration)), CharIndex('<ScriptName>',SubString(Module.ModuleConfiguration,0,CharIndex('</ScriptName>',Module.ModuleConfiguration))),LEN(SubString(Module.ModuleConfiguration,0,CharIndex('</ScriptName>',Module.ModuleConfiguration)))), CharIndex('>',SubString(SubString(Module.ModuleConfiguration,0,CharIndex('</ScriptName>',Module.ModuleConfiguration)), CharIndex('<ScriptName>',SubString(Module.ModuleConfiguration,0,CharIndex('</ScriptName>',Module.ModuleConfiguration))),LEN(SubString(Module.ModuleConfiguration,0,CharIndex('</ScriptName>',Module.ModuleConfiguration))))) + 1,LEN(SubString(SubString(Module.ModuleConfiguration,0,CharIndex('</ScriptName>',Module.ModuleConfiguration)), CharIndex('<ScriptName>',SubString(Module.ModuleConfiguration,0,CharIndex('</ScriptName>',Module.ModuleConfiguration))),LEN(SubString(Module.ModuleConfiguration,0,CharIndex('</ScriptName>',Module.ModuleConfiguration)))))) + SUBSTRING(SubString(SubString(Module.ModuleConfiguration,0,CharIndex('</CommandLine>',Module.ModuleConfiguration)), CharIndex('<CommandLine>',SubString(Module.ModuleConfiguration,0,CharIndex('</CommandLine>',Module.ModuleConfiguration))),LEN(SubString(Module.ModuleConfiguration,0,CharIndex('</CommandLine>',Module.ModuleConfiguration)))), CharIndex('>',SubString(SubString(Module.ModuleConfiguration,0,CharIndex('</CommandLine>',Module.ModuleConfiguration)), CharIndex('<CommandLine>',SubString(Module.ModuleConfiguration,0,CharIndex('</CommandLine>',Module.ModuleConfiguration))),LEN(SubString(Module.ModuleConfiguration,0,CharIndex('</CommandLine>',Module.ModuleConfiguration))))) + 1,LEN(SubString(SubString(Module.ModuleConfiguration,0,CharIndex('</CommandLine>',Module.ModuleConfiguration)), CharIndex('<CommandLine>',SubString(Module.ModuleConfiguration,0,CharIndex('</CommandLine>',Module.ModuleConfiguration))),LEN(SubString(Module.ModuleConfiguration,0,CharIndex('</CommandLine>',Module.ModuleConfiguration)))))) As 'Script Name'
      ,Module.ModuleConfiguration As 'Module Configuration'
      ,TaskView.LastModified
  FROM TaskView
  INNER JOIN Module WITH(NOLOCK) ON TaskView.Id = Module.ParentId
  INNER JOIN ManagementPackView WITH(NOLOCK) ON TaskView.ManagementPackId = ManagementPackView.Id
  INNER JOIN ManagedTypeView WITH(NOLOCK) ON TaskView.TargetMonitoringClassId = ManagedTypeView.Id
  WHERE Module.ModuleConfiguration like '%.PS1%'
  ORDER BY ManagementPackView.FriendlyName   



Download the Management Pack that contains the report - PowerShell Scripts included in Management Packs

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