Splunk Search

Outputlookup Removes Multivalue Fields

Communicator

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

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.

alt text

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?

0 Karma

Explorer

try using output_format=splunk_mv_csv option with outputlookup

|outputlookup output_format=splunk_mv_csv <lookupname>

Esteemed Legend

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.

0 Karma

Path Finder

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>
0 Karma

Esteemed Legend

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

0 Karma

Communicator

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.

0 Karma

Esteemed Legend

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.

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!