cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 7

Including the neighbor node's name in the "A routing neighbor went down" alert

I'm using NPM 11.0.1

I'm trying to do something that seems simple, but I can't get it working.  The alert contains the neighbor's IP address in the message, but that address is generally not in DNS since it's a WAN interface in most cases.  I would like to include the name of the router in the email alert.

I've tried the following in the email body:

${SQL:Select Caption from Nodes where NodeID = ${NeighborID}}

I get the following in the email alert, which doesn't help me at all.

${SQL:Select Caption from Nodes where NodeID = $@NeighborID@}

I tried enclosing the ${NeighborID} in single quotes, and that just does the same thing except that it gives me the single quotes in the email message.

11 Replies
Level 10

Guys,

I'm trying to do the same on a custom property on an interface as like below;

${SQL:SELECT ISNULL((SELECT Top 1CARRIERNAME FROM Interfaces WHERE CARRIERNAME='${N=SwisEntity;M=CustomerProperties.CarrierName}'),'Not Found in NPM')}

But all I get back is the 'Not Found in NPM' in my alert field even though I have a carriername on the interface in question.

0 Kudos
Level 9

I finally got this to work!  I'm using NPM 11.5.2 and this is the syntax I got to work:  ${SQL: SELECT Caption FROM NodesData Where IP_Address='${N=SwisEntity;M=NeighborIP}'}

The placement of the ' ' around the SWQL variable within the Custom SQL variable was critical.  Note that if the NeighborIP doesn't match to any Node IP in your NodeData table the variable syntax will come through in the email.

The syntax that Richard Letts posted works if the Neighbor IP is one of the interface addresses of another monitored node but I was trying to put names on service provider neighboring routers (by adding them as an external node), now I just need to figure out how to combine the two together into a single query.

This is the full body of my "BGP Neighbor is down" alert email:

Node Name: ${N=SwisEntity;M=Router.Nodes.DisplayName}

Neighbor Name: ${SQL: SELECT Caption FROM NodesData Where IP_Address='${N=SwisEntity;M=NeighborIP}'}

Neighbor IP Address: ${N=SwisEntity;M=NeighborIP}

BGP Status: ${N=SwisEntity;M=ProtocolStatusDescription}

Has BGP Neighbor been removed or missing? ${N=SwisEntity;M=IsDeleted}

Note: Idle, Connect, Active, OpenSent or OpenConfirm means DOWN

0 Kudos

Figured out how to combine both into one query:  ${SQL:SELECT Caption FROM NodesData left join NodeIPAddresses on NodesData.NodeID=NodeIPAddresses.NodeID where (NodeIPAddresses.IPAddress='${N=SwisEntity;M=NeighborIP}' OR NodesData.IP_Address='${N=SwisEntity;M=NeighborIP}')}

To take it one step further and set a default neighbor name if none is found:

${SQL:SELECT ISNULL((SELECT TOP 1 Caption FROM NodesData LEFT JOIN NodeIPAddresses ON NodesData.NodeID=NodeIPAddresses.NodeID WHERE (NodeIPAddresses.IPAddress='${N=SwisEntity;M=NeighborIP}' OR NodesData.IP_Address='${N=SwisEntity;M=NeighborIP}')),'Unknown Neighbor')}

The ISNULL function sets the value to "Unknown Neighbor" if no rows are returned. I'm also selecting the "TOP 1". I'm not sure what happens if multiple rows come back, but now I don't have to worry.

Thanks so much for great solution!

0 Kudos

This is exactly what I needed, thanks!

0 Kudos

Sorry to bump an old post, but does anyone know how I would be able to pull the interface name/caption that matches the neighbor IP?  We have some information in the interface caption that I'd like to display in the alert.  I would assume it would be similar to the SQL query for node name, but I'm not familiar with the tables and such of interfaces.  Does anyone know?

0 Kudos
Level 16

try:

${SQL:SELECT top 1 N.caption FROM NodeIPAddresses inner join NodesData N on N.NodeID=NodeIPAddresses.NodeID where NodeIPAddresses.IPAddress=${NeighborIP}}

NOTE: this assumes that NodeIPAddresses is properly populated; IME some routers fail to return their link-local IPv6 addresses; it is useful to include the ${NeighborIP} in the alert in that case, and you'll have to search for it manually 😕

Community Manager
Community Manager

This is what I did for my alert body at a previous company.  Please note that I'm still tinkering with it.  I will update if I have any more information to give.


<html>

<head>

<title>Orion Alert: ${AlertName} for ${Caption}</title>

<style type="text/css">

/*<![CDATA[*/.o {

BACKGROUND-COLOR: #e0e0e0;

}

.11 {

FONT-FAMILY: Verdana;

font-size: 7pt;

COLOR: #000000;

text-align: LEFT;

vertical-align: TOP

}

.12 {

FONT-FAMILY: Verdana;

font-size: 7pt;

COLOR: #000000;

text-align: LEFT;

vertical-align: CENTER

}

.13 {

FONT-FAMILY: Verdana;

font-size: 7pt;

COLOR: #000000;

text-align: LEFT;

vertical-align: BOTTOM

}

