SBFspot Bluetooth homeassistant addon

Share your Projects!
1

Hiyas, if you use SBFspot Bluetooth for home assistant this addon may be for you.

I built this for myself, so I figured I may as well share it for the other 3 people that use Bluetooth communication for their SMA inverters.

It’s functional as it is, and exports to SQL(mariadb) database, and PVoutput.

It needs some more cleaning up, so please be aware of that.

It’s current release shaves about 450mb off the image, and avoids the user issues found in other repos.

Feel free to try it out and leave a bug report at the link below if it needs it.

Cheers

SBFspot Bluetooth addon

3 Likes
2

Hello HasQT,

sounds good, but:

if i try to add your respository (https://github.com/HABuild/hassio_addons) i get the following error:
(Invalid Add-on repository!)

Am i in the wrong menu?

image
image1920×1080 316 KB

It is a raspi 4 with 2022.4.1 based on HassOS

Frank

3

Not sure where the link went wrong there.

https://github.com/habuild/hassio-addons
4

Hello HasQT,

Thanks that works.

The link provided at your githubpage is wrong: https://github.com/HABuild/hassio_addons

image
image1194×310 35.1 KB

There is an underline between hassio and addons

Frank

5

Ahh thanks.

6

Hello HasQT,

being just a user, i have problems to configure the AddOn. I try to add my values into the configuration and i get a error message.

Failed to save add-on configuration, not a valid value. Got {'BTAddress': '00:80:25:xx:xx:3e', 'Password': 'MyPassword', 'MIS_Enabled': 0, 'Plantname': 'MyPlantName', 'OutputPath': '/data/sbfspot/%Y', 'OutputPathEvents': '/data/sbfspot/%Y/events', 'Latitude': xx.42432, 'Longitude': xx.04541, 'CalculateMissingSpotValues': 1, 'DateTimeFormat': '%d/%m/%Y %H:%M:%S', 'DateFormat': '%d/%m/%Y', 'DecimalPoint': 'comma', 'TimeFormat': '%H:%M:%S', 'SynchTime': 0, 'SynchTimeLow': 1, 'SynchTimeHigh': 3600, 'SunRSOffset': 900, 'Locale': 'en-US', 'Timezone': 'Europe/Be...

My configfile is:

BTAddress: '00:80:25:xx:xx:3e'
Password: MyPassword
MIS_Enabled: 0
Plantname: 'MyPlantName'
OutputPath: /data/sbfspot/%Y
OutputPathEvents: /data/sbfspot/%Y/events
Latitude: xx.42432
Longitude: xx.04541
CalculateMissingSpotValues: 1
DateTimeFormat: '%d/%m/%Y %H:%M:%S'
DateFormat: '%d/%m/%Y'
DecimalPoint: comma
TimeFormat: '%H:%M:%S'
SynchTime: 0
SynchTimeLow: 1
SynchTimeHigh: 3600
SunRSOffset: 900
Locale: en-EN
Timezone: Europe/Berlin
BTConnectRetries: 10
CSV_Export: 0
CSV_ExtendedHeader: 1
CSV_Header: 1
CSV_SaveZeroPower: 1
CSV_Delimiter: semicolon
CSV_Spot_TimeSource: Inverter
CSV_Spot_WebboxHeader: 0
SQL_Database: SBFspot
SQL_Hostname: 192.168.178.68
SQL_Port: '3306'
SQL_Username: sbfspot
SQL_Password: MyPassword
MQTT_Publisher: /usr/bin/mosquitto_pub
MQTT_Host: core-mosquitto
MQTT_Port: 'null'
MQTT_Topic: homeassistant/sbfspot_{plantname}/sbfspot_{serial}
MQTT_ItemFormat: '"{key}": {value}'
MQTT_ItemDelimiter: comma
MQTT_PublisherArgs: '-h {host} -u {mqtt-admin} -P {MyPaasword} -t {topic} -m "{{message}}" -d'
MQTT_Data: >-
  Timestamp,SunRise,SunSet,InvSerial,InvName,InvTime,InvStatus,InvTemperature,InvGridRelay,EToday,ETotal,GridFreq,PACTot,UDC1,UDC2,IDC1,IDC2,PDC1,PDC2
LogDir: /dev/stdout >> /proc/1/fd/1
LogLevel: info
PVoutput_SID: 99999
PVoutput_Key: 98xxxx5x6x9x810xx7xxx9104x9x28xx78x8x542
MQTT_ENABLE: '1'

My problem migth be that i do not know where to add ‘’ and where not.

I am grateful for any help.

Frank

7

Try 1883 in the mqtt port
Might want set your locale too. Unless you’re actually in Berlin.

Try also less decimal places on lat and long if the port doesn’t fix it

Edit: bleh the error msg doesn’t help much. Hopefully one day I can find the bug as to why it won’t display UI options

8

my error was also the (beside the mqtt port)

missed that i had to add the serial nummer of the inverter

1 Like
9

have now started the AddOn. I got the Log:

[s6-init] making user provided files available at /var/run/s6/etc...exited 0.
[s6-init] ensuring user provided files have correct perms...exited 0.
[fix-attrs.d] applying ownership & permissions fixes...
[fix-attrs.d] done.
[cont-init.d] executing container initialization scripts...
[cont-init.d] done.
[services.d] starting services
[services.d] done.
PWD is current directory /run/s6/services/sbfspot
PWD is current directory /
Generating /etc/sbfspot/SBFspot.cfg & /etc/sbfspot/SBFspotUpload.cfg
mkdir: can't create directory '/dev/stdout': File exists
mkdir: can't create directory '/proc/1/fd/1': File exists
[cont-finish.d] executing container finish scripts...
[cont-finish.d] done.
[s6-finish] waiting for services.
[s6-finish] sending all processes the TERM signal.
s6-nuke: fatal: unable to kill: No such process

Error at the end

Frank

10

You can leave mqtt port blank I think…
But not as null.
I use 1883

11

Ahh something else I need to fix

You can remove

Dev/stdout  >> proc/1/fd/1

Put instead

/data/sbfspot/log/
12

i get the following erros in the log:

[2022-04-10 13:25:02.932] Error: exec_query() returned : Table 'SBFspot.Inverters' doesn't exist
Executed Statement: INSERT IGNORE INTO Inverters VALUES(212010xxxx,'SN: 212010xxxx','SB 2500HF-30','02.30.06.R',0,0,0,0,0,0,'','',0)
[2022-04-10 13:25:02.932] Error: exec_query() returned : Table 'SBFspot.Inverters' doesn't exist
Executed Statement: UPDATE Inverters SET Name='SN: 212010xxxx',Type='SB 2500HF-30',SW_Version='02.30.06.R' WHERE Serial=212010xxxx

and

Client (null) sending CONNECT
Connection error: Connection Refused: not authorised.
Client (null) received CONNACK (5)
Error: The connection was refused.
MQTT: Failed te execute '/usr/bin/mosquitto_pub' mosquitto client installed?
Error 1280 while publishing to MQTT Broker

the mqtt user is an HomeAssistant User

image
image1584×138 5.42 KB

Frank

13 
MQTT_PublisherArgs: '-h {host} -u mqtt-admin -P yourpass -t {topic} -m "{{message}}" -d'

needs your user and password here, without the brackets.

the other error, have you already got an existing SBFspot database? you need to make one and the tables if you haven’t already.

import that file from the repo using phpMyAdmin addon to make the tables
https://github.com/habuild/hassio-addons/blob/6d0858b7e298468560aae037fa2aee414aff2135/.images/CreateMySQLDB_no_drop.sql

Capture
Capture1079×379 40.4 KB

14

Hello,

in this way my summary:

  • the system is running since yesterday and delivers the values from my ‘historical- bluetooth based’ SMA Sunny Boy 2500 HF inverter.
  • base is a Raspi4 with HAOS
  • the installation problems (see above) are based on my inability to read the documentation properly.

Thanks to HasQT for providing the AddOn and for the very quick help with my problems.

Frank

1 Like
15

You’re welcome,

As a word of caution, I need to learn how to write some error handling. It’s been pretty stable for me except for when I had both HA and SBFspot using MariaDB. In that case when a backup started it would lock the SBFspot DB while backing up HA. This causes SBFspotUpload to stop because it can’t find the database. That error isn’t automatically handled, so you have to manually restart the container.

In short if it stops uploading the best fix atm is to restart the container. That means keeping a vague eye on it though.

16

Hello HasQT,

I am trying your add on and am stuck.
I have a sma3000 TL with bluetooth piggyback. In android i used oxley solar.
I want your add on incorporated in my HAS system.
This is what i did.

  1. created an account in pvoutput.org → available is: login, password, API key, system_id
  2. installed mariaDB → took this website for it:
    Convert Home Assistant to use MariaDB as the database - André Jacobs
    and in yaml config:
    recorder:
    db_url: mysql://homeassistant:<>@core-mariadb/homeassistant?charset=utf8mb4

and config MariaDB:
configured 2 login users and give them rights:
databases:

  • homeassistant
  • SBFspot (UpperCase DB)
    logins:
  • password: <>
    username: homeassistant
  • password: <>
    username: sbfspot (LowerCase User)
    rights:
  • username: homeassistant
    database: homeassistant
  • username: sbfspot
    database: SBFspot

  1. installed Mosquitto MQTT according → Mosquitto MQTT installeren in Home Assistant – Connectix.nl

    add a new usere for the Mosquitto Broker
    User: SMA_Bouma75
    Password: <>

Configurated MQTT:
logins:

  • username: SMA_Bouma75
    password: <>
    require_certificate: false
    certfile: fullchain.pem
    keyfile: privkey.pem
    customize:
    active: false
    folder: mosquitto

Question:
in githup: Download and import either [No Drop DB create]
What are the steps to create database with No Drop DB create?

Could you help me out here?

Kind regards,

John

17

Hey Fritz,

You need phpMyAdmin addon to import the the no drop file (it creates the tables and views)

Also you don’t need the recorder line in config. It’s best to leave HA database in the standard database(not mariadb, that’s the recommended DB now with the recent DB optimisation) and use mariadb for spfspot. See my previous post, backup causes issues.

18

Firstly, thanks for putting in the time to make this add-on.

I’m trying to connect to my SB 5000TL-20 and I’m getting the following error when starting the add-on:

image
image632×707 89.3 KB

I know the bluetooth address and password that I have set are correct as I have Sunny Explorer working fine. Any suggestions?

19

Hmm that’s just a general BT connection issue. Have you had the BT connection working previously? With the same hardware? Might just be a range issue if address and password are correct.

My pi is about 3-4 metres away from my inverter, although I have had it connect at more like 6-7 metres and through a couple of walls. It often has a few tries to connect at the further range.

Edit also I don’t think you can have multiple connections, so make sure sunny Explorer isn’t also running.

20

I thought that too but…

I’m using a USB bluetooth adapter at the end of a USB extension cable. To test I have that passing through to a Windows VM using Sunny Explorer, which works. The connection strength switches between two and three bars.

I then shut down that Windows VM and attach that same adapter (haven’t moved it or anything) to my HASS VM and start that back up. So I would have thought it would be ok to connect.

If I use bluetoothctl and scan for devices it definitely sees the SMA inverter with an RSSI ranging between -64 to -77, which I thought would be ok. Again, it all works perfectly under Windows :frowning: