[SQL & SCOM] What Unsealed MP is storing overrides - SQL query

Here is a usefull query that will help you to retrieve where are stored your overrides by unsealed MP.

It can help you to retrieve where are stored the overrides on a specific MP or what overrides are stored in the specific MP (for example the default MP ;) ) to be able to clean up this management pack.


Here is the query to execute on the OperationsManager :

SELECT MP.MPName, MP.MPVersion, OVRMP.MPName AS 'Unsealed MP', OVRMP.MPVersion AS 'Unsealed MP Version', COUNT(AOV.Id) AS 'Number of Overrides'
FROM AllOverrideView AS AOV LEFT OUTER JOIN
                     
Rules AS R WITH (nolock) ON AOV.TargetId = R.RuleId LEFT OUTER JOIN
Monitor AS M WITH (nolock) ON AOV.TargetId = M.MonitorId LEFT OUTER JOIN
DManagementPack 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
    WHEN AOV.OverrideType = 'DiscoveryProperty' THEN D .ManagementPackId
    WHEN AOV.OverrideType = 'DiscoveryConfiguration' THEN D .ManagementPackId
    END) = MP.ManagementPackId INNER JOIN
ManagementPack AS OVRMP WITH (nolock) ON OVRMP.ManagementPackId = AOV.ManagementPackId
WHERE     (MP.MPName IS NOT NULL)
GROUP BY MP.MPName, OVRMP.MPName, MP.MPVersion, OVRMP.MPVersion
ORDER BY MP.MPName, 'Unsealed MP'

Result exported to excel should be like :

    Click here to download it !

This posting is provided "AS IS" with no warranties.