Heizungsdaten auslesen und in SQL schreiben

Mario_Lang

Ich hab ein kleines Projekt angefangen und da hier einige programmiertechnisch gut drauf sind, könnt ihr mir vielleicht helfen.

Ich habe eine Hargassner Hackgutheizung welche per Telnet angesprochen die aktuellen Werte und Zustände im Sekundentakt in dieser Form ausgibt:

Code

pm 1 1.2 7.5 66.9 0 61.8 0 0 0 0 0 0 0 0 13 3 75 0 18 11 140 1000 54.7 58 -3.2 -2.6 72.1 120 60.9 -20 -20 0 -0 0 0 0 0 3 0 0 0 0 97 2 0 109 50.5 0 100 0 0 13 120 26 -20 27.2 28 23.5 16.5 1 1 43.5 45 23.1 12.9 1 1 55.2 55 20 20 1 1 -20 0 20 20 0 1 -20 0 20 20 0 1 -20 0 20 20 0 1 -20 0 20 20 0 1 -20 0 20 20 0 1 21.7 0 76.5 0 -20 0 -20 0 -20 0 0 55 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 68.2 0 90345.8 100 3 3 3 0 1 6 0 0 0 0 0 0 0 0 0 2 0 10 3 0 0 0 55 0 0 49F 1000 7D00 0 0 0 0 200

nun sollen diese werte abgefangen und in eine SQL geschrieben werden welche diese Struktur hat:

Code