.21 {

FONT-FAMILY: Verdana;

font-size: 7pt;

COLOR: #000000;

text-align: CENTER;

vertical-align: TOP

}

.22 {

FONT-FAMILY: Verdana;

font-size: 7pt;

COLOR: #000000;

text-align: CENTER;

vertical-align: CENTER

}

.23 {

FONT-FAMILY: Verdana;

font-size: 7pt;

COLOR: #000000;

text-align: CENTER;

vertical-align: BOTTOM

}

.31 {

FONT-FAMILY: Verdana;

font-size: 7pt;

COLOR: #000000;

text-align: RIGHT;

vertical-align: TOP

}

.32 {

FONT-FAMILY: Verdana;

font-size: 7pt;

COLOR: #000000;

text-align: RIGHT;

vertical-align: CENTER

}

.33 {

FONT-FAMILY: Verdana;

font-size: 7pt;

COLOR: #000000;

text-align: RIGHT;

vertical-align: BOTTOM

}

.h1 {

FONT-FAMILY: Verdana;

font-size: 12pt;

COLOR: navy;

text-align: LEFT;

font-weight: BOLD;

vertical-align: CENTER

}

.h2 {

FONT-FAMILY: Verdana;

font-size: 10pt;

COLOR: gray;

text-align: LEFT;

font-weight: BOLD;

vertical-align: CENTER

}

.h3 {

FONT-FAMILY: Verdana;

font-size: 7pt;

COLOR: gray;

text-align: LEFT;

font-weight: BOLD;

vertical-align: CENTER

}

.h4 {

FONT-FAMILY: Verdana;

font-size: 7pt;

COLOR: gray;

text-align: LEFT;

vertical-align: CENTER

}

.chl {

FONT-FAMILY: Verdana;

font-size: 7pt;

font-weight: BOLD;

COLOR: white;

text-align: LEFT;

}

.chc {

FONT-FAMILY: Verdana;

font-size: 7pt;

font-weight: BOLD;

COLOR: white;

text-align: CENTER;

}

.chr {

FONT-FAMILY: Verdana;

font-size: 7pt;

font-weight: BOLD;

COLOR: white;

text-align: RIGHT;

}

.toc {

text-align: LEFT;

}/*]]>*/

</style>

</head>

<body>

<table cellspacing="0" cellpadding="1" width="792" border="0">

  <tr>

    <td><table cellspacing="0" cellpadding="0" width="792" border="0">

        <tr>

          <td class='h1'>${Node.Caption} (${Node.IP_Address}) has lost IP Routing with Neighbor at ${NeighborIP}</td>

        </tr>

        <tr>

          <td class='h2'>This alert has been triggered because BGP Status on ${Node.Caption} (${Node.IP_Address}) has been lost with Neighbor (${NeighborIP}).</td>

        </tr>

        <tr>

          <td> </td>

        </tr>

      </table>

      <table cellspacing="0" cellpadding="1" width="792" border="1" bordercolor="#003366">

        <tr>

          <td><table cellspacing="0" cellpadding="3" width="100%" border="0">

              <tr>

                <td align="left" valign="middle" bgcolor="#003366" class='chl'>Node</td>

                <td width="16" align="left" valign="middle" class='12'><img src="http://Orion.Server.Fqdn/Orion/images/StatusIcons/${Node.StatusLED}" alt="${Node.StatusDescription}" width="16" height="16"></td>

                <td align="left" valign="middle" class='12'><a href="http://Orion.Server.Fqdn/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N:${Node.NodeID}">${Node.Caption} / ${Node.IP_Address}</a></td>

              </tr>

              <tr>

                <td align="left" valign="middle" bgcolor="#003366" class='chl'>Neighbor IP</td>

                <td width="16" align="left" valign="middle" class='12'><img src="http://Orion.Server.Fqdn/Orion/images/StatusIcons/${StatusLED}" alt="${StatusDescription}" width="16" height="16"></td>

                <td align="left" valign="middle" class='12'><a href="http://Orion.Server.Fqdn/Orion/DetachResource.aspx?ResourceID=1802&NetObject=N:${Node.NodeID}">${NeighborIP}</a></td>

              </tr>

              <tr>

                <td align="left" valign="middle" bgcolor="#003366" class='chl'>Current Status</td>

                <td width="16" align="left" valign="middle" class='12'><img src="http://Orion.Server.Fqdn/NetPerfMon/images/Interfaces/${InterfaceIcon}" width="16" height="16" alt="${InterfaceTypeDescription}"></td>

                <td align="left" valign="middle" class='12'>${DisplayName} (${ProtocolStatus})</td>

              </tr>

              <tr>

                <td align="left" valign="middle" bgcolor="#003366" class='chl'>DNS Name</td>

                <td width="16" align="left" valign="middle" class='12'> </td>

                <td align="left" valign="middle" class='12'>${Node.DNS}</td>

              </tr>

            </table></td>

        </tr>

      </table></td>

  </tr>

</table>

</body>

</html>

"Shoot for the stars to reach the moon"
0 Kudos
Level 8

Nobody has figured out how to do this yet? I'm very interested as well.

Level 7

I'd also like to know how to do this..

0 Kudos