Datenaufzeichnung mit DB-Access und MySQL

Ich bekomme recht häufig Anfragen dazu, wie ich die Daten der CCU in einer SQL-Datenbank speichere, daher möchte ich die wesentlichen Teile hier kurz vorstellen. Die Lösung ist historisch gewachsen, sehr frickelig und es gibt mittlerweile mit Sicherheit einfachere Möglichkeiten, insbesondere wenn man sein Leben irgendeiner Cloud anvertraut.

Diese Kurzanleitung setzt tiefere Kenntnisse in MySQL, Bash und nicht zuletzt CCU-Scriptprogrammierung voraus und ist absolut nicht für Anfänger geeignet. Nicht, weil es irgendwas Besonderes wäre, sondern weil die Anleitung wirklich nur kurz das generelle Setup anreißt.

Sie wurden gewarnt.

Planung

Der Plan ist, ein paar Umweltdaten alle fünf Minuten in eine Datenbank zu schreiben: Außentemperatur, Windgeschwindigkeit, Helligkeit und Niederschlag. Dazu schreibe ich ein Bash-Script, das per cron alle fünf Minuten die Daten der CCU mittels DB-Access abruft, sie in MySQL speichert und bei Bedarf weiterverarbeitet.

Voraussetzung ist ein Linux-Server, z. B. mit Ubuntu, auf dem MySQL läuft. Das sollte eigentlich auch ein Raspberry oder ähnliches schaffen, es gibt hier nicht so wirklich viel zu tun.

Auf der HomeMatic-Seite braucht man eine CCU, auf der das DB-Access-Add-on läuft. Theoretisch müsste es auf allen Versionen der CCU funktionieren, DB-Access ist nicht besonders  anspruchsvoll, aber getestet habe ich nur mit der CCU-2.

Programme auf der CCU

Die Daten werden nicht im Fünf-Minuten-Takt von den Sensoren geliefert, sondern in kürzeren Abständen. Ich möchte daher z. B. für die protokollierte Windgeschwindigkeit den höchsten Wert innerhalb des Fünf-Minuten-Rasters aufzeichnen.

Dafür erstelle ich eine Systemvariable Datenbank Wind und ein zugehöriges WebUI-Programm, das bei jeder Aktualisierung der Windgeschwindigkeit des Kombisensors ein Script ausführt:

gesamtes Script markieren / kopieren

! HomeMatic-Script
! DATENAUFZEICHNUNG MIT DB-ACCESS UND MYSQL
! http://www.christian-luetgens.de/homematic/db-access/mysql/MySQL-Log.htm

object o = dom.GetObject ("Datenbank Wind");
object dp = dom.GetObject ("Carport.Kombisensor").DPByHssDP("WIND_SPEED");
if (dp.Value() > o.Value()) {
  o.State (dp.Value());
}

!  Ende des Scripts

Meine Systemvariable Datenbank Wind wird nur dann mit dem neuen Wert aktualisiert, wenn er höher als der schon gespeicherte ist. Die Systemvariable wird später bei der Datenbank-Aktualisierung jeweils zurückgesetzt.

Für die Helligkeit erstelle ich eine Systemvariable Datenbank Helligkeit. Hier soll nicht nur die Helligkeit des Kombisensors aktualisiert werden, sondern die Summe der Helligkeiten des Kombisensors und aller Bewegungsmelder, die ich rund ums Haus installiert habe. Dadurch ergibt sich eine sehr viel feinere Auflösung des Helligkeitswertes, mit der ich für irgendwelche späteren Anwendungen mehr anfangen kann.

Das Script wird bei jeder Aktualisierung der Helligkeit des Kombisensors oder eines beliebigen Bewegungsmelders ausgeführt.

gesamtes Script markieren / kopieren

! HomeMatic-Script
! DATENAUFZEICHNUNG MIT DB-ACCESS UND MYSQL
! http://www.christian-luetgens.de/homematic/db-access/mysql/MySQL-Log.htm

integer i = 0;
string s;
foreach (s, "Carport.Bewegungsmelder\tSchuppen.Bewegungsmelder\tVorgarten.Bewegungsmelder\tTerrasse.Bewegungsmelder\tCarport.Kombisensor") {
  i = i + dom.GetObject(s).DPByHssDP("BRIGHTNESS").Value();
}
object o = dom.GetObject("Datenbank Helligkeit");
if (i > o.Value()) {
  o.State (i);
}

