Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

Create Alert message body using SQL language to filter out unwanted information

Dear Thwack,

I need help using the SQL or SQL substring to edit the alert message body to provide output and send alert to our internal support group.

Here is the custom trap info from Log Viewer:

5/18/2020 8:26:21 AM

CISCO-IPSEC-FLOW-MONITOR-MIB:cikeTunnelStop : cikePeerLocalAddr.1.4.69.60.152.62.1.4.192.168.85.81.58929 = 69.60.152.62, cikePeerRemoteAddr.1.4.69.60.152.62.1.4.192.168.85.81.58929 = 192.168.85.81, cikeTunActiveTime.58929 = 3769475, cikeTunHistTermReason.58929 = peerLost(5), sysUpTime = 87 days 7 hours 54 minutes 3.41 seconds, experimental.1057.1.0 = 192.168.84.11, snmpTrapEnterprise = CISCO-IPSEC-FLOW-MONITOR-MIB:cipSecMIBNotificationPrefix

Source Time

5/18/2020 4:26:21 AM

Source

aamot-chicago2-CR-1037681

(192.168.84.11)

Vendor

Cisco

Machine Type

Cisco 4431 ISR

Log Type

Traps

Level

Unknown

Community

aamvanet

Trap Type

CISCO-IPSEC-FLOW-MONITOR-MIB:cikeTunnelStop

Trap OID

1.3.6.1.4.1.9.9.171.2.0.2

VARBIND ELEMENT WITH NAME

cikePeerLocalAddr.1.4.69.60.152.62.1.4.192.168.85.81.58929 (1.3.6.1.4.1.9.9.171.1.2.2.1.6.1.4.69.60.152.62.1.4.192.168.85.81.58929)

69.60.152.62

cikePeerRemoteAddr.1.4.69.60.152.62.1.4.192.168.85.81.58929 (1.3.6.1.4.1.9.9.171.1.2.2.1.7.1.4.69.60.152.62.1.4.192.168.85.81.58929)

192.168.85.81

cikeTunActiveTime.58929 (1.3.6.1.4.1.9.9.171.1.2.3.1.16.58929)

3769475

cikeTunHistTermReason.58929 (1.3.6.1.4.1.9.9.171.1.4.2.1.1.2.58929)

peerLost(5)

sysUpTime (1.3.6.1.2.1.1.3.0)

87 days 7 hours 54 minutes 3.41 seconds

experimental.1057.1.0 (1.3.6.1.3.1057.1.0)

192.168.84.11

snmpTrapEnterprise (1.3.6.1.6.3.1.1.4.3.0)

CISCO-IPSEC-FLOW-MONITOR-MIB:cipSecMIBNotificationPrefix

We would like to see those information in the alert message body, here are example:

CISCO-IPSEC-FLOW-MONITOR-MIB:cikeTunnelStop

cikePeerLocalAddr = 192.168.85.81

cikePeerRemoteAddr = 69.60.152.62

Source : aamot-chicago2-CR-1037681 (192.168.84.11)

Here is the current output of the email alert:

Solarwinds support provide this SQL strong in the message body but it output too many information:

${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}

As you can see it has too many useless information and hard for user to read through it.

Thanks

Find more posts tagged with

Accepted answers

All comments

shuth

A few of us have talked about this in the following thread:

https://thwack.solarwinds.com/t5/LA-Discussions/Editing-Alert-Message-using-SQL-Using-SUBSTRING/td-p/588778

My post in the thread talks about a specific example:

https://thwack.solarwinds.com/t5/LA-Discussions/Editing-Alert-Message-using-SQL-Using-SUBSTRING/m-p/591776/highlight/true#M278

You will most likely need to create 3 of these queries in your alert email:

The type of trap message (cikeTunnelStop)
cikePeerLocalAddr
cikePeerRemoteAddr

The IP addresses built into the name/OID may cause some problems because the number of characters until the start of the actual data will change depending on the IP address. Additionally, whether the data you want is in the OID or the trap value may cause another issue. If it's the bold entry below, it will be easier (you'll still have the variable length problem). If it's the red text below, then it will be a lot harder.

CISCO-IPSEC-FLOW-MONITOR-MIB:cikeTunnelStop : cikePeerLocalAddr.1.4.69.60.152.62.1.4.192.168.85.81.58929 = 69.60.152.62, cikePeerRemoteAddr.1.4.69.60.152.62.1.4.192.168.85.81.58929 = 192.168.85.81, cikeTunActiveTime.58929 = 3769475, cikeTunHistTermReason.58929 = peerLost(5), sysUpTime = 87 days 7 hours 54 minutes 3.41 seconds, experimental.1057.1.0 = 192.168.84.11, snmpTrapEnterprise = CISCO-IPSEC-FLOW-MONITOR-MIB:cipSecMIBNotificationPrefix

