Getting Data In

How do you sum all values of a field that has JSON data?

Path Finder

I have some JSON data , in that i want to sum all values of a key in a Splunk query. Below is the sample data :

data":[{"abc":"1234","mainrate":12},{"abc":"186","mainrate":222},{"abc":"633","mainrate":121},]}],

Tried using sum but that is not giving me sum of all minrate. I'm using the below query to get this done :

eventtype=mytest| eventstats sum(mainrate) AS TotalMinRate
Tags (3)
0 Karma

Revered Legend

Seems like you want to sum the multivalued field mainrate values within same event. Unfortunately, there is no built-in function to do a multivalued field's value sum. Give this workaround a try.

If there are no primary key (some key or keys that uniquely represent each row) in your data, try this

eventtype=mytest | streamstats count as rank | eventstats sum(mainrate) AS TotalMinRate by rank

If there is a primary key in your data, use it in eventstats.

eventtype=mytest | eventstats sum(mainrate) AS TotalMinRate by yourPrimaryKeyField
0 Karma

Path Finder
eventtype=mytest  |eventstats  sum(mainrate) AS TotalMinRate  by PrimaryKey | rename date AS "datetime" | table datetime, TotalMinRate

Above is the full query but it is not doing the sum of all the json key it has for PrimaryKeyField

0 Karma

Revered Legend

Is the name of the primary key field PrimaryKey?

0 Karma

Path Finder

No it's name is different i have just added as an example

0 Karma

SplunkTrust
SplunkTrust

You need to include a function after the pipe. Try using eventstats or stats to do this

eventtype=mytest
| eventstats sum(mainrate) AS TotalMinRate
0 Karma

Path Finder

I tried that as well but it is not working , can we use foreach to get this done?

0 Karma

SplunkTrust
SplunkTrust

No.. I see you updated your answer to reflect eventstats. What is not working? Have you tried stats? Are you trying to create a field with the summed values or trying to show a table view with the summed values? Have you confirmed the field is populating? If it's not then you have an issue with your extraction. Can you confirm minrate is producing results? Can you confirm minrate is numeric? If they are string values then it will not sum

0 Karma

Path Finder

I have tried both stats and eventstats but both are helping me to sum all the key values.

0 Karma

Path Finder

Yes I am trying to create a table that will have totalMinrate field and that field will have the sum of all mainrate.It is showing the TotalMinRate in rate but it is not doing the sum for each minrate present in the Json array.

0 Karma

SplunkTrust
SplunkTrust

You haven't answered my questions.. If these values are NOT numeric then it will not work. You should try testing with another field such as date_minute. I just tested in my env and its working as expected

index=..
| eventstats sum(date_minute)
0 Karma

Path Finder

Yes it is working for numeric values ,
Those are Json values might possible splunk is considering them as a String

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!