SCOM – SQL – Requête d’inventaire des overrides

par Christophe Jourdan | Mar 29, 2017 | Script, sql, Supervision (SCOM, NAGIOS) | 0 commentaires

La requête ci-dessous permet de lister tout les overrides de manière ‘clarifiés’ afin d’identifier les objets a la source de ces overrides, les paramètres concernés et les management pack sources et de destination.

Les paramètres @Startdate et @EndDate permettent de spécifier une fenêtre de temps concernant les dates de création et de dernière modification des overrides.

AllOverridesDetails.sql

declare @StartDate  datetime
declare @EndDate    datetime
set @EndDate = GETDATE()
set @StartDate = DATEADD(day,-60,@EndDate)
 
SELECT DISTINCT MP.MPFriendlyName AS 'Management Pack'
, aov.ParentType AS 'Type'
,(CASE 
       WHEN AOV.OverrideType = 'RuleProperty' THEN R.RuleName 
       WHEN AOV.OverrideType = 'MonitorProperty' THEN M.MonitorName 
       WHEN AOV.OverrideType = 'RuleConfiguration' THEN R.RuleName 
       WHEN AOV.OverrideType = 'MonitorConfiguration' THEN M.MonitorName 
 END) AS 'NameType',
mt.DisplayName AS ContextDisplayName
, aov.OverrideableParameterName
, aov.Value,
(CASE Enforced 
       WHEN '0' THEN 'False' 
       WHEN '1' THEN 'True' 
  END) AS 'Enforced'
  , aov.LastModified
  , aov.TimeAdded
  ,(CASE 
  WHEN AOV.OverrideType = 'RuleProperty' THEN 
             (CASE R.RuleEnabled 
             WHEN '0' THEN 'False' 
             WHEN '2' THEN 'True' 
             WHEN '3' THEN 'True' 
             WHEN '4' THEN 'True' 
             END)
 
  WHEN AOV.OverrideType = 'MonitorProperty' THEN 
             (CASE M.MonitorEnabled 
             WHEN '0' THEN 'False' 
             WHEN '2' THEN 'True' 
             WHEN '3' THEN 'True' 
             WHEN '4' THEN 'True' 
             END)
 
  WHEN AOV.OverrideType = 'RuleConfiguration' THEN 
             (CASE R.RuleEnabled 
             WHEN '0' THEN 'False' 
             WHEN '2' THEN 'True' 
             WHEN '3' THEN 'True' 
             WHEN '4' THEN 'True' 
             END)
             
  WHEN AOV.OverrideType = 'MonitorConfiguration' THEN 
             (CASE M.MonitorEnabled 
             WHEN '0' THEN 'False' 
             WHEN '2' THEN 'True' 
             WHEN '3' THEN 'True'
             WHEN '4' THEN 'True' 
             END) 
END) AS 'Enable_by_default', MPSTORE.MPFriendlyName AS 'MP Stored'
 
FROM AllOverrideView AS aov 
LEFT OUTER JOIN Rules AS R WITH (nolock) ON aov.TargetId = R.RuleId AND (aov.OverrideType = 'RuleProperty' OR aov.OverrideType = 'RuleConfiguration') 
LEFT OUTER JOIN Monitor AS M WITH (nolock) ON aov.TargetId = M.MonitorId AND (aov.OverrideType = 'MonitorProperty' OR aov.OverrideType = 'MonitorConfiguration') 
LEFT OUTER JOIN  ManagementPack AS MP WITH (nolock) ON (CASE 
                                                                                              WHEN AOV.OverrideType = 'RuleProperty' THEN R.ManagementPackId 
                                                                                              WHEN AOV.OverrideType = 'RuleConfiguration' THEN R.ManagementPackId
                                                                                              WHEN AOV.OverrideType = 'MonitorProperty' THEN M.ManagementPackId 
                                                                                              WHEN AOV.OverrideType = 'MonitorConfiguration' THEN M.ManagementPackId
                                                                                              END) = MP.ManagementPackId 
INNER JOIN ManagementPack AS MPSTORE WITH (nolock) ON MPSTORE.ManagementPackId = aov.ManagementPackId 
LEFT OUTER JOIN ManagedTypeView AS mt WITH (nolock) ON mt.Id = aov.ContextId 
LEFT OUTER JOIN ManagedTypeView AS mtv WITH (nolock) ON mtv.Id = aov.ContextObjectId
 
WHERE (MP.MPFriendlyName IS NOT NULL) 
AND (aov.LastModified BETWEEN @StartDate AND @EndDate) 
OR (MP.MPFriendlyName IS NOT NULL) 
AND (aov.TimeAdded BETWEEN @StartDate AND @EndDate)
 
ORDER BY aov.TimeAdded DESC