Timechart intervals starting NOT on the top of the hour

[bpopssplunk]

If you have created a timechart mapping, say, the number of unique users over time, Single Value will display the most recent result with a trend showing the difference between the most recent result and the previous one (so you could see a drop or climb without needing to remember the previous result). From what I understand this is a new feature in 6.3

We use dashboards that show counts of the last 15 minutes for each stat, refreshed every few minutes. So at 3:23 PM, it’s showing the number of users from 3:08 to 3:23, then when it runs again in a few minutes, it’ll show from 3:11 to 3:26.

But when you run a timechart, the time buckets are calculated starting ON the hour no matter how far back you are looking.

So let’s say my time range is 2:38 to 3:08 and I need to report the last 15 minutes of users against the previous 15 minutes in the Single Value trend with a time range of the last 30 minutes.

earliest=-30m latest=now | timechart span=15min count

This query will return something like the following:

2:30 – 100
2:45 – 200
3:00 – 50

2:30 counts the time between 2:30-2:45, 2:45 is 2:45-3:00, and 3:00 is 3:00-3:15. But as it is 3:08 right now, that means that only the 2:45 bucket contains a full 15 minutes worth of data. The 3:00 bucket is only 8 minutes worth and the 2:30 is only about 7 minutes worth.

2:30 – 100 (about 7 minutes of data)
2:45 – 200 (15 minutes of data)
3:00 – 50 (about 8 minutes of data)

The 3:00 bucket is then what is displayed in the Single Value option as it is the most recent result, with the 2:45 bucket using to calculate the trend, which will almost always show a drop because the primary bucket is only partially full of data.

What we need the timechart to display is instead the following:

2:38 – 110
2:53 – 120

So that it shows 15 minute intervals, but with the first interval starting at the earliest minute in the specified time range rather than starting rounded to the nearest hour or quarter of an hour. So it's 2:38 = 2:38-2:53 and 2:53 = 2:53-3:08.

Is there any known way to change where a timechart STARTS calculating the time from? From what I understand of this new Single Value trend, we MUST use a time-based graph for the feature to become available, so we can't hack a query together that looks at time via evals.

[martin_mueller · Accepted solution]

This isn't meant to be solved with timechart, you'll want to drop back to stats. timechart will not let go of "pretty" buckets.

Here's a rough draft:

earliest=-30m latest=now | addinfo | eval midpoint = (info_min_time+info_max_time)/2 | eval _time = if(_time < midpoint, info_min_time, midpoint) | stats count by _time

This will split your time range into two halves, and bucket the data manually into "first half" or "second half".

[dlisicre]

Also worked a charm for me. Thank you Martin

[bpopssplunk]

That worked perfectly! I didn't realize a workaround for this would be so straightfoward. Thanks, Martin!