CREATE TABLE `ECO-HK40` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` datetime DEFAULT NULL,
  `1` float DEFAULT NULL,
  `2` float DEFAULT NULL,
  `3` float DEFAULT NULL,
  `4` float DEFAULT NULL,
  `5` float DEFAULT NULL,
  `6` float DEFAULT NULL,
  `7` float DEFAULT NULL,
  `8` float DEFAULT NULL,
  `9` float DEFAULT NULL,
  `10` float DEFAULT NULL,
  `11` float DEFAULT NULL,
  `12` float DEFAULT NULL,
  `13` float DEFAULT NULL,
  `14` float DEFAULT NULL,
  `15` float DEFAULT NULL,
  `16` float DEFAULT NULL,
  `17` float DEFAULT NULL,
  `18` float DEFAULT NULL,
  `19` float DEFAULT NULL,
  `20` float DEFAULT NULL,
  `21` float DEFAULT NULL,
  `22` float DEFAULT NULL,
  `23` float DEFAULT NULL,
  `24` float DEFAULT NULL,
  `25` float DEFAULT NULL,
  `26` float DEFAULT NULL,
  `27` float DEFAULT NULL,
  `28` float DEFAULT NULL,
  `29` float DEFAULT NULL,
  `30` float DEFAULT NULL,
  `31` float DEFAULT NULL,
  `32` float DEFAULT NULL,
  `33` float DEFAULT NULL,
  `34` float DEFAULT NULL,
  `35` float DEFAULT NULL,
  `36` float DEFAULT NULL,
  `37` float DEFAULT NULL,
  `38` float DEFAULT NULL,
  `39` float DEFAULT NULL,
  `40` float DEFAULT NULL,
  `41` float DEFAULT NULL,
  `42` float DEFAULT NULL,
  `43` float DEFAULT NULL,
  `44` float DEFAULT NULL,
  `45` float DEFAULT NULL,
  `46` float DEFAULT NULL,
  `47` float DEFAULT NULL,
  `48` float DEFAULT NULL,
  `49` float DEFAULT NULL,
  `50` float DEFAULT NULL,
  `51` float DEFAULT NULL,
  `52` float DEFAULT NULL,
  `53` float DEFAULT NULL,
  `54` float DEFAULT NULL,
  `55` float DEFAULT NULL,
  `56` float DEFAULT NULL,
  `57` float DEFAULT NULL,
  `58` float DEFAULT NULL,
  `59` float DEFAULT NULL,
  `60` float DEFAULT NULL,
  `61` float DEFAULT NULL,
  `62` float DEFAULT NULL,
  `63` float DEFAULT NULL,
  `64` float DEFAULT NULL,
  `65` float DEFAULT NULL,
  `66` float DEFAULT NULL,
  `67` float DEFAULT NULL,
  `68` float DEFAULT NULL,
  `69` float DEFAULT NULL,
  `70` float DEFAULT NULL,
  `71` float DEFAULT NULL,
  `72` float DEFAULT NULL,
  `73` float DEFAULT NULL,
  `74` float DEFAULT NULL,
  `75` float DEFAULT NULL,
  `76` float DEFAULT NULL,
  `77` float DEFAULT NULL,
  `78` float DEFAULT NULL,
  `79` float DEFAULT NULL,
  `80` float DEFAULT NULL,
  `81` float DEFAULT NULL,
  `82` float DEFAULT NULL,
  `83` float DEFAULT NULL,
  `84` float DEFAULT NULL,
  `85` float DEFAULT NULL,
  `86` float DEFAULT NULL,
  `87` float DEFAULT NULL,
  `88` float DEFAULT NULL,
  `89` float DEFAULT NULL,
  `90` float DEFAULT NULL,
  `91` float DEFAULT NULL,
  `92` float DEFAULT NULL,
  `93` float DEFAULT NULL,
  `94` float DEFAULT NULL,
  `95` float DEFAULT NULL,
  `96` float DEFAULT NULL,
  `97` float DEFAULT NULL,
  `98` float DEFAULT NULL,
  `99` float DEFAULT NULL,
  `100` float DEFAULT NULL,
  `101` float DEFAULT NULL,
  `102` float DEFAULT NULL,
  `103` float DEFAULT NULL,
  `104` float DEFAULT NULL,
  `105` float DEFAULT NULL,
  `106` float DEFAULT NULL,
  `107` float DEFAULT NULL,
  `108` float DEFAULT NULL,
  `109` float DEFAULT NULL,
  `110` float DEFAULT NULL,
  `111` float DEFAULT NULL,
  `112` float DEFAULT NULL,
  `113` float DEFAULT NULL,
  `114` float DEFAULT NULL,
  `115` float DEFAULT NULL,
  `116` float DEFAULT NULL,
  `117` float DEFAULT NULL,
  `118` float DEFAULT NULL,
  `119` float DEFAULT NULL,
  `120` float DEFAULT NULL,
  `121` float DEFAULT NULL,
  `122` float DEFAULT NULL,
  `123` float DEFAULT NULL,
  `124` float DEFAULT NULL,
  `125` float DEFAULT NULL,
  `126` float DEFAULT NULL,
  `127` float DEFAULT NULL,
  `128` float DEFAULT NULL,
  `129` float DEFAULT NULL,
  `130` float DEFAULT NULL,
  `131` float DEFAULT NULL,
  `132` float DEFAULT NULL,
  `133` float DEFAULT NULL,
  `134` float DEFAULT NULL,
  `135` float DEFAULT NULL,
  `136` float DEFAULT NULL,
  `137` float DEFAULT NULL,
  `138` float DEFAULT NULL,
  `139` float DEFAULT NULL,
  `140` float DEFAULT NULL,
  `141` float DEFAULT NULL,
  `142` float DEFAULT NULL,
  `143` float DEFAULT NULL,
  `144` float DEFAULT NULL,
  `145` float DEFAULT NULL,
  `146` float DEFAULT NULL,
  `147` float DEFAULT NULL,
  `148` float DEFAULT NULL,
  `149` float DEFAULT NULL,
  `150` float DEFAULT NULL,
  `151` float DEFAULT NULL,
  `152` float DEFAULT NULL,
  `153` float DEFAULT NULL,
  `154` float DEFAULT NULL,
  `155` float DEFAULT NULL,
  `156` float DEFAULT NULL,
  `157` float DEFAULT NULL,
  `158` float DEFAULT NULL,
  `159` float DEFAULT NULL,
  `160` float DEFAULT NULL,
  `161` binary(1) DEFAULT NULL,
  `162` binary(1) DEFAULT NULL,
  `163` binary(9) DEFAULT NULL,
  `164` binary(1) DEFAULT NULL,
  `165` binary(1) DEFAULT NULL,
  `166` binary(1) DEFAULT NULL,
  `167` binary(1) DEFAULT NULL,
  `168` binary(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQUE` (`date`)
) ENGINE=MyISAM AUTO_INCREMENT=3043 DEFAULT CHARSET=utf8 
ROW_FORMAT=FIXED;

