How to make dashboard graph panel query scale time series values accordingly when time groupping interval changes?
Example:
I’m using InfluxDB datasource with Grafana 4.1.2.
Suppose I have time series data with bytes_per_5min values stored every 5min and I’m interested in bitrate.
For relatively short time range(24 hours) 5 min resolution is OK and query might look like:
SELECT sum(“bytes_per_5min”)*8/300 as ‘bitrate’ FROM “SomeMeasurement” WHERE $timeFilter GROUP BY time(5m),“device” fill(null)
For longer time range (90 days) 5 minutes steps are usually not needed and 1day steps might be enough.
So, the query should look like:
SELECT sum(“bytes_per_5min”)*8/86400 FROM “SomeMeasurement” WHERE $timeFilter GROUP BY time(1d),“device” fill(null)
Thus scaling factor in query must change synchronously with time range and interval changes. Preferably with intervals selected from a predefined set. Can it be done in current grafana?
Related question: if autoscaling describe above is not supported yet is there a way to restrict users from selecting arbitrary too wide time range windows for any given dashboard?
So that fixed-time-window daily-,weekly-,monthly-dashboards can be pre-created for users without the risk of blowing up their browsers or overloading influxdb server.
This auto scaling is done automatically by defaut in grafana with the built in $__interval and (auto) ($interval in older versons). This is set variable depending on time range and how wide the graph is so there is never more data points than the graphg has pixels. This is built and available in default queries and all queries that use these built in variables.
You can set a lower limit for this auto interval using expressions like >5m (in group by time interval field below your queries or in data source edit page).
You can also create a template variable of type Interval where you add an auto option that also adapt acroding to time range and where you can specify how many time buckets the time range should be divided in
Unfortunately $__interval variable (and auto setting) doesn’t not help in my kind of queries:
$__interval variable cannot be used directly in SELECT math expressions (for example, as a scaling factor ). One can’t rewrite influxdb query like
SELECT sum(“bytes_per_5min”) / 86400 FROM “SomeMeasurement” WHERE $timeFilter GROUP BY time(1d),“device” fill(null)
as
SELECT sum(“bytes_per_5min”) / $__interval FROM “SomeMeasurement” WHERE $timeFilter GROUP BY time(1d),“device” fill(null)
$__interval variable takes values out of my control depending on browser window size and resolution while I need group by time steps be multiple of base step (N*5 minutes in my case).
Also, as far as see in browser console, values look like “5m”,“1h”, …, i.e. not integers. This prevents using them in math calculations.
There is no way to restrict possible $__interval values to a set of values defined in a template variable of type Interval or make use interval type template variable as a value for $__interval.
related to previous: $timeFilter variable is not seen outside of graph query editor - right? Hence, user-defined interval type template variables know nothing about currenty chosen time window and there is no way to set a proper interval template variable value depending on current $timeFilter.
So far as a temporary solution I can only define many graphs with hardcorded scaling factors and explicit time ranges in WHERE clause instead of $timeFilter. But that’s not elegant.
$__interval cannot be used in many places, for example as part of graph title or be assigned to template variable.
It would be niceand handy to have more flexible access to other components of time range and interval related variables and in more global context - for use not only in query editor, but in template variables editor/queries, in Text plugin, dashboard and row titles, etc. Smth like $_variableName.fieldName.format - examples: $__timeRange.from.rfc3339 (as string), $__timeRange.from.seconds (as interger seconds from epoch), $__interval.ms (as interger $__interval value in ms). And this would allow me to scale data in my queries as needed.
SELECT
UNIX_TIMESTAMP(DATE_ADD(the_date, INTERVAL the_hour HOUR)) as time_sec,
the_sum as value,
'hourly' as metric
from (
select
DATE(`timestamp`) as the_date,
HOUR(`timestamp`) as the_hour,
SUM(value) AS the_sum
FROM log c
WHERE workerid = "iotDevice1"
AND metric = "count"
AND $__timeFilter(`timestamp`)
GROUP BY 1,2
ORDER BY `timestamp` ASC
) x
Can you be more clear about how to set minimum interval? Perhaps a screenshot or something? When I try to put time(>5s) into my query editor the graph goes blank, but time(5s) results in a functioning graph. I have tried quoting, doing time($__interval>5s) and any other thing I could think of but no matter what I can’t get my graph to render such that it uses auto-scaling, but has a minimum.
The reason I need a minimum is because my data only comes in every 5 seconds, so anything less than that and my graph starts to have holes (and I don’t want to use fill(whatever)).
I’m having similar issues with data stored every 5 minutes. Backend is InfluxDB. An oddity of this data gathering is that when nothing is reported for a long time many zeros are not recorded so there can be large gaps in the data in this case. I don’t think Grafana/Influx like that!
I started graphing with fill(0) and with this, when viewing short periods (last 24 hours for example), because $__interval is calculated to be less than 5 minutes I see ‘spikes’ in the graph. This can be fixed by using fill(null) and connecting the nulls.
However, the above is not a good idea when viewing longer periods when $__interval is calculated to be 5 minutes or more. This is because over longer periods when there really was no data available for my metric I see connecting lines from one point to the next (that could have been days or weeks later). This is because of the fill(null) and connecting nulls. In this case though, fill(0) works perfectly.
So to sum up: for graphing short periods fill(null) works and for long periods fill(0) does.
I can solve this by forgetting all about the $__interval value and always using 5m but this is inefficient and makes graphs over long periods look untidy.
I can solve this by creating a subquery with a 5m mean, fill(0) and then using $__interval on that which looks nice by again seems very inefficient (and also has to be done manually).
Sorry if this is a bit of a noob thing, but it does seem odd that one cannot easily achieve this. What have I missed?
I have the exact problem as @micahzoltu and @robiin - did either of you come up with a suitable solution? I can’t for the life of me figure out how “>5m” applies to anything.
If I could, I’d use the syntax GROUP BY time(max($__interval, 5m) but of course that doesn’t actually work.
Had exactly the same issue. The way I found out to solve is - is to use Max Data Points field in query options which I set to 10 in my example. In that case Interval is automatically calculated for you based on the time range selected:
None of the solutions here worked perfectly for me, but they helped me greatly to figure it out.
In my case, I have an influxdb data source which is collected every 30s, so my minimum interval is 30s. I must set it in query options. It is in the screenshot by @kostyantynartemov But I am not setting max data points. Then in my query I use *(30000/$__interval_ms) as the value modifier.
e.g.: SELECT NON_NEGATIVE_DIFFERENCE(MEDIAN("http_response.5xx"))*(30000/$__interval_ms) as "5xx" FROM "reverseproxy" WHERE ("sv"='FRONTEND' ...) AND $timeFilter GROUP BY time($__interval) It works because the interval is never less than 30s. $__interval_ms is 30000 so the multiplier is 1 until the time period is large enough to increase the interval value.
I am trying to use a Grafana Dashboard Variable as a time interval parameter inside the group by option (config), so that I can change in drop down alone and the grouping is changed quick and easy. But it is not working
I know this is an old issue but it would be nice if there was a solution (or better way of working) for this.
I’m also looking at a more semi-automated way of switching the group by interval, based on the selected time range. In general a more interactive design (aka plotly) would be very handy for fast investigation (drill down to more detailed datapoints) would be very nice.
I’m trying to do some PoCs in Influx+Grafana vs. Plotly add-on based graphs in HomeAssistant, of course each has it pros and cons, hence my request.
Thanks
Auto option can be customized with Step count config. Use that env variable in the groupping definition, e.g. for InfluxDB GROUP BY time($agg).
You will have option to define auto aggregation or to select custom aggregation. Keep in mind that will allow users to select dangerous combination, which may kill your datasource, e.g. 1 sec time aggregation for one year time period.
