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 (firstname.lastname@example.org in my example).
Any idea why I have this behaviour ?
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 ])
If possible, change the lookup table format to be linear with each email appearing in separate row.
email, user_id email@example.com,375352 firstname.lastname@example.org,375352 ... ..
Your searching would be much easier.
Multivalued fields are supported in
KV-based lookups, but not in
file-based lookups. Switch to a
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
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!
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.
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.