[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.

[SQL & SCOM] List SCOM groups for a list of servers

SELECT TargetMonitoringObjectDisplayName, SourceMonitoringObjectDisplayName AS 'Group'
FROM RelationshipGenericView
WHERE TargetMonitoringObjectDisplayName in ('MyServerFullName',
'MyServerFullName1')
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'

Result will be like :

TargetMonitoringObjectDisplayName	Group
MyServerFullName1	agent group
MyServerFullName	agent group
MyServerFullName1	Agent Managed Computer Group
MyServerFullName	Agent Managed Computer Group
MyServerFullName1	All Windows Computers
MyServerFullName	All Windows Computers
MyServerFullName1	IIS 2003 Computer Group
MyServerFullName	IIS 2003 Computer Group
MyServerFullName1	IIS Computer Group
MyServerFullName	IIS Computer Group
MyServerFullName1	Windows Server 2003 Computer Group
MyServerFullName	Windows Server 2003 Computer Group
MyServerFullName1	Windows Server Computer Group
MyServerFullName	Windows Server Computer Group
MyServerFullName1	Windows Server Instances Group
MyServerFullName	Windows Server Instances Group

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

System Center Operations Manager 2007 usefull SQL Queries

I will try to often update this post with the SQLqueries I've found or developped for specific report needs.

Last update : 2012/03/07

• Retrieve discovered objects for a specific class
• List SCOM groups for a list of servers
• Get SCOM Groups members using a SQL Query on OperationsManager DB
• List all Manual Reset Monitors

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