Alles anzeigen

Das ganze sollte unter Windows laufen (Win Server). Die SQL ist bereits vorhanden und liegt vorläufig auf einem externen NAS zum testen.

Hat hier jemand einen Ansatz oder Lösung?

Danke

SkyBird1980

Ich würde das jetzt mit php lösen. Ohne eine Art Interpreter kommst du eh nicht zur Lösung.
Wie auch immer - warum ist da jetzt Telnet als Schnittstelle gewählt? Canbus oder Modbus sind ja für sowas gemacht.

Mario_Lang

Php verwende ich dann zum Daten anzeigen und Visualisieren
Wie könnte das mit php zeitgesteuert laufen zb. Daten im 5 min takt abrufen

Hargassner ist da etwas speziell, telnet funktioniert als einziges ohne zusätzliche Hardware. LAN-kabel dran und geht.

SkyBird1980

Zitat von Mario_Lang

Wie könnte das mit php zeitgesteuert laufen zb. Daten im 5 min takt abrufen

Na, Windows hat seine Aufgabenplanung, alle 5 Minuten als Trigger zum Scriptaufruf wär doch da drin.

Du musst dich halt nur entscheiden wie Du es machen willst. Wird die Telnet Verbindung automatisch unterbrochen nach der Werteausgabe?

Code: service.batch

@echo off
telnet IP-Adresse -f c:\test.txt
php scriptname

würde nun die ausgabe in die Datei test.txt schreiben.
Und das php script könnte diese Datei dann auswerten und die Daten in mysql schreiben.

Mario_Lang

ok, das schau ich mir mal an.

sobald telnet aufgerufen wird, läuft die verbindung weiter und der datensatz kommt jede sekunde neu mit den aktuellen werten

SkyBird1980

okay dann müsste man den telnet service laufen lassen. im php script kann man dann mit

$lines = file ('c:\test.txt');
$letzte_zeile = $lines[count($lines)-1];
die letzte Zeile bekommen. Die kann man dann ja auseinandernehmen anhand der Leerzeichen und danach als Array speichern und in die MYSQL DB eintragen.

Seh ich so nicht als zu tragisch.

Mario_Lang

Ich habe die Auslesegeschichte wie folgt gelöst

Bash

#!/bin/bash
# shellscript zur Loggen von Telnet Daten einer Hargasser ECO HK40 Baujahr 2015
# die daten werden in einer Excel conformen CSV Datei gespeichert
#
while true; do
#
# Das akutelle Datum umd Zeit wird in eine Datei geschrieben
#
date +%d.%m.%Y" "%H:%M > /home/server/hargassner/Date.[definition='1','0']log[/definition]
#
# 10 Sekunden wird an Telnet gelauscht und in eine Datei geschrieben
#
timeout 10 nc 192.168.1.25 23 > /home/server/hargassner/Temp.[definition='1','0']log[/definition]
#
# Für eine Weitervearbeitung in Excel werden nun Punkte durch Komma ersetzt
#
sed -i 's/\./,/g' /home/server/hargassner/Temp.[definition='1','0']log[/definition]
#
#das "pm" am Anfang der Zeile entfernen
#
sed -i 's/\pm //g' /home/server/hargassner/Temp.[definition='1','0']log[/definition]
#
# Um eine Excel csv Datei zu erstellen werden nun Leerzeichen durch Semikolon ersetzt
#
sed -i 's/ /;/g' /home/server/hargassner/Temp.[definition='1','0']log[/definition]
#
#auf 600 Zeichen kürzen
#
sed -i -r 's/(.{600}).*/\1/;q' /home/server/hargassner/Temp.[definition='1','0']log[/definition]
#
# Nun wird an die Datumsdatei die Temp Datein mit der Aufzeichungszeile angehängt
#
cat /home/server/hargassner/Temp.[definition='1','0']log[/definition] >> /home/server/hargassner/Date.[definition='1','0']log[/definition]
#
# Die Datumsdatei mit Datumsangabe und Aufzeichnungszeile wird nun
# auf die die "alte" Temp Datei kopiert und dabei überschrieben
#
mv /home/server/hargassner/Date.[definition='1','0']log[/definition] /home/server/hargassner/Temp.[definition='1','0']log[/definition]
#
# Nun wird der Zeilenumbruch aus der Datei entfernt und durch ein Simekolon ersetzt
#
sed -i ':a;N;$!ba;s/\n/;/g' /home/server/hargassner/Temp.[definition='1','0']log[/definition]
#
# Da das aktuelle Datum immer zu oberst sei soll, wird nun die die schon bestehende
# cvs Datei an die Temp Datein angehängt
#
cat /home/server/hargassner/hargassner.csv >> /home/server/hargassner/Temp.[definition='1','0']log[/definition]
#
# Die Temp Datei wieder auf die "alte" csv Datei kopieren
#
mv /home/server/hargassner/Temp.[definition='1','0']log[/definition] /home/server/hargassner/hargassner.csv
#
# Damit die Log Datei nicht undendlich Gross wird
# wird nun die Datei durch kopieren der ersten 10000 Zeilen
# und schreiben in Temp Datei, die Aufzeichnungslänge eingekürzt
#
sed -ne '1,10000p'  /home/server/hargassner/hargassner.csv > /home/server/hargassner/Temp.[definition='1','0']log[/definition]
#
# Zurückschreiben der Temp Datei in die csv datei
#
mv /home/server/hargassner/Temp.[definition='1','0']log[/definition] /home/server/hargassner/hargassner.csv
#
cp -R /home/server/hargassner /mnt/c/Web/Apache24/htdocs/csvlog
sleep 2m
done

