Jean-Sébastien DUCHENE Blog's

Actualité, Tips, Articles sur l'ensemble des Technologies Microsoft (SCCM/SMS, EMS, Microsoft Intune, Microsoft Azure, Windows 10, SCOM, MDOP...)

[SCOM 2007/2012] Rapport listant tous les overrides appliqués à une machine

Les rapports fournis avec System Center Operations Manager 2007 peuvent paraître parfois pauvre notamment en matière d’administration quotidienne du produit. Aujourd’hui, je vous propose un rapport permettant de lister tous les overrides qui ont été créé et qui sont appliqués à une machine donnée. Ceci inclut les overrides ciblant :
  • les entités (disques, base de données…) rattachées,
  • les groupes dont la machine ou ses entités font parties
  • les classes
Vous pourrez ainsi retrouver des informations comme le Management Pack, le type, le nom de la règle ou du moniteur, le type de contexte, le nom de la cible, le paramètre changé, la valeur, la valeur de l’option Enforced, la dernière modification, la date d’ajout ou encore le management pack stockant le changement.
Il est à noter que :
  • Le rapport peut être long à s’exécuter de par les informations qui doivent être récupérée.
  • Ne reflète pas forcément la valeur effective sur le client puisque vous pouvez avoir plusieurs changements à valeur différentes qui touche une classe, groupe, objet.
N’oubliez pas de vous créer une datasource pointant sur la base de données opérationnelle avec les droits nécessaires pour que le rapport puisse être exécuté.
 
 
The reports provided with System Center Operations Manager 2007 may sometimes seem particularly poor in terms of daily operations on the product. Today, I offer a report which lists all the overrides that have been created and applied to a given machine. This includes targeting overrides:
  • entities (disk, database ...) attached,
  • groups with the machine or its entities are part
  • class
You can then retrieve information such as the Management Pack, the type (Monitor/rule), the name of the rule or the monitor, the type of context, the target name, the setting overrided, the value, the value of Enforced option, the last modified date, the added date, or the management pack used to store the change.
You have to know that :
  • The report can be long to run.
  • Do not necessarily reflect the actual configuration on the client since you can have several different values overrided affecting a class, group, and object.
Do not forget to create a datasource targeting to the operational database with security rights necessary to generate the report.
 
Here is the query :

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',
(CASE WHEN mt.Name LIKE '%Group%' THEN 'Group' WHEN aov.ContextObjectId IS NOT NULL THEN 'Object' WHEN aov.ContextObjectId IS NULL THEN 'Class' END)
AS 'ContextType', (CASE WHEN aov.ContextObjectId IS NULL THEN mt.DisplayName WHEN aov.ContextObjectId IS NOT NULL THEN bmeo.DisplayName END)
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 ON mt.Id = (CASE WHEN aov.ContextObjectId IS NOT NULL THEN aov.ContextObjectId ELSE aov.ContextId END) LEFT OUTER JOIN
BaseManagedEntity AS bmeo WITH (NOLOCK) ON bmeo.BaseManagedEntityId = aov.ContextObjectId
WHERE (aov.ParentType <> 'Discovery') AND (MP.MPFriendlyName IS NOT NULL) AND (aov.ContextId IN
(SELECT DISTINCT SourceMonitoringObjectId AS 'Group'
FROM RelationshipGenericView
WHERE (TargetMonitoringObjectDisplayName LIKE '%' + @ComputerName + '%') 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)))) OR (aov.ParentType <> 'Discovery') AND
(MP.MPFriendlyName IS NOT NULL) AND (aov.ContextObjectId IN
(SELECT DISTINCT SourceMonitoringObjectId AS 'Group'
FROM RelationshipGenericView AS RelationshipGenericView_1
WHERE (TargetMonitoringObjectDisplayName LIKE '%' + @ComputerName + '%') AND (SourceMonitoringObjectDisplayName IN
(SELECT ManagedEntityGenericView_1.DisplayName
FROM ManagedEntityGenericView AS ManagedEntityGenericView_1 INNER JOIN
(SELECT BaseManagedEntityId
FROM BaseManagedEntity AS BaseManagedEntity_1 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_1 ON
GetTopLevelEntities_1.BaseManagedEntityId = ManagedEntityGenericView_1.Id INNER JOIN
(SELECT DISTINCT BaseManagedEntityId
FROM TypedManagedEntity AS TypedManagedEntity_1 WITH (NOLOCK)
WHERE (ManagedTypeId IN
(SELECT DerivedManagedTypeId
FROM dbo.fn_DerivedManagedTypes(dbo.fn_ManagedTypeId_Group()) AS fn_DerivedManagedTypes_1)))
AS GetOnlyGroups_1 ON GetOnlyGroups_1.BaseManagedEntityId = ManagedEntityGenericView_1.Id)))) OR (aov.ParentType <> 'Discovery')
AND (MP.MPFriendlyName IS NOT NULL) AND (aov.ContextId IN
(SELECT DISTINCT Id
FROM ManagedEntityGenericView AS ManagedEntityGenericView_2
WHERE (Path LIKE '%' + @ComputerName + '%') OR
(DisplayName LIKE '%' + @ComputerName + '%'))) OR
(aov.ParentType <> 'Discovery') AND (MP.MPFriendlyName IS NOT NULL) AND (aov.ContextId IN
(SELECT DISTINCT ManagedType.ManagedTypeId
FROM ManagedType INNER JOIN
BaseManagedEntity AS BaseManagedEntity_2 WITH (NOLOCK) ON ManagedType.ManagedTypeId = BaseManagedEntity_2.BaseManagedTypeId
WHERE (BaseManagedEntity_2.Path LIKE '%' + @ComputerName + '%') OR
(BaseManagedEntity_2.DisplayName LIKE '%' + @ComputerName + '%')))
ORDER BY 'Management Pack', 'NameType', 'ContextType', 'ContextDisplayName'

 
 
 
This report is provided "AS IS" without express or implied warranty of any kind.

 

Facebook Like
Anonymous