Sungrow SG5K-D and HomeAssistant

In December 2021 we decided to install Solar Panels on our house (thanks to Quest Energy in Perth for doing a great job), choosing JA Solar Solar Panels and a Sungrow SG5K-D inverter. Unfortunately there isn't an official Sungrow Inverter integration for HomeAssistant, but it is possible to feed solar data to HomeAssistant by sticking together a few components.

A high-level diagram showing components and data flows is given below:

 

1: SolarIoT connects periodically to Sungrow Inverter using ModBus over TCP and periodically reads the inverter's read registers and holding registers.

2: SolarIoT sends data to pvoutput.org.

3: SolarIoT sends data to a local MQTT broker on a specified topic.

4: HomeAssistant MQTT integration subscribes to the specified topic and makes the Solar Inverter data available via HomeAssistant Sensors.

This setup lets me use HomeAssistant to track my Solar production and energy consumption over the day:

Details of how to configure all the components are below:

Sungrow Inverter

I have a SG5K-D with a wifi module that sends data to iSolarCloud (and Solar Analytics) periodically. It's also possible to connect in to the wifi module and pull down data using ModBus over TCP.

SolarIoT

SolarIoT connects to a ModBus capable inverter and reads registers and then sends the data to other systems. This works well for me as I want to send my inverter data to pvoutput.org, but also want to publish the data to other local systems that will use the solar inverter data. To do the latter I've configured my instance of SolarIoT to publish the data to a local MQTT broker.

I had problems with SolarIoT with my Sungrow SG5K-D where connections would succeed most of the time, but once in a while the inverter would stop responding to ModBus handshakes. I found that I could work around this by doing a TCP connect to the inverter on the ModBus power, waiting a couple of seconds and then disconnecting and I would need to do this whenever the inverter ModBus comms went into a stuck state. I've forked the SolarIoT source here and added a feature to automate the resetting dummy connection when SolarIoT has issues communicating with the inverter (add --sungrow-reset to the command line options to use it).

I also expanded the ModBus map for the SG5K-D so it includes the "daily export energy" register which is needed for HomeAssistant's energy dashboard.

There's also a fix for pvoutput.org with the SG5K-D: the "power meter" value for the SG5K-D measures house power consumption, not the self-consumption of PV energy so this value must not be sent otherwise the data in pvoutput.org will be incorrect.

I'm running SolarIoT on one of my Linux VMs. I'll deploy it into a container eventually, I haven't gotten around to setting it up yet :)

MQTT Broker

I run a MQTT broker (Mosquitto) to manage publishing and subscribing data from various IoT sensors (e.g. Sungrow inverter, Xiaomi / Aqara Zigbee sensors, TP Link Kasa smart switches with power monitoring, OpenSprinkler and a custom battery voltage sensor I built for my powered home sliding gate). SolarIoT publishes payloads to a topic "inverter/stats".

HomeAssistant

HomeAssistant has an MQTT integration but in this case manual configuration in the config files are needed because we need to specify the device class, state class and unit of measurement fields so the energy dashboard can use the sensors. My configuration for this is below (from sensors.yaml): 

- platform: mqtt

  state_topic: "inverter/stats"

  name: "Inverter - Daily Power Yield"

  value_template: '{{ value_json["daily_power_yield"] }}'

  unit_of_measurement: Wh

  device_class: energy

  state_class: total_increasing

- platform: mqtt

  state_topic: "inverter/stats"

  name: "Inverter - Daily Export Energy"

  value_template: '{{ value_json["daily_export_energy"] }}'

  unit_of_measurement: kWh

  device_class: energy

  state_class: total_increasing

- platform: mqtt

  state_topic: "inverter/stats"

  name: "Inverter - Daily Purchased Energy"

  value_template: '{{ value_json["daily_purchased_energy"] }}'

  unit_of_measurement: kWh

  device_class: energy

  state_class: total_increasing

Just a reminder that my configuration.yaml has the following in it to read sensor config from sensors.yaml:

# manually configured sensors

sensor: !include sensors.yaml

And you also need to make sure the recorder integration has been set up to save sensor data, for example mine is below:

recorder:

  purge_keep_days: 28

  db_url: !secret URL_MariaDB

Nb. I don't have any sensors listed in my recorder section as I'm using the implicit behaviour to record all sensors.

Once the MQTT sensors have been configured and their entities are showing values from the inverter, the Energy dashboard can be set up with the MQTT sensors that we set up previously:

