Hinweis: Schöne Alternative für Charts/Plots mit Grafana und DBLog (MySQL)

Begonnen von Thyraz, 08 Oktober 2017, 15:02:38

Vorheriges Thema - Nächstes Thema

Thyraz

@ sku, bin wie gesagt auch MySQL Noob. ;)
Hat daher ein wenig Knobeln und (und Googlen :P) gebraucht, hab aber eine Lösung gefunden denke ich.


SELECT
  time_sec,
  value,
  "Kurvenbezeichnung" as metric
  FROM (
    SELECT
      UNIX_TIMESTAMP(TIMESTAMP) as time_sec,
      VALUE as value,
      IF (@OLDVAL <> VALUE AND @OLDVAL := VALUE, 1, 0) as different
    FROM history
    JOIN (SELECT @OLDVAL := '-1') AS var
    WHERE READING="temperature" AND DEVICE="Thermometer" AND $__timeFilter(TIMESTAMP)
    ORDER BY TIMESTAMP
  ) t
WHERE different = 1
ORDER BY time_sec


Versuche das Ganze mal zu erklären.
Fangen wir mir dem inneren Select an:

Hier wird mit dem Join erstmal eine Variable "OldVal" belegt.
Dann werden alle Zeitstempel und zugehörige Werte für unsere Device/Readings Kombination geholt.

Zusätzlich wird in den zurückgelieferten Daten eine weitere Spalte "different" erzeugt.
Zuerst wird verglichen ob sich der aktuelle Wert von dem aus dem letzten Eintrag unterscheidet, dann der aktuelle Wert als Vergleichswert für die nächste Zeile gespeichert.

Die so erstellte Tabelle dient dann als Quelle für einen weiteren SELECT Aufruf,
welcher sich nur die Einträge holt bei denen die Spalte "different" eine 1 beinhaltet.

Evtl bietet sich zur Darstellung dann eine Staircase Linie an.

P.S. um mit sowas rumzuspielen bietet sich SQL Fiddle an.
Damit kann man sehr schön testen wie sich Änderungen am Select Query auswirken.

Habe so zuerst mal das innere SELECT aufgebaut:
http://sqlfiddle.com/#!9/22fb14/25

und dann das Äußere hinzugefügt:
http://sqlfiddle.com/#!9/22fb14/24

So sieht man an der resultierenden Tabelle sehr gut was aus dem Query zurückkommt.
Fhem und MariaDB auf NUC6i5SYH in Proxmox Container (Ubuntu)
Zwave, Conbee II, Hue, Harmony, Solo4k, LaMetric, Echo, Sonos, Roborock S5, Nuki, Prusa Mini, Doorbird, ...

sku

Vielen Dank für deine Antwort!
Ich kam mit Mysql noch nie zusammen... Sqlfiddle hatte ich bei meiner stundenlangen Suche gefunden, aber ich konnt einfach nix sinnvolles zusammenbauen.

Dein Code funktioniert! Danke!

Eine Sache wollt ich noch verbessern, bin aber wieder gescheitert:
Ich habe versucht deinen Code auch so zu ergänzen, dass "off" durch "0.0" ersetzt wird. Ersetzen funktioniert, die Zeilen mit 0.0 werden angezeigt, wenn ich die vorletzte auskommentiere. Jedoch wird das aus irgendeinem Grund beim Vergleichen nicht als geänderter Wert markiert...
Die Änderung von off auf 0 hat den kosmetischen Hintergrund, dass den Sommer über die desired-temp nicht als Strich durch die Grafik gezogen wird. Damit ich sehe, wann aufgehört wurde, eine desired-temp zu setzen.
Kannst du mir bitte nochmal helfen?

Hier meine Bastelei:
http://sqlfiddle.com/#!9/a41a9/18

sku

Mir fiel eben auf, es funktioniert leider nicht bei Ventilpositionen, der Wert 0 wird weg gelassen... Habe auch einen screenshot angehängt.
http://sqlfiddle.com/#!9/0dda55/1