!  Ende des Scripts

Auch hier nehme ich den höchsten Wert und auch hier wird die Systemvariable später per Script zurückgesetzt.

Bei der Temperatur gilt Ähnliches: Ich habe den Kombisensor und zwei Temperatursensoren. Damit ich nicht die Temperatur in praller Sonne messe, nehme ich den niedrigsten Wert.

Der Wert für die Systemvariable Aussentemperatur wird noch von anderen Programmen gebraucht, daher lasse ich das entsprechende Script per Systemtakt ausführen; bei der Datenbank-Aktualisierung wird diese Systemvariable im Gegensatz zu den anderen nicht zurückgesetzt.

gesamtes Script markieren / kopieren

! HomeMatic-Script
! DATENAUFZEICHNUNG MIT DB-ACCESS UND MYSQL
! http://www.christian-luetgens.de/homematic/db-access/mysql/MySQL-Log.htm

! Allgemein
object o;
string s;
object dp;
real r;


! Temperatur
r = 50;
foreach (s, "Carport.Kombisensor\tTerrasse.Thermometer\tVorgarten.Thermometer") {
  dp = dom.GetObject (s).DPByHssDP ("TEMPERATURE");
  if (dp.Value() < r) {
    r = dp.Value();
  }
}
dom.GetObject ("Aussentemperatur").State (r);


! Regen
o = dom.GetObject ("Carport.Kombisensor").DPByHssDP("RAIN_COUNTER");
r = o.Value() - o.LastValue();
if (r < 0.0) {
  r = r + o.ValueMax();
}
object o_regen = dom.GetObject("Datenbank Regen");
o_regen.State(o_regen.Value() + r);

!  Ende des Scripts

In diesem Script wird auch die Regenmenge aktualisiert. Die Systemvariable Datenbank Regen summiert bei jedem Aufruf die Regenmenge. Genau wie bei Wind und Helligkeit wird diese Variable ebenfalls zurückgesetzt, wenn die Datenbank aktualisiert wird.

Gesamtstatus abrufen

In meiner MySQL-Datenbank namens homematic gibt es eine Tabelle systemstatus, die bei jedem Datenbank-Update einfach den aktuellen Status der CCU aufnimmt. Das hat den Vorteil, dass ich alle weiteren Aktionen direkt in MySQL durchführen kann. Außerdem hat es natürlich den Vorteil, dass die Daten anschließend auch einfach für Programme auf dem Server zur Verfügung stehen.

Die Tabelle entspricht exakt dem Format, dass state.cgi aus DB-Access zurückliefert

Das Bash-Script, mit dem die Daten aus der CCU abgerufen und in die Datenbank geschrieben werden, ist entsprechend einfach. Es wird wie beschrieben durch cron im Fünf-Minuten-Takt ausgeführt.

gesamtes Script markieren / kopieren

#!/bin/bash

# Parameter
credentials='--user=username --password=geheim --database=homematic --silent'
tablename=/var/tmp/homematic-log-tabledata

# Systemstatus abholen
wget -O $tablename -q "http://ccu2/addons/db/table.cgi"
mysql $credentials --execute="LOAD DATA INFILE '"$tablename"' REPLACE INTO TABLE \`systemstatus\` FIELDS TERMINATED BY '\t';"
if [ $? -ne 0 ] ; then
	exit 1
fi
rm $tablename

# Datenbankwerte in CCU zurücksetzen
for i in Regen Wind Helligkeit ; do 
  wget -O /dev/null -q "http://ccu2/addons/db/state.cgi?item=Datenbank $i&value=0"
done

Die Tabelle wird mit wget von der CCU geholt und anschließend über LOAD DATA INFILE in der Tabelle systemstatus der MySQL-Datenbank homematic gespeichert. Wichtig ist, dass MySQL Zugriff auf die temporäre Datei hat, in der die Tabelle liegt, und natürlich muss der MySQL-User das Recht haben, LOAD DATA INFILE zu verwenden.

Anschließend werden Regen, Wind und Helligkeit über state.cgi von DB-Access zurückgesetzt – und das war’s, die Tabelle ist auf dem Server.

Werte protokollieren

Bisher habe ich nur eine regelmäßig aktualisierte Tabelle in meiner Datenbank, die den momentanen Zustand der CCU enthält. Das ist für viele Dinge schon ausreichend, aber noch keine Protokollierung. Eine weitere Tabelle umweltdaten nimmt daher das Protokoll auf.

