Combining measurements from distributed monitoring setup

[acloaf]

My solar installation has two groups of panels that feed separately and are monitored separately. There are 12 panels on my house, which feed into my house panel, and there are 18 panels on my detached garage, which feed into the subpanel that is in my garage. Each panel has its own monitor setup, but I have the monitor on the subpanel set up to send it's data to influx on the monitor on the main panel. I have disabled influx and grafana on the monitor on the subpanel.

I can verify that the data is properly logging to influx but my Solar/Production power chart only shows the production from the array feeding the main panel. Additionally my Max production only displays the max from either of my arrays, not the combined max. I may have to get more familiar with Grafana but I'm so far not able to manipulate any of the formulas to get it to display properly.

[David00]

Hi @acloaf, great use of distributed monitoring! We can definitely get Grafana working the way you want so it shows production totals from both systems.

To start, what you described is working as intended. To combine the readings from multiple power monitors, we have two options -

1. Create a Continuous Query in Influx to automatically do the combination every N seconds/minutes, which does the math for you, and then saves the result in a new table, which you can then have a simply query in Grafana...

Or...

2. Modify the Grafana dashboard to combine the results from multiple queries. Note that this is done on demand, in real time, every time you pull up the dashboard, so it can get a little bit compute-intensive if you're looking back at longer intervals.

The Net Power and Home Power Usage figures are also going to need some attention too, because they rely on the total of all production sources. So, we'd have to create a continuous query (or modify the Grafana dashboard queries) for those, too.

Do you know which one you'd like to do? Either way, I'll help you get setup. My recommendation would be the Continuous Query method because it's more efficient and the Grafana changes will be a lot simpler, but no harm in trying the Grafana-only method!

[acloaf]

I would prefer the continuous query method. I have been able to combine multiple queries in Grafana for some of my dashboard items with moderate success but there are a couple panels that I haven't been able to solve within Grafana. The efficiency of doing it in Influx is a far greater appeal.

The 60 day history chart is one in particular that I'd really like to make work properly.

[David00]

Fantastic. Have you changed the "name" in config.toml for of either power monitor? If so, what are they?

Also, what are the channel numbers for the production sources on both power monitors?

I am working on putting together some queries to test with, and then we can create the Continuous Queries from those.

[acloaf]

The "name" in each config.toml file has been changed to "house" and "shop" respectively.

House has mains on CT1 and CT2, and the solar array on CT3.

Shop has solar array on CT1.

[David00]

Ok, try this, as a test, in a new Time Series panel in Grafana, to make sure it looks like what you'd expect as the sum of both production systems. You'll have to go into the "raw edit" mode of the Grafana query builder to paste this in.

select SUM("power") AS total_production FROM raw_cts WHERE ((id='house' AND ct='3') OR (id='shop' AND ct='1')) AND $timeFilter GROUP BY time($__interval);

If it looks good, we can move onto creating a continuous query for it.

[acloaf]

I assume that the total production should come out as watts. The totals don't make sense to me unless I'm reading it improperly.

For reference, at the time of the screenshot, actual output of my solar is currently 8kW and has produced around 13.5kWh so far today (according to APSystems EMA).

[David00]

Ahh, I see. Can you change the time($__interval) to time(1s)? That should fix it. Right now it's showing the sum of power for a 30 second window because Grafana has that dynamic interval setting, and it chose 30 seconds.

[acloaf]

It corrects the number, just makes the chart a little messy.

[David00]

Ok - let's see if it looks better if the values are averaged out over a 5 second interval with this one:

SELECT mean(total_production) FROM (select SUM("power") AS total_production FROM raw_cts WHERE ((id='house' AND ct='3') OR (id='shop' AND ct='1')) GROUP BY time(1s)) WHERE $timeFilter GROUP BY time(5s);

[acloaf]

If I change the time interval to "Last 5 minutes":

[David00]

Hmm, closer, but not quite right. I'd really like to figure this out, and I have a few ideas, but it'd be easier if I had access to the data.

Would you be able to share a database export with me? You can generate one starting from yesterday with:

influxd backup -portable -start "2024-08-21T00:00:00Z" -db power_monitor /tmp/backup_dir
tar -czf ~/database-backup.tar.gz /tmp/backup_dir
cp database-backup.tar.gz /var/www/html/

