Splunk Search

How to group a list per Index/object?

yomixxxmx
New Member

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

Tags (2)
0 Karma
1 Solution

poete
Builder

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).

View solution in original post

poete
Builder

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).

View solution in original post

yomixxxmx
New Member

Thank you! this works for me perfectly.

0 Karma

diogofgm
SplunkTrust
SplunkTrust

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
------------
Hope I was able to help you. If so, an upvote would be appreciated.
0 Karma

yomixxxmx
New Member

Thank you man! I will also test this 🙂

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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
---
If this reply helps you, an upvote would be appreciated.

yomixxxmx
New Member

Thank you very much for your answer! I will also test this and observe how stats works. thanks!

0 Karma