topic Re: sum multiple value in a json in Getting Data In

sum multiple value in a json

3vi — Wed, 17 Jul 2019 13:08:52 GMT

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

Re: sum multiple value in a json

rbechtold — Thu, 18 Jul 2019 00:49:44 GMT

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!

Re: sum multiple value in a json

3vi — Thu, 18 Jul 2019 09:34:38 GMT

Many thanks! it works well