topic Re: how to show accumulated sum over bin of time in Splunk Search

how to show accumulated sum over bin of time

ericyeh1995 — Mon, 26 Jun 2017 06:36:01 GMT

I would like to calculate the accumulated energy used over a period of 15 minutes. The sum has to start around min%15==0 (hh:00, hh:15, hh:30, hh:45). The plot should look similar to a sawtooth plot with accumulated power reset to 0 every 15 minutes.

Currently, I am able to create an accumulated plot for a specific window:

host=pm_energy  | reverse | accum d.Act_power_realtime{} as tot_pow | timechart last(tot_pow) span=20s

But I couldn't get it to reset every 15 minutes. I want my graph to look like this:

Where the data used in the above graph is calculated in a separate program.

Re: how to show accumulated sum over bin of time

rjthibod — Mon, 26 Jun 2017 12:12:47 GMT

You can use streamtstats and the time_window option.

http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/streamstats

host=pm_energy | streamstats time_window=15m sum(d.Act_power_realtime{}) as tot_pow | timechart last(tot_pow) span=20s

Re: how to show accumulated sum over bin of time

ericyeh1995 — Tue, 27 Jun 2017 00:51:25 GMT

I got an error saying by using the time_window the input has to be in sorted in time order. Any suggestion to fix this?

Re: how to show accumulated sum over bin of time

DalJeanis — Tue, 27 Jun 2017 02:05:22 GMT

Try this -

 host=pm_energy  
| reverse
| rename COMMENT as "Above gets the records in time order"

| rename COMMENT as "Set each 15 minute time period as its own pulse of time" 
| eval timepulse=floor(_time/900) 

| rename COMMENT as "Accumulate power for each pulse" 
| streamstats current=t sum(d.Act_power_realtime{}) as tot_pow by timepulse 

| rename COMMENT as "Drop unneeded data and present results" 
| table _time tot_pow
| timechart last(tot_pow) as tot_pow span=20s

Notes - You might want to consider whether you want 8:15 exactly to be the beginning of 8:15:00.000-8:29:59.999 or the end of 8:00:00.001-8:15:00.000

Re: how to show accumulated sum over bin of time

ericyeh1995 — Tue, 27 Jun 2017 08:09:48 GMT

Thank you! This works great!

Re: how to show accumulated sum over bin of time

rjthibod — Tue, 27 Jun 2017 11:15:21 GMT

Probably add |sort 0 +_time after the initial search filter.