12 Replies Latest reply on Nov 30, 2016 6:17 AM by abdulraheemsidz

    Outage report for nodes on NPM 11.5.2+

    npereira1

      ok, im opening a new discussion as suggested by others so I can get help on this specific request and not getting suggestions on how to do it another way. this is what I need:

       

      I need to have a report showing up in NPM in the nodes detail that would show when the event node down shows and how long (duration) the node was down. Seems pretty simple, yet I know nothing of SQL so I need help. I was given a script that works in the Report Write tool, but not on the web. I need this available (same output) in the web and based on only the node your looking at. Right now, in the report writer, it shows all nodes. On the web, it needs to show for that specific node.

       

      Here is the SQL I was given that works in the report writer but not in the Web.

       

      SELECT * from (

      SELECT

      StartTime.EventTime AS Down_Event_time,

      (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 Caption desc

       

      Capture.PNG

       

      The above is what shows in the report tool, but i need this in the web and filtered based on the node your looking at.

       

      When I add this report to the web on NPM, I get the below:

       

      Capture.PNG

       

       

      So can anyone help me getting this to work properly in the web PLEASE !  Thanks.

        • Re: Outage report for nodes on NPM 11.5.2+
          zackm

          *Edited to add hours column with more granularity*

           

          Ok, so this is going to be a long one, but it should end up getting you the results you want.

           

          First off, you're going to want to ditch the SQL query and get down on some SWQL!!!

           

          In line with that, you're going to want to use the 'Custom Query' resource, instead of a 'Custom Table'. This allows us to make a nice resource where we can limit the # of rows shown on the page, thus conserving that precious pixel space on your Node Details view. It's also key to making a resource that is relative to the node you are on.

           

          Source code will be at the bottom, here are your steps:

           

          1. Navigate to a Node Details view, click 'Customize Page' and add a 'Custom Query' resource to your page
          2. Edit the new 'Custom Query' resource. Give it a meaningful title, copy/paste the source code into the query section, limit the rows you would like to see, and click 'Submit'

          1.jpg

           

          That's it. Now you have a cool resource that will summarize your downtime per node!!!

           

          Node that is currently down (notice the top 'Up Event' is blank...)

          2.jpg

           

          Node that was down at one time

          3.jpg

           

          SWQL Query (with comments to try and explain what we are doing):

           

          SELECT
            -- Device name
            StartTime.Nodes.Caption AS [Device]
          
            -- This will generate the status icon to the left of the device name
            ,'/Orion/images/StatusIcons/Small-' + StartTime.Nodes.StatusIcon AS [_IconFor_Device]
          
            -- I found that the 'Message' column was too wide for my purposes and opted to remove it
            -- If you want it in your resource, just remove the comment marker (--) from the line below
            --,StartTime.Message
          
            -- 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]
            WHERE EndTime.EventTime >= StartTime.EventTime
            AND EndTime.EventType = 5
            AND EndTime.NetObjectType = 'N'
            AND EndTime.NetObjectID = StartTime.NetObjectID
            AND EventTime IS NOT NULL
            ORDER BY EndTime.EventTime
            ) AS [Up Event]
          
            -- Downtime duration in minutes. Based on either the current time (node is still down) or Up Event time (node is back up)
            ,CASE
            WHEN (SELECT TOP 1 ToLocal(EventTime) AS [EventTime] 
            FROM Orion.Events AS [EndTime]
            WHERE EndTime.EventTime >= StartTime.EventTime
            AND EndTime.EventType = 5
            AND EndTime.NetObjectType = 'N'
            AND EndTime.NetObjectID = StartTime.NetObjectID
            AND EventTime IS NOT NULL
            ORDER BY EndTime.EventTime) IS NULL THEN MINUTEDIFF(StartTime.EventTime, GETUTCDATE())
            ELSE MINUTEDIFF(StartTime.EventTime, 
            (SELECT TOP 1 EventTime 
            FROM Orion.Events AS [EndTime]
            WHERE EndTime.EventTime > StartTime.EventTime
            AND EndTime.EventType = 5
            AND EndTime.NetObjectType = 'N'
            AND EndTime.NetObjectID = StartTime.NetObjectID
            ORDER BY EndTime.EventTime))
            END AS [Minutes]
          
            -- Downtime duration in hours. Based on either the current time (node is still down) or Up Event time (node is back up)
            ,CASE
            WHEN (SELECT TOP 1 ToLocal(EventTime) AS [EventTime] 
            FROM Orion.Events AS [EndTime]
            WHERE EndTime.EventTime >= StartTime.EventTime
            AND EndTime.EventType = 5
            AND EndTime.NetObjectType = 'N'
            AND EndTime.NetObjectID = StartTime.NetObjectID
            AND EventTime IS NOT NULL
            ORDER BY EndTime.EventTime) IS NULL THEN ROUND((MINUTEDIFF(StartTime.EventTime, GETUTCDATE())/60.0),2)
            ELSE ROUND((MINUTEDIFF(StartTime.EventTime, 
            (SELECT TOP 1 EventTime 
            FROM Orion.Events AS [EndTime]
            WHERE EndTime.EventTime > StartTime.EventTime
            AND EndTime.EventType = 5
            AND EndTime.NetObjectType = 'N'
            AND EndTime.NetObjectID = StartTime.NetObjectID
            ORDER BY EndTime.EventTime))/60.0),2)
            END AS [Hours]
          
          -- This is the table we are querying
          FROM Orion.Events StartTime
          
          
          -- EventType = 1 is to correlate with our 'Down Event' time stamp from line 13 above
          WHERE StartTime.EventType = 1
          
          
          -- Here's the trick to get the query to dynamically pull details for the node you are looking at
          AND StartTime.NetObjectID = ${NodeID}
          
          
          -- Order our results from the newest to oldest 'Down Event'
          ORDER BY [Down Event] DESC
          

           

          Hope this helps you out. Let me know if there are questions!

           

           

          -ZackM

          Loop1 Systems: SolarWinds Training and Professional Services

          1 of 1 people found this helpful
            • Re: Outage report for nodes on NPM 11.5.2+
              npereira1

              zackm

               

              Thanks for the help this works like a charm.

               

              Once last question, what is the time span of these results? I would like to see the last 90 days. What would need to change?  I think this currently only shows last 30 days.

               

              Thanks !

              • Re: Outage report for nodes on NPM 11.5.2+
                abdulraheemsidz

                Can we have the same SWQL to work in reports ?? I mean instead of ${NodeID} can we specify a particular node and generate report.

                  • Re: Outage report for nodes on NPM 11.5.2+
                    zackm

                    surely, you just need to identify the node you want and add its particular nodeid in place of the ${NodeID} variable

                      • Re: Outage report for nodes on NPM 11.5.2+
                        abdulraheemsidz

                        Thanks for your response, tried giving nodeid in the same query it doesn't work.

                         

                        • Re: Outage report for nodes on NPM 11.5.2+
                          abdulraheemsidz

                          No problem, I used same SWQL query and changed it to plain SQL query which anyone can use in generating reports for specific nodes.

                           

                           

                           

                          SELECT * from (
                          SELECT
                          StartTime.EventTime AS Down_Event_time,
                          (SELECT TOP 1
                          EventTime
                          FROM Events AS Endtime
                          WHERE EndTime.EventTime >= StartTime.EventTime
                          AND EndTime.EventType = 5
                          AND EndTime.NetObjectType = 'N'
                          AND EndTime.NetObjectID = StartTime.NetObjectID
                          AND EventTime IS NOT NULL
                          ORDER BY EndTime.EventTime) AS Up_Event_Time, Nodes.Caption, Nodes.IP_Address
                          
                          
                          -- Downtime duration in Minutes. Based on either the current time (node is still down) or Up Event time (node is back up) 
                          
                          
                          ,CASE
                          WHEN
                          (
                          SELECT TOP 1 EventTime 
                          FROM Events AS Endtime
                          WHERE EndTime.EventTime >= StartTime.EventTime 
                          AND EndTime.EventType = 5 
                          AND EndTime.NetObjectType = 'N'
                          AND EndTime.NetObjectID = StartTime.NetObjectID  
                          AND EventTime IS NOT NULL
                          ORDER BY EndTime.EventTime
                          ) 
                          IS NULL 
                          THEN 
                          ROUND 
                           (
                            (DATEDIFF(Minute, StartTime.EventTime, GETUTCDATE())),2
                           ) 
                          ELSE 
                          ROUND 
                          (
                          (DATEDIFF
                            (Minute, 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.NetObjectID = StartTime.NetObjectID
                            ORDER BY EndTime.EventTime
                            )
                            )
                          ),2
                          ) 
                          END AS Minutes
                          
                          
                          -- Downtime duration in Hours. Based on either the current time (node is still down) or Up Event time (node is back up)
                          
                          
                          ,CASE
                          WHEN
                          (
                          SELECT TOP 1 EventTime 
                          FROM Events AS Endtime
                          
                          
                          WHERE EndTime.EventTime >= StartTime.EventTime 
                          AND EndTime.EventType = 5 
                          AND EndTime.NetObjectType = 'N'
                          AND EndTime.NetObjectID = StartTime.NetObjectID  
                          AND EventTime IS NOT NULL
                          ORDER BY EndTime.EventTime
                          ) 
                          IS NULL 
                          THEN 
                          ROUND 
                           (
                            (DATEDIFF(Minute, StartTime.EventTime, GETUTCDATE())/60.0),2
                           ) 
                          ELSE 
                          ROUND 
                          (
                          (DATEDIFF
                            (Minute, 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.NetObjectID = StartTime.NetObjectID
                            ORDER BY EndTime.EventTime
                            )
                            )/60.0
                          ),3
                          ) 
                          END AS Hours
                          
                          
                          -- Displaying the required columns (in this case Hours, Minutes, IP_Address)
                          
                          
                          FROM Events StartTime INNER JOIN Nodes ON StartTime.NetObjectID = Nodes.NodeID
                          WHERE (StartTime.EventType = 1)
                          ) as uptimetable
                          WHERE Minutes IS NOT NULL
                          
                          
                          -- Specify which nodes you want in this report.
                          
                          
                          AND IP_Address IN ('172.30.1.1', '1.1.1.2')
                          ORDER BY Down_Event_time desc
                          
                    • Re: Outage report for nodes on NPM 11.5.2+
                      npereira1

                      zackm,

                       

                      can you tell me what to change if I want last 90 days instead of last 30 days? i have changes the settings so that the events are kept for a minimum of 90 days.

                       

                      Thanks greatly appreciated.