Create a Nodes Down Report

iso1103

Hello,

I want to make a report including all nodes of a network which are down, their down time and the duration.

I managed to get only the nodes with a duration which is less than a month (31 days), though there are some with a duration of hundreds of days.

Here is the SWQL code I currently have (the Orion Platform version is from 03.2017):

SELECT
     NodeName AS [Node Name],
     StatusIcon AS [_IconFor_Node Name],
     DetailsUrl AS [_LinkFor_Node Name],
     machinetype AS [Machine Type],
     IP_Address AS [IP Address],
     CONCAT(SUBSTRING(tostring(MIN(e.EVENTTIME)), 1, 4), SUBSTRING(tostring(MIN(e.EVENTTIME)), 5, 2),
          SUBSTRING(tostring(tolocal(MIN(e.EVENTTIME))), 12, 8)) AS DownTime,
     CONCAT(HOURDIFF(tolocal(MIN(e.EVENTTIME)),  getdate()) / 24, 'Day(s)',
          HOURDIFF(tolocal(MIN(e.EVENTTIME)), getdate()) - (HOURDIFF(tolocal(MIN(e.EVENTTIME)), getdate())/24) * 24, 'h',
          MINUTEDIFF(tolocal(MIN(e.EVENTTIME)), getdate()) - MINUTEDIFF(tolocal(MIN(e.EVENTTIME)), getdate())/60) * 60, 'm')
          AS Duration
FROM ORION.NODES NODES
INNER JOIN ORION.EVENTS e ON NODES.NODEID = e.NETWORKNODE
WHERE STATUS = 2 AND e.eventtype = 1

And here is the result (the "oldest" node is from a month ago):

I am new to this language so ways to write this code better will also help.

mesverrum

The events table is only held for 30 days, if you made it a left join you would get the others, and you could do something like use a case statement to turn the rows with null eventtimes into a string saying "more than 30 days" or a similar message.