cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 8

Editing Alert Message using SQL: Using SUBSTRING

Jump to solution

I'm using Log Analyzer to capture SNMP traps and turn them into alerts.  Which is working.  When the trap comes in an alert is triggered.  The issue I'm having is in creating a meaningful alert message.  The trap message has a lot of unnecessary information in it.  I'm trying to use SQL SUBSTRING and CHARINDEX to manipulate the string.  Instead of executing the SQL functions its printing it out as text in the message.

 

So specific command is:

${SQL:SELECT SUBSTRING('${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}', CHARINDEX('enterprises.99998.25.3.6 = ', '${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}')+27, CHARINDEX(', enterprises.99998.25.3.7 =', '${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}') - CHARINDEX('enterprises.99998.25.3.6 = ', '${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}'))

 

Which is giving me output (I put the exact output but it’s very long):

${SQL:SELECT SUBSTRING('<SNMP MESSAGE STRING>', CHARINDEX('enterprises.99998.25.3.6 = ','<SNMP MESSAGE STRING>')+27, CHARINDEX('<SNMP MESSAGE STRING>') - CHARINDEX('<SNMP MESSAGE STRING>'))

 

The SNMP MESSAGE STRING is a variable length.

Any help would be greatly appreciated.  Thanks.

Tags (3)
0 Kudos
1 Solution
Level 8

I believe that "${N=SWQL;M=SELECT" and "AS Message FROM Orion.Nodes n}" in your statement where the parts missing from my SQL statement that caused it to print out the statement instead of executing it.  I'm now able to parse any output from any SNMP shout I want to alert on.  Thank you very much for the help.

 

- Sam G

View solution in original post

0 Kudos
13 Replies
MVP
MVP

I've been working on this lately. I was contemplating making a new thread with my findings but saw this thread. Here is how I am able to bring variable length varbind fields into the alert.

Here is an example of a trap message using the default ${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage} variable. However you only want the bolded section for the alertTrapMessage.

BMC-CONTROLEM-MIB:alertTrap.10 : alertTrapUpdateType = I, alertTrapAlertId = 224828, alertTrapControlM = CTMPRD, 
alertTrapMemName = , alertTrapOrderId = 1ax29, alertTrapSeverity = R, alertTrapStatus = Not_Noticed, alertTrapTime = 20200421170003,
alertTrapUser = , alertTrapUpdateTime = , alertTrapMessage = EMAIL TO CTMSUPPORT FAILED, alertTrapOwner = EMDB, alertTrapGroup = HAcheck,
alertTrapApplication = Control-M, alertTrapJobName = CC_CTMHA_CHECK_HA, alertTrapNodeId = fakenodename, alertTrapType = R, alertTrapClosedFromEM = ,
alertTrapTicketNumber = , alertTrapRunCounter = 00000000003, sysUpTime = 0.00 second, experimental.1057.1.0 = xxx.xxx.xxx.xxx, snmpTrapEnterprise = BMC-CONTROLEM-MIB:controlmAlert

 

You can use the following query to get a specific varbind entry.

1. Replace the firsttrapvarbindname -- alertTrapMessage

2. Replace the secondtrapvarbindname  -- alertTrapOwner

3. Update the first number with the number of characters from the start of the varbind name to the start of the varbind message (in this case 19 characters - "alertTrapMessage = ")

4. Update the second number - this is the first number plus the number of characters from the 2nd varbind name to the end of the string (in this case, 2 characters for the ", " (comma space))

 
${N=SWQL;M=SELECT SUBSTRING('${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}',
CHARINDEX('firsttrapvarbindname','${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}')+19,
( CHARINDEX('secondtrapvarbindname','${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}') - CHARINDEX('firsttrapvarbindname','${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}') - 21
)
) AS Message
FROM Orion.Nodes n}

 

If you combine them (and a coincidence the numbers were the same):

Job Name: ${N=SWQL;M=SELECT SUBSTRING('${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}',
CHARINDEX('alertTrapJobName','${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}')+19,
( CHARINDEX('alertTrapNodeId','${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}') - CHARINDEX('alertTrapJobName','${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}') - 21
)
) AS Message
FROM Orion.Nodes n}

Error Message: ${N=SWQL;M=SELECT SUBSTRING('${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}',
CHARINDEX('alertTrapMessage','${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}')+19,
( CHARINDEX('alertTrapOwner','${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}') - CHARINDEX('alertTrapMessage','${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}') - 21
)
) AS Message
FROM Orion.Nodes n}

 

That should result in the output:

Job Name: CC_CTMHA_CHECK_HA

Error Message: EMAIL TO CTMSUPPORT FAILED

Hi guys,

Sorry, but could i do that query for the "alert message displayed when this alert is triggered"?

Thank you,

Regards,

 

Javier.

 

 

0 Kudos
Level 8

I'm not sure I understand what you mean.  Can you supply an example or describe the problem a little more?

Hi!

Yes, I need to trigger an alert when a trap is received, and the the Alert Message field shows only some varbinds of the original message.
By now, I can only capture the entire message with '${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}', but it is too long.

Anotación 2020-05-08 155302.png

Could you give me an idea how to do it?

Regards,

Javier.

0 Kudos
MVP
MVP

Yes, you can include the query in the same field in your screenshot (or in an email, etc) using the code examples in mine or @sguido 's post (his post walks through each step in more detail so I won't rehash it).

shuth_0-1589276421546.png

For example, if you want the alarmNeMap field from your trap screenshot you could use:

 

${N=SWQL;M=SELECT SUBSTRING('${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}',
CHARINDEX('alarmNeMap =','${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}')+13,
( CHARINDEX(', alarmResource =','${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}') - CHARINDEX('alarmNeMap =','${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}') - 13
)
) AS Message
FROM Orion.Nodes n}

The above should return 24-MZA-SGO from your trap message.

It reads: retrieve a part of the full message starting at the alarmNeMap varbind and the length is the difference between the character location of the alarmNeMap and alarmResource varbinds.

Thank you very much, both you and Sguido have helped me a lot.
I was able to put those variables in the message.
I'm an expert in networking, not SQL, and sometimes I have a little trouble interpreting the language.

Now I have a question, following this example. I receive a trap that is generated by the linkdown of a port on a board. I receive that trap from a single object (a server that collects information from severals boards).
The problem I have is that if I receive the trap on board 1 port 1, the alert is generated; then I receive a similar trap from the board 2 port 2, the alert doesn't trigger (because one already exists but on other board).

How could I give a solution to the issue? I need that with the arrival of different traps (from same server), more than one alert will be triggered.

I hope you can help me,
Thank you very much again.

Best Regards,

Javier Rodriguez

0 Kudos
MVP
MVP

This is a problem with Log Analyzer itself. It sees that you already have an alert for that filter against the node and won't trigger another alert. Similarly, if you receive a set of messages in a short time frame, you'll only get the alert for the first one.

There are a couple of feature requests for it but I think it's one of the biggest differences between the legacy Trap/Syslog tools and LA/LV.

https://thwack.solarwinds.com/t5/LA-Feature-Requests/Make-Log-Analyzer-so-it-is-not-single-threaded/...

https://thwack.solarwinds.com/t5/LA-Feature-Requests/Log-Analyzer-Add-quot-Discriminator-quot-to-all...

 

Simply, if I get 10 messages that match my filter, I want 10 separate alerts. LA currently doesn't do that.

0 Kudos
Level 8

The command that works for me to parse out specific information out of the very long list of varbinds is:

${N=SWQL;M=SELECT SUBSTRING('${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}',
CHARINDEX(firstMarkerTextString, '${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}')+firstMarkerTextStringLength,
(CHARINDEX(secondMarkerTextString, '${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}') - CHARINDEX(firstMarkerTextString, '${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}')- firstMarkerTextStringLength)
) AS Message
FROM Orion.Nodes n}

 

For a string like:

"enterprises.99998.25.3.6 = Mainframe ABEND for job Program1234 enterprises.99998.25.3.7 ="

“enterprises.99998.25.3.6 =” is the first marker…its always at the head of my data.

“Mainframe ABEND for job DOSOMETHING” is the data I want…its always between my two markers

“enterprises.99998.25.3.7 =” is the second marker…it always fallows my data.

 

And the command would look like:

${N=SWQL;M=SELECT SUBSTRING('${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}',
CHARINDEX('enterprises.99998.25.3.6 = ', '${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}')+27,
(CHARINDEX(', enterprises.99998.25.3.7 =', '${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}') -
CHARINDEX('enterprises.99998.25.3.6 = ', '${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}')-27)
) AS Message
FROM Orion.Nodes n}

 

So the ${N=SWQL;M=SELECT at the beginning and the AS Message FROM Orion.Nodes n} at the end where the parts I had messed up for a long time and are very important.  They allow you to use the SQL functions SUBSTRING and CHARINDEX to pull apart the message string to get what you want.

SUBSTRING – Returns a substring of characters from a string and the function call is:

SUBSTRING(sourceString, startPosition, lengthOfReturnString)

CHARINDEX - searches for a substring in a string, and returns the position of the first char of that substring.  Function call is:

CHARINDEX(substring, parentString)

So CHARINDEX('OM', 'Customer') would return 5.

 

So, because the varbind message is of unknown length and my data is at an unknown point in that string I use CHARINDEX to find the startPosition and the lengthOfReturnString for SUBSTRING to use to pull out what I need.  It takes a little work to get exactly the part you want but so far I’ve used it for 3 different SNMP traps.

 

Please let me know if this helps.

 

Sam G

Thank you very much, both you and Shuth have helped me a lot.
I was able to put those variables in the message.
I'm an expert in networking, not SQL, and sometimes I have a little trouble interpreting the language.

Now I have a question, following this example. I receive a trap that is generated by the linkdown of a port on a board. I receive that trap from a single object (a server that collects information from severals boards).
The problem I have is that if I receive the trap on board 1 port 1, the alert is generated; then I receive a similar trap from the board 2 port 2, the alert doesn't trigger (because one already exists but on other board).

How could I give a solution to the issue? I need that with the arrival of different traps (from same server), more than one alert will be triggered.

I hope you can help me,
Thank you very much again.

Best Regards,

Javier Rodriguez

0 Kudos
Level 8

I still have a similar problem for my mainframe alerts.  If more then one mainframe job is late or fails it won't show a separate alert, it just updates the alert triggered time.  I haven't tried to fight this battle yet but if I find an answer to that problem I'll post it here.

0 Kudos
Level 8

I believe that "${N=SWQL;M=SELECT" and "AS Message FROM Orion.Nodes n}" in your statement where the parts missing from my SQL statement that caused it to print out the statement instead of executing it.  I'm now able to parse any output from any SNMP shout I want to alert on.  Thank you very much for the help.

 

- Sam G

View solution in original post

0 Kudos
Level 8

I'm sorry...I didn't post the specific code.

Not Working:

${SQL:SELECT SUBSTRING('${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}', CHARINDEX('enterprises.99998.25.3.6 = ', '${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}')+27, CHARINDEX(', enterprises.99998.25.3.7 =', '${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}') - CHARINDEX('enterprises.99998.25.3.6 = ', '${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}'))}

 

Working:

${N=SWQL;M=SELECT SUBSTRING('${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}',
CHARINDEX('enterprises.99998.25.3.6 = ', '${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}')+27,
(CHARINDEX(', enterprises.99998.25.3.7 =', '${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}') -
CHARINDEX('enterprises.99998.25.3.6 = ', '${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}')-27)
) AS Message
FROM Orion.Nodes n}

Product Manager
Product Manager

It is a long shot but have you tried changing the output type of the email message to "Plain Text" when defining the action in the alert? I believe the HTML option contains some procedures to sanitize that might be inadvertently escaping your SQL commands and rendering them instead of executing them.

jvb_0-1587503268282.png

 

0 Kudos