System Uptime Report SQL Query

    Some time ago I was asked by the customer how to differentiate the actual downtime of the server from the network connectivity failure with usage of SCOM. Obviously from SCOM's Availability Reports' perspective, which was a main tool used by a customer to assess the state of their assets, there is no differentiation at all. In order to come up with a backup solution providing this information. Unfortunately the built-in reporting mechanism is not very convenient when it comes particular counter, because when you try to run the report for a group of multiple Health Service objects, it will aggregate all of them and try to calculate a mean value for every sample, which makes absolutely no sense in this case and produces a saw-shaped diagram like the one, you can observe below.

System UpTime report for a group of Health Service objects

    Creation of one report subscription per server could be a daunting task for few hundreds of objects, therefore we took an approach of taking the data directly from the database.

Usage:

    The SQL query presented below will provide the samples os System UpTime performance rule for a particular object from the database. It has to be run against SCOM DataWarehouse database. You have to replace the XXXX values below with the SQL regular expression matching the pattern, that will suit your needs. The example of the output produced by the query is shown below.

Exemplary output of System Uptime SQL Query

Code:

SELECT DisplayName,

[DateTime]

FROM Perf.vPerfRaw

JOIN vManagedEntity ON vPerfRaw.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId

WHERE PerformanceRuleInstanceRowId IN

(SELECT PerformanceRuleInstanceRowId FROM vPerformanceRuleInstance

WHERE RuleRowId IN (SELECT RuleRowId FROM vPerformanceRule WHERE CounterName LIKE 'System Up Time'))

AND DisplayName LIKE '%XXXX%'

AND FullName LIKE '%HealthService%'

ORDER BY DisplayName,[DateTime] DESC