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:
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.
Trying to modify it to group by Device and Order by Down Event in descending order but getting an error.
Trying to modify it to group by Device and Order by Down Event in descending order but getting an error. What is the syntax for that?
Show me what you have tried, and the error you receive, please
Also, are you using SQL? or SWQL Query?
I'm using SWQL for Stevenstadel's query and getting the "there was an error processing the request" message.
I tried placing "group by n.Caption" immediately before the "order by" statement at the end.
Sorry, i will have to see your version of the code... for example.. your query will not work unless you comment out line 7... show your version of the code for assistance
Sorry to take so long to respond. Got off on other things. So I have almost everything I need here wit this query, but I still need:
1. To filter the report based on a specific customer using the node custom property "CustomerName".
2. Need different time frames, specifically, Yesterday, Last Week and Last Month.
How can I do all of these?
Appreciate it.
Is it possible to restrict this to only the previous 24 hour period?
For sure. This changes it to have the Down Event start within the last 24 hours
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] , n.customProperties.IgnoreNodeDown -- Show Mute Node Down Custom Property , 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 down event, if there is no timestamp then it says the event was greater than the number of days in your event retention settings , ISNULL(TOSTRING(t2.[Up Event]), 'Still Down') AS [Up Event] -- shows the timestamp of the up event, unless the object is still down , ISNULL(MINUTEDIFF(t2.[Down Event], ISNULL(t2.[Up Event], GETDATE())), 99999) AS Minutes -- 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 , n.Vendor FROM orion.nodes n LEFT JOIN ( SELECT StartTime.Nodes.NodeID -- Device nodeid used for our join , ToLocal(StartTime.EventTime) AS [Down Event] -- Down 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 AND EndTime.EventType = 5 -- EventType 5 is a node up AND EndTime.NetObjectID = StartTime.NetObjectID AND EventTime IS NOT NULL ORDER BY EndTime.EventTime ) AS [Up Event] -- Up Event time stamp in local time zone FROM Orion.Events StartTime -- This is the table we are querying WHERE StartTime.EventType = 1 -- EventType 1 is a node down AND StartTime.EventTime >= ADDDATE('HOUR', -24, GETUTCDATE()) -- Limit downtime start to the last 24 hours (24 hours * 60 minutes = 1440 minutes) ) t2 ON n.NodeID = t2.nodeid WHERE (n.status = 2 OR t2.nodeid IS NOT NULL) -- this is how I catch nodes that are down but have aged out of the events table ORDER BY t2.[down event] DESC
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.