[SCOM 2007 R2] Requête/Rapport pour lister les Discoveries, moniteurs, règles, tâches et Recovery Tasks qui incluent des scripts PowerShell (Migration SCOM 2012)

Je travaille avec un client pour la migration de son environnement SCOM 2007 constitué de 4000 servers et un peu moins de 500 Management Packs. Voici une requête SQL qui devrait intéresser plus d’une entreprise, elle liste l’ensemble des découvertes, moniteurs, règles, tâches et tâches de recouverte qui contiennent un script PowerShell sur votre environnement System Center Operations Manager 2007 R2. Ceci peut vous aider à identifier l’effort de migration nécessaire pour migrer vers System Center 2012 Operations Manager (SCOM) en identifiant si les scripts embarquent des cmdlets SCOM 2007. 

Voici la requête à exécuter sur votre base de données opérationnelle :

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   
 

Télécharger le rapport Management Pack - PowerShell Scripts included in Management Packs