This post describes how to setup IotaWatt on a single-database (multi-tenant) arrangement in Influx.
Previously I described single database setup for Influx, Grafana and IotaWatt. Much of that information is relevant, but changes are noted here.
It’s better practice to use one database for many iotawatts, but can be confusing. Note there are security implications. Experts only!
#bash influx # first time only: CREATE DATABASE phisaver USE DATABASE phisaver CREATE USER reader WITH PASSWORD 'password' WITH ALL PRIVILEGES GRANT READ ON phisaver TO reader CREATE USER writer WITH PASSWORD 'password' WITH ALL PRIVILEGES GRANT ALL ON phisaver TO reader Then, we use tags to differentiate different iotawatts. Hence we get:
It’s also worth doing any simple arithmetic at this stage to ease life later. This is useful for multiple-circuits-of-one-type and sub-boards. For solar setups, it’s helpful to report the export and import separately, based on the a CT on the supply cable as follows. The odd maths is a sneak to simulate a “if less|more than zero” function. More on this below. For example:
Naming of Circuits to Ease the Pain
I do this:
- Circuits: Use nice names, such as “PowerpointsUpstairs” and “LightsOffice“. Note the category is prefixed. It’s nice if there are clearly defined categories (Lights, Powerpoints) but this is not always the case (e.g. mixed circuits), so don’t worry too much.
- Categories: for categories of one, don’t do anything. For categories such as Powerpoints, create a IotaWatt output which sums them together and call it (e.g.) AllPowerpoints.
- Consumption: sum all consumption
- Production: sum all production. Simplest to keep this positive, but should be negative technically.
- Incomer: can be positive (importing)or negative (exporting)
Here is an example:
|HotWater||Circuit and a category|
|PowerpointsGarage||Circuit in the Powerpoints category|
|PowerpointsEnfantsVmc||Circuit in the Powerpoints category|
|PowerpointsAtelSdbBas||Circuit in the Powerpoints categor|
|LightsSousSol||Circuit in the Lights category|
|LightsSalonEscal||Circuit in the Lights category|
|LightsMaison||Circuit in the Lights category|
|Incomer||+ve or -ve ; This is the net power|
|AllConsumption||Sum of circuits|
|Export||Power exporting. Derived - see notes below|
|Import||Power importing. Derived - see notes below|
|SolarConsumed||Solar power used in-house. Derived - see notes below|
Note the convention for positive and negative. Technically for a control-volume / energy balance approach, all production should be negative. Then you can sum everything and get zero. However, it is confusing for people when you graph things later. So I made everything positive. The exception is Incomer (aka Net) which can be positive (importing) or negative (exporting). This is a bit hacky, but conforms to current norms and people’s expections.
To implement this, there are some tricks:
|Import||+ve||Energy from grid||Incomer : Incomer > 0||0 max incomer|
|Export||+ve||Energy to grid.||Incomer : abs(Incomer<0)||0 min incomer x -1|
|SolarConsumed||+ve||Solar energy produced and consumed inhouse.||Solar - Export||solarandbatt + (0 min incomer)|
|Consumption||+ve||Direct CT or sum circuits|
|Incomer (aka "Net")||+ve Import and -ve Export||Consumption - Solar||Direct CT|
Setup InfluxDB Queries
We’re got data in InfluxDB and could just graph that directly. That’s good for fine-grained, recent data. But for longer-term data we don’t want to continuously be summing from power-to-energy. So we create a Continuous Query to do this:
# Once only
CREATE CONTINUOUS QUERY cq_1 ON iotawatt RESAMPLE EVERY 1d FOR 30d BEGIN SELECT integral(Watts, 1000h) AS kwh INTO ilcibne.autogen.energy FROM ilcibne.autogen.iotawatt WHERE units = 'Watts' GROUP BY time(1h), c t, device fill(0) END
CREATE CONTINUOUS QUERY kwh_h ON ilcibne RESAMPLE EVERY 1h FOR 1d BEGIN SELECT integral(Watts, 1000h) AS kwh INTO ilcibne.autogen.energy FROM ilcibne.autogen.iotawatt WHERE units = 'Watts' GROUP BY time(1h), ct, device fill(0) END
This creates hourly energy data with the right tags. It integrates power over time. Some tests shows much better performance and memory usage in InfluxDB using this method, particular for “Total Energy” type queries.
The “resample” syntax forces the query to run every day and process the last 30 days’ data. This processes data uploaded in bulk after an outage. The “kwh_h” query does the same thing, but runs every hour to keep the dashboard up-to-date.
Watchout for a gotcha: if you don’t have data for an interval, the integration will continue the last know value as constant. This can result in overly large (or small) values. This is a known InfluxDB problem. The fill(0) is ignored in the continuous queries. The only solution is to write a complex query to backfill unknown values with 0.
So all the data are in the database, nicely tagged, and we’ll find that graphing is easy. It’s a standard connection to InfluxDB, and pretty straightforward to graph simple metrics. Here are some of the trickier bits
Limiting unnecessary hits on the database
- Check the returned results using “Query Inspector”.
- For single values, use last() or similar
Working with power versus energy
- For power readings, typically the last week is enough. And often it’s only the instaneous reading that’s important. However, I keep all the 10s readings.
- For further back, use energy (kwh/hour, as created in the continuous query) to plot such graphs as “energy per day” and pie-charts.
- In other words, don’t keep calculating the Integral
Using templating for flexibility and re-usability.
My workflow is currently:
- Create a nice dashboard and ‘pull out’ all variables as template variables.
- Export this (with the “templating” box ticked”) to a file
- Import the file to a new job
[[supply]] : ct name of supply (incomer, can be -ve)
[[solar]] : ct name of solar (positive)
[[consumption]] : ct name of consumption total
cts : manually enter circuits
Validate sums and circuits
If you have named the CTs nicely, you can create a multi-value variable which contain a list of the categories. Sum them to confirm they equal the total incomer and total sum of individual circuits. This is often more trouble than it’s worth, but cool:
# Incomer should equal …
SELECT sum("Watts") FROM "iotawatt" WHERE ("device" =~ /^$device$/ AND "ct" =~ /^$total$/) AND $timeFilter GROUP BY time($__interval) fill(null)
# … sum of categories should equal …
SELECT sum("Watts") FROM "iotawatt" WHERE ("device" =~ /^$device$/ AND "ct" =~ /^$cats$/) AND $timeFilter GROUP BY time($__interval) fill(null)
# … should equal sum of circuits …
SELECT sum("Watts") FROM "iotawatt" WHERE ("ct" =~ /^$circuits$/) AND $timeFilter GROUP BY time($__interval) fill(null)
Find the % of a arbitrary target (kwh/day). Note sometimes it’s necessary to force the timezone to be appended (use a fixed variable). This appears to be a bug.
SELECT sum("kwh")/$targetkwhperday FROM "energy" WHERE ("device" =~ /^$device$/ AND "ct" =~ /^$total$/) AND $timeFilter GROUP BY time(1d) fill(null) $timezone
Based on current time period, how much are your yearly bills? ($1 per day + $x per kwh)
SELECT mean("kwh") *8760/4*$DollarsPerImportKwh+$DollarsPerDay*365/4 FROM "energy" WHERE ("ct" =~ /$Consumption/) AND $timeFilter
Based on current time period, how much are your quarter bills? ($1 per day + $x per kwh)
SELECT mean("kwh") *8760*$dollarsperimportkwh+365 FROM "energy" WHERE ("ct" =~ /^$total$/ AND "device" =~ /^$device$/) AND $timeFilter
Find the current (“last”) top n power (Watts) circuits (ct):
SELECT top(Watts,3) as Watts from (SELECT last("Watts") as Watts FROM "iotawatt" GROUP BY "ct" fill(null)) group by ct
Using Templates and Variables – Advanced
If you name your outputs from IotaWatt such that they are separable by RegEx-s (see table of names, above), then you can use dynamic variables (“Query Variables”) to produce – dynamically- lists of outputs to graph in Grafana. For example:
The magic is the query:
show tag values with key="ct" where device=~/[[device]]/ and units=~/Watts/ and ct=~/^Cat/
which produces a list of all outputs from iotawatt (stored in “ct”) for a device (another, constant, variable) which starts with “Cat”. This will work when you reuse the dashboard, provided the output names (eg. CatPower) are correct. Note that “A template variable of the type
Constant will automatically be hidden in the dashboard, and will also be added as an required input when the dashboard is imported.” Therefore, use “Constant” to get it exported.
Alternatively, use a “custom” variable and manually edit the values. Get these from influx:
show tag values with key = "ct"