What are you trying to achieve?
I have “On” and “Off” data coming from a thermostat, also from wifi smart plugs.
I want to be able to sum up/count the number of devices that are on, and then multiply them by a number. There’s two scenarios, one is just think of adding up the wattage of devices plugged in to the smart plugs. The other is similar, but with know values for cfm, be able to add up the cubic feet per minute of air handling from purifiers and hvac system.
How are you trying to achieve it?
Here’s an example that is close to working:
Query A:
select count(“relay_state”) * 50 as “cfm” from (select last(“relay_state”) AS “relay_state” from “kasa” group by “alias”) where “relay_state” = true GROUP BY time(3m) fill(null)
Query B:
select count(blower) * 1200 from t9 where blower != ‘Off’ GROUP BY time(2m) fill(null)
Expression C: (Math)
$A + $B
What happened?
It’s close to working, except I think the polling data for the t9 thermostat has some gaps. Where there are gaps, even if the blower was on, the time series graphs it as 0. However, if I change fill to (none) then it is always 1200 even when the blower is off.
What did you expect to happen?
See previous
Can you copy/paste the configuration(s) that you are having problems with?
See above
Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.
No
Did you follow any online instructions? If so, what is the URL?
No specific URL. Using ChatGPT and some trial and error
For the overnight hours until 11:34, the blower/HVAC was running all of the time, but there are examples where the polling must have failed so there are gaps in the data which graph as 0
I think what would be idea, is if there is a gap in the data, use the last known value (so you wouldn’t see those dropouts overnight when it is on continuously, same anytime there is a gap in the data)
The t9 data come from a remote API, so it could be an internet connection or other issue. the kasa data come from local lan, so they are more reliable.
Data source is Influx DB … it’s fed by bash scripts that are using either python-kasa or some custom scripts I wrote that do curl’s to Honeywell’s (thermostat) API.
I would be happy to use transforms if that makes better sense, they are more confusing to me since I haven’t used them much yet, but certainly open to it if that’s a better way.
For now I am kind of okay with setting the time to time(10m) in Query B, it seems long enough to cover the drop outs, I’m polling the data every 2 minutes so connection or API must have dropped for up to 8 minutes overnight, when I set to 10m, the lines are smooth but ideally I’d rather not have that lag/loss of resolution in the data.
So I think the transformation that I mentioned would not work for what you are trying to do, which is fill in gaps in the data. I would work nicely if you just wanted to add the last value from each query to produce Total CFM, but I think you want to see a time series graph.
For that, perhaps you could play around with these settings to see if / how your graph improves?
Thanks for your help here. I’m not sure if this is purely a grafana question or more an influx ql question, so I’ll ask here in this thread but look elsewhere if I need to.
I think the issue comes down to (and the reason connecting nulls won’t work) is that if during the time(X) bin window, if during that time there is no data point, then the query I have uses count and would count that as a 0, so it’s not a null actually.
So the solution seems to be something like instead of just select last, there needs to be a way to return a null from the second select statement if there were no matching data points during the time period of the bin. Is that possible?
Even so, I’m not sure how the outer/first select would handle that. If count(null) returns null and null * number equals null then I think grafana would be able to connect the nulls. Or if there is another way for the outer select to return a null when there were no matching points during the bin period. That seems to be the bottom line. Count is causing the drops because counting relay states where relay state = true results in a 0 when relay state is false, but count where relay state is true also returns a 0 when there are no points in the bin period.
I also have explored scenarios similar to what you describe and concluded that InfluxQL is not capable of doing these types of more complex “if there is no data point, then the query…” functions. Maybe someone has done it (incl. using Grafana expressions built from InfluxQL queries), but I have not seen it.
Have you considered moving to Flux? It takes some time to learn, and despite it being “in maintenance mode” by InfluxData, it should live on well into Influx 3.0 (which uses SQL) via this project.
