How to RRD graph MQTT topic from devices

ok. here a quick how to store and graph data from MQTT it is easy> a couple of people have asked

after you have your MQTT being published from your devices - you need to capture them. but before you can do that you need to create a rrd container data base
this one by example averages out 3 sent mqtt and creates a data base called KW with in the container of 2.6 million entries or about 90 days of data stored if you send data every second

   rrdtool create wind.rrd --step 3 DS:kw:GAUGE:5:0:U RRA:MIN:0.5:12:2592000 RRA:AVERAGE:0.5:1:2592000      RRA:MAX:0.5:1:2592000

next you will need to capture the topics the easiest is a simple pl script

#!/usr/bin/perl -w
open(SUB, "/usr/bin/mosquitto_sub -t /wind |");
#SUB->autoflush(1);
while ($wind = <SUB>) {
 $wind=($wind/1000);
print "$wind ";	
system("rrdtool update wind.rrd N:$wind");
}

this listens for a topic called /wind and when it receives a topic it writes it to the rrd container called wind.rrd ( the emontx was sending mqtt in watts so I divided by 1000 to make it kw for the rrd container)

that’s it, that was simple: now to create a graph:

this particular one creates a graph that is 24 hours in length, displays the min avg, and peak. it also calculates your daily cost and projects your monthly production based on that day

 #!/bin/bash

 now=$(date +%s)
now_formatted=$(date +%s | awk '{printf "%s\n", strftime("%c",$1)}' | sed -e 's/:/\\:/g')

 /usr/bin/rrdtool graph /var/www/html/24hrswind.png \
-w 543 -h 267 -a PNG \
--slope-mode \
--start -86200 --end now \
--title "Wind: For the Last 24 Hours" \
--vertical-label "KILOWATTS (KW)" \
DEF:kw=wind.rrd:kw:AVERAGE \
VDEF:Last=kw,LAST \
VDEF:First=kw,FIRST \
VDEF:Min=kw,MINIMUM \
VDEF:Peak=kw,MAXIMUM \
VDEF:Average=kw,AVERAGE \
CDEF:kWh=kw,10,/,168,* \
CDEF:Proj=kw,1,/,720,* \
CDEF:Cost=kWh,.0665,* \
CDEF:Costp=Proj,.0665,* \
LINE1:kw#005199FF:"wind  " \
AREA:kw#00519933:"" \
GPRINT:Last:"%6.4lf%sKW" \
COMMENT:"\\n" \
HRULE:Average#9595FF:"Average" \
GPRINT:kw:AVERAGE:"%6.4lf%sKW" \
COMMENT:"\\n" \
HRULE:Peak#ff0000:"Peak   " \
GPRINT:kw:MAX:"%6.3lf%sKW" \
COMMENT:"\\n" \
 HRULE:Min#58FAF4:"Min    " \
GPRINT:kw:MIN:"%6.4lf%sKW" \
COMMENT:"\\n" \
GPRINT:kWh:AVERAGE:"  total    %6.2lfkWh\\n" \
GPRINT:Cost:AVERAGE:"  cost     %6.2lf $\\n" \
GPRINT:Proj:AVERAGE:"  Projected %6.2lfKWh in a 30 Day period\\n" \
GPRINT:Costp:AVERAGE:"  cost     %6.2lf $\\n" \
COMMENT:" \\n" \

now all you need to to do is in cron set the Mqtt.pl to run on boot to capture mqtt
and the 24hrs graph script to run every few minutes.

for other optoins using rrdtools see RRDtool - RRDtool Documentation

as a suggestion if you are not the most linux savy person it easy to install webmin. once that is install you have a nice webbase administration panel to talk to you pi or sever remotely to move files around and set up cron or what have you…

good luck have fun

Can you explain the maths behind the kWh calculation? I can’t work out where the 10 and 168 come from.

I never noticed that. Good thing you pointed it out. It’s an error carried over from a previous energy monitor project I did 4 or 5 years ago. Change the 10 to a 7, or change it to 1/24. All I did was import these scripts from that project to this one. Never really reviewed for errors.

Here’s the original that corrected now it also gives a cost estimate CDEF:Cost=kWh,.0665,* \ and
CDEF:Costp=Proj,.0665,* \ to match your cost per kwh in case you were wondering about some added non relevant info in the previous script.

  #!/bin/bash

 now=$(date +%s)
now_formatted=$(date +%s | awk '{printf "%s\n", strftime("%c",$1)}' | sed -e 's/:/\\:/g')

 /usr/bin/rrdtool graph /var/www/24hrs.png \
 -w 543 -h 267 -a PNG \
 --slope-mode \
 --start -86200 --end now \
 --title "Power: For the Last 24 Hours" \
 --vertical-label "KILOWATTS (KW)" \
 DEF:kw=ted.rrd:kw:AVERAGE \
 VDEF:Last=kw,LAST \
 VDEF:First=kw,FIRST \
 VDEF:Min=kw,MINIMUM \
 VDEF:Peak=kw,MAXIMUM \
 VDEF:Average=kw,AVERAGE \
