Splunk Search
Highlighted

Flatten Search Results

New Member

I am currently trying to use the Splunk REST API to extract a heap of data.
I have written a search query:

(index=* OR index=_*) (index="myindex") | table "order-no", "order-date", "price"

But it provides a result set which looks like this:

"Order-no", "order-date", "price","quantity"
1        ,       20/05/2017        , 1.0, 1
--------------------, 2.0, 1
---------------------,3.0, 2

It rolls 2.0 and 3.0 up to 1 record along side row order-no = 1.
So when i try to export this as a CSV using REST API, the output is all jumbled up.

Is there a way to flatten this in the search so it shows as:

"Order-no", "order-date", "price","quantity"
1        ,       20/05/2017        , 1.0, 1
1        ,       20/05/2017        , 2.0, 1
1        ,       20/05/2017        , 3.0, 2

I've tried to use mvexpand, but it appears that it works on a individual column level and if i do a mvexpand on both of those last 2 columns, it effectively does a cross join on the entire data which causes incorrect values
Thanks in advance.

0 Karma
Highlighted

Re: Flatten Search Results

SplunkTrust
SplunkTrust

Assuming you have this format

| table order-no order-date price quantity

and price and quantity are both multivalue fields that are aligned with each other, then do this...

| eval price=mvzip(price,quantity,"@")
| mvexpand price
| eval price=split(price,"@")
| eval quantity=mvindex(price,1)
| eval price=mvindex(price,0)

If you had a third field, which might contain an @ sign, and you don't have people entering enthusiastic comments, then I recommend five bangs as a delimiter "!!!!!". You can also substitute any highly unlikely combination of characters ( "!!#@#!!" ).

| table order-no order-date price quantity description
| eval price=mvzip(mvzip(price,quantity,"!!!!!"),description,"!!!!!")
| mvexpand price
| eval price=split(price,"!!!!!")
| eval description=mvindex(price,2)
| eval quantity=mvindex(price,1)
| eval price=mvindex(price,0)
Highlighted

Re: Flatten Search Results

New Member

I tried this solution but it keeps on giving me error:
Error in 'eval' command: The arguments to the 'mvzip' function are invalid.

0 Karma
Highlighted

Re: Flatten Search Results

New Member

Nevermind found out why..... Looks like eval doesn't like columns with "-" in it.
Had to rename the column without a "-" then reference it in mvzip and it worked.

0 Karma
Highlighted

Re: Flatten Search Results

New Member

Looks like using this solution only returns the first record, and doesn't return the other records within the group.

Should look like:
"Order-no", "order-date", "price","quantity"
1 , 20/05/2017 , 1.0, 1
1 , 20/05/2017 , 2.0, 1
1 , 20/05/2017 , 3.0, 2

Instead it returns this:
"Order-no", "order-date", "price","quantity"
1 , 20/05/2017 , 1.0, 1

0 Karma
Highlighted

Re: Flatten Search Results

New Member

I tried this solution.
But it looks like it only returns the first record not all of them.

It should look like this:
"Order-no", "order-date", "price","quantity"
1 , 20/05/2017 , 1.0, 1
1 , 20/05/2017 , 2.0, 1
1 , 20/05/2017 , 3.0, 2

But instead it looks like this:
"Order-no", "order-date", "price","quantity"
1 , 20/05/2017 , 1.0, 1

0 Karma
Highlighted

Re: Flatten Search Results

New Member

Looks like when any of the columns within the mvzip is null / empty string, it drops the entire row. Anyway to fix this?

0 Karma
Highlighted

Re: Flatten Search Results

Esteemed Legend

Sure, give my answer a try.

0 Karma
Highlighted

Re: Flatten Search Results

SplunkTrust
SplunkTrust

@shinglau - These methods are not appropriate for fields that are not precisely matched, with the same number of mv fields. If a field is supposed to be matched and is null, then you need to create a placeholder mv field, for example with the word "NULL" the correct number of times.

Assuming that the description may be null, the price and quantity are always matched and never null, and price and quantity can never appear more than 40 times, that could look something like...

 | table order-no order-date price quantity description
 | eval mynulls=mvappend("((UNUSED))",
   "((NULL))","((NULL))","((NULL))","((NULL))","((NULL))", "((NULL))","((NULL))","((NULL))",
   "((NULL))","((NULL))","((NULL))","((NULL))","((NULL))","((NULL))","((NULL))","((NULL))",
   "((NULL))","((NULL))","((NULL))","((NULL))","((NULL))","((NULL))","((NULL))","((NULL))",
   "((NULL))","((NULL))","((NULL))","((NULL))","((NULL))","((NULL))","((NULL))","((NULL))",
   "((NULL))","((NULL))","((NULL))","((NULL))","((NULL))","((NULL))","((NULL))","((NULL))")
 | eval description=coalesce(description,mvindex(mynulls,1,mvcount(price)))
 | eval price=mvzip(mvzip(price,quantity,"!!!!!"),description,"!!!!!")
 | mvexpand price
 | eval price=split(price,"!!!!!")
 | eval description=mvindex(price,2)
 | eval description=if(description="((NULL))",null(),description)
 | eval quantity=mvindex(price,1)
 | eval price=mvindex(price,0)

If a field is not directly linked to the other fields (price, quantity, cost etc) then do not handle it in the same phase. If it IS directly linked to the other fields but is for some reason not aligned, then it requires a separate question with specifics so that we can handle that error appropriately.

0 Karma
Highlighted

Re: Flatten Search Results

New Member

That works for when the entire MV is null, but doesn't work for when there are 4 rows, but in the MV there is 3 of them which has a value, but the 4th one is non-existent.

0 Karma