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

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

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.

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

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

Yes you can by changing the numbers in the query however those numbers are based on the IP addresses: 69.60.152.62 and 192.168.85.81. 

If you use a value of 61 for the Local Address, you will get 69.60.152.62

BUT, if you have a trap come in with IP addresses: 69.60.1.24 and 192.168.8.81, then will you have a trap similar to:

CISCO-IPSEC-FLOW-MONITOR-MIB:cikeTunnelStop : cikePeerLocalAddr.1.4.69.60.1.24.1.4.192.168.8.81.58929 = 69.60.1.24, cikePeerRemoteAddr.

Your Local Address query will return: 60.1.24  instead of 69.60.1.24 because your query is hardcoded to jump ahead 61 characters.

If all your tunnels have the same number of characters in the IP addresses, then you will be fine. If your tunnels have fewer/more characters, you will get extra characters or cut off the IP address.

You could probably do some fancy SWQL with nested queries but I'm not going to delve into that.

Hi Shuth,

Really appreciate your help, that make sense, thanks again

No worries. It's unfortunate because of the way the specific trap is structured. If it was like below you wouldn't have the problem.

CISCO-IPSEC-FLOW-MONITOR-MIB:cikeTunnelStop :cikePeerLocalAddr = 69.60.152.62, cikePeerRemoteAddr = 192.168.85.81, cikeTunActiveTime = 3769475, etc