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!

Aligning Observability Costs with Business Value: Practical Strategies

 Join us for an engaging Tech Talk on Aligning Observability Costs with Business Value: Practical ...

Mastering Data Pipelines: Unlocking Value with Splunk

 In today's AI-driven world, organizations must balance the challenges of managing the explosion of data with ...

Splunk Up Your Game: Why It's Time to Embrace Python 3.9+ and OpenSSL 3.0

Did you know that for Splunk Enterprise 9.4, Python 3.9 is the default interpreter? This shift is not just a ...