I am using Grafana 4.6.3 with a Postgres datasource. I am successfully graphing a count over time, and using the Legends facility to display the Average, Max and Min values below the graph.
I wondered whether there was a way to automatically plot the Average as a line on the graph, given that this is already calculated by Grafana for the selected time period selected.
Perhaps this should be considered as a request to be added directly into the Display > Threshold section.
Thanks for responding. Calculating the Average is trivial enough, it’s just hard to have that average returned in a manner which makes it easy to plot on the Graph thats all.
Refer to the postgres time series queries documentation and have a look at the $__timeGroup macro. Given that you can do a sum(value) / count(value) to calculate averages over time.
At least in my head this should work even if I’m not a postgres expert, rather MsSql.
SELECT ROUND(AVG(daycount)) AS "Average"
FROM (
SELECT date_trunc('day', observation_date) AS day, COUNT(date_trunc('day', observation_date)) AS daycount
FROM global
WHERE $__timeFilter(observation_date)
GROUP BY day) count
;
This returns a single value, which I want to plot on the graph. However, because this does not return a corresponding timestamp column, I am unable to plot it. The blue line is what I am trying to achieve:
To plot the average in graph as line you must provide average per time unit. If I understand your query correctly you’re doing kind of average per day?
But the only value you’re using are observation_date. That feels a bit weird to me so not sure what you’re trying to accomplish. What does the non-average query look like (in yellow)?
I am wanting to plot the average based on the time filter specified. I know Grafana already calculates the average, because this is handily displayed as part of the Legend.
The data I am plotting only creates a new row in my DB when an event happens. So a count of the observation_date is a count of an event. The blue line is what I am actually trying to draw - this is what I need help with.
Yes, but that’s only a calculated value without any time information based on the series rendered in current time range. You cannot plot this value in the graph.
In order for you to plot the blue line you need to have at least one metric (time + value) in the beginning of time range and one metric (time + value) at the end of the time range. Period.
Well, you can always create a feature request issue if you really want this feature. No guarantee that it will be implemented, kind of depends on if there are a lot of other users wanting this feature.
