Editing Alert Message using SQL: Using SUBSTRING

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}

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

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