Homematic: Status nach MySQL

[masek]

Hi,

ich mag zwar die Homematic, aber programmieren lässt die sich so elegant wie mein alter C64. Vor allem das Fehlen von sinnvollen und globalen Funktionen tut schon weh. Daher habe ich beschlossen, die Programmierung in PERL oder PHP auf dem Raspberry zu machen. Damit ich was machen kann, müssen aber die Daten erst einmal auf das Teil, am besten in eine Datenbank (MySQL).

Früher hätte ich das über den Otto-Normal-Syslog-Daemon gemacht, das ganze in eine "named pipe" geschrieben und mit PERL geparset und in die DB geschrieben. Aber wir sind ja inzwischen im 21. Jahrhundert angekommen. Daher will ich nicht schon dort mit dem Programmieren anfangen, also habe ich überlegt, wie das mit Bordmitteln geht. Die Lösung lauter SYSLOG-NG.

Vorbereitung

Raspbian neu installieren, IP-Adresse setzen und per SSH einloggen.

Als erstes muss man im Standard-Raspberry die fehlenden Pakete nachinstallieren:

Code: Alles auswählen

sudo apt-get install syslog-ng mysql-server mysql-client libdbd-mysql libdbi1

Danach hat man schon alles was man braucht drauf. Nicht vergessen das MySQL-Passwort zu setzen und zu merken.

MySQL vorbereiten

Mit

Code: Alles auswählen

mysql -u root -p

kommt man in die MySQL-Shell. Dazu muss man das oben gewählte MySQL-Passwort angeben.

Hier in der Shell legt man seine Datenbank und das restliche Bohei an:

Code: Alles auswählen

create database homematic;
use homematic;
CREATE USER 'syslogger'@'localhost' IDENTIFIED BY 'topsecret';
GRANT ALL ON homematic.* TO 'syslogger'@'localhost';

