Splunk Search

How to achieve grouping the results using field present inside a json array?

ghostrider
Path Finder

I have below format log messages. At the end I want to group the messages by BID.

{
"details" : [
{
"BID" : "123"
},
{
"BID" :  "456"
}
]
}

These BIDs correspond to some static fields which describe what BID is. Like BID :  123 corresponds to Apple. This detail/description is however not present in the events or search results. The mappings are present in the lookup.csv file with BID and description as 2 columns, which I am using for lookup. But the description is field is getting displayed at the end rather than after/before the BID. I want to show the below expected output but its showing the below actual output. 

Expected Output

Description BID  Count
Apple       123  4
Orange      456  3

 

Actual Output

BID  Count Description
123  4      Apple       
456  3      Orange      

 

I am using below query, Is there any way to achieve the expected output? 

MY_SEARCH
| spath 
| rename "details{}.BID" as BID
| stats count as Count by BID
| lookup lookup.csv BID as BID OUTPUT description as description

 

Labels (8)
0 Karma
1 Solution

johnhuang
Motivator

You can use the "table" command to order your output fields. For example, add this to the bottom of your query.

| table Description BID Count

 

View solution in original post

johnhuang
Motivator

You can use the "table" command to order your output fields. For example, add this to the bottom of your query.

| table Description BID Count

 

Get Updates on the Splunk Community!

2024 Splunk Career Impact Survey | Earn a $20 gift card for participating!

Hear ye, hear ye! The time has come again for Splunk's annual Career Impact Survey!  We need your help by ...

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...