Auch diese Tabelle ist sehr simpel: Zeitstempel sowie die vier Werte, die ich protokollieren möchte.

Das obige Bash-Script geht daher einfach noch eine Zeile weiter:

gesamtes Script markieren / kopieren

# Umweltdaten
mysql $credentials --execute="INSERT INTO \`umweltdaten\` VALUES (CONVERT_TZ(NOW(),'MET','GMT'), (SELECT \`value\` FROM \`systemstatus\` WHERE \`name\`='Aussentemperatur'), (SELECT \`value\` FROM \`systemstatus\` WHERE \`name\`='Datenbank Wind'), (SELECT \`value\` FROM \`systemstatus\` WHERE \`name\`='Datenbank Helligkeit'), (SELECT \`value\` FROM \`systemstatus\` WHERE \`name\`='Datenbank Regen'));"

Gut, die eine Zeile ist etwas lang. Im Grunde ist es ganz einfach: Ich füge eine neue Zeile in meine Tabelle umweltdaten ein, wobei jeder Wert durch ein SELECT aus der vorherigen Tabelle systemstatus kommt.

Duty Cycle

Als der Duty Cycle neu war, habe ich meine CCU argwöhnisch unter Beobachtung gehalten. Um das zu tun, habe ich damals einfach eine weitere Tabelle erstellt und mit einer weiteren Zeile auch den Status meines Duty Cycles protokolliert.

Voraussetzung ist natürlich die Abfrage des Duty Cycles über ein entsprechendes WebUI-Programm.

gesamtes Script markieren / kopieren

# Duty Cycle
mysql $credentials --execute="INSERT INTO \`duty_cycle\` VALUES (CONVERT_TZ(NOW(),'MET','GMT'), (SELECT \`value\` FROM \`systemstatus\` WHERE \`name\`='Duty Cycle'));"

Der Duty Cycle wird jetzt genau wie die Umweltdaten im Fünf-Minuten-Takt protokolliert. Damit kann ich mir den Verlauf der Auslastung der Funkschnittstelle meiner CCU auch graphisch anzeigen lassen:

Wenn man die Probleme, die der Duty Cycle mit sich bringt, einmal im Griff hat, ist so eine Kurve natürlich nicht so spannend. Anders sieht es mit den Umweltdaten aus: Mit meiner MySQL-Datenbank kann ich eine ordentliche Wetterstation bauen, in der ich mir auch etwas komplexer berechnete Werte wie gleitenden Durchschnitt oder meinen Sonnen- und Wolkengrenzwert auf Basis historischer Daten anzeigen lassen kann.

Bonus: Werte auf die CCU zurückschreiben

Meine CCU übernimmt bei mir auch das Öffnen und Schließen der Fenster über die WinMatic. Das entsprechende Programm soll die Fenster geschlossen halten, wenn es innerhalb der letzten Stunde sehr windig war oder leicht windig bei Regen.

Ohne auf die Details meiner „zentralen Klimasteuerung“ näher einzugehen: Die Werte für maximale Windgeschwindigkeit und Regen während der letzten Stunde kann ich durch zwei weitere Zeilen meines Bash-Scripts speichern.

gesamtes Script markieren / kopieren

# CCU-Update
wget -O /dev/null -q "http://ccu2/addons/db/state.cgi?item=Wind_1h&value="`mysql $credentials --execute='SELECT ROUND(MAX(\`wind\`),1) FROM \`umweltdaten\` WHERE \`timestamp\` > CONVERT_TZ(NOW(),"MET","GMT") - INTERVAL 1 HOUR;'`
wget -O /dev/null -q "http://ccu2/addons/db/state.cgi?item=Regen_1h&value="`mysql $credentials --execute='SELECT ROUND(SUM(\`regen\`),1) FROM \`umweltdaten\` WHERE \`timestamp\` > CONVERT_TZ(NOW(),"MET","GMT") - INTERVAL 1 HOUR;'`

Die Systemvariablen Wind_1h und Regen_1h werden durch Werte aus der MySQL-Tabelle umweltdaten aktualisiert: für den Wind der Maximalwert der letzten Stunde, für den Regen die Summe. Ich verwende hier state.cgi aus DB-Access, um die Werte zu schreiben.