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):


The SNMP MESSAGE STRING is a variable length.

Any help would be greatly appreciated.  Thanks.

  • 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

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

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

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

  • Hello,

    Have you by any chance accomplished this?
    I'm struggling with it for some time but still haven't found a way to do it.

    Kind regards,


  • Sorry, I haven't figured out how to fix it yet.

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


    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('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('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('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 "(" 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 (, '${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}')+51,
    (CHARINDEX(TIME:, '${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}') - CHARINDEX(commonMIBAlarmData (, '${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}')- 51)
    ) AS Message
    FROM Orion.Nodes n}

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



  • There is a way how to do it using Advanced Configuration settings + "No reset condition – Trigger this alert each time the trigger condition is met", but the alert flooding protection is there for a reason. Imagine receiving 2k syslogs per second and sending all of them to alerting - that would probably kill the whole alerting.

    It is similar as email action flooding protection - it is also not possible to send an email for each triggered alert, there is a cooldown as well.

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