CDEF:kWh=kw,1,/,24,* \
CDEF:Proj=kw,1,/,720,* \
CDEF:Cost=kWh,.0665,* \
CDEF:Costp=Proj,.0665,* \
LINE1:kw#005199FF:"Power  " \
AREA:kw#00519933:"" \
GPRINT:Last:"%6.4lf%sKW" \
COMMENT:"\\n" \
HRULE:Average#9595FF:"Average" \
GPRINT:kw:AVERAGE:"%6.4lf%sKW" \
COMMENT:"\\n" \
HRULE:Peak#ff0000:"Peak   " \
GPRINT:kw:MAX:"%6.3lf%sKW" \
COMMENT:"\\n" \
HRULE:Min#58FAF4:"Min    " \
GPRINT:kw:MIN:"%6.4lf%sKW" \
COMMENT:"\\n" \
GPRINT:kWh:AVERAGE:"  total    %6.2lfkWh\\n" \
GPRINT:Cost:AVERAGE:"  cost     %6.2lf $\\n" \
GPRINT:Proj:AVERAGE:"  Projected %6.2lfKWh in a 30 Day period\\n" \
GPRINT:Costp:AVERAGE:"  cost     %6.2lf $\\n" \
COMMENT:" \\n" \

While we’re at it, perhaps you would like to compare one week to another, or one month to another. Here’s one for that. I always found it kind of useful.

 #!/bin/bash

now=$(date +%s)
now_formatted=$(date +%s | awk '{printf "%s\n", strftime("%c",$1)}' | sed -e 's/:/\\:/g')

# create power graph for last week
/usr/bin/rrdtool graph /var/www/power-week.png \
--start end-7d --width 543 --height 267 --end $now-1min --slope-mode \
--vertical-label "KiloWatts" --lower-limit 0 \
--alt-autoscale-max \
--title "Power: Last week vs. week before" \
--watermark "(©) $(date +%Y) TED monitor " \
--font WATERMARK:8 \
DEF:kw=ted.rrd:kw:AVERAGE \
DEF:Power2=ted.rrd:kw:AVERAGE:end=$now-7d1min:start=end-7d \
VDEF:Last=kw,LAST \
VDEF:First=kw,FIRST \
VDEF:Min=kw,MINIMUM \
VDEF:Peak=kw,MAXIMUM \
VDEF:Average=kw,AVERAGE \
CDEF:kWh=kw,1,/,168,* \
CDEF:Cost=kWh,.0665,* \
SHIFT:Power2:604800 \
LINE1:Power2#00CF00FF:"Last Week\\n" \
HRULE:Min#58FAF4:"Min    " \
GPRINT:kw:MIN:"%6.4lf%sKW" \
COMMENT:"\\n" \
LINE1:kw#005199FF:"Power  " \
AREA:kw#00519933:"" \
GPRINT:Last:"%6.4lf%sKW" \
COMMENT:"\\n" \
HRULE:Average#9595FF:"Average" \
GPRINT:kw:AVERAGE:"%6.4lf%sKW" \
COMMENT:"\\n" \
HRULE:Peak#ff0000:"Peak   " \
GPRINT:kw:MAX:"%6.3lf%sKW" \
COMMENT:"\\n" \
GPRINT:kWh:AVERAGE:"  total    %6.4lfkWh\\n" \
GPRINT:Cost:AVERAGE:"  cost     %6.2lf $\\n" \
GPRINT:Cost:AVERAGE:"$(printf \\" cost %11s\\" $%.2lf | sed 's/\$/\$ /g')\\n" \
COMMENT:" \\n" \

Thanks for this post, it was useful to me.
Now, I suspect that the step values for your AVERAGE and MAX archives are wrong when you create your database with rrdtool create (namely you specified steps of 1 in place of 12, as for MIN). Was this intended?

I would feel more comfortable this way:
rrdtool create wind.rrd --step 3 \ DS:kw:GAUGE:5:0:U \ RRA:MIN:0.5:12:30d \ RRA:AVERAGE:0.5:12:30d \ RRA:MAX:0.5:12:30d

hi jeremie it could very well be wrong, I originally did this 4-5 years ago with my original energy monitor, i do not remember the reasoning to why I did it as such. but in either case I am happy it helped you out somewhat . but in reviewing the rddtool webpage as a refresher it seams that it maybe wrong as I am averaging over a very small window of only 1 step or perhaps it just a a carry over of a typo from long ago in my records and procedures – sorry for any inconvenience it might of caused you…

Oh do not apologize, especially when you just brought a simple setup to my attention! :smile: Will try with fake data to check the effect, I still need to fully grasp how steps work in RRDttol underneath.