Splunk Search

Outputlookup Removes Multivalue Fields

david_rose
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

nikhiltyagi
Explorer

try using output_format=splunk_mv_csv option with outputlookup

|outputlookup output_format=splunk_mv_csv <lookupname>

cyamal1b4
Engager

This is the proper solution! 

0 Karma

woodcock
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

peter_holmes_an
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

woodcock
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

david_rose
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

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

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

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