Modul 93_DbRep - Reporting und Management von Datenbankinhalten (DbLog)

Begonnen von DS_Starter, 19 Mai 2016, 22:52:13

Vorheriges Thema - Nächstes Thema

DS_Starter

Hallo Alex,

habe get <> versionNotes 2 gleich noch etwas aufgewertet und eingecheckt.
Jetzt kommt in diesem Fall:


     2020-06-27 13:29:48   2020-06-25__Dum.Energy__AutarkyQuote__AVGDMGWS__2020-06-25 52.3
     2020-06-27 13:29:48   2020-06-26__Dum.Energy__AutarkyQuote__AVGDMGWS__2020-06-26 51.6
     2020-06-27 13:29:48   2020-06-27__Dum.Energy__AutarkyQuote__AVGDMGWS__2020-06-27 insufficient values - execute get Rep.CPU versionNotes 2 for further information


Und wenn man das tut ... siehe Anhang.
Sollte jetzt deutlicher werden.

LG,
Heiko
ESXi@NUC+Debian+MariaDB, PV: SMA, Victron MPII+Pylontech+CerboGX
Maintainer: SSCam, SSChatBot, SSCal, SSFile, DbLog/DbRep, Log2Syslog, SolarForecast,Watches, Dashboard, PylonLowVoltage
Kaffeekasse: https://www.paypal.me/HMaaz
Contrib: https://svn.fhem.de/trac/browser/trunk/fhem/contrib/DS_Starter

Nighthawk


ch.eick

Hallo zusammen,
hier kommt mal wieder eins von Christians specialSQL für MySQL Datenbanken;-)

Es wird das letzte reading eines Monats der letzten 12 Monate ermittelt.

Der Hintergrund ist, dass ich einen Plot z.B. der PV Erträge der letzten Monate erstellen möchte. Hierbei ist das device Dum.Energy meine PV Bilanz und das reading PVMonth steigt stundenweise über den ganzen Monat. Im anhängenden Bild wird es eventuell klarer. Somit zeigt der letzte Eintrag in jedem Monat den gesamt Monatsertrag. Es werden nur drei Monate angezeigt, weil ich es noch nicht länger laufen habe.

SET @device = 'Dum.Energy';SET @reading='PVMonth';

