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

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, some karma 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, Karma 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
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...