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
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
.
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!
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.
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.
Upvoted everything.
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.
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
.
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.
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 ])