Once this is all set up, you'll be able to use HomeAssistant's energy dashboard (it will look like this once it has collected enough data to show a whole day's energy production and consumption):

AirRohr particulate matter sensor build

Recently I put together an AirRohr particulate matter sensor to see what the PM10 and PM2.5 levels were outside my house. I ordered a SDS011 fine dust sensor and BME280 from Aliexpress, reused an old NodeMCU v1 ESP8266 module and put together the pipe enclosure using 75mm PVC pipe fittings from Bunnings. The modules are powered using a LM2596HV AC to DC Buck Converter, set to output 5V with an input source of 12V AC from a garden lighting transformer.

Firmware

As I was using an old NodeMCU v1 ESP8266 module (I have a few of these lying around and didn't want to order another v2 or v3 module), I couldn't use the pre-built firmware available from the sensor.community website, so I cloned the git repo and tried flashing the firmware using the Arduino IDE. For some reason I ran into problems with the SSD1306 library and as I wasn't using an OLED display I ended up just wrapping all the code that interfaced with the SSD1306 with #ifdef ... #endif directives referencing an undefined token (effectively the same as commenting the code out). This let me build the code and flash it to my NodeMCU v1 module.

Thanks to the Open Data Stuttgart initiative for making the source and sensor design available!

Hardware

Here in Australia the 75mm PVC pipe fittings are different to what's available in Germany so I ended up using:

• 2x 75mm 90 degree female to female elbows
• 2x 75mm PVC couplings (used to fit flyscreen to stop bugs getting in, see later)
  
• 2x 80mm PVC clips (holds 75mm pipe as it has some give in the design)
  
• 1m 75mm PVC pipe (which needed to be cut into a few pieces)
• Some leftover flyscreen
  

The pipes are mounted on our outdoor patio in an inconspicuous spot which has good airflow away from the house:

I used the PVC couplings as endcaps with flyscreen in them to keep bugs out. Cutting a thin (couple of mm - test out your hacksaw skills!) piece of PVC pipe and bending the circle in at one point, this makes an easy flyscreen holder that can be easily wedged into the coupling. From the back it looks like this:

 

 

From the front it looks nice and tidy:

 

The small triangle void formed from pushing the thin PVC piece in gives a nice route to run the power cable into the pipe pieces. I've also wedged a piece of flyscreen foldered into a cone to block off the rest of the hole bounded by the triangle void.

Software

I noticed that the sensor outputs data in JSON format at http://sensor/data.json, so thought I could use this output and write an integration for HomeAssistant to read the sensor data locally. Luckily in searching I found that someone had already written this (https://github.com/lichtteil/local_luftdaten, thanks!) and it was available through HACS so I've added the integration and configured it by adding a sensor to my yaml configuration:

In configuration.yaml I have this section to reference another file for sensors:

# manually configured sensors

sensor: !include sensors.yaml

And in sensors.yaml I have the following config for local_luftdaten (IP address and name redacted):

# luftdaten sensor

- platform: local_luftdaten

  host: 192.168.20.XXX

  scan_interval: 180

  name: airrohr-nnnnnnnn

  monitored_conditions:

    - SDS_P1

    - SDS_P2

    - BME280_temperature

    - BME280_humidity

    - BME280_pressure

This lets me set up a couple of cards to show the sensor values and their trend over the last 24 hours:

It's been interesting to watch the PM2.5 and PM10 values shoot up during the controlled burns we've had in Perth recently while hiding inside away from the smoke :)

HomeAssistant Core 2022.3.+ and SQL integration

I'm using the sql integration for Home Assistant to query saved sensor data on a MS SQL Server container. The latest HomeAssistant Core release (2022.3 series) is based on Alpine linux and it looks like freetds is no longer included in the container's OS baseline, and this caused pyodbc to return errors saying it couldn't load the FreeTDS driver.

To work around this problem I've set up a HomeAssistant integration that runs on startup and calls a shell command to install freetds and put entries into /etc/odbcinit.ini, so my HomeAssistant is able to make SQL queries again :)

Instructions below are inspired by elRadix's posts in the following HA Community Thread: https://community.home-assistant.io/t/use-apt-get-commands/236873/4

Changes:

Create /config/scripts/startup.sh with the following contents:

#!/bin/sh
echo "Updating Image with freetds"
apk update
apk add freetds

cat >/etc/odbcinst.ini <<EOL
[FreeTDS]
Description             = FreeTDS unixODBC Driver
Driver          = /usr/lib/libtdsodbc.so.0
Setup           = /usr/lib/libtdsodbc.so.0
UsageCount              = 1
EOL

Run the following to give the script permission to execute:

chmod +x /config/scripts/startup.sh

At the end of configuration.yaml, add the following to create a new shell command:

shell_command:
  setup: "/config/scripts/startup.sh"

Add the following to automations.yaml:

- id: startup_1
  initial_state: true
  alias: System Startup Scripts
  trigger:
    platform: homeassistant
    event: start
  action:
    - service: shell_command.setup

Restart HA core and SQL Server queries should start working.

IKEA TRADFRI signal repeater (E1746) and zigbee2mqtt

At home I have Zigbee sensors scattered around the house and a couple of sensors have low link quality that had resulted in gaps in my sensor readings. I'd read that it's possible the extend the range of a Zigbee network and the Ikea near me had Tradfri signal repeaters in stock. I picked up a couple.

 Initially I tried following the instructions on the zigbee2mqtt website to pair the device but didn't have much luck. In the end I found out that I had 20 devices already paired to my CC2531, so adding more wasn't a good idea. I set up a second Raspberry Pi with a second CC2531.

I eventually got the signal repeaters working and found out a few things:

E1746s needed a custom network key

The E1746s wouldn't communicate successfully without a custom network key set up on the Zigbee network. My CC2531 would log messages like this showing that it was seeing something but it wasn't able to decode the payloads:

warn  2020-12-13 10:39:34: Received message from unsupported device with Zigbee model 'undefined' and manufacturer name 'undefined'
warn  2020-12-13 10:39:34: Please see: https://www.zigbee2mqtt.io/how_tos/how_to_support_new_devices.html.

These messages have gone away after pairing with a network key set.

Pairing and configuring process takes minutes

Unlike other Zigbee devices which pair and configure quickly, the process for the E1746s takes minutes and the E1746s seemed to go to sleep (or abandon the process) midway. I had to push the reset button mid-pairing on the E1746 to successfully configure the device. The logs for zigbee2mqtt looked like this (lines with starts and in bold show where I did something):

* pressed reset here *
info  2020-12-13 10:36:45: Device 'Ikea Tradfri Repeater 01' joined
info  2020-12-13 10:36:45: Starting interview of 'Ikea Tradfri Repeater 01'
info  2020-12-13 10:36:45: MQTT publish: topic 'zigbee2mqtt/bridge/log', payload '{"message":{"friendly_name":"Ikea Tradfri Repeater 01"},"type":"device_connected"}'
info  2020-12-13 10:36:45: MQTT publish: topic 'zigbee2mqtt/bridge/log', payload '{"message":"interview_started","meta":{"friendly_name":"Ikea Tradfri Repeater 01"},"type":"pairing"}'
* pressed reset here *
info  2020-12-13 10:38:02: MQTT publish: topic 'zigbee2mqtt/bridge/log', payload '{"message":"announce","meta":{"friendly_name":"Ikea Tradfri Repeater 01"},"type":"device_announced"}'
info  2020-12-13 10:38:07: MQTT publish: topic 'zigbee2mqtt/Ikea Tradfri Repeater 01', payload '{"linkquality":149}'
info  2020-12-13 10:38:08: MQTT publish: topic 'zigbee2mqtt/Ikea Tradfri Repeater 01', payload '{"linkquality":152}'
info  2020-12-13 10:38:08: MQTT publish: topic 'zigbee2mqtt/Ikea Tradfri Repeater 01', payload '{"linkquality":149}'
info  2020-12-13 10:38:08: MQTT publish: topic 'zigbee2mqtt/Ikea Tradfri Repeater 01', payload '{"linkquality":152}'
info  2020-12-13 10:38:08: MQTT publish: topic 'zigbee2mqtt/Ikea Tradfri Repeater 01', payload '{"linkquality":152}'
info  2020-12-13 10:38:08: MQTT publish: topic 'zigbee2mqtt/Ikea Tradfri Repeater 01', payload '{"linkquality":152}'
info  2020-12-13 10:38:08: MQTT publish: topic 'zigbee2mqtt/Ikea Tradfri Repeater 01', payload '{"linkquality":149}'
info  2020-12-13 10:38:08: MQTT publish: topic 'zigbee2mqtt/Ikea Tradfri Repeater 01', payload '{"linkquality":149}'
info  2020-12-13 10:38:08: MQTT publish: topic 'zigbee2mqtt/Ikea Tradfri Repeater 01', payload '{"linkquality":149}'
info  2020-12-13 10:38:08: Successfully interviewed 'Ikea Tradfri Repeater 01', device has successfully been paired
info  2020-12-13 10:38:08: Device 'Ikea Tradfri Repeater 01' is supported, identified as: IKEA TRADFRI signal repeater (E1746)
info  2020-12-13 10:38:08: Configuring 'Ikea Tradfri Repeater 01'
info  2020-12-13 10:38:08: MQTT publish: topic 'zigbee2mqtt/bridge/log', payload '{"message":"interview_successful","meta":{"description":"TRADFRI signal repeater","friendly_name":"Ikea Tradfri Repeater 01","model":"E1746","supported":true,"vendor":"IKEA"},"type":"pairing"}'
info  2020-12-13 10:38:09: Successfully configured 'Ikea Tradfri Repeater 01'

So far I've seen a couple of MQTT messages with link quality paylods, so it looks like they are working now:

MQTT publish: topic 'zigbee2mqtt/Ikea Tradfri Repeater 01', payload '{"linkquality":31,"update_available":false}'

BackWPup S3 policy

I run WordPress for my private blog (a spot where I stash notes and other junk which isn't worth posting publicly) and I'm using BackWPup to back up the contents of my WordPress instance to S3. In order to follow good security practice, we should create a specific user which only has the permissions it requires to save backups. In AWS this means you need to create a user and then create an IAM policy for saving backups and then apply this policy to the user.

The way I’ve done this is to create a new S3 bucket for WordPress backups, created a new user called and created a policy which is then applied to the new user. In this post I've named it "wordpress.backup.homenetwork.dns", if you're copying the code from here replace that with the name of your wordpress backup bucket. The policy JSON looks like this:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "s3:*"
            ],
            "Resource": [
                "arn:aws:s3:::wordpress.backup.homenetwork.dns",
                "arn:aws:s3:::wordpress.backup.homenetwork.dns/*"
            ]
        },
        {
            "Effect": "Allow",
            "Action": "s3:ListAllMyBuckets",
            "Resource": "*",
            "Condition": {}
        }
    ]
}

