Splunk Search

how to show accumulated sum over bin of time

ericyeh1995
Explorer

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:
alt text
Where the data used in the above graph is calculated in a separate program.

0 Karma
1 Solution

DalJeanis
Legend

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

View solution in original post

DalJeanis
Legend

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

ericyeh1995
Explorer

Thank you! This works great!

rjthibod
Champion

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

0 Karma

ericyeh1995
Explorer

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?

0 Karma

rjthibod
Champion

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

0 Karma
Get Updates on the Splunk Community!

Join Us for Splunk University and Get Your Bootcamp Game On!

If you know, you know! Splunk University is the vibe this summer so register today for bootcamps galore ...

.conf24 | Learning Tracks for Security, Observability, Platform, and Developers!

.conf24 is taking place at The Venetian in Las Vegas from June 11 - 14. Continue reading to learn about the ...

Announcing Scheduled Export GA for Dashboard Studio

We're excited to announce the general availability of Scheduled Export for Dashboard Studio. Starting in ...