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)

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.

You can use a basic service monitor to restart, but even if Ops Mgr restarts the service automatic you need to keep an eye on it. You don’t want Operations Manager to restart the service every two minute, if it does, you need to get notified and investigate the root cause. To keep an eye on this your can use a repeated event detection monitor, that will generate an alert if the service is restarted to often. In this example I will create a monitor to monitor the print spooler (spooler) service and restart it if needed. I will also create a monitor to look at “the print spooler service entered the running state” events. If there are more then four events within one hour Operations Manager will generate an alert.

1. Authoring > Monitors > New Monitor > Windows Services > Basic Service Monitor
2. General: Name, Description and target for example Windows Server 2008 Computer
3. Service Details: spooler
4. Configure Health: Next
5. Configure Alerts: Check generate alerts for this monitor, Create
6. Right-click the new monitor and select properties from the context menu
7. Click the Diagnostic and Recovery tab
8. Add a recovery task that (make sure to get the recovery target correct)
– Recalculate monitor state after recovery finishes
– Run a command
– Full path to file: C:\Windows\System32\cmd.exe
– Parameters: /C net start spooler
9. Create, Ok

… next step is create a monitor that will keep track service restarts

1. Authoring > Monitors > New Monitor > Windows Events > Repeated Event Detection
2. General: Name, Description and target for example Windows Server 2008 Computer
3. Event Log Name: System
-Event Expression:
-Event ID: 7036
-Event Level: Information
– EventDescription contains Spooler
- EventDescription contains running
4. Repeat Settings:
– Counting mode, trigger on count sliding, compare count 4
– based on items occurrence within a time interval, interval 1, hours
5. Configure Health: Next
6. Configure Alerts: Check generate alerts for this monitor, Create

Microsoft has released Visio Add-in for System Center Operations Manager 2007 R2 beta (1.0.0.1054). It is a add-on to Microsoft Office Visio 2007 Professional SP1. With this add-on you can link health state from components in Operations Manager 2007 R2 to objects in Visio. This is a great way to visualize complex systems and business processes. You can use the “view in fullscreen” option in Visio to show the Visio documents on a big screen in your IT office or call center.

The Visio Add-in has the following features:

*Distributed applications exported from System Center Operations Manager 2007 R2 (release candidate) as Visio documents automatically show live health state information on the exported objects when opened in Visio.
*You can easily create new Visio documents and link shapes to any managed object (computer, database, web site, perspective etc.) to show the current health state.
*You can automatically link entire existing Visio documents to the computer and network devices managed by Operations Manager by matching computer names or IP addresses.
*Health states can be automatically refreshed in Visio documents. You can use this option along with Visio’s full screen view to create dashboard views suitable for use as a summary display in a datacenter control room.
*Predefined data graphics enable you to toggle from Operations Manager health icons to shape color for health state.

Download the Visio add-in at Connect. (To get access to this release, apply to participate in the “Operations Manager Public Beta” at this site.)

Source: the Operations Manager team blog

Here are some screenshots of the Visio Add-in for Ops Mgr 2007 R2

Monitoring desktop client hardware, operating system and application faults can be of great value in terms of reducing total cost of ownership (TCO) through identification of widespread faults in the monitored environment. In Operations Manager 2007, there are actually three components for monitoring the client experience:

• Agentless Exception Monitoring (AEM)
• Customer Experience Improvement Program (CEIP)
• Management Packs for Windows-based workstation operating systems and applications

I have received a number of questions both from customers and in the community about custom AEM reports, including more information. In this post I will give you some tips how to write custom AEM reports. I wrote another post about author reports in general for Ops Mgr 2007.

I write my AEM reports in Microsoft Visual Studio 2008. When you start Visual Studio you can choose to start a new Report Server Project. When you blank project has opened you should start with creating a shared data source, specify your Operations Manager data warehouse database. Then add a new item, a report, under the Reports folder in the Solution Explorer window.

