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

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
  • Ive been using this method successfuly for a while now, but have come across an issue with oracle OEM - where it spits out an apostrophe in one of the varbinds which messes the SQL query. Has anyone had any luck pre-processing out apostrophes from message text so taht you can filter out the substring you want?

    Cheers

    Rob

  • Had the same mess with Oracle CloudControl (in our case the character ':' make the query fail), so we replace this character with a dot '.' in the message.

    Here is an example :

    ${N=SWQL;M=SELECT TOP 1 ( SUBSTRING(REPLACE (Message,':','.'),CHARINDEX ('oraEMNGEventUserDefinedTgtProp.1 = Line of Business=' , REPLACE (Message,':','.'), 1 ) +52,CHARINDEX ( ', Operating System=' , REPLACE (Message,':','.'),1 )-CHARINDEX ('oraEMNGEventUserDefinedTgtProp.1 = Line of Business=' , REPLACE (Message,':','.'), 1 ) -52 ) ) AS newMessage3 FROM Orion.OLM.LogEntry WHERE NodeID=${N=SwisEntity;M=NodeID} order by messagedatetime DESC}

    br