Thyraz

Ich glaub ich hab den Fehler warum es manchmal nicht geht.
Um die Variable OldVal bei jedem Durchlauf auf den neuen Wert upzudaten, hab ich die Zuweisung ja in das IF-Statement mit reingetrickst.

Bin davon ausgegangen, dass der Rückgabewert der Zuweisung immer als "true" ausgewertet wird.
Bei Nullen oder Strings scheint dies aber nicht der Fall zu sein.
Habe die Zuweisung nun nochmal in ein IF gekapselt, welches immer eine 1 zurückliefert.

Damit scheinen beide Fälle zu funktionieren:
http://sqlfiddle.com/#!9/3df417/5
Fhem und MariaDB auf NUC6i5SYH in Proxmox Container (Ubuntu)
Zwave, Conbee II, Hue, Harmony, Solo4k, LaMetric, Echo, Sonos, Roborock S5, Nuki, Prusa Mini, Doorbird, ...

sku

Funktioniert, DANKE nochmal!

Hier nochmal zur Dokumentation der Code, mit Verbesserungen:
* Zeile 7 timestamp für Grafana, wie Thyraz bereits mal geschrieben hat
* Zeile 12 um " AND $__timeFilter(TIMESTAMP)" ergänzt, damit nicht die komplette DB durchsucht wird
SELECT
  time_sec,
  CONVERT(VALUE, double) as value,
  "Kurvenbezeichnung" as metric
  FROM (
    SELECT
      UNIX_TIMESTAMP(TIMESTAMP) as time_sec,
      IF (VALUE = "off", 0, VALUE) as value,
      IF (STRCMP(@OLDVAL, VALUE) != 0 AND IF(@OLDVAL := VALUE, 1, 1), 1, 0) as different
    FROM history
    JOIN (SELECT @OLDVAL := '-1') AS var
    WHERE READING="temperature" AND DEVICE="Thermometer" AND $__timeFilter(TIMESTAMP)
    ORDER BY TIMESTAMP
  ) t
WHERE different = 1
ORDER BY time_sec


Und einen Screenshot, wie so ein Plot "Last 1 year" ausschaut, hab ich auch angehängt.

EDIT: ich habe im Code die 3. Zeile angepasst, damit sie mit Grafana ab Version 5.1 funktioniert. Vorher stand
value,
jetzt steht
CONVERT(VALUE, double) as value,

Thyraz

@sku

Dir auch mein Dank an für deine letzte Fragestellung.
Erst dadurch hab ich Variablen in MySQL entdeckt. ;)

Hab dadurch nun hinbekommen Zeitwerte zwischen einzelnen Timestamps berechnen zu lassen und somit z.B. Summen-On und Summen-Off Zeiten pro Tag oder Monat für Geräte direkt im MySQL Select zu berechnen. :)

Mit sowas lässt sich dann z.B. Betriebsstunden pro Monat zählen, oder die summierte Heizzeit eines Fußbodenheizkreises pro Tag.
Bisher hab ich dafür das Statistics Modul in FHEM laufen lassen um mir das als Duration pro Tag und Duration pro Monat zusätzlich zum eigentlichen On/Off Wert in die DB loggen zu lassen.

Das mag nicht jeden stören, es verhindert aber zumindest das grafische Auswerten von Alt-Daten bevor man auf die Idee kam so ein Chart zu benötigen.
(Davor hat man wahrscheinlich eher kein Statistics Modul dafür laufen lassen.)
In Zeiten von Big Data möchte man eben einfach sagen können "Ich hab hier einen Haufen Daten aus den letzten Jahren, lass uns das mal auswerten".

Ein Beispiel dafür folgt bald...
Fhem und MariaDB auf NUC6i5SYH in Proxmox Container (Ubuntu)
Zwave, Conbee II, Hue, Harmony, Solo4k, LaMetric, Echo, Sonos, Roborock S5, Nuki, Prusa Mini, Doorbird, ...

