topic Re: Outputlookup Removes Multivalue Fields in Splunk Search

Outputlookup Removes Multivalue Fields

david_rose — Mon, 17 Aug 2015 21:27:29 GMT

I had an extremely expensive query that would return results in this format:

I needed to speed up the query because it was taking 3 minutes to load on the dashboard, so I converted it into a lookup table via outputlookup. Now, when I run inputlookup against the newly created lookup, it does not have the values seperated into new lines.

I have tried running mvexpand against the columns but nothing happens. When I export the results, the csv has all the fields on their own lines as in the first screen shot.

How can I have the lookup table retain the formatting of the source table?

Re: Outputlookup Removes Multivalue Fields

woodcock — Tue, 18 Aug 2015 06:59:08 GMT

You need to link the 3 values together so that their relationship across columns is maintained; otherwise each column's values will be treated totally independently as unrelated multivalued fields.

So instead of something like this (which you are currently using):

VIP,Risk,Port,Protocol
1.2.3.4,High,443,TCP
1.2.3.4,Medium,80,TCP

You do something like this:

VIP,Risk-Port-Protocol
1.2.3.4,High-443-TCP
1.2.3.4,Medium-80-TCP

If necessary, you can split the fields out to separate them after the lookup, but I don't see the point

Re: Outputlookup Removes Multivalue Fields

david_rose — Tue, 18 Aug 2015 14:07:56 GMT

Why were the multi events removed when i created the lookup though? The data was structured fine beforehand.

I tried as you suggested by combing those fields in the original table with:

eval "Risk Details"='Risk'."-".'Port'."-".'Protocol'

But it ignores the multivalue fields and only concatenates the single value ones.

Re: Outputlookup Removes Multivalue Fields

woodcock — Thu, 20 Aug 2015 23:06:07 GMT

Because when you call outputcsv each line must have the same number of fields therefore it does not create any multivalued fields and does an implicit mvexpand before writing them out (it is, after all, creating a CSV). So you need to create a triple-combo field and do the outputcsv again and even then, you are going to have to use makemv (and other commands) when you pull it back out to recreate what you had originally.

Re: Outputlookup Removes Multivalue Fields

peter_holmes_an — Thu, 27 Aug 2015 16:31:37 GMT

So as I understand it a lookup table can only hold four types of data (number boolean, time and string). So when you send a multivalue field into a lookup it gets created as a string, which is not usable by mvexpand or the other mv commands. However, you can split the data with a delimiter using mvjoin:

 ... | eval <new field>=mvjoin(<original field>, "<delimiter>")

This then gives you a sting with a delimiter between each entry (e.g. TCP,TCP,TCP). Once you have that you can then convert this back into a multivalue field when you search the lookup table using:

 ... | makemv delim="<delimiter>" <field>

Re: Outputlookup Removes Multivalue Fields

woodcock — Thu, 27 Aug 2015 23:32:03 GMT

You are not hearing me. You need to maintain the relationship between these 3 fields: Risk, Port, & Protocol. You created these with the list function (e.g. list(Risk) AS Risk and this maintains a "rowish" order so you can see that the first triplet is High / 443 / TCP. If you do not export these together, you will lose this relationship. You must do it the way I have already described (by linking them together as a single field). If you are using outputscv then do it like this:

...  | eval Risk_Port_Protocol = Risk . "-" . Port . "-" Protocol | stats values(Risk_Port_Protocol) by vIP Host | fields vIP Risk_Port_Protocol | outputcsv SomeFile.csv

Then, when you read it back in it will look correct. This is the only way to do it (right). If you persist in a non-joined method, then you will be wasting your time because it will never be correct.

Re: Outputlookup Removes Multivalue Fields

nikhiltyagi — Mon, 21 Oct 2019 06:16:04 GMT

try using output_format=splunk_mv_csv option with outputlookup

|outputlookup output_format=splunk_mv_csv <lookupname>

Re: Outputlookup Removes Multivalue Fields

cyamal1b4 — Thu, 27 May 2021 21:53:53 GMT

This is the proper solution!