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.

Help with Alert "advanced" query - simple query.

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!

  • 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.

    rneigh.png

    Let me know if this is on the right track?

  • 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.

  • 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

    Step1.png

    Step 2 click on Define SQL/SWQL

    Step2.png


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

    Step3.png

  • 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'"

  • 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.

  • @jeilers - That NAILED it!  emoticons_happy.png

    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

  • Happy to hear that got you working!  I honestly didn't know the routing neighbor options were there so I just had to look into this. Thanks for posting this so we could both learn.