Division of two metrics over time in InfluxQL

InfluxDB 2 Dashboards
, ,
1

I’m visualizing the following query in Grafana:

SELECT 1 - COUNT("down_count") / (COUNT("down_count") + COUNT("up_count"))
AS "availability"
FROM (
	SELECT "value"
	AS "down_count"
	FROM "metrics"
	WHERE ("performanceLabel" = 'packetloss' AND "host" = 'server' AND "region" = '$region' AND "value" >= 80) AND $timeFilter
), (
	SELECT "value"
	AS "up_count"
	FROM "metrics"
	WHERE ("performanceLabel" = 'packetloss' AND "host" = 'server' AND "region" = '$region' AND "value" < 80) AND $timeFilter
) fill(0)

It looks at the packet loss for every server within a region and divides the down time by the up time to return a single availability percentage for whatever time interval I have selected in Grafana.

Instead of returning a single percentage, I want to return the availability grouped by time intervals so it can be graphed in a timeseries. However, when I try (for example)

SELECT 1 - COUNT("down_count") / (COUNT("down_count") + COUNT("up_count"))
AS "availability"
FROM (
	SELECT "value"
	AS "down_count"
	FROM "metrics"
	WHERE ("performanceLabel" = 'packetloss' AND "host" = 'server' AND "region" = '$region' AND "value" >= 80) AND $timeFilter GROUP BY time($__interval)
), (
	SELECT "value"
	AS "up_count"
	FROM "metrics"
	WHERE ("performanceLabel" = 'packetloss' AND "host" = 'server' AND "region" = '$region' AND "value" < 80) AND $timeFilter GROUP BY time($__interval)
) WHERE $timeFilter GROUP BY time($__interval), fill(0)

Grafana returns InfluxDB Error: only time() calls allowed in dimensions. I suspect I’ve mangled the syntax for applying the time interval grouping, but I’ve tried various versions with no success. Can anyone tell me a better way to do this, or if this is even possible?

2

I realize I am quite late to be replying, but hopefully this information will prove useful. (Caveat: I do not know the source of the error message, and I did not try what I’m about to suggest. Nonetheless, I have performed similar operations in the past, so I expect my statements below should be correct–or at least close to the mark.)

The inner queries have SELECT “value” combined with GROUP BY TIME. If one groups by time, then an aggregation function needs to be applied to the selected field. You have two options to fix the inner query: apply the count aggregation function or remove the group by time. (If you apply the count aggregation function, then the outer query needs to be modified.)

Side Note: When using Select “value”, the where clause should identify a single time series. (Thus, for example, the $region variable should contain a single value.) If the where clause can return multiple time series, then an aggregation function needs to be applied to the selected metric.

The outer query should have a space between “time($__interval)” and “fill(0)” and no comma. Remove the comma. (Also, I personally would use fill(none), not fill(0).)

I hope this information helps.