Performance Reports and Groups

Posted: 10th May 2009 by Anders Bengtsson in System Center Operations Manager 2007

When running a performance report against a group you get a average value for all the members of the group. Often you need the report to specify each member of the group in the report. You can of course add each member of the group as object to the report. Another solution is to build a report where you can input a group. That will save you some time if you already have updated groups you want to run reports against. The following query can be run against a group that contains computer objects, it will then find the members of the group and run the report against each of them.


SELECT vManagedEntity.ManagedEntityGuid, vManagedEntityTypeImage.Image, Perf.vPerfHourly.DateTime, Perf.vPerfHourly.SampleCount, Perf.vPerfHourly.AverageValue,
Perf.vPerfHourly.StandardDeviation, Perf.vPerfHourly.MaxValue, vManagedEntity.FullName, vManagedEntity.Path, vManagedEntity.Name,
vManagedEntity.DisplayName, vManagedEntity.ManagedEntityDefaultName, vPerformanceRuleInstance.InstanceName, vPerformanceRule.ObjectName,
vPerformanceRule.CounterName
FROM Perf.vPerfHourly INNER JOIN
vManagedEntity ON Perf.vPerfHourly.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId INNER JOIN
vManagedEntityType ON vManagedEntity.ManagedEntityTypeRowId = vManagedEntityType.ManagedEntityTypeRowId LEFT OUTER JOIN
vManagedEntityTypeImage ON vManagedEntityType.ManagedEntityTypeRowId = vManagedEntityTypeImage.ManagedEntityTypeRowId INNER JOIN
vPerformanceRuleInstance ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = Perf.vPerfHourly.PerformanceRuleInstanceRowId INNER JOIN
vPerformanceRule ON vPerformanceRuleInstance.RuleRowId = vPerformanceRule.RuleRowId
WHERE (vPerformanceRule.CounterName LIKE N'%Available MBytes%') and (Perf.vPerfHourly.DateTime > @ReportParameter2 and Perf.vPerfHourly.DateTime < @ReportParameter3)

and vManagedEntity.ManagedEntityGuid in (

select BMETarget.BaseManagedEntityId from OperationsManager.dbo.BaseManagedEntity BMESource
inner join OperationsManager.dbo.Relationship R
on R.SourceEntityId = BMESource.BaseManagedEntityId
inner join OperationsManager.dbo.BaseManagedEntity BMETarget
on R.TargetEntityId = BMETarget.BaseManagedEntityId
inner join OperationsManager.dbo.ManagedType MT
on BMETarget.BaseManagedTypeId = MT.ManagedTypeId

where MT.TypeName = 'Microsoft.Windows.OperatingSystem'
and BMESource.BaseManagedEntityId in (

select BMETarget.BaseManagedEntityId from OperationsManager.dbo.BaseManagedEntity BMESource
inner join OperationsManager.dbo.Relationship R
on R.SourceEntityId = BMESource.BaseManagedEntityId
inner join OperationsManager.dbo.BaseManagedEntity BMETarget
on R.TargetEntityId = BMETarget.BaseManagedEntityId
Where BMESource.DisplayName = @Group)
)

ORDER BY Perf.vPerfHourly.DateTime

In this example the report will show the “Available MBytes” performance counter for a group that you input as parameter @Group. It will show data between @ReportParameter2 and @ReportParameter3 (dates). I get all groups from the database by this query


Select DISTINCT BMESource.DisplayName as [Group Name]
From OperationsManager.dbo.BaseManagedEntity BMESource
Inner Join OperationsManager.dbo.Relationship R
On R.SourceEntityId = BMESource.BaseManagedEntityId
Inner Join OperationsManager.dbo.BaseManagedEntity BMETarget
On R.TargetEntityId = BMETarget.BaseManagedEntityId

The two date parameters, @ReportParameter2 and @ReportParameter3 I get from two queries that returns current data and current date minus seven days.

SELECT convert(date,getdate(),21)
SELECT convert(date,dateadd(day,-7,getdate()),21)

In my report I also added a matrix to show the values. I added the following line as BackgroundColor on the data value cell. This will give me a red background on every value below 100, in this example each time a machine had less than 100 Mb free memory. =iif(Fields!AverageValue.Value < 50, "Red", "White")
Big thanks to Mike Eisenstein for good ideas and SQL help.

  1. Thanks. I dont have that XML here, sorry. But send me a mail if you run into problems when creating the report.

  2. Julien says:

    Hi,

    Very good idea Anders. Where can i fond the MP or the XML file to test your report ?

    Julien