Custom alert variable with nested variable

Hi,

We are trying to parse SNMP traps content within an alert to extract some usefull info for our alert.

our trap come from Oracle Cloud Control and looks like this:

ORACLE-ENTERPRISE-MANAGER-4-MIB:oraEMNGEvent : oraEMNGEventNotifType.1 = NOTIF_NORMAL, oraEMNGEventMessage.1 = This is a sample Event Message to test the SNMP ntofication delivery to the SNMP Manager. The contents of this messages are all sample data., oraEMNGEventMessageURL.1 = https://SampleEMTargetHost:15430/em/redirect?pageType=sdk-core-event-console-detailEvent&issueID=BFD22554A4E23C37E040E50ADDDE1D71, oraEMNGEventSeverity.1 = Critical, oraEMNGEventSeverityCode.1 = CRITICAL, oraEMNGEventRepeatCount.1 = 0, oraEMNGEventActionMsg.1 = This is a sample event action message for SNMP delivery testing., oraEMNGEventOccurrenceTime.1 = May 14, 2012 11:46:27 AM GMT, oraEMNGEventReportedTime.1 = May 14, 2012 11:46:27 AM GMT, oraEMNGEventCategories.1 = Capacity, Security, oraEMNGEventCategoryCodes.1 = Capacity, Security, oraEMNGEventType.1 = Metric Alert, oraEMNGEventName.1 = Program Resource Utilization:Program's Max CPU Utilization (%), oraEMNGAssocIncidentId.1 = , oraEMNGAssocIncidentOwner.1 = , oraEMNGAssocIncidentAcked.1 = No, oraEMNGAssocIncidentStatus.1 = , oraEMNGAssocIncidentPriority.1 = , oraEMNGAssocIncidentEscLevel.1 = , oraEMNGEventTargetName.1 = SampleEMTargetname, oraEMNGEventTargetNameURL.1 = https://SampleEMTargetHost:15430/em/redirect?pageType=TARGET_HOMEPAGE&targetName=SampleEMTargetHost&targetType=host, oraEMNGEventTargetType.1 = Host, oraEMNGEventHostName.1 = SampleEMTargetHost, oraEMNGEventTargetOwner.1 = sysman, oraEMNGEventTgtLifeCycleStatus.1 = Mission Critical, oraEMNGEventTargetVersion.1 = 10.2.0.1, oraEMNGEventUserDefinedTgtProp.1 = , oraEMNGEventSourceObjName.1 = , oraEMNGEventSourceObjNameURL.1 = , oraEMNGEventSourceObjType.1 = , oraEMNGEventSourceObjSubType.1 = , oraEMNGEventSourceObjOwner.1 = , oraEMNGEventCAJobName.1 = , oraEMNGEventCAJobStatus.1 = , oraEMNGEventCAJobOwner.1 = , oraEMNGEventCAJobStepOutput.1 = , oraEMNGEventCAJobType.1 = , oraEMNGEventRuleSetName.1 = sample_rule_set_name, oraEMNGEventRuleName.1 = sample_rule_set_name,dummy_rule_name, oraEMNGEventRuleOwner.1 = sample_rule_set_owner, oraEMNGEventSequenceId.1 = BFD22554A4E23C37E040E50ADDDE1D71, oraEMNGEventRCADetails.1 = , oraEMNGEventContextAttrs.1 = sample_ctx_1=strVal, sample_ctx_2=67891234567, oraEMNGEventUserComments.1 = , oraEMNGEventUpdates.1 = , oraEMNGEventTypeAttr1.1 = Metric GUID=C00480F615898D2FE040E50ADDDE395C, oraEMNGEventTypeAttr2.1 = Severity GUID=C00480F6158A8D2FE040E50ADDDE395C, oraEMNGEventTypeAttr3.1 = , oraEMNGEventTypeAttr4.1 = , oraEMNGEventTypeAttr5.1 = Metric Group=Program Resource Utilization, oraEMNGEventTypeAttr6.1 = Metric=Program's Max CPU Utilization (%), oraEMNGEventTypeAttr7.1 = Metric Description=Testing metric description, oraEMNGEventTypeAttr8.1 = Metric value=85, oraEMNGEventTypeAttr9.1 = Key Value=The keyValue, oraEMNGEventTypeAttr10.1 = Key Column 1=Program Name, oraEMNGEventTypeAttr11.1 = Key Column 1 Value=%, oraEMNGEventTypeAttr12.1 = Key Column 2=Owner, oraEMNGEventTypeAttr13.1 = Key Column 2 Value=%, oraEMNGEventTypeAttr14.1 = , oraEMNGEventTypeAttr15.1 = , oraEMNGEventTypeAttr16.1 = , oraEMNGEventTypeAttr17.1 = , oraEMNGEventTypeAttr18.1 = , oraEMNGEventTypeAttr19.1 = , oraEMNGEventTypeAttr20.1 = , oraEMNGEventTypeAttr21.1 = , oraEMNGEventTypeAttr22.1 = , oraEMNGEventTypeAttr23.1 = , oraEMNGEventTypeAttr24.1 = Number of keys=2, oraEMNGEventTypeAttr25.1 = , sysUpTime = 0,01 second, experimental.1057.1.0 = 192.168.0.92, snmpTrapEnterprise = ORACLE-ENTERPRISE-MANAGER-4-MIB:oraEM4Traps

