topic How do you chart a cumulative sum? in Splunk Search

How do you chart a cumulative sum?

Marinus — Fri, 15 Jun 2012 14:45:56 GMT

I'm calculating the sum of spending over a month period.

* | timechart sum(value) span=1mon

This will produce the cumalative amount, but it won't show you how you arrived at the amount in day incements. Changing the span to 1 day, doesn't produce the desired result nor does bucketing ahead of the timechart.

 * | timechart sum(value) span=1mon

How do you achieve this without some major delta hack?

Re: How do you chart a cumulative sum?

Ayn — Fri, 15 Jun 2012 14:57:35 GMT

You could use accum to create the cumulative sum and then do a timechart last() on this sum to get the last value at the breakpoint of each interval and finally arriving at the total sum:

... | accum value as totalvalue | timechart last(value) span=1d

Re: How do you chart a cumulative sum?

Marinus — Fri, 15 Jun 2012 18:01:33 GMT

Ironically it produces the opposite result. It's stepping down from the cumulative total.
| accum value as totalvalue | timechart last(totalvalue) span=1d

Re: How do you chart a cumulative sum?

Marinus — Fri, 15 Jun 2012 18:05:22 GMT

The answer is not pretty but it works, thanks Ayn.

enter code here| reverse | accum value as totalvalue | timechart last(totalvalue) span=1d

Re: How do you chart a cumulative sum?

sideview — Fri, 15 Jun 2012 19:52:45 GMT

you want to use the streamstats command.

1) simple example, running the timechart first and using streamstats to create the cumulative total on the timechart output rows.

* | timechart count| streamstats sum(count) as cumulative

2) similar, but with a field value instead of the count:

index=_internal source=*metrics.log group=per_sourcetype_thruput | timechart sum(kb) as totalKB | streamstats sum(totalKB) as totalCumulativeKB

3) If you want to go the other way, and use streamstats on the raw events, you can do that, but then you have to use the reverse command.

index=_internal source=*metrics.log group=per_sourcetype_thruput | reverse | streamstats sum(kb) as cumulativeKB | timechart max(cumulativeKB)

4) And streamstats also allows a 'by' term, so for example it can keep track of all of these cumulative numbers separately by some field value like 'series':

With the streamstats before the reporting command:

index=_internal source=*metrics.log group=per_sourcetype_thruput | reverse | streamstats sum(kb) as cumulativeKB by series | timechart max(cumulativeKB) by series

and last but not leasat, if you want to use the other way and use streamstats after the reporting command, you have to get a little more hands-on with stats and bin.

index=_internal source=*metrics.log group=per_sourcetype_thruput | bin _time span=1h | streamstats sum(kb) as totalKB by _time series | timechart sum(totalKB) by series

Re: How do you chart a cumulative sum?

Jason — Wed, 11 Dec 2013 20:26:14 GMT

This will be a lot more efficient if you do a stats table first, statsing by _time and the value you're interested in. Plus, you won't need a reverse anymore, because the stats table will be ordered by time: search ... | bucket _time span=1d | stats sum(value) as value | accum value as totalvalue | timechart last(totalvalue) span=1d - use the same bucketing of _time as the span in timechart and splunk has even less work to do in the timechart.

Re: How do you chart a cumulative sum?

Jason — Mon, 28 Sep 2020 15:27:52 GMT

I found this to work well: statsing by _time and your split-by field. It is more efficient as you are not potentially reversing hundreds of thousands of events: index=_internal source=*license_usage.log type=Usage | eval MB=b/1024/1024 | bucket _time span=1h | stats sum(MB) as MB by st | streamstats sum(MB) as MB by st | timechart span=1h last(MB) as MB by st is a nice cumulative graph of indexing today by sourcetype. And over a day it creates 25 rows, which is a lot easier to reverse than 800,000 original log lines...

Re: How do you chart a cumulative sum?

royimad — Wed, 19 Nov 2014 08:07:11 GMT

I used the same function but but it is accumulating all the sum of 3 status where i want to accumulate the sum by status. Any idea how can i do that ?
| accum value as totalvalue | timechart last(totalvalue) by STATUS

Re: How do you chart a cumulative sum?

ManasNayak — Tue, 22 Nov 2022 19:21:57 GMT

I too had the problem and I sorted using a simple trick.

Instead of timechart or chart use stats. for the time value, you can use time extract command

Note - Remember to select CumulativeTotal as chart overlay to better show the graph in your search panel.

Here is how you can achieve -

index=<indexname> sourcetype=<sourcetypename> <<search string>>
| eval HourMinute=strftime(_time, "%m/%d %H%p")
| stats count(_raw) as count by HourMinute |

appendcols [searchindex=<indexname> sourcetype=<sourcetypename> <<search string>>
| eval HourMinute=strftime(_time, "%m/%d %H%p")
| stats count(_raw) as count by HourMinute |streamstats sum(count) as CumulativeTotal ]