You can then retrieve database-backup.tar.gz from your web browser by going to http://YOUR_PIs_IP_ADDR and clicking on the database-backup.tar.gz file. You should be able to attach it here in a comment.

[acloaf]

database-backup.tar.gz

[acloaf]

Looks like this works. The only change was that I grouped by time($__interval) instead of by time(5s).

SELECT mean(total_production) FROM (SELECT sum("power") AS total_production FROM raw_cts WHERE ((id='house' AND ct='3') OR (id='shop' AND ct='1')) GROUP BY time(1s)) WHERE $timeFilter GROUP BY time($__interval);

edit: it seems to work on the raw kW but when I modify this to integrate it (and /3600000), it doesn't return the correct kWh.

[David00]

Thanks for the data export! This turned out to be a bit trickier than I had first anticipated, but I've got it figured out. The challenge is that Influx v1 is not very well equipped to do operations on data in the fields. Influx v2 is pretty well setup for this with the Flux query language, which Influx v1 supports, but we can't use Flux in continuous queries, so it's a moot point.

To further complicate matters, the shop data and house data has slightly different timestamps because they are sent from different Pis - completely expected/normal, but still an extra challenge. This is what is creating the fuzziness/jagged edge in the data. So, we've got to align the timestamps first before trying to combine them. This will necessitate several CQs to get a total production figure.

Here's a quick explanation on what I've come up with. For both house and shop systems, we'll have power and current metrics, so the below will apply to both metrics:

• Average the shop production and house production individually, every 5 seconds (this is what aligns the timestamps and is key!)
• Sum the figures from shop and house production
• Calculate the energy for shop and house individually, and also calculate the total energy of the two combined

You'll have a new measurement / table for these figures in the database, named total_production. This holds the watts and current (in Amps) for the house, shop, and combined total, updated every 5 seconds, under the following field names:

• house_production_current
• house_production_watts
• shop_production_current
• shop_production_watts
• total_production_current
• total_production_watts

So that takes care of the high resolution real-time data. The next part of this is downsampling these figures into their 5-minute averages, which are stored indefinitely. (The high resolution data is only stored for 30 days). More info on the existing InfluxDB structure here in my docs: https://david00.github.io/rpi-power-monitor/docs/v0.3.0/database-info.html

Since this is all related to solar, and there's already a measurement to hold downsampled solar data, I opted to use the existing solar_power_5m and solar_energy_5m measurements for the new downsampled data.

Inside the solar_power_5m measurement, you will have the following new fields:

• house_production_power
• shop_production_power
• house_production_current
• shop_production_current

Inside the solar_energy_5m measurement, you will have the following new fields:

• house_production_energy
• shop_production_energy
• total_production_energy

---

Configuring InfluxDB with these changes

While this change is only making additions to the database, I'd recommend doing a full backup beforehand. You can use the included backup script after updating the power monitor's config file according to the Enabling Automatic Backups section of the docs. See the note near the end of that page to run the backup manually.

Since continuous queries only start working from the point at which they're created, we'll need to backfill the new fields using the existing data. The backfill process is going to look at just about every single entry in the database and it can be quite disk intensive. I would recommend stopping the power monitors while doing these, so that it's not trying to write to the database at the same time: sudo systemctl stop power-monitor.service

All of these queries should be done from the InfluxDB shell, which you can access with the following command shown below from the terminal.

influx -database power_monitor -precision rfc3339

Paste these lines one at a time in the Influx shell. Do not paste the lines starting with # as these are just comments.

If you get an error on any steps, please stop there and share the error with me here.

Create the high resolution averages for each solar production system.

# BACKFILL for high-res home/shop watts/amps: 
SELECT MEAN(power) AS house_production_watts, MEAN(current) as house_production_current INTO total_production FROM raw_cts WHERE (id='house' AND ct='3') GROUP BY time(5s) fill(0);
SELECT MEAN(power) AS shop_production_watts, MEAN(current) AS shop_production_current INTO total_production FROM raw_cts WHERE (id='shop' AND ct='1') GROUP BY time(5s) fill(0);
# CQ for high-rest home/shop watts/amps: 
CREATE CONTINUOUS QUERY cq_house_production_power ON power_monitor BEGIN SELECT MEAN(power) AS house_production_watts, MEAN(current) as house_production_current INTO total_production FROM raw_cts WHERE (id='house' AND ct='3') GROUP BY time(5s) END;
CREATE CONTINUOUS QUERY cq_shop_production_power ON power_monitor BEGIN SELECT MEAN(power) AS shop_production_watts, MEAN(current) as shop_production_current INTO total_production FROM raw_cts WHERE (id='shop' AND ct='1') GROUP BY time(5s) END;

