Grafana-Abfragen von iobroker MySQL

Balu1973

Hallo,

auch ich versuche mich gerade an Grafana und würde z.B. gerne die Temperaturverläufe meiner Homematic Thermostate anzeigen.

Ich komme mit diesen Beispielen aber nicht klar. Kann jemand mal zeigen, wie das mySQL Statement für einen Datenpunkt, der unter den iobroker Objekten z.B. hm-rpc.0.LEQ1234567.1.TEMPERATURE heißt, aussehen müsste.

Die mySQL Tabelle habe ich nach Anleitung (viewtopic.php?f=35&t=12169&hilit=grafana#p128611) erweitert, bin mir aber nicht sicher ob das komplett richtig gemacht habe.

Danke und Grüße,

Balu

sissiwup

SELECT
  floor(n.ts/1000) as time_sec,
  n.val as value,
  concat(d.sname,"(",d.raum,")") as metric
FROM ts_number n,datapoints d
WHERE $__unixEpochFrom()*1000<n.ts and/$__unixepochto()*1000="">n.ts and n.id = 130 and n.id=d.id
ORDER BY n.ts ASC</n.ts>

oder

SELECT
  floor(n.ts/1000) as time_sec,
  n.val as value,
  "Heizung 1" as metric
FROM ts_number n
WHERE $__unixEpochFrom()*1000<n.ts and/$__unixepochto()*1000="">n.ts and n.id = 130 
ORDER BY n.ts ASC</n.ts>

Die id findest du in der Tabelle Datapoints.

Datapoints kannst du dir übrigens zur einfacheren Auswahl als Variable anzeigen lassen:

sieht dann so aus:

Wenn du jetzt z.B. deinen Datenpunkt Temperatur suchst, gibts du das oben nur noch ein und kannst die ID einfach ablesen.

Balu1973

Hallo Sissi,

Danke für Deine Antwort. Aber entweder bin ich zu blöd, oder habe irgendwo noch einen Fehler.

Ich erstelle in Grafana ein neues Graph Panel. Dann editiere ich dieses und füge den SQL Code hinzu.

Die ID 1 (vor as metric) bekomme ich aus der Tabelle Datapoints für den gewünschten Temperatursensor. Soweit richtig?

Leider sagt er mir "no Datapoints" und das Panel bleibt leer.

Auch das mit den Variablen bekomme ich nicht hin (wobei ich mir die IDs auch "zu Fuß" aus der Tabelle holen könnte). Wahrscheinlich habe ich schon vorher einen Fehler gemacht…

Die neu erstellte Tabelle "namen" sieht übrigens so aus:

Danke und Grüße,Balu

Balu1973

Hallo Sissi,

ich hatte Tomaten auf den Augen!````
n.id = 1


Dann kommen auch Werte!

Danke und Grüße,

Ralf

Dutchman

Hi folks,

Habt ihr je idee wie man daten aus ts_string einbinden kan (also wie der query aussehen muss)?

Ändern des ts_number in ts_string klappt leider nicht, bei mir sind Werte durch Scripte als String in der SQL Datenbank gespeichert anstatt Number die ich darstellen möchte :/

Thanks!

Dutch

–-----------------------

Send from mobile device

Das schöne ios hat Auto Korrektur zum k****

Wer Schreibfehler findet darf sie behalten oder auf eBay verkaufen, mindest Umsatz 10% für die community

Balu1973

Hi Dutch,

Ich habe es gerade mal bei mir probiert. Es reicht die Änderung von ts_number in ts_string aus um Daten darstellen zu können.

Sicher dass die ID richtig ist?

Grüße,

Balu

Dutchman

~~@Balu1973:~~

Ich habe es gerade mal bei mir probiert. Es reicht die Änderung von ts_number in ts_string aus um Daten darstellen zu können.

Sicher dass die ID richtig ist?

Grüße,

Balu `

Hi Balu,

hmm bei mir klappt das nicht :/

Werte in SQL unter id 31
979_screen_shot_2018-04-17_at_09.50.19.png

dauen in flot:
979_screen_shot_2018-04-17_at_09.50.33.png

und dan grafana:
979_screen_shot_2018-04-17_at_09.51.20.png

query:

` > SELECT

floor(n.ts/1000) as time_sec,

n.val as value,

"Datenpunkt" as metric

FROM ts_string n

WHERE $__unixEpochFrom()*1000<n.ts and/$__unixepochto()*1000="">n.ts and n.id = 31

ORDER BY n.ts ASC</n.ts> `

Balu1973

Hmm,

so sieht das im Prinzip bei mir auch aus.

Versuche die Abfrage doch mal nicht in einem Graph, sondern ein einer Tabelle.

Ich hatte nur Daten in meiner Tabelle ts_strings, die ich nicht in einem Graphen darstellen lassen.

Vielleicht interprätiert Grafana die Daten auch nicht als Zahl und stellt es daher nicht dar…

Grüße,

Balu

Dutchman

~~@Balu1973:~~

Vielleicht interprätiert Grafana die Daten auch nicht als Zahl und stellt es daher nicht dar… `

oh man… in ner tabelle gehts als graphic nicht :/, wird Wohl mit dem Tabellen typ zu tun haben

sissiwup

Hallo,

hast du mal in deiner Abfrage

CONVERT(n.val,UNSIGNED INTEGER) AS value

probiert (fals es integer sind, ansonsten anderen Datentyp verwenden).

Dutchman

~~@sissiwup:~~

Hallo,

hast du mal in deiner Abfrage

CONVERT(n.val,UNSIGNED INTEGER) AS value

probiert (fals es integer sind, ansonsten anderen Datentyp verwenden). `

Du bist mein held des Tages ich danke dir !!!!!!!

` > SELECT

floor(n.ts/1000) as time_sec,

CONVERT(n.val,UNSIGNED INTEGER) AS value,

"Datenpunkt" as metric

FROM ts_string n

WHERE $__unixEpochFrom()*1000<n.ts and/$__unixepochto()*1000="">n.ts and n.id = 31

ORDER BY n.ts ASC</n.ts> `

979_screen_shot_2018-04-18_at_10.25.43.png

jetzt bleibt mir noch eine frage offen hoffe da könnt ihr mir auch mit helfen.

Man muss ja jetzt für jeden wert in de Grafik einen eigenen SQL query schreiben mit der dazugehörigen ID.

Oben wird genannt das man Variablen/datanpunkte definieren kann sodas man diese auswählen könnte ich blick da aber nicht ganz durch.

Ich würde halt gerne erreichen das ich nicht jedesmal den query ändern muss, sondern die datenpunkte der DB selektieren kann gibt es dafür eine Möglichkeit ?

cheers und danke soweit !

~Dutch

Dutchman

Könntet ihr mir bitte nochmal auf die Sprünge helfen mit einem query der 2 werte miteinander aufzählt und das Resultat als Linie darstellt ?

Hintergrund : Ich habe stroommessungen die einen tage/nacht wert und Tarif kennen, im moment bilde ich diese werte in grafana ab:

979_screen_shot_2018-04-20_at_13.04.00.png

mit den folgende SQL statements:

Wert 1:

SELECT
floor(n.ts/1000) as time_sec,
n.val*1000 as value,
"Afnahme Dal" as metric
FROM ts_number n
WHERE $__unixEpochFrom()*1000<n.ts and/$__unixepochto()*1000="">n.ts and n.id = 3310
ORDER BY n.ts ASC</n.ts>

Wert 2:

SELECT
floor(n.ts/1000) as time_sec,
n.val*1000 as value,
"Afnahme Piek" as metric
FROM ts_number n
WHERE $__unixEpochFrom()*1000<n.ts and/$__unixepochto()*1000="">n.ts and n.id = 3300 
ORDER BY n.ts ASC</n.ts>

ich würde aber gerne eine Linie haben der wert 1 + wert 2 zeichnet anstatt 2 unterschiedlichen Linien (also das total der beiden werte.

bin für jeden tip sehr dankbar!

sissiwup

Hi,

hast du schonmal in grafana -> Display -> stack probiert. Das addiert für dich die Kurven.

Ansosnten ist das Problem bei deinem Wunsch, das die beiden Sensoren nicht immer zur gleichen Zeit Werte liefern.

Dutchman

~~@sissiwup:~~

Ansosnten ist das Problem bei deinem Wunsch, das die beiden Sensoren nicht immer zur gleichen Zeit Werte liefern. `

Ja nur das klappt in meiner Situation nicht da ich eine Grafik mit Strom Tag/nach und Gas habe (Mal als Beispiel)

Strom muss addiert werden, Gas nicht.

Die Zeiten sind gleich werden durch den Smartmeter Adapter alle 10sekunden übertragen.

Edit: hast Recht Zeiten sind nicht gleich wegen dem "nur loggen bei Änderungen"

Hmm Dan muss ich Gas halt Weck lassen und eigene grafirl bauen.

Problem aber auch das er Linien nicht durchzeichnet. Wen kein Wert da ist, kan man lösen durch alle x min den selben Wert zu loggen vermüllt aber unnötig die DB

~Dutch

–-----------------------

Send from mobile device

Das schöne ios hat Auto Korrektur zum k****

Wer Schreibfehler findet darf sie behalten oder auf eBay verkaufen, mindest Umsatz 10% für die community

sissiwup

Unter Display kannst du sagen ob null-Werte als null oder als Verbunden gehandelt werden.

Ansonsten kannst du unter Display Override dein Gas aus dem Stacking rausnehmen und z.B. auch der rechten Y-Achse zuordnen.

Dutchman

~~@sissiwup:~~

Unter Display kannst du sagen ob null-Werte als null oder als Verbunden gehandelt werden.

Ansonsten kannst du unter Display Override dein Gas aus dem Stacking rausnehmen und z.B. auch der rechten Y-Achse zuordnen. `

Das Stacking gilt leider für die gesamte Grafik, y-Achse links und rechts werden hierbei nicht separiert womit Gas & Strom doch wieder in der Grafik zusammen aufgezählt werden.

Nachteil vom stacking is auch das die Linie zwar aufeinander addiert werden aber kein total wert angezeigt wird (aus durch schaut nach der achse)

979_screen_shot_2018-04-21_at_16.26.03.png

NULL werte connected hab ich an, diese Einstellung verbindet aber nur NULL werten zischen datenpunkten und nicht wen am ende einer fehlt.

Ich habe das jetzt gelöst durch alle 5 Minuten die werte in die DB zu schreiben auch wen diese sich nicht geändert haben diese Lösung finde ich aber nicht schön (unnötige dauen in der DB)

~Dutch

sissiwup

Hallo,

Series Override ist hier das Zauberwort:
609_bildschirmfoto_2018-04-22_um_10.13.02.png

Du siehst hier, alle Werte sind gestackt, nur der my-sql Wert nicht.

Und hier machst du auch die Zuordnung zur rechten Y-Achse.

Du kannst hier nahezu alle Default-Werte für jede Kurve überschreiben.

Beim Hover kannst du angeben, das er die Werte addiert, d.h. wenn du mit der Maus

über die Grafik fährst, siehts du eine Summe und nicht nur die Einzelwerte.

Dutchman

Hi Sissiwup,

Danke dir der Serie override hat das Problem mit dem stacking gelöst diese Funktion hatte ich übersehen :/

Das letztere ergibt leider nicht nicht das gewünschte Resultat, wenn hover auf kumultatief wird der Gesamtwert auf dem bestehenden datenpunkt wiedergegeben.

Dadurch hat mein ein falsches Bild ( zb Strom Abnahme Nacht ist nicht jetziger Wert sondern Nacht + Tag)

Resultat sollte aber sein Nacht = jetziger Wert, Tag = jetziger Wert und dan ein total Nacht + Tag.

~~![](</s><URL url=)<link_text text="https://uploads.tapatalk-cdn.com/201804 ... e3eb19.jpg">https://uploads.tapatalk-cdn.com/20180422/29f94a17332e670d47d9c2b614e3eb19.jpg</link_text>" />

~~![](</s><URL url=)<link_text text="https://uploads.tapatalk-cdn.com/201804 ... d5ac26.jpg">https://uploads.tapatalk-cdn.com/20180422/b949764b71f68d7467d3f9e2fbd5ac26.jpg</link_text>" />

Verzonden vanaf mijn iPad met Tapatalk~~~~

Dutchman

Mal als Beispiel, das ergibt jetzt auch solche lustige Sachen…

Aus holländisch übersetzt, dal = Nacht/Wochenende, Piek = Tag.

Jetzt sieht es in de Grafik aber so aus als hätte ich am Wochenende Tag Strom benutzt was natürlich nicht stimmt. An den Werten sieht man es dan aber die Linie ist lustig so.

Daher hätte ich lieber einen total Wert separat.

~~![](</s><URL url=)<link_text text="https://uploads.tapatalk-cdn.com/201804 ... e4d7f4.jpg">https://uploads.tapatalk-cdn.com/20180422/1e6895ebdc50fd82a976531807e4d7f4.jpg</link_text>" />

Verzonden vanaf mijn iPad met Tapatalk~~

sissiwup

~~@Dutchman:~~

Hi Sissiwup,

Danke dir der Serie override hat das Problem mit dem stacking gelöst diese Funktion hatte ich übersehen :/

Das letztere ergibt leider nicht nicht das gewünschte Resultat, wenn hover auf kumultatief wird der Gesamtwert auf dem bestehenden datenpunkt wiedergegeben.

Dadurch hat mein ein falsches Bild ( zb Strom Abnahme Nacht ist nicht jetziger Wert sondern Nacht + Tag)

Resultat sollte aber sein Nacht = jetziger Wert, Tag = jetziger Wert und dan ein total Nacht + Tag.

~~![](</s><URL url=)<link_text text="https://uploads.tapatalk-cdn.com/201804 ... e3eb19.jpg">https://uploads.tapatalk-cdn.com/20180422/29f94a17332e670d47d9c2b614e3eb19.jpg</link_text>" />

~~![](</s><URL url=)<link_text text="https://uploads.tapatalk-cdn.com/201804 ... d5ac26.jpg">https://uploads.tapatalk-cdn.com/20180422/b949764b71f68d7467d3f9e2fbd5ac26.jpg</link_text>" />

Verzonden vanaf mijn iPad met Tapatalk~~~~ ` ~~~~Hallo,

das lässt sich vermutlich mit ein bisschen SQL-Magic lösen:

SELECT
  time_sec,
  sum(t.value) as value,
  "Strom (Summe)" as metric
FROM 
((select cast(cast(n.ts/1000/600 as signed) * 600 as signed)  as time_sec,
  avg(n.val) as value 
  from ts_number n where $__unixEpochFrom()*1000<n.ts and/$__unixepochto()*1000="">n.ts and n.id = 110 
  group by time_sec)
  union all 
  (select cast(cast(n.ts/1000/600 as signed) * 600 as signed)  as time_sec,
  avg(n.val) as value 
  from ts_number n where $__unixEpochFrom()*1000<n.ts and/$__unixepochto()*1000="">n.ts and n.id = 107 
  group by time_sec)
  ) t
WHERE $__unixEpochFrom()<t.time_sec and/$__unixepochto()="">t.time_sec   
group by t.time_sec
ORDER BY t.time_sec</t.time_sec></n.ts></n.ts>

Wie gehts: die id`s sind die deiner beiden Datenpunkte.

Es wird der Durchschnitt für 10 Minuten (daher die 600) berechnet.

Das wird dann unter den graden Wert gespeichert, also 6:00,6:10,6:20,6:30,6:40 …

Dann werden einfach beide Werte addiert, da sie ja jetzt immer zu exakt selben Zeit vorliegen.

Das Intervall sollte so gewählt werden, das sicher von beiden Adaptern ein Wert vorhanden ist.

Das kannst du auch beinflussen indem du im SQL-Adapter z.B. einstellt alle 9 Minuten Speichern.

Auch ohne Änderung.~~~~

Community Forum

NEWS

Grafana-Abfragen von iobroker MySQL

Support us

393

32.4k

81.5k

1.3m