This gives the user full access to the wordpress.backup.homenetwork.dns bucket. It also gives the user access to list all the buckets in S3 as BackWPup uses this to populate the target bucket drop down list. Apply this policy to your backup user and you're good to go.

VIsual Studio 2015 not starting

Today I had an issue with Visual Studio 2015 not starting today after installing some updates in Visual Studio 2013, so I did some googling about how to diagnose issues with Visual Studio.

Visual Studio can actually write logs about its activities if you run it via:

devenv.exe /log

This causes VS to start logging to:

"%AppData%\Microsoft\VisualStudio\[Version]\ActivityLog.xml"

In my case VS 2015 is version 14, so I was looking in:

c:\Users\[account]appdata\Roaming\Microsoft\VisualStudio\14.0

Looking in the logs there was a log entry at ERROR level:

"No InprocServer32 registered for package [Async Query Service Package]"

My guess is the VS 2013 update messed with the dll for that package.

The resolution to this problem was to get VS 2015 to reinitialize itself by running this as an administrator from the Visual Studio command prompt:

devenv.exe /setup

WPF and broken fonts

Today I had problems with starting up the Work Item Template editor in Visual Studio, and when I tried repairing my VS installation the installer kept crashing!

It turns out these were due to corrupted fonts and WPF is susceptible to this problem!!

