Editing Alert Message using SQL: Using SUBSTRING

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.

Parents
  • 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

  • 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

Reply
  • 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

Children
  • 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}