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.

Custom SQL query in Alert message

ctrader over 9 years ago

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!

Top Replies

0 adatole over 9 years ago

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} }
Cancel
Vote Up +1 Vote Down

Cancel
0 ctrader over 9 years ago in reply to adatole

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.
Cancel
Vote Up 0 Vote Down

Cancel
0 adatole over 9 years ago in reply to ctrader

I'm confused - do you want CircuitID or CarrierName?
Cancel
Vote Up 0 Vote Down

Cancel
0 ctrader over 9 years ago in reply to adatole

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
Cancel
Vote Up 0 Vote Down

Cancel
0 zackm over 9 years ago
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
Cancel
Vote Up +1 Vote Down

Cancel
0 adatole over 9 years ago in reply to zackm

Great catch!
Cancel
Vote Up +2 Vote Down

Cancel
0 ctrader over 9 years ago in reply to zackm

Thanks a lot guys, this works exactly how I want it to!
Cancel
Vote Up +1 Vote Down

Cancel

0 zackm over 9 years ago in reply to ctrader

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._____ = ???