How to trigger email alert on multiple events at different intervals?

[bitnoise]

Hello,

I have seen the question pass multiple times already, so I have searched it already, but  I was unable to find a query for my specific situation.

So, my query ends with: |stats count by sender| where isnull(count) OR count < 100

I had my alert set up that if it occurs, that I get a mail.  The goal is here that this above event must happen twice in a timeframe of 5 minutes before he should send the mail. Can anyone please assist me with this?

Thanks

Danny

[rnowitzki]

Hi @bitnoise ,

Your requirement is not 100% clear to me. Do you want to trigger the alert, when you  search a timeframe of 5 minutes and it finds (at least) 2 "sender" with a count of 0 - 99,  or do you want to run the search every 5 minutes and trigger the alert when it finds something 2 times in a row?

BR
Ralph

[bitnoise]

Hi Ralph,

sorry, I'll try to be more clear.
Currently I trigger a mail when 'sender count' < 100 in 5 minutes.  Now, this "event" should happen twice in a row. (So 10:00 and 10:05 and then he should send the mail).
So, the option 2 in your question:  every 5 mins, 2 times in a row = alert mail.

Thanks

[rnowitzki]

Hi @bitnoise ,

How about a timechart with span=5m with a search timeframe of from -10m to now (or maybe -11m@m to -1m@m to allow some delay).

If you get 2 events, you know that it happened 2 times in a row.

BR
Ralph
--
Karma and/or Solution tagging appreciated.

[bitnoise]

Hi Ralph, I adjusted a test trigger yesterday and let it run all night.  Some strange behavior still, but I'll try to finetune it and come back with the result.

Currently I did this:
SEARCH: xxx | timechart span=11m count by sender| where isnull(count) OR count < 400
CRON: */5 * * * *
TIME RANGE: Last 301 seconds
TRIGGER: n° of results is greater than 1; once

I have the impression the results are not really consistent due to the 11m.

Mail1:

Thu Aug 20 05:32:00 2020	151
Thu Aug 20 05:43:00 2020	128

Mail 2:

Thu Aug 20 05:43:00 2020	214
Thu Aug 20 05:54:00 2020	54

[rnowitzki · Accepted solution]

Hi @bitnoise ,

Change the time range to earliest -11m@m latest -1m@m  (under advanced in the time range picker), this way you look at a 10m timeframe. You could just make it -10m@m and @m, it depends if you see some delay until all the data is ingested and searchable. Just gives you some buffer.

Accordingly change the span to 10m

| timechart span=10m count by sender| where isnull(count) OR count < 400

BR
Ralph
--
Karma and/or Solution tagging appreciated.
 

[bitnoise]

Woohoo! (ignore the high numbers, had to make sure the event happens 😉 )

Thu Aug 20 13:00:00 2020 417
Thu Aug 20 13:05:00 2020 618

Thu Aug 20 13:05:00 2020 618
Thu Aug 20 13:10:00 2020 525

Final config (for others):

SEARCH | timechart span=300s count by sender| where isnull(count) OR count < 1000
SEARCH Advanced: -10m@m > @m
Alert Timerange: -10m@m > @m
Cron: */5 * * * *

The only thing I don't know is how to retrieve the 'count' from both results.
Can I do something like TOTAL MAILS in 5 min: [$result.count$][0] and TOTAL MAILS in 5 min: [$result.count$][1] to retrieve both counts?

Currently I do this: TOTAL MAILS in 5 min: [$result.count$]

Thanks

[rnowitzki]

Hi @bitnoise ,

Let's say your result is

time                                               count
Thu Aug 20 13:05:00 2020 618
Thu Aug 20 13:10:00 2020 525

Than you can just add...

| table count
| transpose  

...to make both of the counts available as a token. Does even work without the | table count, but you will have some (2) internal fields in the table as well...but actually they shouldn't care.

The 618 will be in the field "row 1"  the 525 in "row 2".

For some cosmetic you could add e.g.:
| rename "row 1" as count1, "row 2" as count2

edit: Not only for cosmetic reasons you should add the | rename.  I am not sure if the token reference works with a field that has a space in the name...

Glad I could help
BR
Ralph

[bitnoise]

Strange things happening when I do this with the 'where'.

If I remove the 'where', it shows this: (ps, I just did |table email@domain.com)

[rnowitzki]

Hi @bitnoise,

I you take a closer look, you will notice, that the fields are called "row 1" and "row 2", and you base your where statement on "row1" and "row2" (without blanks).

Therefore my comment to rename the fields. 

So first do this:

| rename "row 1" as count1, "row 2" as count2

And than base the where command on count1 and count2 

BR
Ralph
--
Karma and/or Solution tagging appreciated.

[bitnoise]

Don't I feel stupid now... 🙂
this one works as should now, I just had to adapt my alert trigger to a custom trigger where I also do:

where count1 < xxxx AND count2 < xxxx

Thanks a lot for the great assistance!

Danny