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?
You are not hearing me. You need to maintain the relationship between these 3 fields:
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
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.
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>
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 220.127.116.11,High,443,TCP 18.104.22.168,Medium,80,TCP
You do something like this:
VIP,Risk-Port-Protocol 22.214.171.124,High-443-TCP 126.96.36.199,Medium-80-TCP
If necessary, you can split the fields out to separate them after the lookup, but I don't see the point
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.
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.