topic How to extract all the multi-values in excel? in Splunk Search

How to extract all the multi-values in excel?

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

One of my field in raw data is multivalue(like array) .
I can see those values in a column in Splunk , but when I try to export them to csv then
only the 1st value gets copied and rest disappears .

eg:
In Splunk

col1

val1 val2

val2 val3 val4

While exporting

col1

val1

val2

Re: How to extract all the multi-values in excel?

bowesmana — Sun, 05 Feb 2023 22:25:27 GMT

What command are you using to show the data like that in Splunk. What is your _raw field like in Splunk

How are you exporting? Using outputlookup or using the export command?

Can you apend

| eval count=mvcount(Col1)

to the search and say what the value of count is.

Re: How to extract all the multi-values in excel?

kasis152 — Mon, 06 Feb 2023 04:32:53 GMT

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

What I wrote was like :

| 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

Which Gave me like :

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

But I want it like :

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

Re: How to extract all the multi-values in excel?

bowesmana — Mon, 06 Feb 2023 05:01:10 GMT

You first need to expand the b array to multiple events then extract col5/6

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

so the col1/col2 extraction is done, then the b array is extracted to b field, which you then expand, so the col5/6 end up in the right related events.

Then expand the extracted col6 array