Historical trending with timecharts

[pnoixbeurre]

How can you generate a timechart that includes daily per-minute averages for a given metric?

I got this far, but couldn't figure out a way to turn either of these into a timechart.

 [...] earliest=-30d | eval hour_min=strftime(_time,"%H:%M") | stats avg(pause_time) as avg_pause_time by hour_min 

 [...] earliest=-30d | stats avg(pause_time) as avg_pause_time by date_hour date_minute  

Am I going about this the wrong way?

[sowings]

I've done this in the past by converting the time of the events to a string in a given format (stripping away detail), and then the values are just ordered sets over the same X-axis (time) values.

Using your example, I'd |convert timeformat="%I%P" ctime(_time) AS my_hour then you can chart by my_hour.

This might be over simplifying a bit, but the basic gist is "build the data sets with an overlapping X-axis value, then plot by that value." The trick is to get them to sort in "time" order; I was able to do this with advanced charting properties, so this solution is sadly a bit more involved than you might like....

[sowings]

Oh, and you may find chart more to your liking if you've tweaked with the X-axis (_time) values at all.

[Ayn]

Just use the timechart command and set span=1m!

... | timechart span=1m avg(pause_time)

Or am I missing something in your question? Do you want the average for a given time of day, regardless of day?

[pnoixbeurre]

Sure. Thanks Ayn for helping out here.

Take these 2 sets of data points (only showing the first couple hours worth of data)

-- Day 1 --
12am: 1
1am: 2
2am: 2
3am: 4
...

-- Day 2 --
12am: 0
1am: 2
2am: 1
3am: 2
...

The chart that I'm thinking about would contain a single series with the average over both days.

-- Trend --
12am:(1+0)/2 => .5
1am: (2+2)/2 => 2
2am: (2+1)/2 => 1.5
3am: (4+2)/2 => 3
...

This was a simplified example. In reality, I'd be looking for per-minute averages (instead of the hourly averages shown here). Also, it would take in more history, say the last 30 days.

[Ayn]

Ok, you might need to explain more in detail on how you want the results. Do you want a chart that shows individual series for each day in the result set? So you get a chart covering all hours and minutes of a day, and you get one line (or column, or whatever) for today's data, one for yesterday's data, etc etc?

[pnoixbeurre]

My question was unclear. Sorry about that. And yes, I'd like to see the average for a given time of day, regardless of day.

Basically, the purpose of this search is to create a historical trend against which i can compare the values on a given day. For example, I'd want to know if the pause_time values of today are greater or less than average compared to the last month's worth of activity.