How to use lookup and multivalue field together

[vikashumble]

Hello All,

I have a multivalue field which contains domain names (for this case, say it is in field named emailDomains and it contains 5 values). I have a lookup named whitelistdomains which contains 2000+ domains names. Now, what I want is to look for these multivalue domains names field and check if that domain name is present in my lookup. Is that possible. Example and expected output is below. I did tried doing this using mvexpand but sometimes I end up with memory issues on splunk cloud and hence want to avoid this. I tried using map, mvmap to see somehow I can pass one value at a time in inputlookup command and get the output. But so far, I am not able to figure it out properly. I did achived this via a very dirty method of using appendpipe to get list of values in lookup and then eventstats to create that variable against each event for comparison. But this made search very clunky and I am sure there are better ways of doing this? So, if you can please sugesst a better way, that would be amazing.

emailDomains field:

test.com

sample.com

example.com

whitelistdomains Lookup data:

whitelist.com

sample.com

something.com

example.com

......and so on..

Expected output:

whitelistedDomains (this is a new field after looking up all multifield values against lookup)

sample.com

example.com

[yuanliu · Accepted solution]

This is a little confusing.  There is nothing to prevent multivalue fields from being used in lookup.  There is no need to mvexpand.  All you need to do is

| lookup whitelistdomains url as emailDomains output url as match

 The above assumes that whitelistdomains contain a field named url. for this match job.

To demonstrate, I'm using a lookup table from a previous question called all_urls.  It's content is as follows:

url

www.url1.com

*.url2.com

site.url3.com

This is an emulation - I just changed lookup name from the above

| makeresults
| fields - _time
| eval emailDomains = mvappend("www.url1.com", "site.url3.com", "www.url3.com")
``` data emulation above ```
| lookup all_urls url as emailDomains output url as match

This gives

emailDomains	match
www.url1.com site.url3.com www.url3.com	www.url1.com site.url3.com

[livehybrid]

Hi @vikashumble 

Would something like this work for you?

| makeresults 
| eval _raw="{\"id\": \"12345\", \"domain\": [\"test.com\",\"sample.com\",\"example.com\"]}"
| eval domain=json_array_to_mv(json_extract(_raw,"domain"))
| eval whitelistedDomains=""
| foreach domain mode=multivalue
    [| eval whitelistedDomains=mvappend(IF(tostring(json_extract(lookup("domainallowlist.csv",json_object("domain",<<ITEM>>),json_array("isAllowed")),"isAllowed"))=="1",<<ITEM>>,null()),whitelistedDomains) ]

This relies in having an "isAllowed"=1 value in the lookup but could be adjusted to your scenario?

Please let me know how you get on and consider accepting this answer or adding karma this answer if it has helped.
Regards

Will