Using a lookup to send multiple different emails in an alert

[djemodjenai]

I am using a lookup with a list of hosts, thresholds an email addresses to dynamically send email alerts when a threshold is hit.

It works well when there is a simple mapping:
host1 email1
host2 email1

But it's not working when it's:

host1 email1
host1 email2

The lookup looks like this:

My search is:

(mysearch calculating a rate)
| stats min(rate) as min_rate by host
| lookup mylookup.csv host OUTPUT threshold mail
| where min_rate > threshold | fields host min_rate mail threshold

Then, it sends an email using $result.mail$ within the savedsearches parameters (alert).

The problem is that it groups the results with host1 -> list of emails, hence it fails to separate the different email adresses as $result.mail$.

The result looks like this:
host1 email1.com
-------- email2.com

Instead of this:
host1 email1.com
host1 email2.com

It's probably because of the "by host" in my search, is there a way to make the results "for each"?

Can anyone help me?
I tried playing with the lookup parameters, but I'm stuck... Thanks in advance.

[acfecondo75 · Accepted solution]

Hi @djemodjenai

Try piping the current search that you have into this:

| stats values(min_use_rate) as min_use_rate values(threshold) as threshold by host, mail

I think this should break down your results so you have one row per host and mail combination.

[acfecondo75]

If that doesn't work, you can do
| mvexpand mail
this should create an individual row entry for each value in the mail field.

[acfecondo75]

Yeah so you do it at the end after you've run the lookup to add those fields...

[djemodjenai]

You're right, it works when I pipe after the lookup. I changed your suggestion a little bit to make it work:

[mysearch] |mylookup.csv host OUTPUT threshold mail
| stats min(use_rate) as min_use_rate values(threshold) as threshold by host, mail
| where min_use_rate > threshold | fields host min_use_rate mail threshold

It separates the result as wanted. Now I have to test it IRL.

This is brilliant, thank you @acfecondo75 for your time!

[djemodjenai]

I see what you are trying to do, but there are no threshold nor mail in the initial query. They are brought up with the lookup, so it doesn't work.