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