Node Down Time

Question

All, I am looking for some SWQL/SQL code that can pull out the following information from the database. 
 
 Columns Include: 
 
 Device Name 
 IP Address 
 Time Down 
 Time Up 
 Minutes Node was down

stevenstadel · Accepted Answer

You can use this query as an SWQL report or as a Query Widget. It is excellent as a widget as it includes hyperlinks. Credit to the original creator for adding all the comments! 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]
--Show Mute Node Down Custom Property
, n.customProperties.IgnoreNodeDown
-- 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
,n.Vendor
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}%'
--Filter on Vendor
---and n.Vendor like '%Windows%'

order by t2.[down event] desc

John W Johnson · Answer

OK... the start of this query was online somewhere...i have made it my own... OF COURSE>>>>> IF YOU USE THIS IN A LIVE ENVIRONMENT, it is not supported by SolarWinds! USE AT YOUR OWN RISK... 
 
 Now, after saying that... you will most definately have to modify this for your environment..as i am using some pulling down some custom properties that are only on our environment... 
 
 SELECT * FROM (
 SELECT
 --Nodes.StatusLED,
 Nodes.Caption,
 Nodes.NodeID,
 Nodes.Is_Production,
 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) AND Nodes.Is_Production = '1' AND Nodes.Model = 'Server'
) AS UpTimeTable
where outageDurationInMinutes IS NOT NULL
ORDER BY Caption ASC, DownEventTime ASC

Node Down Time

Top Replies