topic Re: Multivalued fields in a lookup file in Splunk Search

Multivalued fields in a lookup file

lzaexpert — Tue, 29 Sep 2020 17:15:35 GMT

I have a csv file like :
User_id,emails
375352,foo@foo.com foo@foo.ca foobar@foobar.co.uk
872352,toto@foo.com

note: email addresses are blank separated within the csv file

On the other hand I have an email log file in which I have fields such as x_recipients, x_sender that contain email addresses

What I would like to achieve is a search based on the User_id that will show all emails sent or received. What i have done so far is a lookup that matches my event field x_recipient with my csv field emails to output the User_id. It works but only for csv entries where emails has only one value (toto@foo.com in my example).

Any idea why I have this behaviour ?

Many thanks
Laurent

Re: Multivalued fields in a lookup file

DalJeanis — Fri, 15 Dec 2017 15:36:34 GMT

It has that behavior because the "multivalue" field in the lookup table has been flattened into a string.

Try something like this...

 your base search here  
 ( [| inputlookup myemails.csv | where User_id = "375352" | table emails | makemv emails | mvexpand emails | rename emails as  x_recipients ]) 
| the rest of your search code

When you have that working for the recipient field, add something like this...

 OR 
 ( [| inputlookup myemails.csv | where User_id = "375352" | table emails  | makemv emails | mvexpand emails | rename emails as  x_sender ])

Re: Multivalued fields in a lookup file

somesoni2 — Fri, 15 Dec 2017 16:19:35 GMT

If possible, change the lookup table format to be linear with each email appearing in separate row.

email, user_id
foo@foo.com,375352
foo@foo.ca,375352
...
..

Your searching would be much easier.

Re: Multivalued fields in a lookup file

woodcock — Sun, 17 Dec 2017 03:22:26 GMT

Multivalued fields are supported in KV-based lookups, but not in file-based lookups. Switch to a KV Store.
Or, do something like this:

| inputlookup MyLookup.csv
| makemv delim=" " emails
| mvexpand emails
| outputcsv MyLookup.csv

Then create a Lookup definition with Maximum matches set to something large like 20.

Re: Multivalued fields in a lookup file

jkat54 — Wed, 30 Sep 2020 01:43:23 GMT

At some point, they added output_format=splunk_mv_csv to the outputlookup command which allows for mv fields in lookups.

It appears that lookups created with output_format=splunk_mv_csv are quoted with CRLF's OR commas between the multivalues, but also have "_mv" quoted in header because they start with "_" ( "_raw" was quoted in the header in my testing.)

CRLF also known as \r\n.

Both of the examples below worked on splunk 7.x:

mvfield,"__mv_mvfield",otherfield,otherfields
"value1
value2
value3","$value1$;$value2$;$value3$","otherfield","otherfields"

mvfield,"__mv_mvfield",otherfield,otherfields
"value1,value2,value3","$value1$;$value2$;$value3$","otherfield","otherfields"

Hope this helps others!

Re: Multivalued fields in a lookup file

woodcock — Wed, 14 Aug 2019 17:11:57 GMT

I am pretty sure that this option existed as undocumented (and mostly unused) for a LONG time, definitely in v6.?, but nobody noticed it because the default is to not do this. I noticed it as far back as v4.? inside of restults.gz but did not understand what it was.

Re: Multivalued fields in a lookup file

woodcock — Wed, 14 Aug 2019 17:14:04 GMT

I think that no longer applies is an inaccurate way to put it, because it it definitely applies but with additional provisos due to recent discoveries.

Re: Multivalued fields in a lookup file

jkat54 — Thu, 15 Aug 2019 13:59:14 GMT

Yeah i just read that and said "it still applies, what was i thinking?" because KVstore is a perfectly acceptable solution here too.

Editing my answer now.

Re: Multivalued fields in a lookup file

woodcock — Thu, 15 Aug 2019 15:24:10 GMT

Upvoted everything.