Granular Reporting in System Center Operations Manager

Granular Reporting in System Center Operations Manager

Please click the +1 button if you find this post helpful.

The default reports in SCOM 2012, generate using either Daily or Hourly aggregate data. If you find yourself in a situation where you need a more granular level of report detail for a short window of time, then you'll need to create custom reports. The good news is that the raw data is kept in the Data Warehouse. Once you define a new dataset using the vPerfRaw view instead of the Hourly/Daily views, then you're in business. Keep in mind that this information is only ever used by SCOM to generate the AverageValue data used in the Hourly/Daily views, so it is only kept for two days by default. This retention period can be changed, but it's not reccomended unless you fully understand the impact it will have on both performance and database size.

Here is a quick snippit of T-SQL used to generate a dataset to query for % Processor Time stored in the vPerfRaw view.

SELECT Perf.vPerfRaw.DateTime, Perf.vPerfRaw.SampleValue, vManagedEntity.Path, vPerformanceRule.ObjectName, vPerformanceRule.CounterName,
vManagedEntity.FullName, vPerformanceRuleInstance.InstanceName
FROM vPerformanceRuleInstance INNER JOIN
Perf.vPerfRaw ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = Perf.vPerfRaw.PerformanceRuleInstanceRowId INNER JOIN
vManagedEntity ON Perf.vPerfRaw.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId INNER JOIN
vPerformanceRule ON vPerformanceRuleInstance.RuleRowId = vPerformanceRule.RuleRowId
WHERE (vPerformanceRule.CounterName = '% Processor Time') AND (vManagedEntity.Path = @ComputerName)
ORDER BY Perf.vPerfRaw.DateTime

Virtual Phone Service for Your Business

Windows 2008 R2 Server,System Center Operations Manager 2012, SCOM, Reporting, T-SQL, Query, Data Warehouse, Perfmon, Performance Monitor, SQL Server, SSRS
Post a Comment