SNMP TRAP >>> Solarwinds Alert

I know there are a tremendous number of posts regarding traps>>>alerts which resolve similar scenarios, but I can't find one that fits mine. Traps from over 100 devices are all received from the same server (source IP). There is no OID that specifies the device, however, the device name is the last 6 characters of the OID value "enterprises.3263.2.1.1.2.1.10.2.3.4.5.6.7.8.9" (the red portion in this example): enterprises.3263.2.1.1.2.1.10.2.3.4.5.6.7.8.9 = DEV 2 SW TO BKUP SRC BUS 072 PPHINW

I follow the documented process of rules which tag the trap with varBind values, and color the trap with red for SET, and green for CLEAR. Examples of traps below:

TIME OF TRAP

IP ADDRESS

HOSTNAME

TRAP TYPE

TRAP DETAILS

6/1/2017 12:52:12 PM

10.7.1.102

AM-MIB:enterprises.3263.2.1.2.0.6

snmpTrapEnterprise = AM-MIB:enterprises.3263.2.1.2

experimental.1057.1.0 = 10.7.1.102

sysUpTime = 55 days 2 hours 15 minutes 38.85 seconds

snmpTrapOID = AM-MIB:enterprises.3263.2.1.2.0.6

enterprises.3263.2.1.1.2.1.12.2.3.4.5.6.7.8.9 = 2017060116521100Z

enterprises.3263.2.1.1.2.1.11.2.3.4.5.6.7.8.9 = 3

enterprises.3263.2.1.1.2.1.10.2.3.4.5.6.7.8.9 = DEV 2 SW TO BKUP SRC BUS 072 PPHINW

enterprises.3263.2.1.1.2.1.9.2.3.4.5.6.7.8.9 = 1

enterprises.3263.2.1.1.2.1.8.2.3.4.5.6.7.8.9 = 54

enterprises.3263.2.1.1.2.1.7.2.3.4.5.6.7.8.9 = 21

enterprises.3263.2.1.1.2.1.6.2.3.4.5.6.7.8.9 = 54

enterprises.3263.2.1.1.2.1.5.2.3.4.5.6.7.8.9 = 21

enterprises.3263.2.1.1.2.1.4.2.3.4.5.6.7.8.9 = 99

enterprises.3263.2.1.1.2.1.3.2.3.4.5.6.7.8.9 = 1

enterprises.3263.2.1.1.2.1.2.2.3.4.5.6.7.8.9 = 2

enterprises.3263.2.1.1.2.1.1.2.3.4.5.6.7.8.9 = 2

6/1/2017 12:52:06 PM

10.7.1.102

AM-MIB:enterprises.3263.2.1.2.0.2

snmpTrapEnterprise = AM-MIB:enterprises.3263.2.1.2

experimental.1057.1.0 = 10.7.1.102

sysUpTime = 55 days 2 hours 15 minutes 33.26 seconds

snmpTrapOID = AM-MIB:enterprises.3263.2.1.2.0.2

enterprises.3263.2.1.1.2.1.12.2.3.4.5.6.7.8.9 = 2017060116520600Z

enterprises.3263.2.1.1.2.1.11.2.3.4.5.6.7.8.9 = 3

enterprises.3263.2.1.1.2.1.10.2.3.4.5.6.7.8.9 = DEV 2 SW TO BKUP SRC BUS 072 PPHINW

enterprises.3263.2.1.1.2.1.9.2.3.4.5.6.7.8.9 = 2

enterprises.3263.2.1.1.2.1.8.2.3.4.5.6.7.8.9 = 54

enterprises.3263.2.1.1.2.1.7.2.3.4.5.6.7.8.9 = 21

enterprises.3263.2.1.1.2.1.6.2.3.4.5.6.7.8.9 = 54

enterprises.3263.2.1.1.2.1.5.2.3.4.5.6.7.8.9 = 21

enterprises.3263.2.1.1.2.1.4.2.3.4.5.6.7.8.9 = 99

enterprises.3263.2.1.1.2.1.3.2.3.4.5.6.7.8.9 = 1

enterprises.3263.2.1.1.2.1.2.2.3.4.5.6.7.8.9 = 2

enterprises.3263.2.1.1.2.1.1.2.3.4.5.6.7.8.9 = 2

