Splunk Search
Highlighted

Multivalued fields in a lookup file

Explorer

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 xrecipients, xsender that contain email addresses

What I would like to achieve is a search based on the Userid that will show all emails sent or received. What i have done so far is a lookup that matches my event field xrecipient 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

Highlighted

Re: Multivalued fields in a lookup file

SplunkTrust
SplunkTrust

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 ]) 
0 Karma
Highlighted

Re: Multivalued fields in a lookup file

SplunkTrust
SplunkTrust

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.

0 Karma
Highlighted

Re: Multivalued fields in a lookup file

Esteemed Legend

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.

View solution in original post

Highlighted

Re: Multivalued fields in a lookup file

SplunkTrust
SplunkTrust

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

It appears that lookups created with outputformat=splunkmvcsv 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!

Highlighted

Re: Multivalued fields in a lookup file

Esteemed Legend

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.

0 Karma
Highlighted

Re: Multivalued fields in a lookup file

Esteemed Legend

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.

Highlighted

Re: Multivalued fields in a lookup file

SplunkTrust
SplunkTrust

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.

Highlighted

Re: Multivalued fields in a lookup file

Esteemed Legend

Upvoted everything.

0 Karma