Splunk Search

timechart: average out value over missing time

Builder

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
0 Karma
1 Solution

SplunkTrust
SplunkTrust

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

View solution in original post

SplunkTrust
SplunkTrust

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

View solution in original post

Builder

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

0 Karma

Esteemed Legend

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

Builder

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

0 Karma