Le blog technique

/* All Overrides for Rules of language code EUN and FRA, with Original MP and Rule, Parameter Name, Override Value, Scope and Containing MP */

Use OperationsManager
SELECT
mpv2.DisplayName as Rule_MP_Name
,rv.DisplayName as Rule_Name
,IsEnabledByDefault = CASE
    WHEN rv.Enabled = '0' THEN 'NO'
    WHEN rv.Enabled <> '0' THEN 'YES'
    END
,op.OverrideableParameterName as Overrideable_Parameter
,mo.Value as Override_Value 
,IsEnforced = CASE 
    WHEN mo.Enforced = '0' THEN 'NO'
    WHEN mo.Enforced = '1' THEN 'YES'
    END
,mt.TypeName as Override_Scope
,bme.DisplayName as Override_InstanceName
,bme.Path as Override_InstancePath 
,mpv.DisplayName as Override_MP_Name
FROM ModuleOverride mo 
INNER JOIN managementpackview mpv on mpv.Id = mo.ManagementPackId 
INNER JOIN ruleview rv on rv.Id = mo.ParentId 
INNER JOIN ManagedType mt on mt.managedtypeid = mo.TypeContext
INNER JOIN [dbo].[OverrideableParameter] op on op.OverrideableParameterId = mo.OverrideableParameterId
INNER JOIN managementpackview mpv2 on mpv2.Id = rv.ManagementPackId
LEFT JOIN BaseManagedEntity bme on bme.BaseManagedEntityId = mo.InstanceContext 
WHERE mpv.Sealed = 0
AND rv.LanguageCode in ('ENU','FRA')
AND mpv.LanguageCode in ('ENU','FRA')
AND mpv2.LanguageCode in ('ENU','FRA')

--ORDER BY mpv2.DisplayName

/*All Overrides for Monitor of language code EUN and FRA, with Original MP and Monitor, Parameter Name, Override Value, Scope and Containing MP */

Use OperationsManager
SELECT
mpv2.DisplayName as Monitor_MP_Name
,mv.DisplayName as Monitor_Name
,IsEnabledByDefault = CASE
    WHEN mv.Enabled = '0' THEN 'NO'
    WHEN mv.Enabled <> '0' THEN 'YES'
    END
,op.OverrideableParameterName as Overrideable_Parameter
,mo.Value as Override_Value 
,IsEnforced = CASE 
    WHEN mo.Enforced = '0' THEN 'NO'
    WHEN mo.Enforced = '1' THEN 'YES'
    END
,mt.TypeName as Override_Scope
,bme.DisplayName as Override_InstanceName
,bme.Path as Override_InstancePath 
,mpv.DisplayName as Override_MP_Name
FROM MonitorOverride  mo 
INNER JOIN managementpackview mpv on mpv.Id = mo.ManagementPackId 
INNER JOIN monitorview mv on mv.Id = mo.MonitorId
INNER JOIN ManagedType mt on mt.managedtypeid = mo.TypeContext
INNER JOIN [dbo].[OverrideableParameter] op on op.OverrideableParameterId = mo.OverrideableParameterId
INNER JOIN managementpackview mpv2 on mpv2.Id = mv.ManagementPackId
LEFT JOIN BaseManagedEntity bme on bme.BaseManagedEntityId = mo.InstanceContext 
WHERE mpv.Sealed = 0
AND mv.LanguageCode in ('ENU','FRA')
AND mpv.LanguageCode in ('ENU','FRA')
AND mpv2.LanguageCode in ('ENU','FRA')
ORDER BY mpv2.DisplayName

-- All Overrides for Discoveries of language code EUN and FRA, with Original MP and Discovery, Parameter Name, Override Value, Scope and Containing MP */

Use OperationsManager
SELECT
mpv2.DisplayName as Discovery_MP_Name
,dv.DisplayName as Discovery_Name
,mo.ParentType
,IsEnabledByDefault = CASE
    WHEN dv.Enabled = '0' THEN 'NO'
    WHEN dv.Enabled <> '0' THEN 'YES'
    END
