How to edit the syntax for a lookup in my search to check sender domains against a watch list?

[packet_hunter]

Scenario: Looking at email logs and want to check the sender domain (sender@domain.tld) against a watch list. The watch list is two columns with two fields: domain and flag. In the domain column are values of domains (domain.tld), in the flag column are all values of 1.

I run the following code, but only see a single "no" or "yes" for a list of domain results. Ideally I would like to see:

domain1.tld no
domain2.tld yes

index=mail sourcetype=xemail [search index=mail sourcetype=xemail subject = "Blah" |stats count by UID| fields UID] 
| stats list(subject) as subj list(sender) as sender list(recipient) as recp list(vendor_action) as status by UID 
| search status= "Message done" 
| rex field=sender "[@\.](?<domain>\w+\.\w+)$" 
| stats list(domain) as sender_domain
| lookup watch_list.csv domain as sender_domain OUTPUT flag  
| eval on_watch_list=if(flag=1,"yes","no") 
| fields - flag

Please provide an example of the correct syntax.

Thank you

[Jeremiah · Accepted solution]

When you do the stats list(domain) as sender_domain, you are creating a single multivalue field. Better to just count by domain, so that each domain is its own event in a single value field. Try this:

 index=mail sourcetype=xemail [search index=mail sourcetype=xemail subject = "Blah" |stats count by UID| fields UID] 
 | stats list(subject) as subj list(sender) as sender list(recipient) as recp list(vendor_action) as status by UID 
 | search status= "Message done" 
 | rex field=sender "[@\.](?<domain>\w+\.\w+)$" 
 | stats count by domain
 | rename domain AS sender_domain
 | lookup watch_list.csv domain as sender_domain OUTPUT flag  
 | eval on_watch_list=if(flag=1,"yes","no") 
 | fields - flag

[packet_hunter]

Thank you Jeremiah, your code works as I intended.

If you don't mind explaining one other topic:
Why is it that my initial | stats list(subject) as subj... etc does not appear in the results?
I assume that | rex field = sender (etc.).... supersedes the previous code...

(if you don't mind) please explain how to just add the rex, lookup, eval results (i.e. domain, yes/no) as additional columns in the results, so subj, sender, recp, status, sender_domain, yes/no displays together.

Your explanation is much appreciated!!!!
Please convert your comment to answer so that I can accept.

[Richfez]

packet_hunter,

I converted Jeremiah's comment to an answer so you can Accept it.

The reason those don't show up is because the later stats command summarizes them out. If you'd like all of those, you might be able to do something like ...

index=mail sourcetype=xemail [search index=mail sourcetype=xemail subject = "Blah" |stats count by UID| fields UID] 
| rex field=sender "[@\.](?<domain>\w+\.\w+)$" 
| stats list(subject) as subj list(sender) as sender list(recipient) as recp list(vendor_action) as status by UID, domain 
| search status= "Message done" 
| rename domain AS sender_domain
| lookup watch_list.csv domain as sender_domain OUTPUT flag  
| eval on_watch_list=if(flag=1,"yes","no") 
| fields - flag

No guarantees on that. 🙂 Do you see what I did, though? I moved the rex for domain to before the stats (so "domain" is available in the stats), then added domain to the by clause. Again, that will probably need some tweaking to get it to work, but it might be enough to get you started on it.

[packet_hunter]

Thank you Rich!!! I will give it a try and let you know.

[packet_hunter]

index=mail sourcetype=xemail 
    [search index=mail sourcetype=xemail subject = "Blah" |stats count by UID| fields UID] 
        | rex field=sender "[@\.](?<domain>\w+\.\w+)$"   
            | stats list(subject) as subj list(sender) as sender list(recipient) as recp list(vendor_action) as status by UID 
                | search status= "Message done" 
                    | lookup watch_list.csv domain as sender_domain OUTPUT flag    
                        | eval on_watch_list=if(flag=1,"yes","no")   
                            | fields - flag
                                |eventstats count(eval(on_white_list = "yes")) as total_on_white_list

This seems to work with some tweaks but I am not sure if it the best way to do it. Any tips or comments appreciated.