![\"\"](\"http:\/\/contoso.se\/files\/perfReport01.JPG\")
<\/p>\nWhen running a performance report against a group you get a average value for all the members of the group. Often you need\u00c2\u00a0the 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.<\/p>\n
<\/p>\n
and vManagedEntity.ManagedEntityGuid in (<\/p>\n select BMETarget.BaseManagedEntityId from OperationsManager.dbo.BaseManagedEntity BMESource where MT.TypeName = 'Microsoft.Windows.OperatingSystem' select BMETarget.BaseManagedEntityId from OperationsManager.dbo.BaseManagedEntity BMESource 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<\/p>\n The two date parameters, @ReportParameter2 and @ReportParameter3 I get from two queries that returns current data and current date minus seven days.<\/p>\n 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. When running a performance report against a group you get a average value for all the members of the group. Often you need\u00c2\u00a0the 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 … Continue reading
\nSELECT vManagedEntity.ManagedEntityGuid, vManagedEntityTypeImage.Image, Perf.vPerfHourly.DateTime, Perf.vPerfHourly.SampleCount, Perf.vPerfHourly.AverageValue,
\nPerf.vPerfHourly.StandardDeviation, Perf.vPerfHourly.MaxValue, vManagedEntity.FullName, vManagedEntity.Path, vManagedEntity.Name,
\nvManagedEntity.DisplayName, vManagedEntity.ManagedEntityDefaultName, vPerformanceRuleInstance.InstanceName, vPerformanceRule.ObjectName,
\nvPerformanceRule.CounterName
\nFROM Perf.vPerfHourly INNER JOIN
\nvManagedEntity ON Perf.vPerfHourly.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId INNER JOIN
\nvManagedEntityType ON vManagedEntity.ManagedEntityTypeRowId = vManagedEntityType.ManagedEntityTypeRowId LEFT OUTER JOIN
\nvManagedEntityTypeImage ON vManagedEntityType.ManagedEntityTypeRowId = vManagedEntityTypeImage.ManagedEntityTypeRowId INNER JOIN
\nvPerformanceRuleInstance ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = Perf.vPerfHourly.PerformanceRuleInstanceRowId INNER JOIN
\nvPerformanceRule ON vPerformanceRuleInstance.RuleRowId = vPerformanceRule.RuleRowId
\nWHERE (vPerformanceRule.CounterName LIKE N'%Available MBytes%') and (Perf.vPerfHourly.DateTime > @ReportParameter2 and Perf.vPerfHourly.DateTime < @ReportParameter3)<\/p>\n
\ninner join OperationsManager.dbo.Relationship R
\non R.SourceEntityId = BMESource.BaseManagedEntityId
\ninner join OperationsManager.dbo.BaseManagedEntity BMETarget
\non R.TargetEntityId = BMETarget.BaseManagedEntityId
\ninner join OperationsManager.dbo.ManagedType MT
\non BMETarget.BaseManagedTypeId = MT.ManagedTypeId<\/p>\n
\nand BMESource.BaseManagedEntityId in (<\/p>\n
\ninner join OperationsManager.dbo.Relationship R
\non R.SourceEntityId = BMESource.BaseManagedEntityId
\ninner join OperationsManager.dbo.BaseManagedEntity BMETarget
\non R.TargetEntityId = BMETarget.BaseManagedEntityId
\nWhere BMESource.DisplayName = @Group)
\n)<\/p>\n
\n<\/code><\/p>\n
\nSelect DISTINCT BMESource.DisplayName as [Group Name]
\nFrom OperationsManager.dbo.BaseManagedEntity BMESource
\nInner Join OperationsManager.dbo.Relationship R
\nOn R.SourceEntityId = BMESource.BaseManagedEntityId
\nInner Join OperationsManager.dbo.BaseManagedEntity BMETarget
\nOn R.TargetEntityId = BMETarget.BaseManagedEntityId
\n<\/code><\/p>\nSELECT convert(date,getdate(),21)<\/code>
\nSELECT convert(date,dateadd(day,-7,getdate()),21)<\/code><\/p>\n![\"\"](\"http:\/\/contoso.se\/files\/perfReport02.JPG\")
<\/p>\n=iif(Fields!AverageValue.Value < 50, \"Red\", \"White\")<\/code>
\nBig thanks to Mike Eisenstein <\/a>for good ideas and SQL help.<\/p>\n","protected":false},"excerpt":{"rendered":"