,op.OverrideableParameterName as Overrideable_Parameter
,mo.Value as Override_Value 
,IsEnforced = CASE 
    WHEN mo.Enforced = '0' THEN 'NO'
    WHEN mo.Enforced = '1' THEN 'YES'
    END
,mt.TypeName as Override_Scope
,bme.DisplayName as Override_InstanceName
,bme.Path as Override_InstancePath 
,mpv.DisplayName as Override_MP_Name
FROM ModuleOverride mo 
INNER JOIN managementpackview mpv on mpv.Id = mo.ManagementPackId 
INNER JOIN DiscoveryView dv on dv.Id = mo.ParentId 
INNER JOIN ManagedType mt on mt.managedtypeid = mo.TypeContext
INNER JOIN [dbo].[OverrideableParameter] op on op.OverrideableParameterId = mo.OverrideableParameterId
INNER JOIN managementpackview mpv2 on mpv2.Id = dv.ManagementPackId
LEFT JOIN BaseManagedEntity bme on bme.BaseManagedEntityId = mo.InstanceContext 
WHERE mpv.Sealed = 0
AND dv.LanguageCode in ('ENU','FRA')
AND mpv.LanguageCode in ('ENU','FRA')
AND mpv2.LanguageCode in ('ENU','FRA')
AND mo.ParentType = 'Discovery'
ORDER BY mpv2.DisplayName

-- QUERY TO DISPLAY ALL RULES WITH TARGET CLASSES AND THEIR OVERRIDE STATE (ANYWAY MORE THAN 1 OVERRIDE PER RULE) - (FILTERED ON 'ENU' AND 'FRA' LANGUAGES)
-- THE FINAL 'GROUP BY' CLAUSE IS TO DISPLAY ONLY ONE RULE OCCURENCE (WE ONLY WANT TO KNOW THAT RULE IS OVERRIDEN AT LEAST ONE TIME.
-- THAT IS EQUIVALENT TO DELETE THE 'ALL' STATEMENT IN UNION CLAUSE.

PRINT 'ALL RULES THAT IS OVERRIDEN AT LEAST 1 TIME OR NOT';

WITH TEMP1 (Rule_Name,Target_Class,Override_State)
AS
(
SELECT rv.DisplayName as Rule_Name
,MTV.DisplayName as Target_Class
,Override_State =  CASE
    WHEN mo.ParentId is null THEN 'NOT OVERRIDEN'
    WHEN mo.ParentId is not null THEN 'OVERRIDEN'
    END
FROM RuleView rv
FULL JOIN ModuleOverride mo on rv.Id = mo.ParentId
INNER JOIN [dbo].[ManagedTypeView] MTV on rv.TargetMonitoringClassId = MTV.Id
WHERE rv.Id  not in (select mo.ParentId from ModuleOverride mo)
AND rv.LanguageCode in ('ENU','FRA')
AND MTV.LanguageCode in ('ENU','FRA')

UNION ALL

SELECT rv.DisplayName as Rule_name
,MTV.DisplayName as Target_Class
,Override_State =  CASE
    WHEN mo.ParentId is null THEN 'NOT OVERRIDEN'
    WHEN mo.ParentId is not null THEN 'OVERRIDEN'
    END
FROM RuleView rv
FULL JOIN ModuleOverride mo on rv.Id = mo.ParentId
JOIN [dbo].[ManagedTypeView] MTV on rv.TargetMonitoringClassId = MTV.Id
WHERE rv.Id  in (select mo.ParentId from ModuleOverride mo)
AND rv.LanguageCode in ('ENU','FRA')
AND MTV.LanguageCode in ('ENU','FRA')
)

SELECT Rule_Name
,Target_Class
,Override_State
 FROM TEMP1
 GROUP BY Rule_Name,Target_Class,Override_State
 ORDER BY Rule_Name