topic timechart: average out value over missing time in Splunk Search

timechart: average out value over missing time

suarezry — Tue, 31 May 2016 14:07:01 GMT

I have bills that come in at irregular periods. Here is an example for 1 type:

{name:building1Water, startDate:2015-12-30, Cost:300}
{name:building1Water, startDate:2015-09-30, Cost:100}
{name:building1Water, startDate:2015-08-30, Cost:100}

In this example, the bill for 2015-12-30 covers the months of December ($100) , November($100), and October($100). I would like to average out the Cost over the missing months. What would be the timechart syntax for this?

I would like to see:

 2015-12   building1Water   100
 2015-11   building1Water   100
 2015-10   building1Water   100
 2015-09   building1Water   100
 2015-08   building1Water   100

Re: timechart: average out value over missing time

woodcock — Tue, 31 May 2016 14:14:44 GMT

What are you expecting to see as a resulting dataset to graph?

Re: timechart: average out value over missing time

suarezry — Tue, 31 May 2016 14:34:21 GMT

Thanks woodcock. I have updated my question to indicate what I would like to see.

Re: timechart: average out value over missing time

somesoni2 — Tue, 31 May 2016 15:01:36 GMT

Try like this (assuming _time is set based on startDate field)

your base search | timechart span=1mon values(Cost) as Cost | streamstats current=f window=1 values(_time) as prev_time | eval days=(strftime(_time,"%m")-strftime(prev_time,"%m") | eval Cost=Cost/days | eval temp=mvrange(0,days,1) | mvexpand temp | eval _time=relative_time(_time,"-".temp."mon@mon") | table _time Cost

Re: timechart: average out value over missing time

suarezry — Tue, 29 Sep 2020 09:50:45 GMT

That is exactly what I needed! timechart the cost per day! modifying _time with relative_time was new to me. Thank you!