The example report below is a “per user” report, which will show you all AEM events for a specified username. I have included some numbers, with total number of users, total number of events, average number of events per user and total number of events for the specified user. I have also included info about the last event for the specified user and a table with all events for the specified user.

In my example report I use four dataset.

1. One to get all Aem users that have a event in the database
2. One to get info for the first table, for example total number of events
3. One to get last crash for the specified user
4. One to get all the events for the specified user

List usernames

SELECT DISTINCT AemUserName FROM [CM].[vCMAemRaw] Rw
INNER JOIN dbo.AemComputer Computer ON Computer.AemComputerRowID = Rw.AemComputerRowID
INNER JOIN dbo.AemUser Usr ON Usr.AemUserRowId = Rw.AemUserRowId
INNER JOIN dbo.AemErrorGroup EGrp ON Egrp.ErrorGroupRowId = Rw.ErrorGroupRowId
INNER JOIN dbo.AemApplication App ON App.ApplicationRowId = Egrp.ApplicationRowId

All info for the first table in the example

SELECT (SELECT COUNT(CrashID) FROM CM.vCMAemRaw) AS TotalEvents, (SELECT COUNT(DISTINCT AemUserRowID) FROM CM.vCMAemRaw) AS TotalUsers, (SELECT (SELECT COUNT(CrashID) FROM CM.vCMAemRaw ) / (SELECT COUNT(DISTINCT AemUserRowID) FROM CM.vCMAemRaw)) AS TotalAverage, (SELECT COUNT(CrashID) FROM [CM].[vCMAemRaw] Rw
INNER JOIN dbo.AemComputer Computer ON Computer.AemComputerRowID = Rw.AemComputerRowID
INNER JOIN dbo.AemUser Usr ON Usr.AemUserRowId = Rw.AemUserRowId
WHERE AemUserName=@UserName) AS UserTotal

Note that I use a parameter, @UserName. Last problem for the specified user

SELECT top 1 CrashTime FROM [CM].[vCMAemRaw] Rw
INNER JOIN dbo.AemComputer Computer ON Computer.AemComputerRowID = Rw.AemComputerRowID
INNER JOIN dbo.AemUser Usr ON Usr.AemUserRowId = Rw.AemUserRowId
INNER JOIN dbo.AemErrorGroup EGrp ON Egrp.ErrorGroupRowId = Rw.ErrorGroupRowId
INNER JOIN dbo.AemApplication App ON App.ApplicationRowId = Egrp.ApplicationRowId
WHERE AemUserName = @UserName ORDER BY CrashTime desc

Info for the “all events for user” table

SELECT CrashTime, AemComputerName,BucketType,Parameter1,Parameter2,CompanyName, AemUserName FROM [CM].[vCMAemRaw] Rw
INNER JOIN dbo.AemComputer Computer ON Computer.AemComputerRowID = Rw.AemComputerRowID
INNER JOIN dbo.AemUser Usr ON Usr.AemUserRowId = Rw.AemUserRowId
INNER JOIN dbo.AemErrorGroup EGrp ON Egrp.ErrorGroupRowId = Rw.ErrorGroupRowId
INNER JOIN dbo.AemApplication App ON App.ApplicationRowId = Egrp.ApplicationRowId
WHERE AemUserName = @UserName

When you have added the fields from all datasets to your report, you can simple deploy it to your reporting server. The base SQL query I used is

select * from [CM].[vCMAemRaw] Rw
inner join dbo.AemComputer Computer on Computer.AemComputerRowID = Rw.AemComputerRowID
inner join dbo.AemUser Usr on Usr.AemUserRowId = Rw.AemUserRowId
inner join dbo.AemErrorGroup EGrp on Egrp.ErrorGroupRowId = Rw.ErrorGroupRowId
Inner join dbo.AemApplication App on App.ApplicationRowId = Egrp.ApplicationRowId

Thanks to Kevin Holman for that one. I think that with the base SQL query for AEM you can build all kind of AEM reports that your organization needs. And when doing that, check out all new cool gauges in Visual Studio 2008.