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

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...