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!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...