TL;DR:

If you have broken fonts in your Windows installation, running WPF apps (or running apps that use WPF like Visual Studio) will unexpectedly crash with a Type Initializer exception.

That will keep happening until you fix your fonts.

---

The installer logs the errors that the installer runs into. It logs to c:\users\he152234\appdata\local\temp\dd_vs_[xyz]_[date].log.

My VS installer was crashing with this error:
[0A94:25CC][2015-07-28T16:08:47]e000: MUX:  ERROR: The type initializer for 'System.Windows.Media.FontFamily' threw an exception.

[0A94:25CC][2015-07-28T16:08:47]e000: MUX:  Stack:    at System.Windows.Media.Typeface..ctor(FontFamily fontFamily, FontStyle style, FontWeight weight, FontStretch stretch)

It turns out VS’s installer is a WPF application and WPF apps are susceptible to broken fonts.

So.. what worked for me was:

Open cmd.exe and copy all files from c:\windows\fonts to some other folder (c:\fonts for me).

Open regedit and navigate to HKLM\Software\Microsoft\Windows NT\CurrentVersion\Fonts

Delete all font registrations (a little scary).

Open Fonts in Control Panel.

Drag all fonts from c:\fonts to the Control Panel Fonts window (this re-registers all valid fonts).

Restart the computer.

And I can run VS again! :)

Dave