Hi,
I would like to ask for help in grouping a list per Index/object. I have tried using tables but the values are combined without respecting object indexes.
Data set: (json)
{
"container_id":"ABC0001",
"name_box":"mangoes",
"total_boxes_inside":3,
"boxes":[
{
"box_index":"121212",
"box_label":[
"sweet",
"yellow",
"imported"
]
},
{
"box_index":"232323",
"box_label":[
"green",
"local",
"sour",
"hybrid"
]
},
{
"box_index":"343434",
"box_label":[
"very sweet",
"local",
"round",
"pink"
]
}
]
}
Splunk query:
| rename boxes{}.box_index as box_index, boxes{}.box_label as box_label
| table container_id, name_box, box_index, box_label
Result (values of "box_indexes" and "box_label" are combined per column):
The result disregarded the grouping in the list and merge all value per column
container_id name_box box_index box_label
ABC0001 mangoes 121212 sweet
232323 yellow
343434 imported
green
local
sour
hybrid
very sweet
local
round
pink
I would like to group each "box_label" per "box_index" and should have a result like this:
container_id name_box box_index box_label
ABC0001 mangoes 121212 sweet
yellow
imported
ABC0001 mangoes 232323 green
local
sour
hybrid
ABC0001 mangoes 343434 very sweet
local
round
pink
Thank you
Hello @yomixxxmx,
this gives the result you expect.
| makeresults
| eval _raw = "{ \"container_id\":\"ABC0001\",\"name_box\":\"mangoes\",\"total_boxes_inside\":3, \"boxes\" : [ { \"box_index\":\"121212\",\"box_label\": [ \"sweet\",\"yellow\",\"imported\" ] } , { \"box_index\":\"232323\",\"box_label\": [ \"green\",\"local\",\"sour\",\"hybrid\" ] } , { \"box_index\":\"343434\",\"box_label\": [ \"very sweet\",\"local\",\"round\",\"pink\" ] } ] }"
| spath
| table _raw,container_id,name_box
| spath path=boxes{}
| rename boxes{} as boxes
| mvexpand boxes
| spath input=boxes
| rename box_label{} as box_label
| table container_id,name_box, box_index,box_label
I got the idea from the spah documentation, bottom example (https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Spath).
Hello @yomixxxmx,
this gives the result you expect.
| makeresults
| eval _raw = "{ \"container_id\":\"ABC0001\",\"name_box\":\"mangoes\",\"total_boxes_inside\":3, \"boxes\" : [ { \"box_index\":\"121212\",\"box_label\": [ \"sweet\",\"yellow\",\"imported\" ] } , { \"box_index\":\"232323\",\"box_label\": [ \"green\",\"local\",\"sour\",\"hybrid\" ] } , { \"box_index\":\"343434\",\"box_label\": [ \"very sweet\",\"local\",\"round\",\"pink\" ] } ] }"
| spath
| table _raw,container_id,name_box
| spath path=boxes{}
| rename boxes{} as boxes
| mvexpand boxes
| spath input=boxes
| rename box_label{} as box_label
| table container_id,name_box, box_index,box_label
I got the idea from the spah documentation, bottom example (https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Spath).
Thank you! this works for me perfectly.
Use stats instead of table
| rename boxes{}.box_index as box_index, boxes{}.box_label as box_label
| stats values(box_label) by container_id, name_box, box_index
Thank you man! I will also test this 🙂
The table
command does not do grouping, but you can do it with stats
. Try this:
| rename boxes{}.box_index as box_index, boxes{}.box_label as box_label
| stats values(box_label) as box_label by container_id, name_box, index
| table container_id, name_box, box_index, box_label
Thank you very much for your answer! I will also test this and observe how stats works. thanks!