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
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
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
Is the name of the primary key field PrimaryKey
?
No it's name is different i have just added as an example
You need to include a function after the pipe. Try using eventstats
or stats
to do this
eventtype=mytest
| eventstats sum(mainrate) AS TotalMinRate
I tried that as well but it is not working , can we use foreach to get this done?
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
I have tried both stats
and eventstats
but both are helping me to sum all the key values.
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.
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)
Yes it is working for numeric values ,
Those are Json values might possible splunk is considering them as a String