7 Replies Latest reply on Nov 15, 2017 9:52 AM by jeilers

    Help with Alert "advanced" query - simple query.

    pseudocyber

      I'm working on an alert for the routing protocol neighbor down.  The alert variables provide the node ID with the problem, ${N=SwisEntity;M=NeighborID} - but not the name or the caption.  Could someone help me with the "Define SQL/SWQL Variable (Advanced) to find the name and/or caption of the node with the problem?

       

      Thanks!

        • Re: Help with Alert "advanced" query - simple query.
          jeilers

          I'm not sure if this is helpful but it sounds to me like you are wanting the node name in the message?

           

          I opened up one of the messages and looked at what I have to pull from.

           

           

           

          Let me know if this is on the right track?

          • Re: Help with Alert "advanced" query - simple query.
            pseudocyber

            Thanks jeilers .  The alert is for a router with a routing protocol neighbor problem between routers A and B.  The Caption(node) gives me the name of router A.  I'm looking for the name of router B.  The ${N=SwisEntity;M=NeighborID} gives me the node id of the neighbor router B, but not the name or caption.  So, if I have the node ID, I'm looking for some kind of query that will give me the name or caption, of the neighbor B, knowing the neighbor's nodeID.

              • Re: Help with Alert "advanced" query - simple query.
                jeilers

                That makes sense. If that is the case then I think this should do the trick for you.(Your table names might be different at the from part but I think this should get you what you are looking for.)

                 

                ${SQL: SELECT

                       [Caption]

                  FROM [NetPerfMon].[dbo].[Nodes]

                where nodeid=${N=SwisEntity;M=NeighborID}}

                 

                Go the alert message click on insert variable

                Step 1

                 

                Step 2 click on Define SQL/SWQL


                Step 3 put in the query I pasted above and you might have what you need.

                 

              • Re: Help with Alert "advanced" query - simple query.
                pseudocyber

                Thanks! When I try to paste in your text, I get an error: "Only Select statements are allowed as SQL/SWQL macros."

                 

                If I modify your text to read, SELECT [Caption] FROM [NetPerfMon].[dbo].[Nodes] where nodeid=${N=SwisEntity;M=NeighborID}}

                Solarwinds replaces it all with ${SQL: SELECT [Caption] FROM [NetPerfMon].[dbo].[Nodes] where nodeid=${N=SwisEntity;M=NeighborID}}

                 

                However, this results with an error:  "MACRO SQL ERROR - Invalid object name 'NetPerfMon.dbo.Nodes'"

                  • Re: Help with Alert "advanced" query - simple query.
                    jeilers

                    Yeah that is correct in the message for solarwinds to have it as ${SQL: SELECT [Caption] FROM [NetPerfMon].[dbo].[Nodes] where nodeid=${N=SwisEntity;M=NeighborID}}

                     

                    That ${SQL: *The Actual Query*}  is how it knows you are wanting to do an sql query.

                     

                    For your table I am going to guess that the NetPerfMon is the issue. An easy way to find out is going in to "All Settings" -> "Database Details" and replacing NetPerfMon with what ever you have as the catalog.

                    1 of 1 people found this helpful
                      • Re: Help with Alert "advanced" query - simple query.
                        pseudocyber

                        @jeilers - That NAILED it! 

                         

                        This is a routing protocol alert.  Routing protocols maintain "neighbor relationships". The neighbor peering should be up, but may have a problem.

                         

                        The node with the problem is, <b>${N=SwisEntity;M=Router.Nodes.DisplayName}</b> located at ${N=SwisEntity;M=Router.Nodes.Location}.

                        It is a ${N=SwisEntity;M=Router.Nodes.HardwareHealthInfos.ParentObjectVendor} ${N=SwisEntity;M=Router.Nodes.HardwareHealthInfos.Model}.

                         

                        Node comment:  ${N=SwisEntity;M=Router.Nodes.CustomProperties.NODECOMMENT}

                         

                        Node, <b>${N=SwisEntity;M=Router.Nodes.Caption}</b>, has a routing problem with its <b>${N=SwisEntity;M=ProtocolName}</b> protocol.

                        The protocol status is:  ${N=SwisEntity;M=ProtocolOrionStatus} - ${N=SwisEntity;M=ProtocolStatusDescription}

                         

                        Link to the down neighbor:  https://orion/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N:${N=SwisEntity;M=NeighborID}

                         

                        Problem on neighbor:  ${SQL: SELECT [Caption] FROM [SolarWindsOrion].[dbo].[Nodes] where nodeid=${N=SwisEntity;M=NeighborID}}

                         

                        ==========================

                         

                        This is a routing protocol alert. Routing protocols maintain "neighbor relationships". The neighbor peering should be up, but may have a problem.

                         

                        The node with the problem is, ROTUERA located at 1234 MAIN ST, ANYTOWN, USA.

                        It is a Cisco ASR1001-X.

                         

                        Node comment: This is the WAN router. If it is down, traffic to SITE2 and SITE3 will be impacted. SITE3 will be down. Fix ASAP.

                         

                        Node, ROUTERA, has a routing problem with its Cisco EIGRP protocol.

                        The protocol status is: 1 - Established

                         

                        Link to the down neighbor: https://orion/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N:23

                         

                        Problem on neighbor: ROUTERB