Downsample the high-res averages into 5 minute intervals.

# BACKFILL for 5-minute downsamples: 
SELECT mean(house_production_watts) AS house_production_power, mean(house_production_current) AS house_production_current INTO power_monitor.rp_5min.solar_power_5m FROM power_monitor.autogen.total_production GROUP BY time(5m) fill(0); 
SELECT mean(shop_production_watts) AS shop_production_power, mean(shop_production_current) AS shop_production_current INTO power_monitor.rp_5min.solar_power_5m FROM power_monitor.autogen.total_production GROUP BY time(5m) fill(0);
SELECT mean(shop_production_watts) + mean(house_production_watts) AS total_production_power, mean(shop_production_current) + mean(house_production_current) AS total_production_current INTO power_monitor.rp_5min.solar_power_5m FROM power_monitor.autogen.total_production GROUP BY time(5m) fill(0);

# CQ for 5-minute downsamples: 
CREATE CONTINUOUS QUERY cq_house_production_power_5m ON power_monitor BEGIN SELECT mean(house_production_watts) AS house_production_power, mean(house_production_current) AS house_production_current INTO power_monitor.rp_5min.solar_power_5m FROM power_monitor.autogen.total_production GROUP BY time(5m) END;
CREATE CONTINUOUS QUERY cq_shop_production_power_5m ON power_monitor BEGIN SELECT mean(shop_production_watts) AS shop_production_power, mean(shop_production_current) AS shop_production_current INTO power_monitor.rp_5min.solar_power_5m FROM power_monitor.autogen.total_production GROUP BY time(5m) END;
CREATE CONTINUOUS QUERY cq_total_production_power_5m ON power_monitor BEGIN SELECT mean(shop_production_watts) + mean(house_production_watts) AS total_production_power, mean(shop_production_current) + mean(house_production_current) AS total_production_current INTO power_monitor.rp_5min.solar_power_5m FROM power_monitor.autogen.total_production GROUP BY time(5m) END;

Create the high resolution total production (sum of both house and shop systems).

# BACKFILL for high-res totals:
SELECT MEAN(house_production_watts) + MEAN(shop_production_watts) AS total_production_watts INTO total_production FROM total_production GROUP BY time(5s) fill(0);
SELECT MEAN(shop_production_current) + MEAN(house_production_current) AS total_production_current INTO total_production FROM total_production GROUP BY time(5s) fill(0);
# CQ for high-res total: 
CREATE CONTINUOUS QUERY cq_total_production_power ON power_monitor BEGIN SELECT MEAN(house_production_watts) + MEAN(shop_production_watts) AS total_production_watts INTO total_production FROM total_production GROUP BY time(5s) END;

Remove the no-longer needed default solar power CQs

DROP CONTINUOUS QUERY cq_solar_power_5m ON power_monitor;
DROP CONTINUOUS QUERY cq_solar_energy_5m ON power_monitor;

Create the production energy figures - three total - one for house system, one for shop system, and one for the total between both.