Trap Viewer:

SET Condition:

enterprises.3263.2.1.1.2.1.9.2.3.4.5.6.7.8.9 is equal to 2

Trigger Actions:

Color the Trap with: 8388863

Tag the Trap with: ${vbData9} | ${vbData12} | ${vbData10}

Stop Processing Trap Rules

CLEAR Condition:

enterprises.3263.2.1.1.2.1.9.2.3.4.5.6.7.8.9 is equal to 1

Clear Actions:

Color the Trap with: 8454016

Tag the Trap with: ${vbData9} | ${vbData12} | ${vbData10}

Stop Processing Trap Rules

Advanced Alert SQL queries:

Trigger Condition:
where nodeid in

(SELECT a.nodeid

FROM traps a with (nolock)

where colorcode=8388863 and acknowledged=0

and not exists ( select 1 from traps b where a.nodeid=b.nodeid and b.colorcode=8454016 and b.datetime>a.datetime)

)

Reset Condition:

where nodeid not in

(SELECT a.nodeid

FROM traps a with (nolock)

where colorcode=8388863 and acknowledged=0

and not exists ( select 1 from traps b where a.nodeid=b.nodeid and b.colorcode=8454016 and b.datetime>a.datetime)

)

The problem with the queries is that it uses 'nodeid' to match sets/clears. Since the 'nodeid' is always the same in my case, I'm getting inconsistent results.

