Continuous query prefixes with Grafana

Telegraf
,
1

Similar to existing issues, both here on and GitHub (#7332,
#7858, etc., I am fighting with the impedance mismatch between InfluxDB and sensibly querying my data in Grafana.

I was naively hoping that with CQ’s set up, I can have a single query in Grafana and just see my data become more and more course-grained over time. It appears that this isn’t how life works.

Desired outcomes

I’m keen on seeing:

  1. What others are doing
  2. If my continuous queries are sane; and if so
  3. If my proposed suggestion is workable

Issues

There are two key issues:

  1. Fields created by continuous queries are prefixed by the aggregation function. A field called biscuits, aggregated by the CQ, is named mean_biscuits. This prevents any query reuse in Grafana.

  2. I cannot see a way to query multiple retention policies at the same time. Unless I’m misinterpreting, #2625 indicates that this may just not be possible.

I already have five dashboards in Grafana with around 20 graphs total (excluding repeaters). This is only going to grow, so this behaviour is untenable if I need to repeat every graph three times in order to display data across each of my three retention policy.

My retention policies

Here are my retention policies:

CREATE RETENTION POLICY "48_hour" ON "OnlineAPI" DURATION 48h  REPLICATION 1 DEFAULT
CREATE RETENTION POLICY "6_month" ON "OnlineAPI" DURATION 180d REPLICATION 1
CREATE RETENTION POLICY "archive" ON "OnlineAPI" DURATION 0s   REPLICATION 1

And the associated CQs:

CREATE CONTINUOUS QUERY cq_15m ON OnlineAPI BEGIN SELECT mean(*) INTO "6_month".:MEASUREMENT FROM "48_hour"./.*/ GROUP BY time(15m), * END
CREATE CONTINUOUS QUERY cq_1h  ON OnlineAPI BEGIN SELECT mean(*) INTO "archive".:MEASUREMENT FROM "48_hour"./.*/ GROUP BY time(1h),  * END

The reason I am using wildcards is that I have around 30 measurements, each with between 1-5 values, and these measurements can increase with each release. I don’t want to burden the development team on having to manually update CQs each time they add a measurement.

Potential solution

I understand that the field prefix issue is only because I’m using wildcards. To avoid burdening developers who aren’t directly using InfluxDB, I was considering creating a tool which ran nightly. The tool would drop all existing CQ’s, query all measurements for the selected database, identify all fields and create continuous queries explicitly listing fields. Would this work? What do others do?

Unless I am grossly misusing CQs, as it stands CQs and Grafana only seem suited to the most trivial of use cases.

2

I’ve worked around both issues.

Based on the following:

  1. InfluxDB doesn’t complain about missing fields in queries
  2. My measurements are all created programmatically and have a fixed subset of field names

I’ve modified my CQs to list all of these fields instead of a wildcard, together with aliases to fix the default InfluxDB naming. If the values are not present in a measurement, they will be ignored.

e.g.

CREATE CONTINUOUS QUERY cq_15m ON OnlineAPI BEGIN SELECT mean(value) AS value, mean("count.hist") AS "count.hist", mean(last) AS last, mean(max) AS max, mean(mean) AS mean, ...

Now I can create a template variable for each dashboard in Grafana to list all retention policies:

SHOW RETENTION POLICIES ON "OnlineAPI"

and modify my queries to suit. It’s not as nice as I was hoping, but appears to do the job.