Getting Data In
Highlighted

sum multiple value in a json

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
Highlighted

Re: sum multiple value in a json

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
Highlighted

Re: sum multiple value in a json

Engager

Many thanks! it works well