# BACKFILL for 5-minute energy calculations:
SELECT integral(house_production_watts) / 3600000 AS house_production_energy INTO power_monitor.rp_5min.solar_energy_5m FROM power_monitor.autogen.total_production GROUP BY time(5m) fill(0);
SELECT integral(shop_production_watts) / 3600000 AS shop_production_energy INTO power_monitor.rp_5min.solar_energy_5m FROM power_monitor.autogen.total_production GROUP BY time(5m) fill(0);
SELECT integral(total_production_watts) / 3600000 AS total_production_energy INTO power_monitor.rp_5min.solar_energy_5m FROM power_monitor.autogen.total_production GROUP BY time(5m) fill(0);
# CQ for 5-minute energy calculations: 
CREATE CONTINUOUS QUERY cq_house_production_energy_5m ON power_monitor BEGIN SELECT integral(house_production_watts) / 3600000 AS house_production_energy INTO power_monitor.rp_5min.solar_energy_5m FROM power_monitor.autogen.total_production GROUP BY time(5m) END;
CREATE CONTINUOUS QUERY cq_shop_production_energy_5m ON power_monitor BEGIN SELECT integral(shop_production_watts) / 3600000 AS shop_production_energy INTO power_monitor.rp_5min.solar_energy_5m FROM power_monitor.autogen.total_production GROUP BY time(5m) END;
CREATE CONTINUOUS QUERY cq_solar_energy_5m ON power_monitor BEGIN SELECT integral(total_production_watts) / 3600000 AS total_production_energy INTO power_monitor.rp_5min.solar_energy_5m FROM power_monitor.autogen.total_production GROUP BY time(5m) END;

---

We will also have to fix the net power figure in the database, using another batch of continuous queries to combine the home_power and the new total_production figures, but let's get this part done first.

Once this is done, you should be able to see your total solar production in Grafana with a simple query like this:

SELECT mean("total_production_watts") FROM total_production WHERE $timeFilter GROUP BY time($__interval) fill(null)

You'll even be able to plot each system individually on the same graph if you wanted to, like this:

[acloaf]

This is fantastic, thanks for looking at this.

I ran into one error, it was the very last cq to be entered.

> CREATE CONTINUOUS QUERY cq_solar_energy_5m ON power_monitor BEGIN SELECT integral(total_production_watts) / 3600000 AS total_production_energy INTO power_monitor.rp_5min.solar_energy_5m FROM power_monitor.autogen.total_production GROUP BY time(5m) END; returned this: ERR: continuous query already exists

In following the naming pattern you established, I changed the name of that CQ to cq_total_production_energy_5m.

[David00]

Great! I should have put the DROP statements before the creations to avoid that error, but renaming it is perfectly fine.

Next we'll need to create CQs and backfills for the home power and the net power with the same approach. Can you share another database export (same command as last time, starting at the same datestamp)? That way I can make sure I'm working off what you have.

[acloaf]

One other note, I commented out the lines in power_monitor.py (on both devices) that created the two CQs that we dropped as they were recreated immediately upon restarting power-monitor.service.

Here's the updated DB backup

database-backup (1).tar.gz

[David00]

Hey @acloaf, has the power monitor been running? I am not seeing any newer data in the database export.

[acloaf]

It's been running, I used exactly the same export commands. I'll try again.

[acloaf]

database-backup.tar.gz

[David00]

That's odd - for some reason, it's not including any additional data. Can you try to remove /tmp/backup_dir first with rm -r /tmp/backup_dir? Maybe it's refusing to backup to the directory that has old stuff in it.

[acloaf]

/tmp/backup_dir removed, database-backup.tar.gz removed from home folder, creating export again:

database-backup.tar.gz

[David00]

So odd - still the same for me. Maybe try to remove the start and db flags, so just:

influxd backup -portable  /tmp/new_backup_dir
tar -czf ~/new-database-backup.tar.gz /tmp/new_backup_dir
cp new-database-backup.tar.gz /var/www/html/

[acloaf]

file size too big to upload. I'll try adding back the -db flag.

edit: file size still too big to upload after adding back the -db flag

[David00]

Ok - that's a good thing! All the previous DB exports were about 5 MB.

If that doesn't work, I'd suggest to add back in the -start flag with the same 8/21 timestamp, but leave the -db flag off. I wonder if the Influx backup tool somehow excludes certain things when -db is specified.

[acloaf]

adding back the -start flag reduces the file to 5MB

it's close to 90MB without that flag

[acloaf]

This is 88MB. Generated with -db flag but not -start

Google Drive link

[acloaf]

I'm trying to get my head wrapped around my power data as well.

CT1 & CT2 are on each leg of my main service, at night (no production) they are both positive as expected, but during the day, once I have solar power production, CT1 goes into the negative power as expected, but CT2 does the exact opposite and increases in the positive direction proportional to my power generation.

What confuses me about this even more is that the current displays in the expected direction. I can't figure out why the power and the current diverge in direction only on CT2.

