topic Re: Historical trending with timecharts in Splunk Search

Historical trending with timecharts

pnoixbeurre — Mon, 28 Sep 2020 11:43:16 GMT

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?

Re: Historical trending with timecharts

Ayn — Tue, 24 Apr 2012 21:25:49 GMT

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?

Re: Historical trending with timecharts

pnoixbeurre — Wed, 25 Apr 2012 00:20:58 GMT

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.

Re: Historical trending with timecharts

Ayn — Wed, 25 Apr 2012 06:31:54 GMT

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?

Re: Historical trending with timecharts

pnoixbeurre — Thu, 26 Apr 2012 20:31:39 GMT

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.

Re: Historical trending with timecharts

sowings — Mon, 30 Apr 2012 18:59:56 GMT

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....

Re: Historical trending with timecharts

sowings — Mon, 30 Apr 2012 19:01:40 GMT

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