Alles anzeigen

dies funktioniert soweit eigentlich gut. Außer der host / Kessel ist nicht erreichbar (LAN-Kabel abgesteckt, Stromausfall, ...). Dann wird eine leere Zeile (nur Datum Uhrzeit) geschrieben und Grafana hat dann sein Probleme

kann ich das Script soweit anpassen das leere Zeilen entfernt werden bzw. gar nicht geschrieben werden?

Original Datenzeile die alle 0,5 Sek. per Telnet kommt

Code

pm 1 1.2 7.5 61.4 0 57.3 0 0 0 0 0 0 0 0 10 1 75 0 18 11 140 1000 59.1 58 0.4 -1.7 71.6 120 45.9 -20 -20 1 0 0 0 0 0 3 0 0 0 0 78 2 0 107 50.5 0 100 0 0 13 120 24 -20 26.1 27 23.5 16.5 1 1 44.0 45 23.1 12.9 1 1 54.7 54 20 20 1 1 -20 0 20 20 0 1 -20 0 20 20 0 1 -20 0 20 20 0 1 -20 0 20 20 0 1 -20 0 20 20 0 1 21.4 0 77.0 0 -20 0 -20 0 -20 0 0 54 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 61.0 0 91304.6 100 3 3 3 0 1 6 0 0 0 0 0 0 0 0 0 2 0 10 3 0 0 0 54 0 0 49B 1000 7800 0 0 0 0 200

Anbei noch meine Umsetzung der Datenauswertung:

Das Diagramm wird von Grafana erstellt, dazu habe ich ebenfalls eine Frage:

Die Website läuft auf meinem W10 Server mittels Apache2.4 auf dem üblichen Port 80
Grafana läuft auf dem Server unter dem Port 3000 und stellt seinen eigenen Webserver

Was muss im Apache eingestellt werden das Grafana per iframe auf dem Apache Port 80 auch öffentlich angezeigt wird? Ich habe jetzt schon einiges über Reverse Proxy gelesen und probiert, doch nichts funktioniert

Mario_Lang

aja, daten werden in eine .csv-Datei geschrieben

Mario_Lang

Das auslesen mach ich jetzt trotzdem mit php. Da hab ich die Daten besser im griff und brauche das WSL nicht zusätzlich bemühen.

Einzig die Geschicht mit Grafana und Apache konnte ich noch nicht lösen.

Zufällig einer eine Idee wie Reverse Proxy eingestellt werden muss?

Jetzt mitmachen!