How do I include the Interface IP in the 'email me when an Interface goes down' alert?

I'm working with the SWQL variables in the email Alert Message to try to do this. Here's the original code:

The Loopback IP is: ${N=SwisEntity;M=Node.IP_Address}
Interface is: ${N=SwisEntity;M=InterfaceName} - ${N=SwisEntity;M=Alias}
And is located at: ${N=SwisEntity;M=Node.Location}
This is an: ${N=SwisEntity;M=Node.MachineType}
IOS Version is: ${N=SwisEntity;M=Node.IOSVersion}
The iOS Image is: ${N=SwisEntity;M=Node.IOSImage}
Device last boot was: ${N=SwisEntity;M=Node.LastBoot;F=DateTime}

What I need to do is add a fourth line that basically says this:

The Interface {i.caption} at IP Address {nip.ipadress} on Node {nd.caption} is no longer in an 'UP' state.

There is not a variable that I can find for the Interface IP Address. I can find the Node's IP and the MAC address

I do have this SQL code that will find it

SELECT i.nodeid         AS "NodeID",
       i.interfacename AS "Int Name",
       i.caption,
       i.interfacealias AS Description,
       i.ifname,
       i.status,
       nip.ipaddress    AS "IP Address",
       nd.caption       AS "Node Name"
FROM   [interfaces] AS i,
       [nodeipaddresses] AS nip,
       [nodesdata] AS nd
WHERE i.nodeid = nip.nodeid
       AND nip.nodeid = nd.nodeid
       AND i.interfaceindex = nip.interfaceindex
ORDER BY i.nodeid

The code is sound and will work in Database Manager. It does pull the Interface IP Address

But how do I include that SQL Query in the Alert and then extract the information I need to place into the statement? Or is there a simpler way and I'm over thinking it?

Find more posts tagged with

sql

SWQL

alert variables

interface down

Accepted answers

All comments

mesverrum

I feel like whoever wrote that must be a very old SQL user, that style of join syntax has been out of fashion for a long while haha. In any case if the GUI doesn't have a variable for the IP on the interface then you can click the insert variable button and at the bottom there is a Custom SQL option, use that and insert it like so

SELECT top 1 nip.ipaddress

FROM [interfaces] AS i,

[nodeipaddresses] AS nip,

[nodesdata] AS nd

WHERE i.nodeid = nip.nodeid

AND nip.nodeid = nd.nodeid

AND i.interfaceindex = nip.interfaceindex

and i.interfaceid=${N=SwisEntity;M=InterfaceID}

The tricky part I ran into with my testing is if the query doesn't resolve a number then it looks like it dumps out the whole query in the email. It should resolve for nodes that are polled via SNMP and have IP addresses on the interface, so if the things you are alerting on all match that profile then you are all set.

-Marc Netterfield

Loop1 Systems: SolarWinds Training and Professional Services

LinkedIN: Loop1 Systems
Facebook: Loop1 Systems
Twitter: @Loop1Systems

greg.remer

Thus this is still the same even today.

I created an IF EXISTS, Then, else in the SQL Query to display text and it works when using SQL MS, but when in the alert I still get the SQL query in full...

My guess is it's something to do with the way it's calling the SQL.

One for the developers?

greg.remer

Yeah i too found that it dumps the SQL Query into the alert....

Would be great if they could change that to a variable like "N/A" or "not located in database"

The other thing I thought of would be to try and get the SQL Query to do a DNS lookup and import the output?

But not had no luck on this as yet.

mesverrum

Since I wrote this I have done a bunch more alerts using custom SQL and I had switched to a model where I just always build the logic in such a way that it always gets at least 1 row, and if its not the row I NEEDED then I just isnull the value, so in this case I would do something like

SELECT top 1 isnull(nip.ipaddress,'')

from interfaces i

left join [NodeIPAddresses] nip on nip.nodeid=i.nodeid and i.interfaceindex=nip.interfaceindex

where i.interfaceindex=${N=SwisEntity;M=InterfaceID}

greg.remer

This is my current SQL query I have in the alert which try's to lookup a name and again, if not found all the SQL Query is entered.

Any idea what I could do with this to replicate what you mentioned about using ISNULL?

BGP Neighbor Name:

${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}') OR (NodeIPAddresses.IPAddress IS NULL OR NodesData.IP_Address IS NULL)}

Is there a way you could change the ISNULL to display a variable? like "Not Found"

Many Thanks

Greg