OM 2007 Availability Report

For the last few weeks I was pondering the idea to create a custom availability report based on servers and also the AD domains of these servers.
The first idea I had was to use the events 6005,6006 and 6008 to calculate the time-lapsed between these events and use that time period to work out the downtime and subsequest uptime of these servers.
The second idea was to use the System/System Up Time counter to work out the total uptime in seconds of the servers.

Basically, I setup scom to gather the System Up Time counter for me every hour and store it.

I then use the following query to populate a database table

select distinct Path, FullName, ObjectName, CounterName, InstanceName, SampleValue, DateTime ,id = identity(int,1,1) into #TempUpTime
from Perf.vPerfRaw pvpr
inner join vManagedEntity vme on pvpr.ManagedEntityRowId = vme.ManagedEntityRowId
inner join vPerformanceRuleInstance vpri on pvpr.PerformanceRuleInstanceRowId = vpri.PerformanceRuleInstanceRowId
inner join vPerformanceRule vpr on vpr.RuleRowId = vpri.RuleRowId
WHERE objectname = ‘System’
and CounterName = ‘System Up Time’
and fullname like ‘Microsoft.Windows.OperatingSystem%’
and datetime>=getdate() – ’04:28:00′
Order By path, DateTime asc

insert into nworkslookup.dbo.uptimeavail
SELECT f1.path as servername,
case when (f2.samplevalue – f1.samplevalue)>0 then round(f2.SampleValue – f1.SampleValue,0) else f2.samplevalue end as ‘Difference’,
dateadd(“hh”, 2, f2.DateTime) as ‘DateTime’,’0′
FROM #TempUpTime f1, #TempUpTime f2
where = and f1.fullname=f2.fullname

Basically, the first query populate a temp SQL table called “TempUpTime”. The second table query process this temp table and calculates the difference between the previous sample and the current sample, the information is then stored in a table called uptimeavail.

The structure of the table “uptimeavail”
[ServerName] [nchar](200) NULL,
[Difference] [float] NULL,
[DateTime] [datetime] NULL,
[Reboots] [numeric](18, 0) NULL

The reboots column is for later use not right now.
Example of the contents of the “uptimeavail” table

Servername1 3600 2011/04/01 07:00:00 0
Servername2 3201 2011/04/01 07:00:00 0

This data in other words mean or can be read like this “Servername 1 was available for 3600 seconds from 06:00:00 to 07:00:00 on the 1st of April 2011.

Screenshot of the report