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
- Other useful Performance Counters:
- Memory
- LogicalDisk
- in the code, replace “XXXX” with the name of the host
Code description
- Selects the relevant columns from the various tables that hold the information required
- vPerfHourly holds the entry date and value
- vManagedEntity holds the name of the host
- vPerformanceRule holds the names of the performance objects and and counters
- Performs joins to marry up IDs to names
- Filters the display name to a particular host
- 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”
- 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