Splunk Search

Multivalued fields in a lookup file

lzaexpert
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 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

1 Solution

woodcock
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

jkat54
SplunkTrust
SplunkTrust

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!

woodcock
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.

jkat54
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.

woodcock
Esteemed Legend

Upvoted everything.

woodcock
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

woodcock
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.

somesoni2
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

DalJeanis
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
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...