SELECT TIMESTAMP,READING, VALUE
   FROM (
     (SELECT TIMESTAMP,READING,VALUE FROM (SELECT TIMESTAMP,READING,VALUE FROM history WHERE DEVICE = @device AND READING = @reading AND TIMESTAMP BETWEEN LAST_DAY(NOW() - interval  0 month) AND LAST_DAY(NOW() - interval  0 month) + interval 1 day ORDER BY TIMESTAMP DESC LIMIT 2) AS X1 ORDER BY TIMESTAMP ASC LIMIT 1)
     UNION ALL
     (SELECT TIMESTAMP,READING,VALUE FROM (SELECT TIMESTAMP,READING,VALUE FROM history WHERE DEVICE = @device AND READING = @reading AND TIMESTAMP BETWEEN LAST_DAY(NOW() - interval  1 month) AND LAST_DAY(NOW() - interval  1 month) + interval 1 day ORDER BY TIMESTAMP DESC LIMIT 2) AS X1 ORDER BY TIMESTAMP ASC LIMIT 1)
     UNION ALL
     (SELECT TIMESTAMP,READING,VALUE FROM (SELECT TIMESTAMP,READING,VALUE FROM history WHERE DEVICE = @device AND READING = @reading AND TIMESTAMP BETWEEN LAST_DAY(NOW() - interval  2 month) AND LAST_DAY(NOW() - interval  2 month) + interval 1 day ORDER BY TIMESTAMP DESC LIMIT 2) AS X1 ORDER BY TIMESTAMP ASC LIMIT 1)
     UNION ALL
     (SELECT TIMESTAMP,READING,VALUE FROM (SELECT TIMESTAMP,READING,VALUE FROM history WHERE DEVICE = @device AND READING = @reading AND TIMESTAMP BETWEEN LAST_DAY(NOW() - interval  3 month) AND LAST_DAY(NOW() - interval  3 month) + interval 1 day ORDER BY TIMESTAMP DESC LIMIT 2) AS X1 ORDER BY TIMESTAMP ASC LIMIT 1)
     UNION ALL
     (SELECT TIMESTAMP,READING,VALUE FROM (SELECT TIMESTAMP,READING,VALUE FROM history WHERE DEVICE = @device AND READING = @reading AND TIMESTAMP BETWEEN LAST_DAY(NOW() - interval  4 month) AND LAST_DAY(NOW() - interval  4 month) + interval 1 day ORDER BY TIMESTAMP DESC LIMIT 2) AS X1 ORDER BY TIMESTAMP ASC LIMIT 1)
     UNION ALL
     (SELECT TIMESTAMP,READING,VALUE FROM (SELECT TIMESTAMP,READING,VALUE FROM history WHERE DEVICE = @device AND READING = @reading AND TIMESTAMP BETWEEN LAST_DAY(NOW() - interval  5 month) AND LAST_DAY(NOW() - interval  5 month) + interval 1 day ORDER BY TIMESTAMP DESC LIMIT 2) AS X1 ORDER BY TIMESTAMP ASC LIMIT 1)
     UNION ALL
     (SELECT TIMESTAMP,READING,VALUE FROM (SELECT TIMESTAMP,READING,VALUE FROM history WHERE DEVICE = @device AND READING = @reading AND TIMESTAMP BETWEEN LAST_DAY(NOW() - interval  6 month) AND LAST_DAY(NOW() - interval  6 month) + interval 1 day ORDER BY TIMESTAMP DESC LIMIT 2) AS X1 ORDER BY TIMESTAMP ASC LIMIT 1)
     UNION ALL
     (SELECT TIMESTAMP,READING,VALUE FROM (SELECT TIMESTAMP,READING,VALUE FROM history WHERE DEVICE = @device AND READING = @reading AND TIMESTAMP BETWEEN LAST_DAY(NOW() - interval  7 month) AND LAST_DAY(NOW() - interval  7 month) + interval 1 day ORDER BY TIMESTAMP DESC LIMIT 2) AS X1 ORDER BY TIMESTAMP ASC LIMIT 1)
     UNION ALL
     (SELECT TIMESTAMP,READING,VALUE FROM (SELECT TIMESTAMP,READING,VALUE FROM history WHERE DEVICE = @device AND READING = @reading AND TIMESTAMP BETWEEN LAST_DAY(NOW() - interval  8 month) AND LAST_DAY(NOW() - interval  8 month) + interval 1 day ORDER BY TIMESTAMP DESC LIMIT 2) AS X1 ORDER BY TIMESTAMP ASC LIMIT 1)
     UNION ALL
     (SELECT TIMESTAMP,READING,VALUE FROM (SELECT TIMESTAMP,READING,VALUE FROM history WHERE DEVICE = @device AND READING = @reading AND TIMESTAMP BETWEEN LAST_DAY(NOW() - interval  9 month) AND LAST_DAY(NOW() - interval  9 month) + interval 1 day ORDER BY TIMESTAMP DESC LIMIT 2) AS X1 ORDER BY TIMESTAMP ASC LIMIT 1)
     UNION ALL
     (SELECT TIMESTAMP,READING,VALUE FROM (SELECT TIMESTAMP,READING,VALUE FROM history WHERE DEVICE = @device AND READING = @reading AND TIMESTAMP BETWEEN LAST_DAY(NOW() - interval 10 month) AND LAST_DAY(NOW() - interval 10 month) + interval 1 day ORDER BY TIMESTAMP DESC LIMIT 2) AS X1 ORDER BY TIMESTAMP ASC LIMIT 1)
     UNION ALL
     (SELECT TIMESTAMP,READING,VALUE FROM (SELECT TIMESTAMP,READING,VALUE FROM history WHERE DEVICE = @device AND READING = @reading AND TIMESTAMP BETWEEN LAST_DAY(NOW() - interval 11 month) AND LAST_DAY(NOW() - interval 11 month) + interval 1 day ORDER BY TIMESTAMP DESC LIMIT 2) AS X1 ORDER BY TIMESTAMP ASC LIMIT 1)

  ) AS X1 ORDER BY TIMESTAMP;
+---------------------+---------+--------+
| TIMESTAMP           | READING | VALUE  |
+---------------------+---------+--------+
| 2020-04-30 23:00:00 | PVMonth | 563.77 |
| 2020-05-31 23:00:00 | PVMonth | 511.61 |
| 2020-06-30 19:00:00 | PVMonth | 465.54 |
+---------------------+---------+--------+


