This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Node Downtime with Duration and Minimum Length Filtering

**REQUIRES ORION PLATFORM 2018.2 OR ABOVE**

I had assembled this based on a much older SQL report, and then updated it to SWQL, then added some more intelligence to it so you can filter it based on the duration of the outage, search by the device names, and it has a method of letting you know when nodes have been down so long they aged out of the events table.

Based on popular requests I figured it was time to put it out here to make it easier for the Thwackers to find and use.  This is intended to be used inside the Custom Query Resource


pastedImage_1.png

select n.caption as [Device]
-- shows the current status icon
, '/Orion/images/StatusIcons/Small-' + n.StatusIcon AS [_IconFor_Device]
-- makes a clickable link to the node details
, n.DetailsUrl as [_linkfor_Device]
-- shows the timestamp of the down event, if there is no timestamp then is says the event was greater than the number of days in your event retention settings
, isnull(tostring(t2.[Down Event]),concat('Greater than ',(SELECT CurrentValue FROM Orion.Settings where settingid='SWNetPerfMon-Settings-Retain Events'),' days ago')) as [Down Event]
-- shows the timestamp of the up event, unless the object is still down
, isnull(tostring(t2.[Up Event]),'Still Down') as [Up Event]
-- figures out the minutes between the down and up events, if the object is still down it counts from the down event to now, displays 99999 if we cannot accurately determine the original downtime, and
, isnull(MINUTEDIFF(t2.[Down Event], isnull(t2.[Up Event],GETDATE())),99999) as Minutes


from orion.nodes n
left join (SELECT   
-- Device nodeid used for our join  
StartTime.Nodes.NodeID    

-- Down Event time stamp in local time zone   
,ToLocal(StartTime.EventTime) AS [Down Event]   
 
-- Up Event time stamp in local time zone   
,(SELECT TOP 1   
ToLocal(EventTime) AS [EventTime]   
FROM Orion.Events AS [EndTime]   
-- picks the first up event that is newer than the down event for this node
WHERE EndTime.EventTime >= StartTime.EventTime  
-- EventType 5 is a node up
AND EndTime.EventType = 5   
AND EndTime.NetObjectID = StartTime.NetObjectID   
AND EventTime IS NOT NULL   
ORDER BY EndTime.EventTime   
) AS [Up Event]   
 
-- This is the table we are querying   
FROM Orion.Events StartTime   
 
-- EventType 1 is a node down
WHERE StartTime.EventType = 1   
   
) t2 on n.NodeID = t2.nodeid


-- this is how I catch nodes that are down but have aged out of the events table
where (n.status = 2 or t2.nodeid is not null)


-- If you want to filter the results to only show outages of a minimum duration uncomment the below line
--and MINUTEDIFF(isnull(t2.[Down Event],(GETUTCDATE()-30)), isnull(t2.[Up Event],GETUTCDATE())) >  60


-- if you want to use this query in a search box of the Custom Query resource uncomment the below line
--and n.Caption like '%${SEARCH_STRING}%'


order by t2.[down event] desc

-Marc Netterfield

    Loop1 Systems: SolarWinds Training and Professional Services

Parents
  • mesverrum awesome work dude, I've been trying to work on something like this but time (and knowledge) has alluded me. I too am unable to get your query working in 12.2 I suspect due to the way the JOIN is created, it doesn't seem to like the different FROM statements, I suspect you might be able to use a UNION to get around this? I'm still new to SQL/SWQL.

    For now, I've dumbed it down a little to get it to work for clients running 12.2 although I think once I upgrade to 12.3 I will be moving back to your version.

    -- Nodes Down Duration

    SELECT

    n.Caption AS [Device]

    ,'/Orion/images/StatusIcons/Small-' + n.StatusIcon AS [_IconFor_Device]

    ,n.DetailsUrl AS [_LinkFor_Device]

    ,CONCAT(SUBSTRING(tostring(MAX(e.EVENTTIME)),1,4),SUBSTRING(tostring(MAX(e.EVENTTIME)),5,2),

            SUBSTRING(tostring(tolocal(MAX(e.EVENTTIME))),12,8)) as Downtime,

      CONCAT(HOURDIFF(tolocal(max(e.eventtime)),getdate())/24,' Day(s) ',

            HOURDIFF(tolocal(max(e.eventtime)),getdate())-(HOURDIFF(tolocal(max(e.eventtime)),getdate())/24)*24,'h ',

            MINUTEDIFF(tolocal(max(e.eventtime)),getdate())   -   (MINUTEDIFF(tolocal(max(e.eventtime)),getdate())/60)*60,'m') AS Duration

    FROM Orion.Nodes n

    INNER JOIN Orion.Events e ON n.NodeID = e.NetworkNode

    WHERE STATUS = 2 and E.Eventtype=1 --and nodes.customproperties.SystemsGrouping Like '%CPE%'

    GROUP BY NodeName, StatusIcon, DetailsUrl

    ORDER BY MINUTEDIFF(tolocal(MAX(E.EventTime)),getdate())  desc

    pastedImage_0.png

    Hope it helps people until they can upgrade emoticons_wink.png

Reply
  • mesverrum awesome work dude, I've been trying to work on something like this but time (and knowledge) has alluded me. I too am unable to get your query working in 12.2 I suspect due to the way the JOIN is created, it doesn't seem to like the different FROM statements, I suspect you might be able to use a UNION to get around this? I'm still new to SQL/SWQL.

    For now, I've dumbed it down a little to get it to work for clients running 12.2 although I think once I upgrade to 12.3 I will be moving back to your version.

    -- Nodes Down Duration

    SELECT

    n.Caption AS [Device]

    ,'/Orion/images/StatusIcons/Small-' + n.StatusIcon AS [_IconFor_Device]

    ,n.DetailsUrl AS [_LinkFor_Device]

    ,CONCAT(SUBSTRING(tostring(MAX(e.EVENTTIME)),1,4),SUBSTRING(tostring(MAX(e.EVENTTIME)),5,2),

            SUBSTRING(tostring(tolocal(MAX(e.EVENTTIME))),12,8)) as Downtime,

      CONCAT(HOURDIFF(tolocal(max(e.eventtime)),getdate())/24,' Day(s) ',

            HOURDIFF(tolocal(max(e.eventtime)),getdate())-(HOURDIFF(tolocal(max(e.eventtime)),getdate())/24)*24,'h ',

            MINUTEDIFF(tolocal(max(e.eventtime)),getdate())   -   (MINUTEDIFF(tolocal(max(e.eventtime)),getdate())/60)*60,'m') AS Duration

    FROM Orion.Nodes n

    INNER JOIN Orion.Events e ON n.NodeID = e.NetworkNode

    WHERE STATUS = 2 and E.Eventtype=1 --and nodes.customproperties.SystemsGrouping Like '%CPE%'

    GROUP BY NodeName, StatusIcon, DetailsUrl

    ORDER BY MINUTEDIFF(tolocal(MAX(E.EventTime)),getdate())  desc

    pastedImage_0.png

    Hope it helps people until they can upgrade emoticons_wink.png

Children
No Data