Splunk Search

How to extract field value from JSON and add the total of the value?

jitin_ratra
New Member

I have the following JSON format .

Content : {

"purchaseId":12345,
"items":[

{ }
],
"total":1100.24
},

{

"purchaseId":67890,
"items":[

{ }
],
"total":1100.24
}

I want to extract the field total and add the sum of the field "total" and create a table based on the sum. Tried to see the usage of spath in my case but no luck. Looking for final output as table like below. Appreciate any advise here.

| Total |

2200.48

Tags (3)
0 Karma

jitin_ratra
New Member

Thanks @niketnilay and @skoelpin for your feedback. I used JSON validator to validate the JSON format i posted above. The solution works when we evaluate the JSON data for 2 or 3 events but since i have lot of events which i need to add , it still does not show me any results.

Below is the sample of query i ran based on your suggestion above . I can see there are 12951 events but they dont turn up in statistics.

alt text

0 Karma

niketn
Legend

@jitin_ratra, you seem to be missing max_match=0 argument for the rex command in the screenshot provided. Can you please check?

  <yourCurrentBaseSearch>
 | rex "\"total\":(?<total>[^\s]+)\s" max_match=0
 | mvexpand total
 | stats sum(total) as total
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

niketn
Legend

@jitin_ratra, the JSON data you have shared in your question does not seem to be valid JSON packet. So, you would need to ensure that JSON tree structure is corrected first. You can then use
1) KV_MODE=json to have fields from JSON automatically extracted at search time (refer to KV_MODE details in props.conf documentation) or
2) spath command to traverse the JSON data.

Following is a run anywhere example with valid JSON data structure:

| makeresults
| eval _raw="{   \"Content\" : 
    {   
        \"purchaseId\":12345, 
        \"items\":[ { } ], 
        \"total\":1100.24 
    },
    \"Content\" :
    {   
        \"purchaseId\":67890, 
        \"items\":[ { } ], 
        \"total\":1100.24 
    }
}"
| spath
| mvexpand Content.total
| stats sum(Content.total) as total

In case you do not have access to modify the JSON data format, you mat have to use the option suggested by @skoelpin of using rex command to apply regular expression field extraction. Only difference is that since you have a multivalue total field, you would need to use argument max_match=0 for unlimited matches or some high number based on maximum total fields that you can have in your JSON.

Following is a run anywhere search with rex command based on your sample data (as is):

| makeresults
| eval _raw="Content : { 
\"purchaseId\":12345,
\"items\":[ 
{ }
],
\"total\":1100.24
},

{ 
\"purchaseId\":67890,
\"items\":[ 
{ }
],
\"total\":1100.24
}"
| rex "\"total\":(?<total>[^\s]+)\s" max_match=0
| mvexpand total
| stats sum(total) as total
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

jitin_ratra
New Member

@niketnilay, looks like something else is missing here. Tried to use max_match=0 as well. but no events returned now.
Below is my exact JSON data and highlighted is the field i want to extract and sum

alt text

alt text

0 Karma

niketn
Legend

@jitin_ratra from the screenshot of your SPL, seems like you have not copied over the rex command correctly. Please ensure double quotes and escaped double quote are applied as is.

 | rex "\"total\":(?<total>[^\s]+)\s" max_match=0
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

skoelpin
SplunkTrust
SplunkTrust

Add this

index=...
| rex total\"\:(?<total>\d+\.\d+)
| stats sum(total) AS Total

niketn
Legend

@skoelpin, slight correction since you plan to use rex max_match=0 or some high number depending on data needs to be added. I have added two approach in my answer 🙂

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
Get Updates on the Splunk Community!

Enterprise Security Content Update (ESCU) | New Releases

In September, the Splunk Threat Research Team had two releases of new security content via the Enterprise ...

New in Observability - Improvements to Custom Metrics SLOs, Log Observer Connect & ...

The latest enhancements to the Splunk observability portfolio deliver improved SLO management accuracy, better ...

Improve Data Pipelines Using Splunk Data Management

  Register Now   This Tech Talk will explore the pipeline management offerings Edge Processor and Ingest ...