@Heiko Wie könnte ich daraus im DBRep einen SVG Plot erstellen? Ich gestehe, dass ich das noch nicht erarbeitet habe :-(

Internals:
   DATABASE   fhem
   DEF        LogDB
   FUUID      5e57f222-f33f-61a8-e514-be207ebe812c1bee
   FVERSION   93_DbRep.pm:v8.40.2-s22287/2020-06-27
...
   MODEL      Client
   NAME       LogDBRep
   NOTIFYDEV  global,LogDBRep
   NR         420
   NTFY_ORDER 50-LogDBRep
   ROLE       Client
   STATE      done
   TYPE       DbRep
   UTF8       1
   HELPER:
     DBLOGDEVICE LogDB
     GRANTS     ALL PRIVILEGES,USAGE
     IDRETRIES  3
     MINTS      2019-04-03 00:23:42
     PACKAGE    main
     SQLHIST   
     VERSION    8.40.2
     CV:
       aggregation no
       aggsec     1
       destr      2020-06-30
       dsstr      2020-06-30
       epoch_seconds_end 1593554399
       mestr      06
       msstr      06
       testr      23:59:59
       tsstr      00:00:00
       wdadd      518400
       yestr      2020
       ysstr      2020
     DBREPCOL:
       COLSET     1
       DEVICE     64
       EVENT      512
       READING    64
       TYPE       64
       UNIT       32
       VALUE      128
   OLDREADINGS:
   READINGS:
     2020-06-30 19:33:16   SqlResultRow_1  TIMESTAMP|READING|VALUE
     2020-06-30 19:33:16   SqlResultRow_2  2020-04-30 00:00:00|PVMonth|545.34
     2020-06-30 19:33:16   SqlResultRow_3  2020-05-31 00:00:00|PVMonth|492.22
     2020-06-30 19:33:16   SqlResultRow_4  2020-06-30 00:00:00|PVMonth|451.55
     2020-06-30 19:33:16   sqlCmd          ...Mein Select...
     2020-06-30 19:33:16   sqlResultNumRows 3
     2020-06-30 19:33:16   state           done
Attributes:
   DbLogExclude .*
   allowDeletion 0


Viele Gruesse
    Christian
RPI4; Docker; CUNX; Eltako FSB61NP; SamsungTV H-Serie; Sonos; Vallox; Luxtronik; 3x FB7490; Stromzähler mit DvLIR; wunderground; Plenticore 10 mit BYD; EM410; SMAEM; Modbus TCP
Contrib: https://svn.fhem.de/trac/browser/trunk/fhem/contrib/ch.eick

DS_Starter

Hallo Christian,

ich denke das geht einfacher oder ich habe etwas nicht verstanden.
Also grundsätzlich kannst du nur Werte mit SVG plotten die sich in der DB befinden.

Wenn du aber schon ein Reading PVMonth in der DB hast, reicht ein

set <> maxValue display


mit gesetzten Attribut

aggregation = month

Dann bekommst du die max. Werte jeden Monats in den gesetzten time* Grenzen.

Und wenn du jetzt


set <> maxValue writeToDB


ausführst, werden die ermittelten MAX Werte mit neuem Readingnamen in die DB zurückgeschrieben und du kannst sie plotten.  :)

Grüße,
Heiko
ESXi@NUC+Debian+MariaDB, PV: SMA, Victron MPII+Pylontech+CerboGX
Maintainer: SSCam, SSChatBot, SSCal, SSFile, DbLog/DbRep, Log2Syslog, SolarForecast,Watches, Dashboard, PylonLowVoltage
Kaffeekasse: https://www.paypal.me/HMaaz
Contrib: https://svn.fhem.de/trac/browser/trunk/fhem/contrib/DS_Starter

ch.eick

Okay, das werde ich mal testen.

EDIT: Das ist es was ich gesucht hatte, mein SQL war somit eine schöne Uebung und klappt auf dem MySQL Promt.

Hier die Attribute und readings mit DBLog
Anmerkung: Der erste Wert im Monat um 00-00-00 gehört leider zum Vormonat, aber da schaue ich nochmal mit meinem Timing...

Attributes:
   aggregation month
   device     Dum.Energy
   reading    PVMonth
    timestamp_begin current_year_begin
   timestamp_end current_day_end


   READINGS:
     2020-07-01 11:05:02   2020-01-01__Dum.Energy__PVMonth__MAX__2020-01 0.0000
     2020-07-01 11:05:02   2020-02-01__Dum.Energy__PVMonth__MAX__2020-02 0.0000
     2020-07-01 11:05:02   2020-03-01__Dum.Energy__PVMonth__MAX__2020-03 0.0000
     2020-07-01 11:05:02   2020-04-30_23-00-00__Dum.Energy__PVMonth__MAX__2020-04 563.7700
     2020-07-01 11:05:02   2020-05-01_00-00-00__Dum.Energy__PVMonth__MAX__2020-05 564.1500
     2020-07-01 11:05:02   2020-06-01_00-00-00__Dum.Energy__PVMonth__MAX__2020-06 512.1700
     2020-07-01 11:05:02   2020-07-01_00-00-00__Dum.Energy__PVMonth__MAX__2020-07 467.5600


Vielen Dank Christian
RPI4; Docker; CUNX; Eltako FSB61NP; SamsungTV H-Serie; Sonos; Vallox; Luxtronik; 3x FB7490; Stromzähler mit DvLIR; wunderground; Plenticore 10 mit BYD; EM410; SMAEM; Modbus TCP
Contrib: https://svn.fhem.de/trac/browser/trunk/fhem/contrib/ch.eick

ch.eick

Hallo nochmal,

jetzt habe ich die max_month_PVMonth Werte am letzten Tag des Monats um 23:59 Uhr in der DBLog.
Die Krönung wäre natürlich, wenn man die Beschriftung under den bar verschieben könnte, aber so reicht es auch :-)

Wenn ich das nun mit SVG visualisiere sieht das Ganze wie folgt aus:

# Created by FHEM/98_SVG.pm, 2020-07-01 13:57:43
set terminal png transparent size <SIZE> crop
set output '<OUT>.png'
set xdata time
set timefmt "%Y-%m-%d_%H:%M:%S"
set xlabel " "
set title 'PV_Bilanz'
set ytics
set y2tics
set grid
set ylabel ""
set y2label ""

#LogDB Dum.Energy:max_month_PVMonth::

plot "<IN>" using 1:2 axes x1y2 title 'PVMonth' ls l2 lw 1 with bars


Vielen Dank fuer die Hilfe
     Christian
RPI4; Docker; CUNX; Eltako FSB61NP; SamsungTV H-Serie; Sonos; Vallox; Luxtronik; 3x FB7490; Stromzähler mit DvLIR; wunderground; Plenticore 10 mit BYD; EM410; SMAEM; Modbus TCP
Contrib: https://svn.fhem.de/trac/browser/trunk/fhem/contrib/ch.eick

DS_Starter

Hi Christian,

prima.  :D

ZitatDie Krönung wäre natürlich, wenn man die Beschriftung under den bar verschieben könnte, aber so reicht es auch :-)
Kannst du ja mal im SVG Forum nachfragen. Ich bin da nicht so der Experte der Feinheiten vom SVG.

Grüße,
Heiko
ESXi@NUC+Debian+MariaDB, PV: SMA, Victron MPII+Pylontech+CerboGX
Maintainer: SSCam, SSChatBot, SSCal, SSFile, DbLog/DbRep, Log2Syslog, SolarForecast,Watches, Dashboard, PylonLowVoltage
Kaffeekasse: https://www.paypal.me/HMaaz
Contrib: https://svn.fhem.de/trac/browser/trunk/fhem/contrib/DS_Starter

DS_Starter

@CHristian,

es wäre trotzdem schön wenn du dein Statement wieder im Wiki einbringen würdes. Man kann davon eigene Übungen ableiten.  8)

Grüße,
Heiko
ESXi@NUC+Debian+MariaDB, PV: SMA, Victron MPII+Pylontech+CerboGX
Maintainer: SSCam, SSChatBot, SSCal, SSFile, DbLog/DbRep, Log2Syslog, SolarForecast,Watches, Dashboard, PylonLowVoltage
Kaffeekasse: https://www.paypal.me/HMaaz
Contrib: https://svn.fhem.de/trac/browser/trunk/fhem/contrib/DS_Starter

ch.eick

Zitat von: DS_Starter am 01 Juli 2020, 14:21:57
es wäre trotzdem schön wenn du dein Statement wieder im Wiki einbringen würdes. Man kann davon eigene Übungen ableiten.  8)
Waere es da nicht besser das von Dir verwendete SQL, das den Monatswert ermittelt, zu dokumentieren?
RPI4; Docker; CUNX; Eltako FSB61NP; SamsungTV H-Serie; Sonos; Vallox; Luxtronik; 3x FB7490; Stromzähler mit DvLIR; wunderground; Plenticore 10 mit BYD; EM410; SMAEM; Modbus TCP
Contrib: https://svn.fhem.de/trac/browser/trunk/fhem/contrib/ch.eick

DS_Starter

