topic Re: How do you sum all values of a field that has JSON data? in Getting Data In

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

gauravepi — Thu, 13 Sep 2018 14:54:01 GMT

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

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

skoelpin — Thu, 13 Sep 2018 14:59:08 GMT

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

eventtype=mytest
| eventstats sum(mainrate) AS TotalMinRate

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

gauravepi — Thu, 13 Sep 2018 15:09:40 GMT

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

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

skoelpin — Thu, 13 Sep 2018 15:13:26 GMT

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

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

gauravepi — Thu, 13 Sep 2018 15:19:32 GMT

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.

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

gauravepi — Thu, 13 Sep 2018 15:21:38 GMT

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

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

skoelpin — Thu, 13 Sep 2018 15:43:24 GMT

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)

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

gauravepi — Thu, 13 Sep 2018 15:58:18 GMT

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

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

somesoni2 — Thu, 13 Sep 2018 16:12:20 GMT

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

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

gauravepi — Thu, 13 Sep 2018 16:19:29 GMT

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

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

somesoni2 — Thu, 13 Sep 2018 20:55:28 GMT

Is the name of the primary key field PrimaryKey?

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

gauravepi — Fri, 14 Sep 2018 16:59:43 GMT

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