5 Replies Latest reply on May 7, 2015 3:06 PM by ido.networkstuff

    Create alert when specific route missing from routing table

    ido.networkstuff

      I have a single device that has specific routes I would like to monitor.  I would like to configure an alert to trigger when these routes go missing.

       

      I've verified that solarwinds is already pulling the routing table information from the device (routing table poller).  I just can't figure out how to configure the alert.

      I tried opening a ticket with solarwinds and they stated it was not a feature so I was trying to use custom sql query to trigger the alert.

       

      I tested my query statement in a report using the advanced database query and it shows the routes I'm looking to monitor.

      The problem seems to be that when I create an alert and select custom SQL/SWQL alert, it auto generates the SELECT and FROM statement so I can't choose the specific table I want to query.

      Unfortunately there is no option in the pulldown for RoutingTable.

      Maybe there is another table containing the info for this pulldown that I can change?

       

      Below is the query I used in a test report to show the routes I want to monitor.

      (I'm thinking I would change the LIKE to NOT LIKE when configuring the trigger, but I can't get that far to test)

      Custom Query

      SELECT RouteDestination

      FROM Orion.Routing.RoutingTable

      WHERE

          (

              NodeID=

                  (

                  SELECT NodeID

                  FROM Orion.Nodes

                  WHERE IPAddress='1.2.3.4'

                  )

              AND

              (

                  (RouteDestination LIKE '2.2.2.0')

                  OR

                  (RouteDestination LIKE '3.3.3.0')

                  OR

                  (RouteDestination LIKE '4.4.4.0')

                  OR

                  (RouteDestination LIKE '5.5.5.0')

                  OR

                  (RouteDestination LIKE '6.6.6.0')

              )

          )

       

      Maybe I'm going about this all wrong..  Any thoughts?

        • Re: Create alert when specific route missing from routing table
          njoylif

          try

          select n.Uri, n.DisplayName, rt.routedestination

          FROM Orion.Nodes n

          LEFT JOIN Orion.Routing.RoutingTable rt on n.Nodeid=rt.nodeid

          WHERE

               n.ipaddress='1.2.3.4' and

               rt.routedestination in ('2.2.2.0','3.3.3.0','4.4.4.0',<etc..>)

            • Re: Create alert when specific route missing from routing table
              ido.networkstuff

              Thank you for your response.

               

              Your query works in SWQL Studio.

              On solarwinds:

                  When validating as SWQL, it says the query condition is not valid.

                  When validating as SQL, it comes back saying query is valid.  But under summary it says it will trigger on a couple hundred objects.

               

              Under summary, the trigger condition looks like below because of the forced SELECT FROM statement (seen circled in first post):


              Summary Tab: Trigger Condition
              SELECT Nodes.NodeID, Nodes.Caption FROM Nodes
              select n.Uri, n.DisplayName, rt.routedestination FROM Orion.Nodes n LEFT JOIN Orion.Routing.RoutingTable rt on n.Nodeid=rt.nodeid WHERE n.ipaddress='1.1.1.1' and rt.routedestination in ('2.2.2.2','3.3.3.3','4.4.4.4','5.5.5.5')


              When the above is put in SWQL Studio, i get error "mismatched input 'select' expecting 'EOF'.


              If I figure out how to get this to work i'll post what i did.

                • Re: Create alert when specific route missing from routing table
                  ido.networkstuff

                  Update: getting closer thanks to njoylif !!

                  I changed his query a little bit and it is now triggering an alert for each specified route on the specified node (total of 10)

                   

                  Current SWQL Query including forced SELECT FROM statement

                  SELECT Nodes.Uri, Nodes.DisplayName FROM Orion.Nodes AS Nodes

                  LEFT JOIN Orion.Routing.RoutingTable AS RT ON Nodes.NodeID = RT.NodeID

                  WHERE

                      Nodes.IPAddress='1.1.1.1' AND

                      RT.RouteDestination IN ('2.2.2.2','3.3.3.3','4.4.4.4'.'5.5.5.5')

                   

                  I'd like only one alert to trigger when any of these routes go missing

                       ~when the above query does not return 10 rows/routes (without changing the initial SELECT FROM statement).

                    • Re: Create alert when specific route missing from routing table
                      ido.networkstuff

                      I think I got it.  Will be testing off hours to verify alerts trigger as expected.

                       

                      solution?

                      SELECT Nodes.Uri, Nodes.DisplayName FROM Orion.Nodes AS Nodes

                      WHERE

                          Nodes.IPAddress='1.1.1.1' AND

                          (

                          SELECT COUNT(RouteDestination) AS RD

                          FROM Orion.Routing.RoutingTable

                          WHERE(RouteDestination IN ('2.2.2.2','3.3.3.3'.'4.4.4.4','5.5.5.5','6.6.6.6'))

                          ) <> 10


                        • Re: Create alert when specific route missing from routing table
                          ido.networkstuff

                          Thought I would share my final solution.

                          Little change up, instead of matching specific routes, I decided to count all the routes received from specific interfaces minus the directly connected routes. (although now that I think about it, that last part is not really necessary lol)

                           

                          Final Solution

                          SELECT Nodes.Uri, Nodes.DisplayName FROM Orion.Nodes AS Nodes

                          WHERE

                              Nodes.IPAddress='1.1.1.1' AND

                              (

                              SELECT COUNT(RouteDestination) AS RD

                              FROM Orion.Routing.RoutingTable

                              WHERE

                                  (

                                  InterfaceName IN ('GigabitEthernet1/0/44','GigabitEthernet2/0/44') AND

                                  RouteDestination NOT IN ('8.8.8.8','9.9.9.9')

                                  )

                              ) <> 10

                           

                          Also, in order to change the routing table polling interval you have to make manual SQL entries/changes.  These are the instructions I received from solarwinds tech support.

                           

                          Change Routing Table Polling

                          - For all nodes-
                          Settings table, SettingID - NPM_Settings_Routing_RouteTable_PollInterval

                          To change routing poll interval for all nodes  UPDATE Settings SET CurrentValue=<minutes> where SettingID='NPM_Settings_Routing_RouteTable_PollInterval'

                          - For per node-
                          NodeSettings table, SettingName - NPM.RoutingTablePollInterval

                          To change it per node INSERT INTO NodeSettings (NodeID,SettingName,SettingValue) VALUES (<NodeID>,'NPM.RoutingTablePollInterval',<minutes>)

                           

                          I change the setting for a single node and it worked flawlessly.