CREATE TABLE homedata (
id_homedata INT AUTO_INCREMENT,
t_date TIMESTAMP,
i_time INT UNSIGNED,
s_host VARCHAR(255),
s_device VARCHAR(128),
i_channel INT,
s_key varchar(255),
s_value varchar(255),
PRIMARY KEY (`id_homedata`),
KEY `s_host` (`s_host`),
KEY `t_date` (`t_date`),
KEY `i_time` (`i_time`),
KEY `s_device` (`s_device`),
KEY `i_channel` (`i_channel`),
KEY `s_key` (`s_key`),
KEY `s_value` (`s_value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Das "topsecret" ersetzt man durch ein eigenes Passwort. Die Shell lasst Ihr am besten offen und geht ein zweites mal per SSH auf das System.

Syslog-NG konfigurieren

Mit

Code: Alles auswählen

sudo vi /etc/syslog-ng/syslog-ng.conf

kann man die Konfigurationsdatei aufmachen. Wenn man den "vi" nicht mag, kann man auch "nano" nehmen. Dort fügt man am Ende der Datei an:

Code: Alles auswählen

destination d_homesql {
  sql(
    type("mysql")
    username("syslogger")
    password("topsecret")
    database("homematic")
    host("localhost")
    table("homedata")
    columns("s_host", "t_date", "i_time", "s_device", "i_channel", "s_key", "s_value")
    values("$HOST","$S_ISODATE","$S_UNIXTIME", "${HOME.DEVICE}", "${HOME.CHANNEL}", "${HOME.KEY}", "${HOME.VALUE}" )
  );
};

parser p_home {
  csv-parser(columns("HOME.EVENT", "HOME.DEVICE", "HOME.CHANNEL", "HOME.KEY", "HOME.VALUE") delimiters(":.=") flags(greedy,strip-whitespace) );
};

filter f_rfd { match("rfd" value(PROGRAM)); };
filter f_event { match("^Event:" value(MESSAGE)); };

log { source(s_net); filter(f_rfd); filter(f_event); parser(p_home); destination(d_homesql); };

Bitte daran denken, das "topsecret" durch das eigene Passwort zu ersetzen.

Syslog-NG neu starten

Das geht mit

Code: Alles auswählen

sudo service syslog-ng restart

und danach sieht man in der MySQL-Shell

Code: Alles auswählen

mysql> select * from homedata  LIMIT 10;
+-------------+---------------------+------------+-----------+------------+-----------+----------------+---------------+
| id_homedata | t_date              | i_time     | s_host    | s_device   | i_channel | s_key          | s_value       |
+-------------+---------------------+------------+-----------+------------+-----------+----------------+---------------+
|         429 | 2016-03-06 20:48:03 | 1457293683 | 10.0.1.10 | LEQ******* |         1 | TEMPERATURE    | 19.800000     |
|         430 | 2016-03-06 20:48:03 | 1457293683 | 10.0.1.10 | LEQ******* |         1 | HUMIDITY       | 47            |
|         431 | 2016-03-06 20:48:04 | 1457293684 | 10.0.1.10 | KEQ******* |         1 | TEMPERATURE    | 20.100000     |
|         432 | 2016-03-06 20:48:04 | 1457293684 | 10.0.1.10 | KEQ******* |         1 | HUMIDITY       | 52            |
|         433 | 2016-03-06 20:48:12 | 1457293692 | 10.0.1.10 | LEQ******* |         1 | TEMPERATURE    | 19.400000     |
|         434 | 2016-03-06 20:48:12 | 1457293692 | 10.0.1.10 | LEQ******* |         1 | HUMIDITY       | 51            |
|         435 | 2016-03-06 20:48:35 | 1457293715 | 10.0.1.10 | MEQ******* |         1 | BOOT           | false         |
|         436 | 2016-03-06 20:48:35 | 1457293715 | 10.0.1.10 | MEQ******* |         1 | ENERGY_COUNTER | 528983.000000 |
|         437 | 2016-03-06 20:48:35 | 1457293715 | 10.0.1.10 | MEQ******* |         1 | POWER          | 1345.000000   |
|         438 | 2016-03-06 20:48:51 | 1457293731 | 10.0.1.10 | KEQ******* |         1 | TEMPERATURE    | 20.200000     |
+-------------+---------------------+------------+-----------+------------+-----------+----------------+---------------+

An allem kann man noch einiges optimieren, aber dazu komme ich erst wieder am Wochenende.

Nächste Schritte

Keine Ahnung, aber ich werde mir ein paar "stored procedures" in MySQL ausdenken, um die Daten besser griffbereit zu haben. Danach packe ich mal ein hübsches Dashboard dazu.

Bitte wundert Euch nicht, wenn es einige Zeit dauert, bis das Projekt bei mir weiter geht.

Gruß, Martin

[Familienvater]

Hi,

ich bin früher auch über die Auswertung des Syslogs gegangen, das ist aber eine Einbahnstraße, damit bekommt man nur das Zeugs aus der CCU raus, und ist auf das Syslog-Format festgelegt. Ich habe nämlich beim Umstieg von der CCU1 auf die CCU2 das Problem gehabt, das sich das Syslog-Format geändert hat, ich habe damals allerdings nicht die (multiplen) EVENT-Zeilen, sondern die eine "low-Level" rfd-Meldung inkl. ggf. der Hex-Data-Dumps zerlegt.

Was mir weiter am Syslog-Auswerten nicht gefällt, ist das man den rfd und den hs485d auf Debug stellen muss, bei mir kommen da pro Tag gut und gerne 60-70 MB nur an syslog aus der CCU2.

Die richtige Vorgehensweise ist einen XML-RPC-Server zu betreiben, an den die CCU die Events meldet (und die CCU meldet alle Events), ich habe das mit C#-Code hier aus dem Forum als Basis gemacht, und darauf aufgebaut, und schreibe das an Daten auf einen MS-SQL weg, was ich für späteren Auswertungen aufheben will.

Über XML-RPC kann man auch Geräte schalten, ist zwar erstmal etwas mehr Aufwand, aber es ist die in meinen Augen sauberste Lösung.

Nicht falsch verstehen, aber ich weiß nicht, ob ich meine Haussteuerungs-Logik in Datenbank-Triggern abbilden möchte.

Just my 2 cents,
der Familienvater

[masek]

Hallo,

aus meiner Sicht war der Weg erst einmal in zweierlei Hinsicht schneller: a) weniger Aufwand und b) kommen die Daten schneller in die DB. XML-RPC ist von der Latenz sicher um eine Größenordnung langsamer.

Die Größe des Logfiles ist ohne Bedeutung, da davon ja nix auf die Platte geschrieben wird, sondern nur die Daten in das MySQL.

XML-RPC ist die saubere Variante, aber eine ohne jegliches Sex-Appeal (Nischentechnologie) . Wissen über Syslog-NG hingegen kann ich auch im Job immer gebrauchen.

Aber spätestens wenn ich Aktionen auslösen will, und da hast Du voll und ganz Recht, muss ich mir XML-RPC antun. Auf der anderen Seite ist es dann ein viel überschaubarer Teil.

Ich mache das für mich zum Spaß. Hier packe ich es nur hin, damit andere ggf. nicht bei Null anfangen müssen. Ich erwarte nicht, dass andere es nutzen oder nutzen sollen.

Gruß, Martin

[Familienvater]

Hi,

aus meiner Sicht war der Weg erst einmal in zweierlei Hinsicht schneller: a) weniger Aufwand

Vom Aufwand her gebe ich Dir Recht, deshalb habe ich es damals ja auch so gemacht...

b) kommen die Daten schneller in die DB. XML-RPC ist von der Latenz sicher um eine Größenordnung langsamer.

Ich würde nicht behaupten, das XMLRPC langsamer ist. Meine CCU2 feuert pro 24h in Summe ca. 100.000 Events über rfd und hs485d raus, und ich könnte jetzt nicht sagen, das es in irgendeiner Weise langsam ist, oder das die Events mit "zig" Sekunden Verspätung erst aufschlagen. Selbst ein testweiser Sturm von 7.000 Events in 5 min hat das ganze nicht ins Wanken gebracht, wobei ich mit den Events nichts gemacht habe.

Da meine "Logikschicht" Homeputer auf der CCU2 auch nur die Events per XMLRPC bekommt, und ich dort weiß, wie die Latenz von einer Zentralenverknüpfung (Taste wird gedrückt, Event geht an HPCL, HPCL entscheidet und schaltet Ausgang) ist, und das alles über XMLRPC läuft, würde ich sagen max. 0,2 Sekunden Round-Trip.
Eine Direktverknüpfung ist um "Welten" schneller, aber eben nur bedingt intelligent.

Der Familienvater

[z000ao8q]

Hallo,
Kannst Du aus der Praxis berichten wir es mit den datenmängen jetzt aussieht?
Schreibt er wirklich so viel weg?
Kann man ggf mit den filtern mehr arbeiten?

[z000ao8q]

Hallo,

ein Hinweis noch, der mich 1/2h gekostet hat
in der .conf muss der port-Listener noch aktiviert werden - und zwar auf UDP
Ist am oberen Ende der Datei und muss ent-kommentiert werden

Code: Alles auswählen

source s_net { network(ip(192.168.0.23) port(514) transport("udp")); };

eine Frage: über den Syslog bekomme ich keine Variablen, oder?
Dann wäre es genau das richtige für mich gewesen

[masek]

Hallo,

mit Grafana kann ich mir aus dem gespeicherten Infos direkt ein Dashboard darüber setzen.

Das schaut im Moment so aus (Bild scheint für das Forum zu groß zu sein):



Gruß, Martin