I’m trying to calculate a percentage of time in a Gauge as follow:
SELECT("Alarm") / (${__to:date:seconds}-${__from:date:seconds} - "NA") * 100 AS "AlarmTime" from (SELECT Sum("TotalAlarmTime") as "Alarm", Sum("NotActiveTime") as "NA" FROM "$Database"."autogen"./^$Device$/ WHERE $timeFilter fill(0))
Consider that ${__to:date:seconds}-${__from:date:seconds} return the selected interval in seconds, so if I select 24hours I will have 86400 as result.
The problem is: if there are no measurements of “NotActiveTime”, the denominator of the fraction will be “No data” but I expected 86400 - 0 = 86400!
I’ve tried adding “fill (0)” but that didn’t seem to make any difference.
What am I doing wrong?
Grafana version is 8.0.3
InfluxDB version is 1.8.4
fill(0) won’t magically cause the return of a zero if there’s no data - you’ll need to add a GROUP BY time(...) clause to your inner query also. Then for any periods without readings, the query should return zero. If I’m not mistaken, you’ll need to actually put the above-mentioned as yet another nested query (so 3 levels), and take the sum() of all grouped values at the “middle” level of nesting. Not sure if I’m making sense, but happy to try to explain better.
Disclaimer: there may well be a fundamentally better way to get what you need than what I’m describing; this is just the first thing that comes to mind.
Hello svetb, thank you for your reply.
I think your suggestion can improve the query but the result is not the expected (I still have 0 at denominator for some devices).
I’ve edited like:
SELECT "SumAlarm" / (${__to:date:seconds}-${__from:date:seconds} - "SumNA") * 100 AS "AlarmTime" FROM (SELECT Sum("Alarm") as "SumAlarm", Sum("NA") as "SumNA" FROM
(SELECT "TotalAlarmTime" as "Alarm", "NotActiveTime" as "NA" FROM $Database"."autogen"./^$Device$/ WHERE $timeFilter fill(0)) GROUP BY time($__interval))
In addiction, how can modify the query in order to show every devices in panel, even those who haven’t readings of “TotalAlarmTime”?
I think your GROUP BY clause is one level “too high” in the query hierarchy - it needs to be at the same level as the fill() statement, i.e. inside the brackets to the left of it.
I’m afraid I don’t think that’s possible with Influx. When faced with a similar issue, we resorted to having a separate service that generates synthetic “heartbeat” data for devices that should be shown but are not themselves actively generating data. That’s obviously far from an ideal solution. Another thing you could try is to extending the timespan of your queries far enough back that it picks up data from all devices (not just those who have generated data recently).
