Lookup table search criteria each row

[praveenvvn]

hello, looking for help on a search query using lookup table

col1,col2,col3

aaa,100,a@a.com

bbb,200,b@b.com

i need to use this lookup table and use col1 and col2 as search criteria to a base query. If it yields any result I’ll need to use the email from col3 and send an email notification. 

this i need to do for every row from the lookup table.  And this whole process needs to be automated. 

am trying to find if such requirements were answered in this community but to no luck so far. 

can you pls guide me here. Any pointers is appreciated. Thanks. 

[bowesmana]

base_search [ | inputlookup yourlookup.csv | table col1, col2 ]
| lookup yourloolup.csv col1 col2 
... you now have col3

Hope this helps

[praveenvvn]

Thanks bowesmana for the reference script.

I made it to work like below:

basesearch | stats count as resultset by col1 col2 | where resultset > 0 | table col1, col2, col3 | lookup mylookupfile.csv col1 col2 OUTPUTNEW col3 | where isnotnull(col3) | rename col2 as newcol2

 To send email notification for each record from the resultset, I saved as an alert and chose For each result in trigger, the trigger action is to send email. To had $result.col3$ and subject/message used $result.col1$ and $result.col2$.

Apparently I need to use a different email template for the value from col2. I guess I'll need to look at the Run a script option from the list of trigger actions.

[bowesmana]

@praveenvvn 

One comment on your SPL below

basesearch 
| stats count as resultset by col1 col2 
| where resultset > 0 
| table col1, col2, col3 

That 'col3' in the table command is redundant, as you will not have any field called col3 after you do the stats by col1 and col2. col3 will only come when you do the lookup.

[praveenvvn]

Thanks @bowesmana I corrected the script in splunk to remove the redundant col from the table. Appreciate your help.