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 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:

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"
  System started
  Item SMA_TimeStampStr changed
  Item SMA_TotalPacStr changed
  Item SMA_ETotalStr changed
  Item SMA_ETodayStr changed
  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"))

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.

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?