topic Re: Display all the values of the multi-value column(array-like) in different rows in Splunk Search

How to display all the values of the multi-value column(array-like) in different rows?

kasis152 — Fri, 03 Feb 2023 22:16:26 GMT

I have the raw data in format :
{"col1":"1",{col2":"2"},{.........(continue)
which if I have to visualize using https://codebeautify.org/string-to-json-online :

Object{1}
  ->a{4}
     col1: 1
     col2: 2
     col3: 3
     col4: 4
  ->b[3]
     ->0{3}
        col5:"5
        col6[0]
           0:6
     ->1{3}
        col5: "55"
        col6[1]
          0:66
      ->2{3}
        col5: 55
        col6[1]
          0:666

And if my Splunk query is like
index="api"
| rename a.col1 as "col1",a.col2 as "col2", b{}.col5 as "col5", b{}.col6{} as "col6"
| table "col1","col2","col5","col6"

it display me:

col1	col2	col5	col6
1	2	5 55	6 66 666

Moreover , if I export it in csv
It only shows me first value of array(multi-value)

col1	col2	col3	col4
1	2	5	6

but should be like : (each row 1:1 mapped)
MY DESIRED TABLE

col1	col2	col5	col6
1	2	5	6
1	2	55	66
1	2	55	666

Re: Display all the values of the multi-value column(array-like) in different rows

richgalloway — Fri, 03 Feb 2023 19:37:07 GMT

It would help if you showed the raw data rather than some strange pretty-printed version of it. We can't test solutions with the string-to-json-online output.

I believe there is more to the query than is shown because what is shown won't produce the given output. I suspect there is a stats values(...) command being used and that's why only one 55 appears in the col5 field. If you replace values() with list() then the values list won't be deduplicated. Then you can expand col5 and col6 after first zipping them together.

This presumes col5 and col6 will always have the same number of values. If that's not the case then another solution is needed.

Re: Display all the values of the multi-value column(array-like) in different rows

kasis152 — Fri, 03 Feb 2023 20:34:59 GMT

@richgalloway
Yes you were right , Raw data might help u better answer my question .
but your query gave me such better shape .
my visualization was kind of off .

Can you help me rewrite the query , given that objective is still the save to obtain the same desired output as above.

Correction:
My raw data :
{"col1":"1","col2":"2","col3":"3","col4":"4","b":[{"col5":"5","col6":["6"]},{"col5":"55","col6":["66","666"]}]}

Re: Display all the values of the multi-value column(array-like) in different rows

richgalloway — Fri, 03 Feb 2023 21:40:48 GMT

I don't have a way to produce the format you seek, but I can combine the values so they should all export. Perhaps Excel offers a way to break them up.

| makeresults | eval _raw="{\"col1\":\"1\",\"col2\":\"2\",\"col3\":\"3\",\"col4\":\"4\",\"b\":[{\"col5\":\"5\",\"col6\":[\"6\"]},{\"col5\":\"55\",\"col6\":[\"66\",\"666\"]}]}" | spath | rename b{}.col5 as "col5", b{}.col6{} as "col6" | eval col5=mvjoin(col5,", "), col6=mvjoin(col6, ", ") | table col1 col2 col5 col6

Stepping back a bit. What problem are you trying to solve with Excel? Perhaps a native Splunk report/dashboard can do the job.

Re: Display all the values of the multi-value column(array-like) in different rows

kasis152 — Fri, 03 Feb 2023 21:42:53 GMT

The ultimate goal is to have the Dashboard in Tableau .
Hence, wanted a formatted data.

Re: Display all the values of the multi-value column(array-like) in different rows

richgalloway — Fri, 03 Feb 2023 22:05:41 GMT

Tableau can access data in Splunk using ODBC (at least it could - I heard rumors that's not possible with some versions of Tableau). With ODBC, Tableau launches a saved search that runs on Splunk and the results are automatically exported to Tableau. It's up to the saved search to format the output, unless Tableau can perform additional transformations. It's been several years since I used Tableau so that's about all the help I can offer there.

Re: How to display all the values of the multi-value column(array-like) in different rows?

kasis152 — Fri, 03 Feb 2023 22:44:39 GMT

Appreciate your help