Edit: for more information, this is the only CT I have that is configured as reversed = true. I did this because I had a negative power when I was calibrating in the evening after the sun went down and had no production. The arrow on the CTs on the main service are pointing 'from' the grid towards my panel. The +/- termination is the same order as CT1.

[David00]

Sorry about the wait! I was away over the long Labor Day weekend and am revisiting this now.

The new database export worked this time.

For your CT2's power appearing opposite, I think we can get to the bottom of that pretty easily. It is normal (for us in North America) to have to set one of your mains channels as reversed, because it actually is reversed from the other phase in our split-phase setups.

Can you share a screenshot of the query used for the panel labeled Main B?

The power monitor does do a couple of things to modify the sign of the power measurement while calculating the power. I want to make sure you have the latest version of the software because there was a small fix I did a couple months ago. In your terminal, can you do the following?

cd ~/rpi_power_monitor/
git status

If git status shows "Not currently on any branch", you'll have to do this one-time modification to be able to update the power monitor software.

sed -i 's|+refs/tags/v0.3.*:refs/tags/v0.3.*|+refs/heads/*:refs/remotes/origin/*|g' .git/config
git fetch
git checkout master

If the output of git status does not show the "Not currently on any branch", can you just do git fetch and git pull?

[acloaf]

Reversing the CT made sense to me when I did it because I'm familiar with our residential power setups. What surprised me was that the power is showing as though I'm consuming more on that leg once the power production ramps up.

git status showed "not currently on any branch" so I ran all three commands.

Here's the query for the panel labeled Main B:

[David00]

Ok - quite odd. The query looks very straightforward and we've now ruled out Grafana. Let's generate a plot of the raw data that the power monitor grabs - this will tell us if the CT1 and CT2 data is actually negative or positive right now.

sudo systemctl stop power-monitor.service
cd ~/rpi_power_monitor/rpi_power_monitor
python3 power_monitor.py --mode plot --samples 10000

In the output of the --mode plot command, you'll see a link to the plot that gets generated. Can you please view the plot, save the actual HTML file itself (either your browser with file -> save when viewing it, or by copying out the html file from /var/www/html/)? I'll need the HTML file (as opposed to a screenshot) to drill down into the data in the interactive plot.

Then, you can restart the power monitor with sudo systemctl start power-monitor.service.

[acloaf]

Generated-Plot_09-05-24_195150.zip Wouldn't upload html so i zipped it

[David00]

Thanks. The plot shows CT2's current waveform as negative with respect to the voltage wave - this just means that CT2's power measurement, at the time you generated the plot, would be negative without setting reversed = True.

However, power and current should always have the same sign, so I am thinking this is a bug, and I already have a suspicion in the code. I'll give you an updated power_monitor.py file to drop into your Pi to see if it resolves the issue, and if it does, I'll push a fix to the repo.

[David00]

Can you please drop this file in and replace your existing power_monitor.py located at ~/rpi_power_monitor/rpi_power_monitor/power_monitor.py?

The .txt extension will need to be removed - Github didn't like the .py extension.

power_monitor.py.txt

Once replaced, you'll have to restart the power monitor with sudo systemctl restart power-monitor

[acloaf]

Looks as I expected now. I'll check again when the sun goes down.

[acloaf]

Noting one issue. My production current is excessively high.

[David00]

Can you try adding another query to that panel and pulling from the total_production measurement we created to see if it looks better?

SELECT mean("total_production_current") FROM total_production WHERE $timeFilter GROUP BY time($__interval) fill(null)

[acloaf]

For some reason that doesn't even display a graph. I am trying to figure out why but haven't got it yet.

Edit: If I select shop_production_current or house_production_current I get the graph I expect. I get no data from total_production_current

Edit 2:

cq_total_production_power CREATE CONTINUOUS QUERY cq_total_production_power ON power_monitor BEGIN SELECT mean(house_production_watts) + mean(shop_production_watts) AS total_production_watts INTO power_monitor.autogen.total_production FROM power_monitor.autogen.total_production GROUP BY time(5s) END

I don't have total_production_current on the autogen RP, only on rp_5min. I'll need to add that.

[acloaf]

Added total_production_current to that cq last night. This morning it looks like we've got a good start. This is way better. I know home_load still isn't correct but we're getting there.