Custom SQL query in Alert message

I have a custom properties setup on each of my interfaces that lists off the circuit id, carrier, and TAC number for that circuit and carrier.

I have an alert setup to alert me when a BGP neighbor goes down, and alert me.

What I want to do is create an alert that looks like this:

Location: <location>

Timestamp: <timestamp>

Carrier: <carrier>

Circuit ID: <circuit_id>

Status: <status>

Actions to take:

1. See if there is maintenance currently happening that would affect this circuit, if no maintenance is being done to the affected circuit go to step 2.

2. If no maintenance is being done, contact <carrier> at <tac_number> and report a "circuit down" issue for <circuit_id>.

3. Contact company network engineer and notify them of the issue.

4. After a ticket has been opened with <carrier>, email <carrier> account team with the <carrier> ticket number.

I don't see a way that I can get the relevant "interface" information into an alert that is monitoring the "routing neighbors" property. Any help is greatly appreciated!

Find more posts tagged with

advanced_alerts

custom-properties

Accepted answers

zackm

ctrader wrote:
an alert that is monitoring the "routing neighbors" property.

That's the key right there. When you create alerts based on the 'Routing Neighbors' type, you are not pulling any interface information into your query.

You are really pulling your information out of the NPM_RoutingNeighbor_V view. So your options are going to be limited. This is the information in that view:

So, you are limited to connecting the Nodes table. Unless you have a way to identify the specific interface from a node, you might be in a pickle.

For instance:

SELECT Interfaces.CarrierName FROM Interfaces JOIN Nodes ON Nodes.${NodeID}=Interfaces.NodeID WHERE Interfaces._____ = ???

Are your nodes setup to only have one BGP link per node? If so, add a custom property to the interface marking it as BGP; then use that as your limiting factor on your query.

-ZackM

Loop1 Systems: SolarWinds Training and Professional Services

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

All comments

adatole

This is going to take a little SQL-fu, but you can do it.

The secret(s) is/are the ${SQL: } variable.

The other secret is knowing that you always have the interfaceID no matter what kind of (interface) alert you build. So to grab the location, you would create something LIKE this:

(CAVEATE CODER: it's not exact, you will need to test this using MS SQL Management Studio to ensure it works as expected)

${SQL: select interfaces.location from interfaces where interfaces.interfaceid = ${InterfaceID} }

ctrader

When I use ${SQL: select interfaces.CarrierName from interfaces where interfaces.interfaceid = ${InterfaceID}} I get a "MACRO SQL ERROR - Incorrect syntax near '{'." error.

I have verified that if I run the following query: select interfaces.CircuitID from interfaces where interfaces.InterfaceID = 474

I do get the correct CircuitID returned in MS SQL Management Studio.

adatole

I'm confused - do you want CircuitID or CarrierName?

ctrader

Oh, sorry I pasted the wrong one. I'm going to gather both, but in separate queries.

The SQL query I ran was : select interfaces.CarrierName from interfaces where interfaces.InterfaceID = 474

zackm

ctrader wrote:
an alert that is monitoring the "routing neighbors" property.

That's the key right there. When you create alerts based on the 'Routing Neighbors' type, you are not pulling any interface information into your query.

You are really pulling your information out of the NPM_RoutingNeighbor_V view. So your options are going to be limited. This is the information in that view:

So, you are limited to connecting the Nodes table. Unless you have a way to identify the specific interface from a node, you might be in a pickle.

For instance:

SELECT Interfaces.CarrierName FROM Interfaces JOIN Nodes ON Nodes.${NodeID}=Interfaces.NodeID WHERE Interfaces._____ = ???

Are your nodes setup to only have one BGP link per node? If so, add a custom property to the interface marking it as BGP; then use that as your limiting factor on your query.

-ZackM

Loop1 Systems: SolarWinds Training and Professional Services

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

adatole

Great catch!

ctrader

Thanks a lot guys, this works exactly how I want it to!

zackm

Sorry, now that I look at this again, I gave you the wrong query. Try this:

SELECT Interfaces.CarrierName FROM Interfaces JOIN Nodes ON Nodes.NodeID=Interfaces.NodeID WHERE Nodes.NodeID=${NodeID} AND Interfaces._____ = ???