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. An example resultset:
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,
(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%')
where RowNumber = 1
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
An example resultset: