Grafana-Abfragen von iobroker MySQL

[piForscher]

Hallo,

ich würde gerne Grafana zur Visualisierung von iobroker-Daten nutzen, verstehe aber nicht so richtig, wie ich die Abfragen gestalten muss, um die Daten aus einer iobroker-Mysql-DB zu holen:

Die eigentlichen Daten sind ja in der Tabelle 'ts_number', die Kanalnamen in 'datapoints'.

In Grafana brauche ich eine Abfrage, die ungefähr so aussieht:
` > SELECT

UNIX_TIMESTAMP(ts) as time_sec,

val as value,

id as metric

FROM ts_number

WHERE $__timeFilter(ts) `

So kommen aber keine Datenpunkte zurück (Verbindung zur MySQL ist ok)…

Hat mal jemand ein Beispiel, wie ich das aufbauen muss ?

Danke!

[apollon77]

Erst einmal willst Du die "id"-SPalte nicht abfragen sondern du willst Sie selektieren, Die muss also ins ""WHERE".

Dann ist die time_sec denke ich falsch, da das Feld ein Millisekunden-Zahlenwert ist und muss eher sowas sein wie

cast(timestamp/1000 as signed integer) as time_sec

Ansonsten tippe ich das das $__timeFilter(ts) hier mit einem Zahlenfeld nicht kompatibel ist. Fürchte das muss Du auch selbst umschreiben in

ts >= $__unixEpochFrom()*1000 AND ts <= $__unixEpochTo()*1000

Laut Doku gehen die von einem Unix-Timestamp-Feld aus. Das sollten Sekunden sein. ioBroker speichert aber Millisekunden.

Versuch mal … ich denke da musst du "tweaken"

[sissiwup]

Name wird aus Datapoints übernommen

SELECT
  floor(n.ts/1000) as time_sec,
  n.val as value,
  d.name 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>

Name wird vorgegeben

SELECT
  floor(n.ts/1000) as time_sec,
  n.val as value,
  "Text" 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>

Mehrere Bool-Werte in einem Diagramm

SELECT
  floor(n.ts/1000) as time_sec,
  case when n.val=true then 9 else 0 end as value,
  "Bool1" as metric
FROM ts_bool n
WHERE $__unixEpochFrom()*1000<n.ts and/$__unixepochto()*1000="">n.ts and n.id = 225 
ORDER BY n.ts ASC

SELECT
  floor(n.ts/1000) as time_sec,
  case when n.val=true then 19 else 10 end as value,
  "Bool2" as metric
FROM ts_bool n
WHERE $__unixEpochFrom()*1000<n.ts and/$__unixepochto()*1000="">n.ts and n.id = 226 
ORDER BY n.ts ASC</n.ts></n.ts>

[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

dauen in flot:

und dan grafana:

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> `

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:

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)

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:

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.