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.