What I would like to be able to do is somehow parse the name of the device (it's always the last 6 characters of the OID value "enterprises.3263.2.1.1.2.1.10.2.3.4.5.6.7.8.9 = DEV 2 SW TO BKUP SRC BUS 072 PPHINW

and include the device name in the query instead of the 'nodeid' Unfortunately, my SQL skills are not this advanced. Any help or suggestions is appreciated!

Find more posts tagged with

trap_alerts

sql alerts

Accepted answers

All comments

tkercher

anyone? Bueler?

designerfx

coping tdanner as maybe he has some good ideas of how to go from devicename (presumably the auto generated one) -> nodeID with a sql query.

tkercher

sorry, this isn't answered, accidentally clicked the link and can't change it back

tdanner

If I understand correctly, you want to look up a NodeID by matching the last 6 characters of a trap tag with the node's caption. Is that right?

Here's a simple query that will do that:

SELECT N.NodeID, N.Caption, N.Status

FROM Traps T

LEFT JOIN Nodes N ON RIGHT(T.Tag, 6) = N.Caption

Working that into your trigger condition query, I come up with something like this (not tested! use with caution!):

where nodeid in

(SELECT N.NodeID

FROM traps a with (nolock)

LEFT JOIN Nodes N ON RIGHT(a.Tag, 6) = N.Caption

where colorcode=8388863 and acknowledged=0

and not exists ( select 1 from traps b where RIGHT(a.Tag,6)=RIGHT(b.Tag,6) and b.colorcode=8454016 and b.datetime>a.datetime)

)

tkercher

tdanner,

First, thank you so much for your help. Briefly, here is the scenario: We have a fairly dated but important application that listens to all of our radio channels and detects silence (silence is bad). When silence is detected, the application sends a trap message to Solarwinds. All traps come into Solarwinds from the same source IP, and unfortunately the only way to identify the channel that is silent is by looking at the last 6 characters of an OID value string. Example: enterprises.3263.2.1.1.2.1.10.2.3.4.5.6.7.8.9 = DEV 2 SW TO BKUP SRC BUS 072 PPHINW

I'm using RichardLetts method of creating alerts from traps. In trap viewer, I TAG the trap with the OID that includes the 6 character channel name, and color the trap red or green based on SET/CLEAR. (This part is working great). Then in advanced alerts, I use Richard's query. The problem I'm having is my current query assumes the nodeID represents the impacted device (channel). But in my case, the NodeID never changes and isn't really relevant. So what I need to do is modify the query to use either the last 6 characters of the TAG instead of nodeID. I'm barely SQL literate, so just wondering if this is what the above query does?

Thank you again!

designerfx

I think that's what he was going for? I just knew you'd know a cleaner/faster way than me trying to remember off the top of my head

RichardLetts

this assumes that the last 6 characters of whatever is in your tag is the 6 significant characters

assuming the caption on your nodes matches the 6 characters you have in the tag you pulled from the trap.

where caption in

(SELECT RIGHT(a.Tag,6)

FROM traps a with (nolock)

where colorcode=8388863 and acknowledged=0

and not exists ( select 1 from traps b where a.nodeid=b.nodeid and b.colorcode=8454016 and b.datetime>a.datetime and RIGHT(a.Tag,6)=RIGHT(b.Tag,6))

)

Alternatively if you have some custom properly on the nodes with the 6-character value that matches the tag:

where SomeCustomProperty in

(SELECT RIGHT(a.Tag,6)

FROM traps a with (nolock)

where colorcode=8388863 and acknowledged=0

and not exists ( select 1 from traps b where a.nodeid=b.nodeid and b.colorcode=8454016 and b.datetime>a.datetime and RIGHT(a.Tag,6)=RIGHT(b.Tag,6))

)

I suggest you get a copy of SQL for dummies if you're going to be playing around with this sort of thing.

Please also open a support case asking for this feature to be simplified so cobrien knows that people still need to be able to generate alerts from traps, like almost every other network management application can.

edit : added tdanner's fix for the right string functions

Richard

tkercher

You guys are awesome, I cannot thank you enough!

tdanner

I think this piece is a problem:

and not exists ( select 1 from traps b where a.nodeid=b.nodeid and b.colorcode=8454016 and b.datetime>a.datetime)

Because all of tkercher's traps are coming from the same source regardless of which radio they are about, this filter will "match up" any two red and green traps regardless of whether they are about the same channel. Instead of joining on a.nodeid=b.nodeid, we need to join on RIGHT(a.Tag,6)=RIGHT(b.Tag,6).

tkercher

RichardLetts quick question about this line "assuming the caption on your nodes matches the 6 characters you have in the tag you pulled from the trap."

The only real node here is the single server that sends traps for all stations. Will this work assuming the single source, or do I need to add an external device for each channel for this to work?

RichardLetts

yes

All alerts need an object to be associated with an alert -- I am not sure if you can raise alerts on external nodes.

object = node, interface, port, etc

tdanner

Yes, you can raise alerts on "external" nodes. That might be the best way for tkercher to make this work: create a placeholder external node for each radio channel and set the name of that node to that channel's six character identifier.

sja

Colud be nice if solarwinds will finally do something about SNMP TRAP

Many just move trap out from Orion

that FR could help if you move trap to kiwisyslog

tkercher

RichardLetts , tdanner, -

I took your advice and purchased SQL for Dummies. Learned a ton, and making things far easier. Led to a few findings and a slightly different approach. But, having problem with the trigger query.

As stated in previous messages, I don't care about the Node or NodeID since all traps are coming from a single server (IP address). What I do care about is the last 6 characters of the TAG string. These last 6 characters represent different radio stations. What I learned from using the " RIGHT(TAG,6) " query on my data is that the tag field has trailing spaces, resulting in the last 6 characters almost always being blank. So, using SQL for Dummies, I found the RTRIM function and the query becomes: RIGHT(RTRIM(TAG),6) I've checked this against my data and it works, returning a list of 6 character radio stations (my first SQL success).

So now that I've isolated the Station having the problem, all I need to do is create a trigger query that looks for any Radio Station with a red trap, and not a green trap received after the red trap. Note, I have other systems using the red/green trap colors, so I included the source IP address to ensure this query only applies to radio stations. Using what the two of you already provided, and some of my own, I came to the following:

where tag in

(SELECT RIGHT(RTRIM(a.tag),6)

FROM traps a with (nolock)

where IPAddress='10.7.1.102' AND colorcode=8388863 and acknowledged=0

and not exists ( select 1 from traps b where RIGHT(RTRIM(a.Tag),6)=RIGHT(RTRIM(b.Tag),6) and b.colorcode=8454016 and b.datetime>a.datetime )

)

Unfortunately, this fails validation in the alert trigger query. I've been through it a number of times, and tried many variations, but obviously still missing something. Please let me know where you think I went south. Thanks

tkercher

Note, I also tried this (we call the stations 'Bus' codes:

where tag in

(SELECT RIGHT(RTRIM(a.tag),6) AS Bus

FROM traps a with (nolock)

where IPAddress='10.7.1.102' AND colorcode=8388863 and acknowledged=0

and not exists ( select 1 from traps b where RIGHT(RTRIM(a.Tag),6)=RIGHT(RTRIM(b.Tag),6) and b.colorcode=8454016 and b.datetime>a.datetime )

)

RichardLetts

Assuming you have external nodes that match the 6-letter bus codes:

where caption in

(SELECT RIGHT(RTRIM(a.tag),6)

FROM traps a with (nolock)

where IPAddress='10.7.1.102' AND colorcode=8388863 and acknowledged=0

and not exists ( select 1 from traps b where RIGHT(RTRIM(a.Tag),6)=RIGHT(RTRIM(b.Tag),6) and b.colorcode=8454016 and b.datetime>a.datetime )

)

[and the inverse would be

where caption not in

(SELECT RIGHT(RTRIM(a.tag),6)

FROM traps a with (nolock)

where IPAddress='10.7.1.102' AND colorcode=8388863 and acknowledged=0

and not exists ( select 1 from traps b where RIGHT(RTRIM(a.Tag),6)=RIGHT(RTRIM(b.Tag),6) and b.colorcode=8454016 and b.datetime>a.datetime )

)

tkercher

RichardLetts & tdanner, one final question. My trigger and clear conditions ended up as follows (below). This seems to be working great and associating Traps to individual stations/channels. But do I need to tweak my SQL statement (bold) to display the trap? ${SQL: SELECT top 1 TAG FROM [dbo].[TRAPS] Where caption=${Caption} order by datetime desc }; I've tried a few things and this part still doesn't seem to work. Tried several options and can't figure out what I'm doing wrong?

where caption in

(SELECT RIGHT(RTRIM(a.tag),6)

FROM traps a with (nolock)

where colorcode=8388863 and acknowledged=0

and not exists ( select 1 from traps b where RIGHT(RTRIM(a.Tag),6)=RIGHT(RTRIM(b.Tag),6) and b.colorcode=8454016 and b.datetime>a.datetime )

)

where caption not in

(SELECT RIGHT(RTRIM(a.tag),6)

FROM traps a with (nolock)

where colorcode=8388863 and acknowledged=0

and not exists ( select 1 from traps b where RIGHT(RTRIM(a.Tag),6)=RIGHT(RTRIM(b.Tag),6) and b.colorcode=8454016 and b.datetime>a.datetime )

)

tdanner

If I am following this correctly, you want to use this ${SQL: ...} macro in a trigger or reset action to include the most recent trap. You have created an "External" node for each channel, so this alert is associated with the channel's node. The channel node's caption is just the 6-letter bus code, which is also the tag for all traps associated with that channel.

How do you want to show the trap? Is there a specific varbind value that has the relevant message?

tkercher

tdanner & RichardLetts,

Guys, thank you guys so much for all your help and support. I believe the trigger/reset queries which parse the text string and associate the silence to a station is working correctly. However, I have one final problem that I've been struggling with for 2 weeks. Once the trigger condition is met, the alert action fires, but the SQL statement used to send the TAG information to ServiceNow is using the 'most recent' ( top ) TAG, instead of using the tag which triggered the alert ( the silent station). This is most apparent when we have several stations go silent at once, because the top TAG is the same. My thought is that the Alert Action statement needs to include a "WHERE" that specifies the station, but nothing I've tried works.

Here is my trigger condition:

Trigger condition

where caption in

(SELECT RIGHT(RTRIM(a.tag),6)

FROM traps a with (nolock)

where colorcode=8388863 and acknowledged=0

and not exists ( select 1 from traps b where RIGHT(RTRIM(a.Tag),6)=RIGHT(RTRIM(b.Tag),6) and b.colorcode=8454016 and b.datetime>a.datetime )

)

Alert Action:

${SQL: SELECT TOP 1 TAG FROM [dbo].[Traps] where colorcode=8388863 order by datetime desc };

tdanner

I think you just need to add a NodeID condition to that action query. Like this (not tested):

${SQL: SELECT TOP 1 TAG FROM [dbo].[Traps] where colorcode=8388863 AND NodeID=${NodeID} order by datetime desc };

tkercher

Thank you tdanner, I will test and report results.