SolarWinds have mentioned including sub-string extraction in the product roadmap but for now it can only be done with custom SQL/SWQL.

https://thwack.solarwinds.com/t5/LA-Documents/What-We-re-Working-on-for-Log-Analyzer-Updated-Nov-13th-2019/ta-p/514465

ttseng

Dear Shuth,

Sorry to bother you again, I am not sure if I can understand all the variables and substring I have to put in, this is what I have I don't know if make sense, I don't really understand the number part

${N=SWQL;M=SELECT SUBSTRING('${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}',
CHARINDEX('cikePeerLocalAddr','${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}')+62,
( CHARINDEX('cikePeerRemoteAddr','${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}') - CHARINDEX('cikePeerLocalAddr','${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}') - 21 (Not sure what number I have to put in)
)
) AS Message
FROM Orion.Nodes n}

Could you help me out here please? really appreciate it.

shuth

No problem. Let's break the query down. There are a couple of problems due to the IP address length but I offer an alternative at the bottom.

The basic structure of the SUBSTRING function is SUBSTRING(source, starting position, # of characters to return).

${N=SWQL;M=SELECT SUBSTRING('${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}',

This part says to run a SWQL query and we're going to select a part (substring) from a source field. In this case, the source message variable ${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage} which is your trap message

CHARINDEX('cikePeerLocalAddr','${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}')+62,

This part says which character position in the message do you want to start getting your substring. You are saying start where the text cikePeerLocalAddr is found and then add 62 characters to that number. In the part of the message below it would find the text at character position 47 and add 62 characters, telling your query to start at character position 109.

If you didn't add the 62 characters, you would start the text at cikePeerLocalAddr.

In the below example, it would start reading your IP address as 9.60.152.62 as the IP starts at character position 108 so you would need to use 61 characters.

NOTE: This is where I said you'll run into a problem because of the IP addresses in the message after the cikePeerLocalAddr text - depending on the IP, that number will change.

CISCO-IPSEC-FLOW-MONITOR-MIB:cikeTunnelStop : cikePeerLocalAddr.1.4.69.60.152.62.1.4.192.168.85.81.58929 = 69.60.152.62, cikePeerRemoteAddr

( CHARINDEX('cikePeerRemoteAddr','${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}') - CHARINDEX('cikePeerLocalAddr','${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}') - 21 (Not sure what number I have to put in)

This is the tricky part. This part of the query says how many characters do you want to bring back from the message.

It says to start at the character position of the next varbind (cikePeerRemoteAddr) which would be position 122 but seeing we don't want to return 122 characters, we have to reduce that number. So we subtract the starting position of the previous field (cikePeerLocalAddr) and then further subtract the number of characters getting us to our message (the 61 characters).

In my original query, I then subtracted 2 more positions (63 characters) to remove the ", " before the cidePeerRemoteAddr.

Alternatively you can alter the query to start at the , before the varbind, e.g. CHARINDEX(', cikePeerRemoteAddr',) then you can reuse the same number you used in the first part of the query.

) ) AS Message FROM Orion.Nodes n}

This is needed as part of the SWQL query.

If you don't mind a little bit of extra noise in your message, but it will avoid the variable length problem. You could try using:

Trap Type: ${N=SWQL;M=SELECT SUBSTRING('${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}',
CHARINDEX('MIB:','${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}')+4,
( CHARINDEX(' : ','${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}') - CHARINDEX('MIB:','${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}') - 4
)
) AS Message
FROM Orion.Nodes n}

Local Address: ${N=SWQL;M=SELECT SUBSTRING('${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}',
CHARINDEX('cikePeerLocalAddr','${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}')+17,
( CHARINDEX(', cikePeerRemoteAddr','${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}') - CHARINDEX('cikePeerLocalAddr','${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}') - 17
)
) AS Message
FROM Orion.Nodes n}

Remote Address: ${N=SWQL;M=SELECT SUBSTRING('${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}',
CHARINDEX('cikePeerRemoteAddr','${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}')+18,
( CHARINDEX(', cikeTunActiveTime','${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}') - CHARINDEX('cikePeerRemoteAddr','${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}') - 18
)
) AS Message
FROM Orion.Nodes n}

I would expect that to return:

Trap Type: cikeTunnelStop

Local Address: .1.4.69.60.152.62.1.4.192.168.85.81.58929 = 69.60.152.62

Remote Address: 1.4.69.60.152.62.1.4.192.168.85.81.58929 = 192.168.85.81

ttseng

Hi Shuth,

Thanks for your info, but I have one more question, is there a way not showing those information on the output?

Local Address: .1.4.69.60.152.62.1.4.192.168.85.81.58929 = 69.60.152.62

Remote Address: 1.4.69.60.152.62.1.4.192.168.85.81.58929 = 192.168.85.81

Ideal output:

Local Address: = 69.60.152.62

Remote Address: = 192.168.85.81