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!

SOCin’ it to you at Splunk University

Splunk University is expanding its instructor-led learning portfolio with dedicated Security tracks at .conf25 ...

Credit Card Data Protection & PCI Compliance with Splunk Edge Processor

Organizations handling credit card transactions know that PCI DSS compliance is both critical and complex. The ...

Stay Connected: Your Guide to July Tech Talks, Office Hours, and Webinars!

What are Community Office Hours?Community Office Hours is an interactive 60-minute Zoom series where ...