Useful SCOM 2012 Queries

I’m starting this post to keep track of some useful queries that can be run directly against SCOM 2012 OperationsManager database.

There are pros and cons of doing this directly off your production database; in my experience, as long as you are only querying and not modifying data, and not placing unnecessary locks on the database, there is really no harm in doing this. Queries like the ones below can be really handy in constructing your own lightweight dashboards.

% Free Space on All Monitored Logical Disks

SELECT bme.Path, ps.PerfmonInstanceName, pdav.SampleValue, pdav.TimeSampled 
FROM PerformanceDataAllView (NOLOCK) AS pdav  
INNER JOIN PerformanceSource (NOLOCK) ps ON pdav.PerformanceSourceInternalId = ps.PerformanceSourceInternalId 
INNER JOIN Rules (NOLOCK) r ON ps.RuleId = r.RuleId 
INNER JOIN BaseManagedEntity (NOLOCK) bme ON ps.BaseManagedEntityID = bme.BaseManagedEntityID 
WHERE r.RuleName = 'Microsoft.Windows.Server.2008.LogicalDisk.FreeSpace.Collection' 
AND pdav.TimeSampled = (SELECT MAX(TimeSampled) FROM PerformanceDataAllView WHERE PerformanceSourceInternalId = pdav.PerformanceSourceInternalId )
ORDER BY bme.Path

Note the (nolock) hint on all joined tables.

SELECT bme.Path, ps.PerfmonInstanceName, pdav.SampleValue, pdav.TimeSampled 
FROM PerformanceDataAllView (NOLOCK) AS pdav  
INNER JOIN PerformanceSource (NOLOCK) ps ON pdav.PerformanceSourceInternalId = ps.PerformanceSourceInternalId 
INNER JOIN Rules (NOLOCK) r ON ps.RuleId = r.RuleId 
INNER JOIN BaseManagedEntity (NOLOCK) bme ON ps.BaseManagedEntityID = bme.BaseManagedEntityID 
WHERE r.RuleName = 'Microsoft.Windows.Server.2008.LogicalDisk.FreeSpace.Collection' 
AND pdav.TimeSampled = (SELECT MAX(TimeSampled) FROM PerformanceDataAllView WHERE PerformanceSourceInternalId = pdav.PerformanceSourceInternalId )
AND SampleValue < 20
ORDER BY SampleValue

This query returns a list of all logical disks showing current % free space, with a date and value of the most recent sampling.

Top 10 Disks Approaching Capacity

A minor variation of the query above, but could provide a useful view to a NOC type operation, or shortlist drives for disk space augmentation, etc:

SELECT top 10 bme.Path, ps.PerfmonInstanceName, pdav.SampleValue, pdav.TimeSampled 
FROM PerformanceDataAllView (NOLOCK) AS pdav  
INNER JOIN PerformanceSource (NOLOCK) ps ON pdav.PerformanceSourceInternalId = ps.PerformanceSourceInternalId 
INNER JOIN Rules (NOLOCK) r ON ps.RuleId = r.RuleId 
INNER JOIN BaseManagedEntity (NOLOCK) bme ON ps.BaseManagedEntityID = bme.BaseManagedEntityID 
WHERE r.RuleName = 'Microsoft.Windows.Server.2008.LogicalDisk.FreeSpace.Collection' 
AND pdav.TimeSampled = (SELECT MAX(TimeSampled) FROM PerformanceDataAllView WHERE PerformanceSourceInternalId = pdav.PerformanceSourceInternalId )
ORDER BY SampleValue

All Disks Over 80% Utilization Threshold

Another minor variation of the two queries above, but with a 80% cutoff:

SELECT bme.Path, ps.PerfmonInstanceName, pdav.SampleValue, pdav.TimeSampled 
FROM PerformanceDataAllView (NOLOCK) AS pdav  
INNER JOIN PerformanceSource (NOLOCK) ps ON pdav.PerformanceSourceInternalId = ps.PerformanceSourceInternalId 
INNER JOIN Rules (NOLOCK) r ON ps.RuleId = r.RuleId 
INNER JOIN BaseManagedEntity (NOLOCK) bme ON ps.BaseManagedEntityID = bme.BaseManagedEntityID 
WHERE r.RuleName = 'Microsoft.Windows.Server.2008.LogicalDisk.FreeSpace.Collection' 
AND pdav.TimeSampled = (SELECT MAX(TimeSampled) FROM PerformanceDataAllView WHERE PerformanceSourceInternalId = pdav.PerformanceSourceInternalId )
AND SampleValue < 20
ORDER BY SampleValue

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *