Subquery not working - moving average of a derivative (network usage)

I want to monitor the bandwidth usage on some links. I use Telegraf / InfluxDB / Grafana for it and it’s working perfectly fine for the actual data collected on the servers. The query I use (in Grafana) is:

SELECT non_negative_derivative(mean("bytes_recv"), 10s) FROM "net" WHERE ("host" =~ /^$hostname$/ AND "interface" =~ /^$interface$/) AND $timeFilter GROUP BY time($__interval) fill(null)

Since the data is very spiky, I also want to monitor the smoothed out average bandwidth usage. Unfortunately, everything I tried doesn’t work. Grafana-GUI gives me:

SELECT moving_average(non_negative_derivative(mean("bytes_recv"), 10s), 10) FROM "net" WHERE ("host" =~ /^$hostname$/ AND "interface" =~ /^$interface$/) AND $timeFilter GROUP BY time($__interval) fill(null)

Trying myself with Subqueries (like the following) doesn’t work either:

SELECT moving_average("rate", 40) FROM (SELECT non_negative_derivative(mean("bytes_recv"), 10s) AS "rate" FROM "net" WHERE ("host" =~ /^$hostname$/ AND "interface" =~ /^$interface$/) AND time > now() - 12h GROUP BY time(1m) fill(null) ) WHERE time > now() - 12h GROUP BY time(1m)

I tried lots of different queries as well but I seem to be missing something general here - and hint and comments on what I’m doing wrong is more than welcome!

1 Like

Ok, I think I resolved this one myself - however, the solution was something else than expected and investigated (I note it down here anyhow for completeness in case someone else should encounter it as well).

I installed InfluxDB from the standard Debian (stretch 9) repositories and didn’t realize that this is a quite old version (in fact 1.0). Only the next Debian version (sid) will bring InfluxDB to version 1.6. So I added the influxdata repo but the upgrade initially showed errors. The solution in the end was to manually uninstall (remove) influxdb-client and then install InfluxDB from the new repo (Version 1.7).

Now nested functions are working finally :slightly_smiling_face: