Getting Data In

sum multiple value in a json

3vi
Engager

Hello, I have a raw like this:

.success [{"importo":2,"tipologiaOperazione":"AAA"},{"importo":1.82,"tipologiaOperazione":"BBB"}, {"importo":1,"tipologiaOperazione":"AAA"}]

I need to have the sum of importo, by tipologiaOperazione, in this case, the result will be:

AAA: 3
BBB: 1.82

How can I do that?

I tried with this regex

| rex max_match=100000 "(?ms)(?P<importoDistinta>(?<=\"importo\":)(\\d*\\.?\\d*)(?=,))" | rex max_match=100000 "(?ms)(?P<tipologiaOperazione>(?<=\"tipologiaOperazione\":\")(\\w*\\s*\\w*\\w*\\s*\\w*)(?=\",))" | stats sum(importoDistinta) by tipologiaOperazione

but I've got this result:

AAA: 4.82
BBB: 4.82

0 Karma
1 Solution

rbechtold
Communicator

Hey 3vi,

Using the raw data you provided, I've created a search that should give you the correct numbers you're looking for (you can copy and paste this into any Splunk instance):

| makeresults count=1
| eval _raw =  ".success [{\"importo\":2,\"tipologiaOperazione\":\"AAA\"},{\"importo\":1.82,\"tipologiaOperazione\":\"BBB\"}, {\"importo\":1,\"tipologiaOperazione\":\"AAA\"}]"
| rex field=_raw max_match=0 "\"importo\"\:(?<importo>[^\,]+)\,\"tipologiaOperazione\"\:\"(?<tipologiaOperazione>[^\"]+)"
| eval zippedfields = mvzip(importo,tipologiaOperazione)
| mvexpand zippedfields
| rex field=zippedfields "(?<importo>[^\,]+)\,(?P<tipologiaOperazione>.*)"
| fields - zippedfields
| stats sum(importo) by tipologiaOperazione

Since there are multiple importos and tipologiaOperaziones in each log, you'll need to use mvexpand to make sure all the importos are accounted for before summing them together.

If you run into any problems, or the solution doesn't work for you, let me know and I'll do my best to further assist!

View solution in original post

0 Karma

rbechtold
Communicator

Hey 3vi,

Using the raw data you provided, I've created a search that should give you the correct numbers you're looking for (you can copy and paste this into any Splunk instance):

| makeresults count=1
| eval _raw =  ".success [{\"importo\":2,\"tipologiaOperazione\":\"AAA\"},{\"importo\":1.82,\"tipologiaOperazione\":\"BBB\"}, {\"importo\":1,\"tipologiaOperazione\":\"AAA\"}]"
| rex field=_raw max_match=0 "\"importo\"\:(?<importo>[^\,]+)\,\"tipologiaOperazione\"\:\"(?<tipologiaOperazione>[^\"]+)"
| eval zippedfields = mvzip(importo,tipologiaOperazione)
| mvexpand zippedfields
| rex field=zippedfields "(?<importo>[^\,]+)\,(?P<tipologiaOperazione>.*)"
| fields - zippedfields
| stats sum(importo) by tipologiaOperazione

Since there are multiple importos and tipologiaOperaziones in each log, you'll need to use mvexpand to make sure all the importos are accounted for before summing them together.

If you run into any problems, or the solution doesn't work for you, let me know and I'll do my best to further assist!

0 Karma

3vi
Engager

Many thanks! it works well

Get Updates on the Splunk Community!

Prove Your Splunk Prowess at .conf25—No Prereqs Required!

Your Next Big Security Credential: No Prerequisites Needed We know you’ve got the skills, and now, earning the ...

Splunk Observability Cloud's AI Assistant in Action Series: Observability as Code

This is the sixth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how to ...

Splunk Answers Content Calendar, July Edition I

Hello Community! Welcome to another month of Community Content Calendar series! For the month of July, we will ...