5 Replies Latest reply on Jan 7, 2020 8:19 AM by edenoveso

    How to Get a List of Down Devices and the Time They Went Down?

    drcp

      I have found several permutations of this question, but could never track down a solid answer for my specific question. I need to run an SQL Query via the reporting Resource. I am a network admin so my SQL knowledge is rather limited, but I do have a background in coding. Below is my attempt at a basic start to this mess:

       

           SELECT Nodes.Caption as Name,  Nodes.Status as Status, Nodes.Monitoring_CAT as Is_CAT, EventTime as Events

           FROM [dbo].[Nodes], [dbo].[Events]

           WHERE Nodes.Monitoring_CAT=1 AND (Status=0 OR Status=2)

           ORDER BY Nodes.Caption

       

      This query above does work. So, down to the root of my issues...

          1. I need to be able to get the Last event from EventTime, but have no clue how to do that. I suspect there some crazy INNER JOIN stuff for that one.

       

      I was using the Query below for reference from another Thwack post, but there is very little I can understand from it once you get into the event time section:

       

      SELECT * FROM (

          SELECT

          Nodes.StatusLED,

          Nodes.Caption,

          Nodes.NodeID,

          Nodes.MachineType,

          StartTime.Message,

          StartTime.EventTime AS DownEventTime,

          Monitoring_CCM,

          (

              SELECT TOP 1 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)

      ) AS UpTimeTable

      where outageDurationInMinutes IS NOT NULL AND outageDurationInMinutes >= '15'

      AND datepart(hour, DownEventTime) >= 8

      AND datepart(hour, UpEventTime) >= 8

      AND datepart(hour, DownEventTime) <= 17

      AND datepart(hour, UpEventTime) <= 17

      ORDER BY Caption ASC, DownEventTime DESC

       

       

      All help is appreciated, Thanks!

        • Re: How to Get a List of Down Devices and the Time They Went Down?
          tdanner

          Will the LastSystemUpTimePollUtc property work for you? This is the time of the last successful poll of the device. That would make for a simpler query than finding the most recent event.

            • Re: How to Get a List of Down Devices and the Time They Went Down?
              drcp

              I have already tried the LastSystemUpTimePollUtc property. The issue with that field is that it seems to only work on devices polled by SNMP. Unfortunately this query is being run on a lot of ICMP polled devices. I've been messing around with the NodeDownTimeHistory and Alert Last Trigger Time, but I don't actually have access to my database to see the schema for that. As such I have to kind of guess the database names which as you can probably guess really hard to do.

                  • Re: How to Get a List of Down Devices and the Time They Went Down?
                    drcp

                    One of the earlier variations that someone posted in the comments of that post seems to work. Now I just need to tweak it for my purposes. Since i'm running 12.2 I am using the watered down version posted in the comments from that post:

                     

                    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 

                     

                    Good find. Thanks!

              • Re: How to Get a List of Down Devices and the Time They Went Down?
                edenoveso

                Hi I'm just new here, just want to add a script that worked on me.

                 

                My target was to show Down time for nodes but I had to do it in such a way that Switches, Routers, and Firewalls are separated on different table to provide a single-glance Monitoring on NOC.

                 

                I'm using SolarWinds 2019.4, btw.

                 

                Below is the outcome of the script that I have created.

                 

                Code below is for Switch only, you can just replace it with whatever classification you have.

                 

                SELECT

                n.Caption AS [Switch Name]

                ,n.IPAddress AS IP

                ,n.detailsurl as [_linkfor_IP]

                ,c.City AS Location

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

                ,n.DetailsUrl AS [_LinkFor_Switch Name]

                ,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.NodesCustomProperties c ON n.NodeID = c.NodeID 

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

                WHERE STATUS = 2 and E.Eventtype=1 and c.Machine_Type = 'Switch'

                >"Machine_Type" is a group that I have created to classify the devices, I have Switch, Router, Firewall, and Server

                GROUP BY NodeName, StatusIcon, DetailsUrl, IPAddress, c.City 

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

                 

                Kindly rate if helpful. Thanks