SCOM 2012 R2 – SQL Queries to retrieve SCOM performance data

You may be asked by management to retrieve performance data from SCOM, and dealing with the clunky Console can take a very long time to get the data you are after.

This post contains some useful queries to get performance data from both the Operations and Operations Data Warehouse databases.

Retrieve all CPU processor time entries from the hourly performance counter table in the data warehouse database. 

Notes

1. Other useful Performance Counters:
   1. Memory
   2. LogicalDisk
2. in the code, replace “XXXX” with the name of the host

Code description

1. Selects the relevant columns from the various tables that hold the information required
   1. vPerfHourly holds the entry date and value
   2. vManagedEntity holds the name of the host
   3. vPerformanceRule holds the names of the performance objects and and counters
2. Performs joins to marry up IDs to names
3. Filters the display name to a particular host
4. Filters ObjectName to be contain “Process” to only return CPU related values, 2003 servers have “Processor” as their ObjectName and 2008+ servers have “Processor Information”
5. Orders by DateTime in descending order

Code

SELECT
Perf.vPerfHourly.DateTime, Perf.vPerfHourly.AverageValue, vManagedEntity.DisplayName, vPerformanceRule.ObjectName, vPerformanceRule.CounterName
FROM
Perf.vPerfHourly INNER JOIN
vPerformanceRuleInstance ON Perf.vPerfHourly.PerformanceRuleInstanceRowId = vPerformanceRuleInstance.PerformanceRuleInstanceRowId INNER JOIN
vPerformanceRule ON vPerformanceRuleInstance.RuleRowId = vPerformanceRule.RuleRowId INNER JOIN
vRelationship ON Perf.vPerfHourly.ManagedEntityRowId = vRelationship.TargetManagedEntityRowId INNER JOIN
vManagedEntity ON vRelationship.SourceManagedEntityRowId = vManagedEntity.ManagedEntityRowId
WHERE vManagedEntity.DisplayName = 'XXXXX'
AND vPerformanceRule.ObjectName like '%Process%'
AND vPerformanceRule.CounterName = '% Processor Time'
ORDER BY Perf.vPerfHourly.DateTime DESC

Advertisements