screetch82

Hi

das sieht echt super aus. Damit kann ich ja auch nicht FHEM datenbanken visualisieren und als png Bild in FHEM oder als HTML Webseite anzeigen richtig?

(Hintergrund: Ich habe eine Weishaupt solarthermie anlage. Diese logt ca 15 Parameter und Temperaturen im CSV format auf eine Wifi SD Karte. Per Cronjob kopiere
diese von der SD auf den Raspi und dann in ein MariaDB auf der NAS.  Die Werte möchte ich nun auslesen und die Wärmemenge pro Stunde, Tag, Woche, Monat
berechnen und als Verlausdiagramm ausgeben ohne den Umweg zu gehen die Werte in FHEM als Readings zu importieren und nochmal neu mit DBLog zu schreiben)

Thyraz

Klaro,

wo die Daten herkommen ist ja erstmal egal.
Du brauchst in der DB eben Zeitstempel/Werte Paare welche du dir in Grafana ziehen kannst.

Embedding Optionen zum Einbinden in FHEM wurden auf der ersten Seite auch schon angesprochen und glaube auch verlinkt.
Fhem und MariaDB auf NUC6i5SYH in Proxmox Container (Ubuntu)
Zwave, Conbee II, Hue, Harmony, Solo4k, LaMetric, Echo, Sonos, Roborock S5, Nuki, Prusa Mini, Doorbird, ...

andies

Ich habe bei der Installation Probleme, kann mir jemand helfen? Ich habe aus svn die Daten geholt und dann bei ci build.sh aufgerufen. Das ergab bei meinem RPi3
Die folgenden Pakete haben unerfüllte Abhängigkeiten:
crossbuild-essential-armhf : Hängt ab von: gcc-arm-linux-gnueabihf (>= 4.9.1-1) soll aber nicht installiert werden
                              Hängt ab von: g++-arm-linux-gnueabihf (>= 4.9.1-1) soll aber nicht installiert werden
E: Probleme können nicht korrigiert werden, Sie haben zurückgehaltene defekte Pakete.
+ CC=arm-linux-gnueabihf-gcc
+ CXX=arm-linux-gnueabihf-g++
+ install_phjs
+ PHJSURL=https://github.com/fg2it/phantomjs-on-raspberry/releases/download/v2.1.1-wheezy-jessie
+ PHJS=/tmp/armv7/phantomjs
+ mkdir -p /tmp/armv7
+ curl -sSL https://github.com/fg2it/phantomjs-on-raspberry/releases/download/v2.1.1-wheezy-jessie/phantomjs -o /tmp/armv7/phantomjs

Wie komme ich da weiter?
FHEM 6.1 auf RaspPi3 (Raspbian:  6.1.21-v8+; Perl: v5.32.1)
SIGNALduino (433 MHz) und HM-UART (868 MHz), Sonoff, Blitzwolf, Somfy RTS, CAME-Gartentor, Volkszähler, Keyence-Sensor, Homematic-Sensoren und -thermostat, Ferraris-Zähler für Wasseruhr, Openlink-Nachbau Viessmann

Thyraz

Hast du denn die Abhängigkeiten die er dir anzeigt per apt-get zu installieren versucht?

Ansonsten da du einen Pi hast: Gibt es einen Grund es selbst kompilieren zu wollen?
In Post #2 ist ein Link zu fertigen Packages für den Pi.
Fhem und MariaDB auf NUC6i5SYH in Proxmox Container (Ubuntu)
Zwave, Conbee II, Hue, Harmony, Solo4k, LaMetric, Echo, Sonos, Roborock S5, Nuki, Prusa Mini, Doorbird, ...

andies

Ich finde die Seite https://bintray.com/fg2it/deb/grafana-on-raspberry/v4.6.1#files/main%2Fg etc total unübersichtlich. Ich "sehe" da keine ausführbaren Dateien...

