Nodes availability report with downtime

[kasper091]

Hi,

Anyone please help me with query to create a nodes availability report including downtime in minutes.

similar to attached images including ip address, node name, device type, vendor, availability in percentage, downtime in minutes.

Thanks

 @BGee @serena @aLTeReGo @Seashore 

[kasper091]

anyone please help me to get this report,

thanks

[jblankjblank · Accepted solution]

@kasper091 The best route would be to utilize SWQL to generate this report. For now, I have a quick SQL query that could assist. Create a new Report > Select Custom Table >  and for the Data Source - choose Advanced and hit the radio button for SQL. Input the following query:

SELECT * FROM (

 SELECT
 Nodes.StatusLED,
 Nodes.Caption,
 Nodes.NodeID,
 StartTime.Message,
 StartTime.EventTime AS DownEventTime,
 (
  SELECT TOP 1 EventTime
  FROM Events AS EndTimeTable
  where EndTimeTable.EventTime >= StartTime.EventTime
   AND EndTimeTable.EventType = 5
   AND EndTimeTable.NetObjectType = 'N'
   AND EndTimeTable.NetworkNode = StartTime.NetworkNode
   AND EventTime IS NOT NULL
  ORDER BY EndTimeTable.EventTime
 ) AS UpEventTime,
DATEDIFF(Mi, StartTime.EventTime,(
   SELECT TOP 1 EventTime FROM Events AS Endtime
   where EndTime.EventTime > StartTime.EventTime AND EndTime.EventType = 5 AND EndTime.NetObjectType = 'N'
    AND EndTime.NetworkNode = StartTime.NetworkNode  ORDER BY EndTime.EventTime)
  ) AS OutageDurationInMinutes
 FROM Events StartTime
 INNER JOIN Nodes ON StartTime.NetworkNode = Nodes.NodeID
 WHERE (StartTime.EventType = 1)
) AS UpTimeTable
where outageDurationInMinutes IS NOT NULL
ORDER BY Caption ASC, DownEventTime DESC

[kasper091]

Thanks for helping, Would you please add timestamp in this query so i can filter specific time results from this query, thanks