Splunk Search

timechart: average out value over missing time

suarezry
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

somesoni2
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

somesoni2
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

suarezry
Builder

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

0 Karma

woodcock
Esteemed Legend

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

suarezry
Builder

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...