How to create an Alert which iterate values from lookup file?

[LIS]

I have lookup file which is updated periodicaly and has three columns:

Source, Dest, Contact

a,                   k,             111mail.com 

b,                   l,              112mail.com 

c,                   m,           113mail.com 

I want to create one alert that will iterate every row,  use Source, Dest as filters and send email to particular email address.

For example:
index=test sourcetype=main Source=a Dest=k   | table *  -> send to 111mail.com 
index=test sourcetype=main Source=b Dest=l    | table * -> send to 112mail.com 
index=test sourcetype=main Source=c Dest=m | table * -> send to 113mail.com 

Thanks, Lis

[yuanliu]

Yes, this is explained in Use a search command to send an email notification and Example - Send email to different recipients based on search results.  The command is sendemail.

index=test sourcetype=main
``` whichever other filters ```
| lookup source dest output contact
| sendemail to=contact server=<server info> subject="Here is an email notification for " . contact message="This is an example message" sendresults=true inline=true format=raw sendpdf=true

<server info> is something you will need to fill out based on your deployment.

[LIS]

Hi @yuanliu  thank you for your reply.

I have seen this general approach how to send particular one row to certain email, but in my case it is something more complex).

I need to use Source and Dest from lookup as filer in every search and send a table with results(might be many rows) to certain contact. 

Lookup_x:

source	dest	contact
a	x	1@email.com
b	y	2@email.com
c	z	3@email.com

index=test sourcetype=main
| inputlookup Lookup_x.csv 
| where source = a AND dest = x

| table *

source	dest	Another field	Another field	Another field
a	x
a	x
a	x

Send the table above to 1@email.com

 

index=test sourcetype=main
| inputlookup Lookup_x.csv 
| where source = b AND dest = y

| table *

source	dest	Another field	Another field	Another field
b	y
b	y
b	y

Send the table above to 2@email.com

[yuanliu]

So, the solution is exactly what I described; just perform lookup after stats. (And correct a syntax error.)

index=test sourcetype=main (filters)
| stats count by dest source
| where count > 100
| lookup Lookup_x.csv source dest ``` contact is output ```
| sendemail to=contact server=<server info> subject="Here is an email notification for " . contact message="This is an example message" sendresults=true inline=true format=raw sendpdf=true

[LIS]

Hi @yuanliu , 

I dont need send one row of statistical data to particular email, but group of many records/rows to particular email as discribed below.

[yuanliu]

Because of SPL commands are generally row-based, the multi-row table format you are asking is not really possible.  The best you can do is to compact all rows that come with the same contact into a CSV, like the following

index=test sourcetype=main (filters)
| stats count by dest source
| where count > 100
| lookup Lookup_x.csv source dest ``` contact is output ```
| eval row = dest . "," . source . "," . count
| stats values(row) as row_csv by contact
| eval row_csv = mvappend("dest,source,count", row_csv) ``` add a header row ```
| sendemail to=contact server=<server info> subject="Here is an email notification for " . contact message="This is an example message" sendresults=true inline=true format=raw sendpdf=true

[yuanliu]

First of all, you need to clarify how the lookup table is related to source data.  Does the source field for index=test sourcetype=main match a, b, or c in the lookup table?  Or is it some other field with a different name?  Is there a field named dest in index=test sourcetype=main that matches x, y, or z in the lookup table?

Secondly, inputlookup command cannot be applied in the fashion you listed.  The command most appropriate is perhaps lookup.  Regardless of which command, the above questions must be answered first.

[LIS]

My apologies for not clear explination. 
Yes, dest and source values will be fetched and stored to lookup in prior query from the same index and sourcetype.
My first query will fetches the list of dest and source with particular thresholds, look below:

index=test sourcetype=main (filters)
| stats count by dest source
| where count > 100

source	dest	Count_of_records
a	x	345
b	y	247
c	z	144

but I need to send not statistical data, but certain records with more details.

So, i will run second query with filter SOURCE and DEST from my lookup, for the same time period without grouping to get all records with all available fields.

This is not fit my requirements, because I will send all (345+247+144):

index=test sourcetype=main  [(| inputlookup Lookup_x.csv)]
| table *

While I need send 345 to first email, and 247 to second and so on...