PS Ich bin mal präziser. Einmal heißt es "No direct downloads selected for this package." Dann steht "Files" als Menüpunkt und da sind eine Menge files, aber anscheinend keine ausführbaren. Und so geht das weiter.

<edit> gelöst. So geht das
wget https://s3-us-west-2.amazonaws.com/grafana-releases/release/grafana-4.6.1.linux-x64.tar.gz
tar -zxvf grafana-4.6.1.linux-x64.tar.gz
FHEM 6.1 auf RaspPi3 (Raspbian:  6.1.21-v8+; Perl: v5.32.1)
SIGNALduino (433 MHz) und HM-UART (868 MHz), Sonoff, Blitzwolf, Somfy RTS, CAME-Gartentor, Volkszähler, Keyence-Sensor, Homematic-Sensoren und -thermostat, Ferraris-Zähler für Wasseruhr, Openlink-Nachbau Viessmann

andies

Zu früh gefreut

$ sudo ./grafana-server
./grafana-server: 8: ./grafana-server: Syntax error: word unexpected (expecting ")")
$ ./grafana-server
-bash: ./grafana-server: Kann die Binärdatei nicht ausführen: Fehler im Format der Programmdatei
FHEM 6.1 auf RaspPi3 (Raspbian:  6.1.21-v8+; Perl: v5.32.1)
SIGNALduino (433 MHz) und HM-UART (868 MHz), Sonoff, Blitzwolf, Somfy RTS, CAME-Gartentor, Volkszähler, Keyence-Sensor, Homematic-Sensoren und -thermostat, Ferraris-Zähler für Wasseruhr, Openlink-Nachbau Viessmann

Thyraz

Auf der Bintray Seite war ich noch nie, wie kamst du denn da hin?

Auf der Github Seite aus Post #2 findet man unter Releases direkt Downloads.
Evtl. hätte ich direkt auf diese Unterseite verlinken sollen.
https://github.com/fg2it/grafana-on-raspberry/releases

Ich habe dort direkt das .deb File genommen und installiert.

Edit: Hab Post #2 entsprechend angepasst.
Fhem und MariaDB auf NUC6i5SYH in Proxmox Container (Ubuntu)
Zwave, Conbee II, Hue, Harmony, Solo4k, LaMetric, Echo, Sonos, Roborock S5, Nuki, Prusa Mini, Doorbird, ...

andies

Danke, läuft bei mir.  Ich habe jetzt die ausführbare Datei installiert. Wie kann ich dafür sorgen, dass sie bei Systemstart ausgeführt wird? Auf http://docs.grafana.org/installation/debian/ steht das nur für die kompilierten, aber ganz unten finde ich nichts.
FHEM 6.1 auf RaspPi3 (Raspbian:  6.1.21-v8+; Perl: v5.32.1)
SIGNALduino (433 MHz) und HM-UART (868 MHz), Sonoff, Blitzwolf, Somfy RTS, CAME-Gartentor, Volkszähler, Keyence-Sensor, Homematic-Sensoren und -thermostat, Ferraris-Zähler für Wasseruhr, Openlink-Nachbau Viessmann

Thyraz

Da bei dir auch Jessie läuft laut Signatur,
sollte man das per systemctl aktivieren können.

Einmalig starten sollte denke ich über
sudo systemctl start grafana
gehen.

Dauerhaft bei jedem booten:
sudo systemctl enable grafana

edit:
Auf der von dir verlinkten Seite heißt der service nicht Grafana sondern grafana-server.service
Wäre dann
sudo systemctl enable grafana-server.service
Fhem und MariaDB auf NUC6i5SYH in Proxmox Container (Ubuntu)
Zwave, Conbee II, Hue, Harmony, Solo4k, LaMetric, Echo, Sonos, Roborock S5, Nuki, Prusa Mini, Doorbird, ...