Nodes availability report with downtime

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

     

Top Replies

  •  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…

  • anyone please help me to get this report,

    thanks

  •  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
  • Thanks for helping, Would you please add timestamp in this query so i can filter specific time results from this query, thanks