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
  • Hello gang, I am having the same needs, but for the life of me I'm having trouble pulling out the relevant alert data.  Here is the example of the trap I receive and the output I am going after:

    trap / varbind:

    commonMIBAlarmData (1.3.6.1.4.1.562.3.10.10.2.10.0)
    CUST 0 911 CALL ALERT TIME: 05:02:25 DEC 17, 2020 NAME: EMERGENCY RECPT ORIG DN: 1234567 ;; DES: 1093ED SET: DIGITAL SET TER RTMB: 29-137 ACOD: 1010029 CALLED#: 911 CALLING#: 1231231234 OSN000 RECORD END

    desired formatted alert:

    Alert: CUST 0 911 CALL ALERT

    Time: 05:02:25 DEC 17, 2020

    Name: EMERGENCY RECPT

    Originating Phone Number: 1234567

    Is someone able to help me fix my SWQL statement in the email - here is what I have so far in the alert email/message body:

    Alert: ${N=SWQL;M=SELECT SUBSTRING('${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}',
    CHARINDEX('commonMIBAlarmData','${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}')+19,
    ( CHARINDEX('TIME:','${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}') - CHARINDEX('commonMIBAlarmData','${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}') - 5
    )
    ) AS Message
    FROM Orion.Nodes n}

    Time: ${N=SWQL;M=SELECT SUBSTRING('${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}',
    CHARINDEX('TIME:','${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}')+5,
    ( CHARINDEX('NAME:','${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}') - CHARINDEX('TIME:','${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}') - 5
    )
    ) AS Message
    FROM Orion.Nodes n}

    Name: ${N=SWQL;M=SELECT SUBSTRING('${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}',
    CHARINDEX('NAME:','${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}')+5,
    ( CHARINDEX('ORIG DN:','${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}') - CHARINDEX('NAME:','${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}') - 8
    )
    ) AS Message
    FROM Orion.Nodes n}

    Originating Phone Number: ${N=SWQL;M=SELECT SUBSTRING('${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}',
    CHARINDEX('ORIG DN:','${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}')+8,
    ( CHARINDEX('DES:','${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}') - CHARINDEX('ORIG DN:','${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}') - 4
    )
    ) AS Message
    FROM Orion.Nodes n}

  • I find I have to play around a little bit to get the character counts right but I think this will get you what you need.  It looked to me like you didn't account for "(1.3.6.1.4.1.562.3.10.10.2.10.0)" in your counting.  I only worked out the first one but you should be able to use the same formula for the rest.  Please let me know if this works for you.

    The formula I used:

    ${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}

    To get the first part of your message I think you need:

    Alert: ${N=SWQL;M=SELECT SUBSTRING('${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}',
    CHARINDEX(commonMIBAlarmData (1.3.6.1.4.1.562.3.10.10.2.10.0), '${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}')+51,
    (CHARINDEX(TIME:, '${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}') - CHARINDEX(commonMIBAlarmData (1.3.6.1.4.1.562.3.10.10.2.10.0), '${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}')- 51)
    ) AS Message
    FROM Orion.Nodes n}

Reply
  • I find I have to play around a little bit to get the character counts right but I think this will get you what you need.  It looked to me like you didn't account for "(1.3.6.1.4.1.562.3.10.10.2.10.0)" in your counting.  I only worked out the first one but you should be able to use the same formula for the rest.  Please let me know if this works for you.

    The formula I used:

    ${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}

    To get the first part of your message I think you need:

    Alert: ${N=SWQL;M=SELECT SUBSTRING('${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}',
    CHARINDEX(commonMIBAlarmData (1.3.6.1.4.1.562.3.10.10.2.10.0), '${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}')+51,
    (CHARINDEX(TIME:, '${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}') - CHARINDEX(commonMIBAlarmData (1.3.6.1.4.1.562.3.10.10.2.10.0), '${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}')- 51)
    ) AS Message
    FROM Orion.Nodes n}

Children
No Data