I'm trying to list the NODE DOWN and NODE UP by event timestamps in a report (Report Writer) for the "LAST 7 DAYS".
Can anyone offer a solution to finish this report where I would list the corresponding Node Up Timestamps? I'm struggling to figure this out. I can get the timestamp for NODE DOWN just fine. My problem is getting the corresponding timestamp for when that node came back up.
(Ideally I'd also want to show number of minutes/hours the node was actually down but I'm not there yet...)
Here's the present report SQL (DEPARTMENT is a Custom Property):
SELECT Nodes.IP_Address AS IP_Address,
Nodes.Department AS Department,
Nodes.Caption AS NodeName,
Events.EventTime AS Event_Time
Nodes INNER JOIN (Events INNER JOIN EventTypes Events_EventTypes ON (Events.EventType = Events_EventTypes.EventType)) ON (Nodes.NodeID = Events.NetworkNode)
( EventTime BETWEEN 40063 AND 40070 )
(Events.EventType = 1) AND
(Nodes.Caption LIKE 'RemoteOffice%')