SWQL Node Outages with duration

Colby

Hey All,

I’m new to SolarWinds reporting and would like to create a report with the following information:

Node Name
Start of Downtime
End of Downtime
Duration

I only need the report to cover the last 7 days.

From my research, it seems that Orion.AlertStatus might be the best table to pull the required information.

Another Note: I only want to include nodes—no groups or other entities.

Any guidance or tips would be greatly appreciated!

Find more posts tagged with

SWQL

Comments

Colby

A bit more information:

I wrote the following code, but it only works for SQL, not SWQL.

SELECT
Nodes.Caption AS [Node Name],
Events.EventTime AS [Start of Downtime],
(SELECT TOP 1 EventTime
FROM Events AS EndEvent
WHERE EndEvent.NetworkNode = Events.NetworkNode
AND EndEvent.EventTime > Events.EventTime
AND EndEvent.EventType = 5) AS [End of Downtime],
DATEDIFF(MINUTE,
Events.EventTime,
(SELECT TOP 1 EventTime
FROM Events AS EndEvent
WHERE EndEvent.NetworkNode = Events.NetworkNode
AND EndEvent.EventTime > Events.EventTime
AND EndEvent.EventType = 5)) AS [Duration (Minutes)]
FROM
Events
JOIN
Nodes ON Events.NetworkNode = Nodes.NodeID
WHERE
Events.EventType = 1 -- Node Down Event
ORDER BY
Events.EventTime DESC;

Seashore

Just converted your SQL code to SWQL and added some naming and formatting.

SELECT
    N.Caption AS [Node Name],
    E.EventTime AS [Start of Downtime],
    (SELECT TOP 1 
        EventTime
     FROM Orion.Events AS EndEvent
        WHERE 
            EndEvent.NetworkNode = E.NetworkNode
            AND EndEvent.EventTime > E.EventTime
            AND EndEvent.EventType = 5) AS [End of Downtime]
    ,MINUTEDIFF(E.EventTime,
    (SELECT TOP 1 
            EventTime
     FROM Orion.Events AS EndEvent
     WHERE 
        EndEvent.NetworkNode = E.NetworkNode
        AND EndEvent.EventTime > E.EventTime
        AND EndEvent.EventType = 5)
    ) AS [Duration (Minutes)]
FROM Orion.Events AS E
INNER JOIN Orion.Nodes AS N ON E.NetworkNode = N.NodeID
WHERE
    E.EventType = 1 -- Node Down Event
    AND E.EventTime>ADDDAY(-7,GETDATE())
ORDER BY 
    E.EventTime DESC

Colby

Thank You!!!!

Question: Do you have link or YouTube I can reference for training?

donrobert5

https://www.youtube.com/@marcin_kazmierczak

www.youtube.com/@solarwinds

gadbekr

This is almost perfect, but how would I add a filter to filter for a specific node name attribute?

Lofstrand

Hi @gadbekr

If you want to search for a node named something like "MyServer" you can just add below line to the WHERE-part of the query:

AND N.Caption LIKE '%MyServer%'

The end will look like this then:

gadbekr

That worked perfectly. Now I have been asked another question. How would we limit the report to show only the devices that have been down for 60mins or longer?

Lofstrand

I think we have to wrap the first query in another SELECT part, so we can have a WHERE -clause on the duration. Like this where I on the last line adjust the number of minutes the down time has to be:

SELECT
    T.[Node name]
    ,T.[Start of Downtime]
    ,T.[End of Downtime]
    ,T.[Duration]
FROM (

    SELECT
        N.Caption AS [Node Name],
        E.EventTime AS [Start of Downtime],
        (SELECT TOP 1 
            EventTime
         FROM Orion.Events AS EndEvent
            WHERE 
                EndEvent.NetworkNode = E.NetworkNode
                AND EndEvent.EventTime > E.EventTime
                AND EndEvent.EventType = 5) AS [End of Downtime]
        ,MINUTEDIFF(E.EventTime,
        (SELECT TOP 1 
                EventTime
         FROM Orion.Events AS EndEvent
         WHERE 
            EndEvent.NetworkNode = E.NetworkNode
            AND EndEvent.EventTime > E.EventTime
            AND EndEvent.EventType = 5)
        ) AS [Duration]
    FROM Orion.Events AS E
    INNER JOIN Orion.Nodes AS N ON E.NetworkNode = N.NodeID
    WHERE
        E.EventType = 1 -- Node Down Event
        AND E.EventTime>ADDDAY(-7,GETDATE())
    ORDER BY 
        E.EventTime DESC
) AS T
WHERE
    T.[Duration]>30  -- The amount of minutes to report on