Naja, das sieht man ja wenn man verbose 4, respective 5,  einschaltet  ;)
ESXi@NUC+Debian+MariaDB, PV: SMA, Victron MPII+Pylontech+CerboGX
Maintainer: SSCam, SSChatBot, SSCal, SSFile, DbLog/DbRep, Log2Syslog, SolarForecast,Watches, Dashboard, PylonLowVoltage
Kaffeekasse: https://www.paypal.me/HMaaz
Contrib: https://svn.fhem.de/trac/browser/trunk/fhem/contrib/DS_Starter

ch.eick

Zitat von: DS_Starter am 01 Juli 2020, 14:31:24
Naja, das sieht man ja wenn man verbose 4, respective 5,  einschaltet  ;)
Dokumentation im Code war schon immer geil :-)
RPI4; Docker; CUNX; Eltako FSB61NP; SamsungTV H-Serie; Sonos; Vallox; Luxtronik; 3x FB7490; Stromzähler mit DvLIR; wunderground; Plenticore 10 mit BYD; EM410; SMAEM; Modbus TCP
Contrib: https://svn.fhem.de/trac/browser/trunk/fhem/contrib/ch.eick

DS_Starter

#1241
 :) ... ich komme einfach nicht mehr hinterher bei allem was ich momentan an den verschiedensten Module so weiterentwickle. Sonst dokumentiere ich auch gerne und viel ...

EDIT: Will damit sagen wenn jemand gerne das Wiki mit Doku abgeleitet aus DbRep-Statements ergänzen möchte ist er gern eingeladen und ich freue mich über die Unterstützung !
ESXi@NUC+Debian+MariaDB, PV: SMA, Victron MPII+Pylontech+CerboGX
Maintainer: SSCam, SSChatBot, SSCal, SSFile, DbLog/DbRep, Log2Syslog, SolarForecast,Watches, Dashboard, PylonLowVoltage
Kaffeekasse: https://www.paypal.me/HMaaz
Contrib: https://svn.fhem.de/trac/browser/trunk/fhem/contrib/DS_Starter

dk3572

Hallo,

gibt es eine Möglichkeit doppelte bzw. zeitlich kurz aufeinanderfolgende (<15 sec.) Datensätze zu identifizieren und dann zu löschen?

Doppelter Datensatz mit ...Diff_1

     2020-07-14 19:14:17   2020-07-14_05-00-22__1__SMA_Zaehler__Bezug_WirkP_Zaehler_Diff 0.0048
     2020-07-14 19:14:17   2020-07-14_05-00-22__1__SMA_Zaehler__Bezug_WirkP_Zaehler_Diff_1 0.0518


Zeitabstand sollte ca. 60 sec sein. Hier nur 2 sec, daher löschen.

    2020-07-14 19:14:17   2020-07-14_05-41-30__1__SMA_Zaehler__Bezug_WirkP_Zaehler_Diff 0.0031
    2020-07-14 19:14:17   2020-07-14_05-41-32__1__SMA_Zaehler__Bezug_WirkP_Zaehler_Diff 0.2383


Für Ideen sind Heiko und ich dankbar  ;)

VG Dieter

ch.eick

Ich überlege mal mit, kann aber etwas dauern.
Als erstes würde ich das Abfrageintervall schon mal verlängern :-)
Gruß Christian
RPI4; Docker; CUNX; Eltako FSB61NP; SamsungTV H-Serie; Sonos; Vallox; Luxtronik; 3x FB7490; Stromzähler mit DvLIR; wunderground; Plenticore 10 mit BYD; EM410; SMAEM; Modbus TCP
Contrib: https://svn.fhem.de/trac/browser/trunk/fhem/contrib/ch.eick

DS_Starter

Danke fürs Mitdenken Christian :)

Die Aussage

Zitat
Doppelter Datensatz mit ...Diff_1

ist nicht ganz korrekt.. Die Werte sind different, bei doppelten DS wären sie identisch.
Deswegen reduziert sich die Aufgabe DS zeitlich in einem bestimmten kleinen Intervall zu identifizieren und den späteren von beiden zu löschen.
Ein geschicktes SQL wird gesucht.  :D

LG,
Heiko
ESXi@NUC+Debian+MariaDB, PV: SMA, Victron MPII+Pylontech+CerboGX
Maintainer: SSCam, SSChatBot, SSCal, SSFile, DbLog/DbRep, Log2Syslog, SolarForecast,Watches, Dashboard, PylonLowVoltage
Kaffeekasse: https://www.paypal.me/HMaaz
Contrib: https://svn.fhem.de/trac/browser/trunk/fhem/contrib/DS_Starter