This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Route Flap Monitoring

I am trying to figure out a way to make route flap monitoring useful. Right now I can look at a node and see what routes have flapped in that node's routing table and that is useful. What I want is a way to add these route flaps to the "top 10" view. I want to see top 10 routes that have flapped in the last hour. Anyone know how to do this or if it is possible?

I also want to know what other people are doing with route flap monitoring to identify issues in the network.

  • I have SQL script which flags route flaps based on 2 variables: [NUMBER OF FLAPS] over the last [NUMBER OF HOURS]

    For example - to flag any interfaces with more than 10 flaps over the last 7 days:

    SELECT

      Nodes.NodeID

    ,'Warning.gif' AS 'ICON'

    ,Nodes.Caption AS 'NODE'

    ,Nodes.IP_Address AS 'IP'

    ,rtf.RouteDestination AS 'DEST'

    ,rtf.CIDR AS 'CIDR'

    ,COUNT(*) AS 'FLAPS'

    ,CONVERT(NVARCHAR(50), MAX(rtf.DateTime)) + ' (' + CONVERT(NVARCHAR(50), DATEDIFF(MINUTE,MAX(rtf.DateTime),GETDATE())) + ' min ago)' AS 'LAST_FLAP'

    FROM SolarWindsOrion.dbo.Nodes

    INNER JOIN SolarWindsOrion.dbo.NPM_RoutingTable_Flap rtf ON rtf.NodeId = Nodes.NodeID

    WHERE

      rtf.DateTime > DATEADD(HOUR,-168,GETDATE()) --OVER WHAT TIME

    GROUP BY Nodes.NodeID, Nodes.Caption, Nodes.IP_Address, rtf.RouteDestination, rtf.CIDR

      HAVING

        COUNT(*) >= 10 --HOW MANY FLAPS

    ORDER BY [LAST_FLAP] desc, Nodes.NodeID, COUNT(*) DESC

    If you will then build Report with Report-Writer you can then add this as a resource on page:

    Capture.jpg

    In your case if you want over the last hour you may want as well restrict number of flaps to 1-2 only + if you will replace first line "SELECT" with "SELECT TOP 10" - you will get your desired top 10 results only.

    Have a play,

    Alex

  • Thank you Alex. This worked perfectly. I made a few tweaks to the query to get what I needed. In addition to getting the view I needed your reply helped me learn a few new things about Orion. I did not know I could add reports as a resource or the process to do so. A quick search showed me how.

    The only odd part is the count displays as "2 per minute" when it should be displayed as "2". Any idea why that is happening?

    flapcap.JPG

    SELECT TOP 10

      Nodes.NodeID

    ,'Warning.gif' AS 'ICON'

    ,Nodes.Caption AS 'NODE'

    ,Nodes.IP_Address AS 'IP'

    ,rtf.RouteDestination AS 'DEST'

    ,rtf.CIDR AS 'CIDR'

    ,COUNT(*) AS 'FLAPS'

    ,CONVERT(NVARCHAR(50), MAX(rtf.DateTime)) + ' (' + CONVERT(NVARCHAR(50), DATEDIFF(MINUTE,MAX(rtf.DateTime),GETDATE())) + ' min ago)' AS 'LAST_FLAP'

    FROM Nodes

    INNER JOIN NPM_RoutingTable_Flap rtf ON rtf.NodeId = Nodes.NodeID

    WHERE

    The

      rtf.DateTime > DATEADD(HOUR,-1,GETDATE()) --OVER WHAT TIME

    and

    Nodes.NodeID <>  855 

    and

    Nodes.NodeID <>  902 

    and

    Nodes.NodeID <>  756

    and

    Nodes.NodeID <>  1346

    and

    Nodes.NodeID <>  1001

    and

    Nodes.NodeID <>  1944

    GROUP BY Nodes.NodeID, Nodes.Caption, Nodes.IP_Address, rtf.RouteDestination, rtf.CIDR

      HAVING

        COUNT(*) >= 2 --HOW MANY FLAPS

    ORDER BY [LAST_FLAP] desc, Nodes.NodeID, COUNT(*) DESC

  • Well done, I am glad to hear this has worked for you

    First, go back to your report and under FIELD FORMATING tab select ICON tab and assign Format = "Icon" and Icon Type = "Status" - this way you will see actual icon, rather than "Warning.gif"

    Second - check what you setting you have for FLAPS column. It should be Format = "No Formatting" and Valid Range = "All Values"

    Third, when you specify nodes to exclude in your SQL I would suggest the below format instead - this will make it more cleaner:

    AND Nodes.NodeID NOT IN (855, 902, 756, 1346, 1001, 1944)

  • I applied the suggested formatting and it looks much better. Thanks for your help!

  • Nice post! BTW you should change GETDATE to GETUTCDATE as Solarwinds now stores in UTC

  • This is nice!  I am being asked to take this one step further and alert off of the information.  The network group here is actually after an alert if there are more than 3 flaps in 60 minutes on an interface.  Adjusting the time in the report shows this with no problem.  My guess is I will need to do an SQL based alert, but I am just beginning with this type of an alert.

  • Hi Alex,

      Shall we used the above query in Web based report. Could you please help me on this one. actually we are trying to given the query in web based report.  getting the error. "Not valid"

    Current environment 2020.2.5 version