Hello Friends,
I need to have a dashboard report that shows nodes that are currently down and the duration of downtime. Searching through Thwack I found some resources and ended up with the SQL statement below but it only works partially for some reason. I have a couple dozen "down" nodes right now but this report only comes back with 8 nodes. Any idea what I've got wrong in this query?
SELECT
N.StatusLED as Status,N.Caption AS Device,MAX(E.EventTime) AS DownTime, Cast(DateDiff(day,MAX(E.EventTime),getdate()) as varchar) + ' Day(s) ' + convert(char(8),dateadd(second,DateDiff(second,MAX(E.EventTime),getdate()),0),14) as Duration
FROM
Nodes N
INNER JOIN Events E ON E.NetworkNode = N.NodeID
where N.status = 2
GROUP BY
N.StatusLED,
N.Caption
ORDER BY DownTime
Thanks,
Eric
