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. I tried using the below query but I am not getting any events even though there are events that qualify my query. 

 

 

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

 

 

 

Expected Output : 

 

BID  Count
123  4
456  3

 

 

Query I am using :

 

{my_search}
| rex field=MESSAGE "(?<JSON>\{.*\})"
| spath input=JSON path=details.BID
| stats values(details.BID) as "BID" by CORRID
| stats count as Count by "BID"

 

 

0 Karma

ghostrider
Path Finder

Thanks you so much!! I just had one doubt. So 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. Is there any way to configure these as static fields which will get displayed when the billing code is displayed Like below

Output

Description BID  Count
Apple       123  4
Orange      456  3



0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@ghostrider 

You can create a lookup with BID and description mapping. And use the lookup in your search.

BID,Description
123,Apple

 

https://docs.splunk.com/Documentation/SplunkCloud/latest/Knowledge/ConfigureCSVlookups

https://docs.splunk.com/Documentation/SplunkCloud/latest/SearchReference/Lookup

 

I hope this will help you.

Thanks
KV
If any of my replies help you to solve the problem Or gain knowledge, an upvote would be appreciated.

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@ghostrider 

Can you please try this?

YOUR_SEARCH
| rename "details{}.BID" as BID
| stats count as Count by BID

OR

YOUR_SEARCH | spath
| rename "details{}.BID" as BID
| stats count as Count by BID

 

My Sample Search :

| makeresults | eval _raw="{
\"details\" : [
{
\"BID\" : \"123\"
},
{
\"BID\" :  \"456\"
}
]
}" | spath
| rename "details{}.BID" as BID
| stats count as Count by BID

 

Screenshot 2022-09-30 at 12.26.58 PM.png

 

I hope this will help you.

Thanks
KV
If any of my replies help you to solve the problem Or gain knowledge, an upvote would be appreciated.

Get Updates on the Splunk Community!

The Splunk Success Framework: Your Guide to Successful Splunk Implementations

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...

Splunk Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...