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
All, I am looking for some SWQL/SQL code that can pull out the following information from the database.
Columns Include:
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
When I try to run his Query in SWQL Studio I get an error
for your error... try changing 'Nodes.' to 'Node.'
have you tried my query above?
Yes I tried yours and its working, but failing to validate the out put of query to actual events on the nodes. I am finding that there is different in actual down event on the device when I see it from the console. Still validating
If you are trying to use SQL... try this...
SELECT * from ( SELECT StartTime.EventTime AS DownEventTime, (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 AND EventTime IS NOT NULL ORDER BY EndTime.EventTime) AS UpEventTime, Nodes.Caption, StartTime.Message, 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 DownEventTime DESC
If you are trying to use SQL... try this...
SELECT * from ( SELECT StartTime.EventTime AS DownEventTime, (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 AND EventTime IS NOT NULL ORDER BY EndTime.EventTime) AS UpEventTime, Nodes.Caption, StartTime.Message, 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 DownEventTime DESC
Thanks John its working,
But somehow its not taking all the down event against a device. Eg. there is a device for which I can see 3 down/up event in web console events tab for last month but SQL output shows only first two down/up even pair.
I am not sure why this behavior, please suggest.
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 195,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process.