I recently worked on a customer request for a daily SCOM System Uptime report for all servers in their environment.  The customer had been using a 3rd party MP, but although the functionality worked they were unable to get accurate data and the System Uptime performance counter did not provide them with data in the format they were looking for.

SO…off to SQL Management Studio!

After playing around with some queries, I came up with a pretty cool query and created a report that met the customers requirement successfully.  The customer currently has the report scheduled daily as a CSV file, which works great for their needs.

For the purpose of this post, let’s take a look at the SQL query…

UPDATE:  The SQL query has been updated to add flexibility for all time zones and reflect the correct rule and collection interval from the latest MP.  The default collection interval is now 6 hours.  Updated sections are in bold.  I will be adding the MP code in the next update for viewing and scheduling in the SCOM console. 

The first part of the query is simply defining the columns that will be output in the report.  I am converting the “SampleValue” output from the System Uptime counter from the default UTC time to the current time zone.  This logic should work in any time zone.  The LastRebootDate logic is simply subtracting the sample value from the current date.

SELECT distinct vme2.DisplayName, RuleDefaultName,
DATEADD(second, -SampleValue, DATEADD(HOUR,-DATEDIFF(HOUR,GETDATE(),GetUTCDate()),DateTime)) as LastRebootDate,
(FLOOR( SampleValue/ 86400 )) AS Days,
(FLOOR( ( SampleValue / 3600 ) – FLOOR( SampleValue / 86400 ) * 24 )) AS Hours,
FLOOR( ( SampleValue / 60 ) – FLOOR( SampleValue/ 3600 ) * 60 ) AS Minutes,
Round(SampleValue,0) as SampleValue,
DATEADD(HOUR,-DATEDIFF(HOUR,GETDATE(),GetUTCDate()),DateTime) as LastSampleTime 

The second part of the query is joining several tables so that we can grab all of the requested columns.  I won’t dig too dep into this, but feel free to query the different tables to get an idea of the data in each table.  Also, notice that I am querying the PerfRaw table.  This is due to inaccurate data output while querying the PerfHourly table.

from
vPerformanceRule pr
join vPerformanceRuleInstance pri on pri.rulerowId=pr.RuleRowId
join vRule vr on vr.RuleRowId=pr.RuleRowId
join Perf.vPerfRaw vpd on vpd.PerformanceRuleInstanceRowId=pri.PerformanceRuleInstanceRowId
join vManagedEntity vme on vme.ManagedEntityRowId=vpd.ManagedEntityRowId
join vManagedEntity vme2 on vme2.ManagedEntityRowId=vme.TopLevelHostManagedEntityRowId

In the third last part of the query I am defining my filters.  In my case, I am filtering for samples within the last 1.5 hours to avoid duplicate entries.  The sample interval for the System Uptime collection rule is 1 hour, so I found this time period to be very effective. Additionally, I am filtering on the “RuleDefaultName” as there are a few different collection rules that will return data without this filter.  Lastly, I am filtering out any object with “CLS” in the “DisplayName” to avoid returning clusters.

where pr.ObjectName=’System’
and pr.CounterName like’%Up Time%’
and DateTime > DATEADD(HOUR,-6,GETUTCDATE())
and vme2.DisplayName not like ‘%CLS%’
order by LastRebootDate desc, LastSampleTime

After plugging the query into a data source in BIDs and doing a little configuration, here is what you get!

NOTE: This management pack is for sample use only and should be tested extensively before using in production.

To access the System Uptime Report management pack and report files click here.

This posting is provided “AS IS” with no warranties.