Check if domain is found in local lookup file containing over 10 000 entries

[licroBI_0x1]

Hi all,

I been working on new rule and I just can't get it work fully. I know that there are many similar questions/answers on the forum related to this but none of them work for me.

The events contain field "TargetUserOrGroupName" containing an email address e.g. 

TargetUserOrGroupName = testmail@gmail.com

I use split and mvindex to get only email domain out of TargetUserOrGroupName:

| eval email_domain = mvindex(split(TargetUserOrGroupName, "@"),1)

 
Then I want to check if "email_domain" is in lookup "free_email_domains.csv"

I was able to get this easily working (partial) with sub search and inputlookup

| search email_domain=* [|inputlookup free_email_domains.csv.csv | fields email_domain]

But there is issue with getting all data as sub-search returns only 10 000 entries resulting in free email domains not being in first 10k rows are not matched.

The local csv file contains only column email_domains (i did added "is_free_domain" column with value "Yes" in lookup while testing but it can be removed if not needed)

Any help is welcome as I cant get lookup command to work (maybe due to additional extracting of field value)

[richgalloway · Accepted solution]

To determine if a given field value is in a lookup file, use the lookup command.

| eval email_domain = mvindex(split(TargetUserOrGroupName, "@"),1)
| lookup free_email_domains.csv.csv email_domain OUTPUT is_free_domain
``` If email_domain is not in the lookup file then is_free_domain will be null ```
| where isnotnull(is_free_domain)

[licroBI_0x1]

Hi richgalloway,

Thank you for reply, I did try as you suggested with lookup command and it didn't work but....

Because of you response I went and tried it again, this time utilizing lower() option and finding it work 🙂 

Thank you for help 💪