I'm tracking the performance of a wireless network and need to protocol when it is better or worse than expected. To protocol this I want a Grafana dashboard where the measured values (every 2 hours) are compared to the average measured previously (either moving or over the whole series).
I have tried many different combinations of sub-queries and the like, all not resulting in a working system. I have also tried inner joins (but these were removed?).
Wanted State
This is the closest to what would be desired, and the closest to "should work":
SELECT last("sum") / moving_average("sum", 72) * 100 FROM (
SELECT "metadata_gateways_0_rssi" + "metadata_gateways_0_snr" AS "sum"
FROM "ip6-test-7" WHERE $timeFilter
) GROUP BY "payload_fields_location", time($__interval)
fill(previous)
This doesn't work even if moving_average(mean("sum"), 72) is used, actually using the "advanced syntax" doesn't work at all for me.
Current State
Works but isn't really what is needed:
SELECT last("sum") / mean("sum") * 100 FROM (
SELECT "metadata_gateways_0_rssi" + "metadata_gateways_0_snr" AS "sum"
FROM "ip6-test-7" WHERE $timeFilter
) GROUP BY "payload_fields_location", time(1d)
fill(previous)
Having $__interval as 2h results in the series always being 100 (obviously).
