Report listing computers that crashed in a certian time period.

[Technut27]

Hi community,

We had an issue come up today that caused a number of machines to crash and reboot over a period of a few hours. Some users reported it but I'm sure many did not. I've noticed that the up-time calendar tracks unexpected reboot so is it possible to create a report that would list the computers that crashed between a period of time? Or is there an existing report that could be tweaked to do this?

Thanks!

[Technut27 · Accepted solution]

That is better, the rest of the duplicates are gone now and I like the Crash Count total. I just added tsysOS.Image As icon so I can see what OS they are running at a glance.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.IPAddress,
subquery.CrashCount,
subquery.LastCrash,
tblAssets.Lastseen,
tblAssets.Lasttried,
tsysOS.Image As icon
From tblAssets
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join (Select Top 1000000 tblUptime.AssetId,
Count(tblUptime.UptimeID) As CrashCount,
Max(tblUptime.EventTime) As LastCrash
From tblUptime
Where tblUptime.EventTime > GetDate() - 1 And tblUptime.EventType = 5
Group By tblUptime.AssetId) subquery On subquery.AssetId = tblAssets.AssetID
Order By subquery.CrashCount Desc

[Hemoco]

The DISTINCT won't help much without removing the eventtime as this is probably different for each event. Using a subquery might be better. If you used a left join you could even use the subquery for any report to add this information.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.IPAddress,
subquery.CrashCount,
subquery.LastCrash,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join (Select Top 1000000 tblUptime.AssetId,
Count(tblUptime.UptimeID) As CrashCount,
Max(tblUptime.EventTime) As LastCrash
From tblUptime
Where tblUptime.EventTime > GetDate() - 1 And tblUptime.EventType = 5
Group By tblUptime.AssetId) subquery On subquery.AssetId = tblAssets.AssetID
Order By subquery.CrashCount Desc

[Technut27]

That is pretty good. The only think I notice is I'm getting a lot of duplicates in the list. If I look at the event log locally on a machine there is one Event 6008 on 2/20/2014 in the time frame I'm looking for but it is listed 4 times in the report. Did I use distinct properly here to eliminate those? The report is much shorter now. I also narrowed it to the last 24 hours.

Select Distinct Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblUptime.EventTime,
tblUptime.EventType,
Case tblUptime.EventType When 1 Then 'power on' When 2 Then 'power off'
When 3 Then 'Start sleep' When 4 Then 'End sleep'
When 5 Then 'Unexpected shutdown' End As Event
From tblAssets
Inner Join tblUptime On tblAssets.AssetID = tblUptime.AssetId
Where tblUptime.EventTime > GetDate() - 1 And tblUptime.EventType = 5
Order By tblUptime.EventTime Desc

[Hemoco]

The uptime calendar is based on the table tblUptime that stores the events.

Only 5 events are stored:
Case tblUptime.EventType
When 1 Then 'power on'
When 2 Then 'power off'
When 3 Then 'Start sleep'
When 4 Then 'End sleep'
When 5 Then 'Unexpected shutdown'
End As Event,


Using this select case and filter on event type 5 this could be an example report showing the unexpected shutdowns of the last 2 days (assuming the have been rescanned after the event):

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblUptime.EventTime,
tblUptime.EventType,
Case tblUptime.EventType When 1 Then 'power on' When 2 Then 'power off'
When 3 Then 'Start sleep' When 4 Then 'End sleep'
When 5 Then 'Unexpected shutdown' End As Event
From tblAssets
Inner Join tblUptime On tblAssets.AssetID = tblUptime.AssetId
Where tblUptime.EventTime > GetDate() - 2 And tblUptime.EventType = 5
Order By tblUptime.EventTime Desc