[OpsMgr 2007 R2][OpsMgr 2012] Hourly aggregated report on Performance Data for an agent

Source : Jean-Sébastien DUCHÊNE - in this article >>> HERE <<<

The reports provided with System Center Operations Manager (SCOM) may sometimes seem particularly poor in terms of daily operations on the product. Today, I offer a report to select a performance rule of an agent to display a chart on a given interval. It will use hourly aggregated data.
Beware: the proposed data depend on the retention time of the Data WareHouse.
Do not forget to create a datasource targeting to the data warehouse with security rights necessary to generate the report. This reports works with System Center Operations Manager 2007 R2, System Center 2012 Operations Manager  and SQL Server 2008 RTM/R2.

Here is the query:

SELECT Distinct vPerformanceRuleInstance.InstanceName, vPerformanceRuleInstance.PerformanceRuleInstanceRowId FROM vRule
INNER JOIN vPerformanceRule WITH(NOLOCK) ON vRule.RuleRowId = vPerformanceRule.RuleRowId
INNER JOIN vPerformanceRuleInstance WITH(NOLOCK) ON vPerformanceRule.RuleRowId = vPerformanceRuleInstance.RuleRowId
INNER JOIN Perf.vPerfDaily WITH(NOLOCK) ON  vPerformanceRuleInstance.PerformanceRuleInstanceRowId = Perf.vPerfDaily.PerformanceRuleInstanceRowId
INNER JOIN vManagedEntity WITH(NOLOCK) ON Perf.vPerfDaily.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId
WHERE  Path like '%' + @ManagedEntityId + '%' And vRule.RuleDefaultName = @RuleDefaultName
ORDER BY vPerformanceRuleInstance.InstanceName ASC 

 

Download Agent - Performance Hourly Aggregation Report

This posting is provided "AS IS" with no warranties.