Sharing SMA SUNNY TRIPOWER 15000TL integration using bluetooth and a mysql database


(Marco Höfle) #1

For those who are interested I am sharing the integration of a SMA inverted rectifier into openhab via bluetooth, SBF spot and a mysql data base.
The SMA inverted rectifier has a bluetooth interface which is normally used by a SMA Windows software to fetch the production data. My openhab installation runs on a VM using Debian Jessie as OS. The intention was to visualize at least the current power, the daily harvested energy and the total harvested energy:

The key software which is collecting the data from the SMA converter is called “SBF spot”. Many thanks to the guys involved in this project.

SBF spot is periodically queering our SMA 15000TL and uploading the data to a mysql database.
openhab is periodically fetching the values from this database.
The reason for this approach is to have all the data in a commonly used database so that for example the same data can be visualized in a self hosted website, not only in openhab. Also I don’t want to have “my” data at public sites.

SBF spot was built from https://github.com/sbf-/SBFspot on the same linux vm. The sql database and the SBFspot.cfg was created according to the readme.

SBF spot is periodically called using cron:

***/1 *   * * *   daemon  /opt/SBFspot/bin/Release_MySQL/SBFspot**

On the openhab machine I have the following shell script to get the data from the sql database:

#!/bin/bash
user=sql_user   
password=sql_passwd
sql_name=$1
val=$(mysql -h "sql server ip"  -u $user -p$password -N -e "select $sql_name from SBFspot.Inverters")
echo $val

I stored it in /etc/openhab2/scripts

The following “things” were created in /etc/openhab2/things/sma.things

Thing exec:command:sma_TotalPac [command="/etc/openhab2/scripts/sma.script TotalPac", interval=1, timeout=1]

Thing exec:command:sma_EToday [command="/etc/openhab2/scripts/sma.script EToday", interval=4, timeout=1]
  
Thing exec:command:sma_ETotal [command="/etc/openhab2/scripts/sma.script ETotal", interval=4, timeout=1]

Thing exec:command:sma_TimeStamp [command="/etc/openhab2/scripts/sma.script TimeStamp", interval=1, timeout=1]

Thing exec:command:sma_OperatingTime [command="/etc/openhab2/scripts/sma.script OperatingTime", interval=1, timeout=1]

Thing exec:command:sma_ertag_heute [command="/etc/openhab2/scripts/sma.script EToday", interval=4, timeout=1]

these items:

Group gSMA  "Wechselrichter"    <pie>
Group gSMA_Chart
String SMA_TimeStampStr "[%s]" (gSMA) {channel="exec:command:sma_TimeStamp:output"}
String SMA_TotalPacStr "[%s]" (gSMA, gSMA_Chart) {channel="exec:command:sma_TotalPac:output"}
String SMA_ETotalStr "[%s]" (gSMA) {channel="exec:command:sma_ETotal:output"}
String SMA_ETodayStr "[%s]" (gSMA) {channel="exec:command:sma_EToday:output"}
String SMA_OperatingTimeStr "[%s]" (gSMA) {channel="exec:command:sma_OperatingTime:output"}
String SMA_TotalPac_Msg "Aktuelle Leistung: [%s]" (gSMA)
String SMA_TimeStamp_Msg "Timestamp: [%s]" (gSMA)
String SMA_OperatingTime_Msg "Betriebsstunden: [%s]" (gSMA)
String SMA_ETotal_Msg "Energie gesamt: [%s]" (gSMA)
String SMA_EToday_Msg "Energie heute: [%s]" (gSMA)
Number SMA_Chart_Period     "Chart Period"
Number SMA_TotalPac "Aktuelle Leistung [%d]" (gSMA, gSMA_Chart)

and this rule to fetch the data:

rule "SMA Msg"
when
  System started
  or
  Item SMA_TimeStampStr changed
  or
  Item SMA_TotalPacStr changed
  or
  Item SMA_ETotalStr changed
  or
  Item SMA_ETodayStr changed
then
  var String msg
  msg = SMA_TotalPacStr.state.toString + " W"
  postUpdate(SMA_TotalPac_Msg, msg)
  msg = SMA_ETotalStr.state.toString + " kWh"
  postUpdate(SMA_ETotal_Msg, msg)
  msg = SMA_ETodayStr.state.toString + " kWh"
  postUpdate(SMA_EToday_Msg, msg)
  msg = SMA_TimeStampStr.state.toString
  postUpdate(SMA_TimeStamp_Msg, msg)
  msg = SMA_OperatingTimeStr.state.toString + " h"
  postUpdate(SMA_OperatingTime_Msg, msg)
  postUpdate(SMA_TotalPac, Integer.parseInt(SMA_TotalPacStr.state.toString))
  logInfo("SMA Wechselrichter","Actual Power: " + SMA_TotalPac.state.format("%d"))
end

The final inclusion of the items in my top sitemap:


                Text item=SMA_TotalPac_Msg icon="solarplant" label="Photovoltaik" {
                        Frame label="Ertrag" {
                                Text item=SMA_TotalPac_Msg icon="solarplant"
                                Text item=SMA_OperatingTime_Msg icon="clock"
                                Text item=SMA_ETotal_Msg icon="solarplant"
                                Text item=SMA_EToday_Msg icon="solarplant"
                        }
                        Frame {
                                Switch item=SMA_Chart_Period label="Chart Period" icon="chart" mappings=[0="Hour", 1="Day", 2="Week"]
                                Chart item=gSMA_Chart period=h refresh=600 visibility=[SMA_Chart_Period==0, SMA_Chart_Period=="NULL"]
                                Chart item=gSMA_Chart period=D refresh=3600 visibility=[SMA_Chart_Period==1]
                                Chart item=gSMA_Chart period=W refresh=3600 visibility=[SMA_Chart_Period==2]
                        }
                }

Maybe this is helpful to other openhabeners. Improvement hints are welcome.
Cheers,
Marco


Exec binding PaperUI Thing with Parameter
(Angelos) #2

What an excellent & simple solution to perform sql queries! I will re-use your solution for a similar problem that I was trying to solve ! :thumbsup:

By the way: You could use InfluxDB to persist the states of your items and then plot them with Grafana for cooler graphs (or use MySQL with Grafana directly?)


(Marco Höfle) #3

Hello Angelos,
thanks for the Feedback.
Agree, the best solution would be using Grafana directly with the SQL data (now the data is stored twice).
I’ll give it a try and I will update this post if I get it working.


(Andrew Pawelski) #4

Simple question but im stuck on getting the script to run - says mysql - command not found - can anyone help - what do I need installed?