I have a series of data points that are captured by telegraf and stored in an Influx db. The data points, call them c1 and c2, are stored together in single records in influx against the time. The requirement is that I combine these two values, which are either 0 or 1, and display them in a Grafana graph to show the trend, with an alert set to 1.5, to monitor whether either of them go to 0.
time c1 c2
2019-01-14T10:00:00Z 1 0
2019-01-14T10:00:10Z 1 1
2019-01-14T10:00:20Z 0 1
2019-01-14T10:00:30Z 1 1
2019-01-14T10:00:40Z 1 1
So, my query seems fairly basic:
select c1 + c2 from table1
which returns either 0, 1, or 2.
The problem is that:
a) my retention policy is 30d
b) the data is updated every 10s
which means there are almost 250k records in the table. That doesn’t seem too bad but I have 6 of these on one dashboard and the dashboard is updating every 15s - so things start to slow up.
I’d like to use last and timeFilter but not having much luck. I’ve tried:
select cs1 + cs2 from (select last(c1) as cs1, last(c2) as cs2 from table1 where time > now() - 1m)
which influx is happy with, but transposing it to Grafana as:
select cs1 + cs2 from (select last(c1) as cs1, last(c2) as cs2 from table1 where $timeFilter GROUP BY time($__interval))
I don’t see a graph.
What am I doing wrong? Any ideas?