How to send e-mail alert that contains a variable

[rleyba828]

Hi Team,

  Given a set of logs like below:

Mar 2 12:56:34 10.1.2.3 router-01: 2021 Mar 2 12:56:34.628 AEST: %SLA_SENDER-3-IPSLATHRESHOLD: IP SLA: Threshold Cleared for timeout
Mar 2 12:52:30 10.1.2.3 router-01: 2021 Mar 2 12:52:30.562 AEST: %SLA_SENDER-3-IPSLATHRESHOLD: IP SLA: Threshold Occurred for timeout
Mar 2 11:13:59 10.1.2.3 router-01: 2021 Mar 2 11:13:59.912 AEST: %SLA_SENDER-3-IPSLATHRESHOLD: IP SLA: Threshold Cleared for timeout
Mar 2 11:13:55 10.1.2.3 router-01: 2021 Mar 2 11:13:55.912 AEST: %SLA_SENDER-3-IPSLATHRESHOLD: IP SLA: Threshold Occurred for timeout
Mar 2 10:52:29 10.1.2.3 router-01: 2021 Mar 2 10:52:29.848 AEST: %SLA_SENDER-3-IPSLATHRESHOLD: IP SLA: Threshold Cleared for timeout
Mar 2 10:52:25 10.1.2.3 router-01: 2021 Mar 2 10:52:25.850 AEST: %SLA_SENDER-3-IPSLATHRESHOLD: IP SLA: Threshold Occurred for timeout
Mar 2 10:26:04 10.1.2.3 router-01: 2021 Mar 2 10:26:04.843 AEST: %SLA_SENDER-3-IPSLATHRESHOLD: IP SLA: Threshold Cleared for timeout
Mar 2 10:26:00 10.1.2.3 router-01: 2021 Mar 2 10:26:00.838 AEST: %SLA_SENDER-3-IPSLATHRESHOLD: IP SLA: Threshold Occurred for timeout
Mar 2 10:09:19 10.1.2.3 router-01: 2021 Mar 2 10:09:19.918 AEST: %SLA_SENDER-3-IPSLATHRESHOLD: IP SLA: Threshold Cleared for timeout

with our setup for the above as: 

index=syslog  sourcetype=Cisco AND "IP SLA:"

I am trying to send an e-mail alert that will send only the LAST event for "Threshold Cleared" and more importantly, a variable that computes (time delta) from the last "Occurred" to the last "Cleared" event, in this case 244 seconds (12:56:34 - 12:52:30).

I have some knowledge of subsearches but only as part of another inline search and can't get my head on how to assign the result as a "variable" and then subsequently include that variable in an e-mail alert.

Basically the email alert I want to construct is:

"Latest IP SLA threshold has cleared at 12:56:34 PM.   Event duration was 244 seconds"

Any suggestions on the syntax will be much appreciated.

Thanks.

[bowesmana · Accepted solution]

See this example search with your data

| makeresults
| eval _raw="Mar 2 12:56:34 10.1.2.3 router-01: 2021 Mar 2 12:56:34.628 AEST: %SLA_SENDER-3-IPSLATHRESHOLD: IP SLA: Threshold Cleared for timeout
Mar 2 12:52:30 10.1.2.3 router-01: 2021 Mar 2 12:52:30.562 AEST: %SLA_SENDER-3-IPSLATHRESHOLD: IP SLA: Threshold Occurred for timeout
Mar 2 11:13:59 10.1.2.3 router-01: 2021 Mar 2 11:13:59.912 AEST: %SLA_SENDER-3-IPSLATHRESHOLD: IP SLA: Threshold Cleared for timeout
Mar 2 11:13:55 10.1.2.3 router-01: 2021 Mar 2 11:13:55.912 AEST: %SLA_SENDER-3-IPSLATHRESHOLD: IP SLA: Threshold Occurred for timeout
Mar 2 10:52:29 10.1.2.3 router-01: 2021 Mar 2 10:52:29.848 AEST: %SLA_SENDER-3-IPSLATHRESHOLD: IP SLA: Threshold Cleared for timeout
Mar 2 10:52:25 10.1.2.3 router-01: 2021 Mar 2 10:52:25.850 AEST: %SLA_SENDER-3-IPSLATHRESHOLD: IP SLA: Threshold Occurred for timeout
Mar 2 10:26:04 10.1.2.3 router-01: 2021 Mar 2 10:26:04.843 AEST: %SLA_SENDER-3-IPSLATHRESHOLD: IP SLA: Threshold Cleared for timeout
Mar 2 10:26:00 10.1.2.3 router-01: 2021 Mar 2 10:26:00.838 AEST: %SLA_SENDER-3-IPSLATHRESHOLD: IP SLA: Threshold Occurred for timeout
Mar 2 10:09:19 10.1.2.3 router-01: 2021 Mar 2 10:09:19.918 AEST: %SLA_SENDER-3-IPSLATHRESHOLD: IP SLA: Threshold Cleared for timeout"
| multikv noheader=t
| rex field=_raw ".*router-01: (?<date>\d+ \w{3} \d+ \d+:\d+:\d+\.\d+)"
| eval _time=strptime(date, "%Y %b %d %H:%M:%S")
| table _time _raw
| rex field=_raw ".*(?<state>Cleared|Occurred)"
| streamstats range(_time) as duration latest(eval(if(state="Cleared", _time, null))) as latest reset_before="("state==\"Cleared\"")"
| where !isnull(latest) AND state="Occurred"
| head 1
| eval l=strftime(latest, "%l:%M:%S %p")
| eval message="Latest IP SLA threshold has cleared at ".l.".  Event duration was ".round(duration)." seconds"
| table message

I've assumed that it's just a stream of on/off events, but you may need to modify the search as needed. In practice you only need the most recent 3 events as they should contain either 

cleared, occurred, cleared 

or 

occurred,cleared, occurred

so you could do a head 3 at the start.

but basically the streamstats is your tool.

Hope this helps

[rleyba828]

HI bowesmana,

  Many thanks for this....I'll adapt your query string a bit to see if fits some other variations of how the logs arrive, but essentially, it is the structure/combination of SPL commands that you provided that should make it work.   

Thanks again.