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.

Editing Alert Message using SQL: Using SUBSTRING

sguido over 4 years ago

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.

Top Replies

Parents

0 shuth over 4 years ago

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

0 sguido over 4 years ago in reply to shuth

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

Cancel

Reply

0 sguido over 4 years ago in reply to shuth

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

Cancel

Children

0 sguido over 4 years ago in reply to sguido

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}