Thoughts Needed on Complex Alerting Function

Question

Hey All, our company has over 50 remote sites; each with a primary circuit...usually T1 or Fiber Ring. We also have a VPN backup connection off of the same router. Currently we have alerting setup for the primary interface to email us if the interface goes down. I would LOVE to have ability to include a statement in the alert that would indicate whether or not the backup (tunnel) interface were up or down.

For example, if the primary were to go down due to a fiber cut or a rogue back-hoe, the backup should be up. However, if the the remote site were to lose power, more than likely both interfaces will go down.

I'm wondering if it is possible to pass a second variable to the Email Action that would provide the state of the tunnel interface as well as the primary interface. Below is the current alert that I'm using. Thoughts are appreciated. Let me know if I need to clarify anything.

--------------------------------------------------------

Device: ${Node.Caption}
Monitor: ${InterfaceName}
State: ${OperStatus}
IP: ${Node.IP_Address}
Location: ${Location}

Message sent on ${Date} at ${MediumTime}

--------------------------------------------------------

vhcato · Answer

Dirty,

How about trying something like this:

Backup interface status: ${SQL:SELECT CASE Status WHEN 0 THEN 'Unknown' WHEN 1 THEN 'Up' WHEN 2 THEN 'Down' WHEN 3 THEN 'Warning' WHEN 4 THEN 'Shutdown' END FROM Interfaces WHERE NodeID = '${NodeID}' AND InterfaceName = 'Tunnel1'}

I played around with this quite a bit in our environment, as I wanted to do something similar in the near future anyway. The thing you run into is that the Interfaces table doesn't store the status as text, only as a numeric value; hence the reason I opted to use the "CASE" expression in the query. All this does is map the numeric value resulting from the query to a textual representation of the status. The database does store the status icon stuff as text, but they end in ".gif", and I couldn't figure out a way to extract the extra characters.

Something else to keep in mind is that if you are using this alert to monitor devices that don't have a "Tunnel1" interface, then the query returns no value, and the email will simply display the entire query string as text. I thought we could use an "ELSE" in the "CASE" statement, but for some reason this didn't work. Perhaps someone who knows a little more about SQL than I, can explain why this doesn't work. :/   If you have any cases like this, you could use a custom property to determine which devices do (or don't) need to receive this additional info.

Oh, and if you do have interfaces other than "Tunnel1" that provide backup services, you could always populate some value in a custom property and replace the "InterfaceName = 'Tunnel1'" with the custom property and expected value... Or you could key off the "InterfaceAlias" or "Caption".

Hope this helps... :)

DirtySouth · Answer

Thanks for the replies so far. All of the tunnels are "Tunnel1" and usualy have an interface description of "VPN Tunnel". If they don't all, I can certainly make them all identical. I'm definitely not a DBA, so any suggestions would be very much appreciated.

vhcato · Answer

You should be able to do this with a SQL query in your trigger action. Is there some commonality to the interface names (are they all tunnel1, or something) or some other mechanism by which you can query the other interface on the same device? If there is, I'll take a shot at building the query for you.