Network Management

Nodes report showing downtime in minutes during the last 30 days

An example of a custom SQL query for generating a report for nodes downtime in minutes for the last 30 days.

First published date

10/24/2018 9:34 AM

Last published date

12/8/2021 11:48 AM

Overview

The below SQL query can be used to generate a report that shows node downtime in minutes for the last 30 days.

Product section

Orion Platform

Resolution

-- Scripts are not supported under any SolarWinds support program or service. 
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further 
-- disclaims all warranties including, without limitation, any implied warranties 
-- of merchantability or of fitness for a particular purpose. The risk arising 
-- out of the use or performance of the scripts and documentation stays with you. 
-- In no event shall SolarWinds or anyone else involved in the creation, 
-- production, or delivery of the scripts be liable for any damages whatsoever 
-- (including, without limitation, damages for loss of business profits, business 
-- interruption, loss of business information, or other pecuniary loss) arising 
-- out of the use of or inability to use the scripts or documentation.

Group_Position="Beginning" SQL="SELECT MAX(VendorIcon) AS VendorIcon,  MAX(Caption) AS Caption,  MAX(StatusLED) AS StatusLED, NodeID, SUM(OutageDurationInMinutes) AS SumOutageDurationInMinutes  FROM (
    SELECT
    Nodes.VendorIcon,
    Nodes.Caption,
    Nodes.StatusLED,
    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
GROUP BY NodeID
ORDER BY Caption ASC



See the Node downtime reports post in Thwack for more information.