OpsMgr 2007 Current Performance Instances

In the default Operations Manager 2007 reports there doesn’t seem to be a report that will show you the most recent gathered performance instance of something. For example, for a group of servers, I wanted a report that showed the current free disk space on all logical drives of those servers.

I couldn’t find anything in OpsMgr 2007, so to start with I’ve written a SQL query that will provide the information from the Operations Manager Data Warehouse database.

Note that I don’t know much about SQL or Operations Manager, so this may not be the best method.

The following query generates a temporary named result set, partitioning that result set using the row-number() ranking windowing function, over managed entities by time. This provies a method of selecting the most recent performance rule instance for each the specified rule and managed entity.

WITH CurrentDiskFree AS
(SELECT PRI.Instancename, DateAdd(Hour, 10, PPR.DateTime) as DateTime,
ME.Path, ME.ManagedEntityRowID, PPR.SampleValue,
ROW_NUMBER() OVER
(partition by ME.ManagedEntityRowID order by PPR.DateTime DESC)as RowNumber
FROM vPerformanceRuleInstance PRI
inner join vPerformanceRule PR on PRI.RuleRowID = PR.RuleRowID
inner join perf.vPerfRaw PPR on PRI.PerformanceRuleInstanceRowID = PPR.PerformanceRuleInstanceRowID
inner join vManagedEntity ME on ME.ManagedEntityRowID = PPR.ManagedEntityRowID
inner join vRule RU ON RU.RuleRowID = PR.RuleRowID
WHERE RU.RuleDefaultName = 'Logical Disk Free Megabytes'
AND ME.Path like '%server%')

SELECT *
FROM CurrentDiskFree
where RowNumber = 1

Notes:

1. You could also restrict the query based on the members of a group, a more standard method in Operations Manager terms.
2. One managed entity path can and will usually have more than one performance rule instance and ManagedEntityRowID. For example, C: and D: drive in a server would have one row each.
3. The date is recorded in the database is UTC – GMT+0, I’ve calculated GMT+10 for my local timezone in the SQL query.
4. To make this generic in a reporting sense, something with ManagementGroupID should be added to query and use the appropriate management group.

An example resultset:

C:	2008-05-06 22:10:50.000	server1.domain.com	277	25150	1
D:	2008-05-06 22:15:50.000	server1.domain.com	278	36749	1
C:	2008-05-06 22:30:49.000	server2.domain.com	282	12816	1
D:	2008-05-06 22:10:49.000	server3.domain.com	183	36770	1