Following different Thwack post we have tried the different variable to extract some content, the simplies ssems to be :

${N=SWQL;M=SELECT SUBSTRING('${N=OLM.AlertingMacros;M=OLMAlertMessage.EventMessage}',2,10) AS Message FROM Orion.Nodes n}

This kind of variable give use this output:

${N=SWQL;M=SELECT SUBSTRING('ORACLE-ENTERPRISE-MANAGER-4-MIB:oraEMNGEvent : oraEMNGEventNotifType.1 = NOTIF_NORMAL, oraEMNGEventMessage.1 = This is a sample Event Message to test the SNMP ntofication delivery to the SNMP Manager. The contents of this messages are all sample data., oraEMNGEventMessageURL.1 = https://SampleEMTargetHost:15430/em/redirect?pageType=sdk-core-event-console-detailEvent&issueID=BFD22554A4E23C37E040E50ADDDE1D71, oraEMNGEventSeverity.1 = Critical, oraEMNGEventSeverityCode.1 = CRITICAL, oraEMNGEventRepeatCount.1 = 0, oraEMNGEventActionMsg.1 = This is a sample event action message for SNMP delivery testing., oraEMNGEventOccurrenceTime.1 = May 14, 2012 11:46:27 AM GMT, oraEMNGEventReportedTime.1 = May 14, 2012 11:46:27 AM GMT, oraEMNGEventCategories.1 = Capacity, Security, oraEMNGEventCategoryCodes.1 = Capacity, Security, oraEMNGEventType.1 = Metric Alert, oraEMNGEventName.1 = Program Resource Utilization:Program's Max CPU Utilization (%), oraEMNGAssocIncidentId.1 = , oraEMNGAssocIncidentOwner.1 = , oraEMNGAssocIncidentAcked.1 = No, oraEMNGAssocIncidentStatus.1 = , oraEMNGAssocIncidentPriority.1 = , oraEMNGAssocIncidentEscLevel.1 = , oraEMNGEventTargetName.1 = SampleEMTargetname, oraEMNGEventTargetNameURL.1 = https://SampleEMTargetHost:15430/em/redirect?pageType=TARGET_HOMEPAGE&targetName=SampleEMTargetHost&targetType=host, oraEMNGEventTargetType.1 = Host, oraEMNGEventHostName.1 = SampleEMTargetHost, oraEMNGEventTargetOwner.1 = sysman, oraEMNGEventTgtLifeCycleStatus.1 = Mission Critical, oraEMNGEventTargetVersion.1 = 10.2.0.1, oraEMNGEventUserDefinedTgtProp.1 = , oraEMNGEventSourceObjName.1 = , oraEMNGEventSourceObjNameURL.1 = , oraEMNGEventSourceObjType.1 = , oraEMNGEventSourceObjSubType.1 = , oraEMNGEventSourceObjOwner.1 = , oraEMNGEventCAJobName.1 = , oraEMNGEventCAJobStatus.1 = , oraEMNGEventCAJobOwner.1 = , oraEMNGEventCAJobStepOutput.1 = , oraEMNGEventCAJobType.1 = , oraEMNGEventRuleSetName.1 = sample_rule_set_name, oraEMNGEventRuleName.1 = sample_rule_set_name,dummy_rule_name, oraEMNGEventRuleOwner.1 = sample_rule_set_owner, oraEMNGEventSequenceId.1 = BFD22554A4E23C37E040E50ADDDE1D71, oraEMNGEventRCADetails.1 = , oraEMNGEventContextAttrs.1 = sample_ctx_1=strVal, sample_ctx_2=67891234567, oraEMNGEventUserComments.1 = , oraEMNGEventUpdates.1 = , oraEMNGEventTypeAttr1.1 = Metric GUID=C00480F615898D2FE040E50ADDDE395C, oraEMNGEventTypeAttr2.1 = Severity GUID=C00480F6158A8D2FE040E50ADDDE395C, oraEMNGEventTypeAttr3.1 = , oraEMNGEventTypeAttr4.1 = , oraEMNGEventTypeAttr5.1 = Metric Group=Program Resource Utilization, oraEMNGEventTypeAttr6.1 = Metric=Program's Max CPU Utilization (%), oraEMNGEventTypeAttr7.1 = Metric Description=Testing metric description, oraEMNGEventTypeAttr8.1 = Metric value=85, oraEMNGEventTypeAttr9.1 = Key Value=The keyValue, oraEMNGEventTypeAttr10.1 = Key Column 1=Program Name, oraEMNGEventTypeAttr11.1 = Key Column 1 Value=%, oraEMNGEventTypeAttr12.1 = Key Column 2=Owner, oraEMNGEventTypeAttr13.1 = Key Column 2 Value=%, oraEMNGEventTypeAttr14.1 = , oraEMNGEventTypeAttr15.1 = , oraEMNGEventTypeAttr16.1 = , oraEMNGEventTypeAttr17.1 = , oraEMNGEventTypeAttr18.1 = , oraEMNGEventTypeAttr19.1 = , oraEMNGEventTypeAttr20.1 = , oraEMNGEventTypeAttr21.1 = , oraEMNGEventTypeAttr22.1 = , oraEMNGEventTypeAttr23.1 = , oraEMNGEventTypeAttr24.1 = Number of keys=2, oraEMNGEventTypeAttr25.1 = , sysUpTime = 0,01 second, experimental.1057.1.0 = 192.168.0.92, snmpTrapEnterprise = ORACLE-ENTERPRISE-MANAGER-4-MIB:oraEM4Traps',2,10) AS Message FROM Orion.Nodes n}

The nested variable itself is correctly converted however the main variable remain just as type initially 

${N=SWQL;M=SELECT '....',2,10) AS Message FROM Orion.Nodes n}

I have even tried with some more basic variable:

${N=SWQL;M=SELECT substring('${N=SwisEntity;M=Caption}',3,5)}

 and this give me simply

${N=SWQL;M=SELECT substring('myserverhostname',3,5)} 

So that are there any requirement to be able to use nested variable in alerts ?

I have configured my variable in the body of an email and also in the alert message and none of them is working.

i can however used a simply swql and it works like

${N=SWQL;M=SELECT top 1 caption fron orion.nodes n}

 This give me correctly one caption.

Can anyone advice on this ? we definitively like to extract data from SNMP traps for our teams to have some meaningfull alerts.

Cheers