System Center Operations Manager 2012

Like all SCOM Admins have to do at some time, I have to make reports as to what overrides are running in SCOM. This is to make sure there is not some override to a rule that is making data collection of a performance rule occur ever second instead of every 15 minutes, yes that has happened at several places I’ve worked at. Kevin Holman has the best SQL Queries on the planet for SCOM. The query I modified is under the Misc OpsDB Query listing whose description is 

–To dump out all the rules and monitors that have overrides, and display the context and instance of the override:

I needed to get some additional clarity on what some overrides were referencing, as you know the Free Disk Space has many overrides that are possible, like MB Free Space and % Free Space, System and non-system disk. There are other monitors for sure that could have more than one override to it. To clear up the possible confusion this could cause I added one additional column to the query output called ParamName. This column comes from the table dbo.OverrideableParameter and the field named OverrideableParameterName. Naturally I had to add an inner join to both sub queries so the data would be available to both monitors and rules. This query does take a few seconds longer to run but the output is much more usable when you really want to know what that override is being applied to. This does work for SCOM 2012 R2 and SCOM 2016.

The full query is listed below ENJOY:

--To dump out all the rules and monitors that have overrides, and display the context and instance of the override:

select rv.DisplayName as WorkFlowName, OverrideName, mo.Value as OverrideValue, OP.OverrideableParameterName as ParamName,

mt.TypeName as OverrideScope, bme.DisplayName as InstanceName, bme.Path as InstancePath, 

mpv.DisplayName as ORMPName, mo.LastModified as LastModified 

from ModuleOverride mo 

inner join OverrideableParameter OP on OP.OverrideableParameterId = mo.OverrideableParameterId

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 

left join BaseManagedEntity bme on bme.BaseManagedEntityId = mo.InstanceContext 

Where mpv.Sealed = 0 

UNION ALL 

select mv.DisplayName as WorkFlowName, OverrideName, mto.Value as OverrideValue, OP.OverrideableParameterName as ParamName,

mt.TypeName as OverrideScope, bme.DisplayName as InstanceName, bme.Path as InstancePath, 

mpv.DisplayName as ORMPName, mto.LastModified as LastModified 

from MonitorOverride mto

inner join OverrideableParameter OP on OP.OverrideableParameterId = mto.OverrideableParameterId

inner join managementpackview mpv on mpv.Id = mto.ManagementPackId 

inner join monitorview mv on mv.Id = mto.MonitorId 

inner join ManagedType mt on mt.managedtypeid = mto.TypeContext 

left join BaseManagedEntity bme on bme.BaseManagedEntityId = mto.InstanceContext 

Where mpv.Sealed = 0 

Order By mpv.DisplayName