[RRD] Digitemp data visualisation on your website

Digitemp data visualisation on your website

How is it done?

Say what?

Just a computer with some .php pages? Or something else? Find out and make one yourself!
This is a small, simple "Mini-How-To" in (the pain) making a temperature-logger.

Update: Since I moved to RRDTool 1.2.11 I had to change the code for a bit. Code below is accurate.

Hardware

First you have to build the setup to get temperatures to computer. So you need to either make or buy a interface for the Maxim-IC's sensors, you can either use DS1820 (old) or the newer one's, DS18S20. (Or any compatible sensor) Just the sensors aren't enough, you have to either build or buy a interface for the sensors to talk with your precious computer. This is something everyone with even the most basic electronical skills should be able to do themselves, the schematic for a passive 9097-interface is really simple:

This connects to serialport, pin-out for DB9 is DTR - Pin4, GNS - Pin5, RXD - Pin2, and TXD - Pin3. Also note that length of the cable between sensors and adapter can't be too long, somewhere near 10 meter's (~33 feet) is considered a max lenght with a passive adapter. This can be increased if one put's a schottky diode to the last sensor in line. (Some reportst even 300 meter / 1000 feet cables) For example my outside-sensor is on approx. 10 meter's cable (~33 feet) and I experienced a lot of glitches (spikes) when importing values from sensors, after I soldered a diode in the outside-sensor all values have been perfect. If you need more info, Google is your friend, search with "1-wire sensor wire lenght".

Software

Applications you need to run a full setup are Digitemp and RRDTtool. If you do it my way, you also need MySQL or some other SQL server. I assume You know how to make a database to SQL and how to build querys so I'm not going there. Digitemp is the reader, it reads values from sensors, but it doesn't store them, (it can log to a basic logfile but not much more) so you have to log them somewhere else. This is where RRDTool comes in, it is capable to both log the values from sensors to a internal database and do the graphics. If You are doing several things with the temperature-values you should consider SQL. Digitemp can use only one query at the time, and since it takes some 15+ second's to read these values you'll get into trouble with< NaN>-values if You have Your digitemp-values crontabbed. Of course you could put them on different minutes, but using SQL is more versatile, for example "when was the coldest day?" or similar stat's can't be done from RRDTool's datafiles. (Not that I'm aware of at least) One other thing with SQL is that it can handle several action at the same time, a thing I noticed RRDTool has some difficulties with. So, go MySQL.

Commands

Shortly put the commandchain is:
- Initialize Digitemp (Fetch info of connected sensors)
- Get current values and store them to SQL-database
- Update RRDTool (it's database) with fresh values
- Make graphics & other needed outputs with suitable intervals

Digitemp Initialization

Digitemp have to be initialized in the boot process, in debian just do a shell-script and update it to rc-runlevels, after that just forget it. I included a "echo -n" command there to see it in boot-log. (Yes, even debian needs to be rebooted sometime) The init-command itself is a one-liner, run the command "digitemp", telling it where to search (-s) for 9097-adapter (/dev/ttyS0, COM1 in my case) and to initialize (-i switch) :

#!/bin/bash

echo -n "Initializing Digitemp Sensors..."

/etc/digitemp/digitemp -s/dev/ttyS0 -i

To top of page

Create .rrd database

The main usage of Digitemp <-> RRDTool is relatively simple. First you have to create the database-file, this done only once, in the installing-phase:

rrdtool create /log/temp/temperature.rrd \
DS:out:GAUGE:600:-50:50 \
DS:in:GAUGE:600:-50:50 \
RRA:AVERAGE:0.5:1:600 \
RRA:AVERAGE:0.5:6:700 \
RRA:AVERAGE:0.5:24:775 \
RRA:AVERAGE:0.5:288:797 \
RRA:MIN:0.5:1:600 \
RRA:MIN:0.5:6:700 \
RRA:MIN:0.5:24:775 \
RRA:MIN:0.5:288:797 \
RRA:MAX:0.5:1:600 \
RRA:MAX:0.5:6:700 \
RRA:MAX:0.5:24:775 \
RRA:MAX:0.5:288:797

This makes a rrd-database with two DS's, one named out and one named in and their minimum value is -50 and maximum is +50 (Remember, I'm on centigrade here) Later on there's three types of databases, a average, max and one min-section for each timespan. ( Day, week, month and year).

Logging values to MySQL

I do the logging first to MySQL database and then update RRDTool (and some additional tasks) with most recent values in the database. I do this in two section, first a modified perl-script named simply "log_temp_to_sql.pl" that reads values from sensors, then updates MySQL with them. (Originally by Brian C. Lane)

#!/usr/bin/perl -W
#
# DigiTemp RRD logging script
# Copyright 1997-2002 by Brian C. Lane <bcl@brianlane.com> www.brianlane.com
# Modified 2004 by Tero Salminen
#<tero.salminen@oravaistemperature.com> www.oravaistemperature.com
# All Rights Reserved
# This program is free software under the terms of the GNU General Public
# License as published by the Free Software Foundation
#
# You should have received a copy of the GNU General Public License along
# with this program; if not, write to the Free Software Foundation, Inc.,
# 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA

use DBI;

# Database info
my $db_name = "<DATABASE>";
my $db_user = "<USERNAME>";
my $db_pass = "<PASSWORD>";

# Files to use
my $digitemp_rcfile = "/root/.digitemprc";
my $digitemp_binary = "/etc/digitemp/digitemp";

my $debug = 0;

# Connect to the database
my $dbh = DBI->connect("dbi:mysql:$db_name","$db_user","$db_pass")
or die "I cannot connect to dbi:mysql:$db_name as $db_user - $DBI::errstr\n";

# Gather information from DigiTemp
# Read the output from digitemp
# Output in form SerialNumber<SPACE>Temperature in Celsius<SPACE>Temperature in Fahrenheit
open( DIGITEMP, "$digitemp_binary -q -a -o\"%R %.2C %.2F\" |" );

while( <DIGITEMP> )
{
print "$_\n" if($debug);
chomp;

($serialnumber,$celsius,$fahrenheit) = split(/ /);

my $sql="INSERT INTO digitemp SET SerialNumber='$serialnumber',Celsius='$celsius',Fahrenheit='$fahrenheit'";
print "SQL: $sql\n" if($debug);
$dbh->do($sql) or die "Can't execute statement $sql because: $DBI::errstr";
}

close( DIGITEMP );

$dbh->disconnect;

exit;

To top of page

Process temperatures from SQL-database

Now all we gotta do is to export the data from MySQL to RRDTool, draw the graphics and "current temperature is"- files in centigrade and fahrenheit for php to use in webpages. This is done with a file called process_data.sh, a simple script where I collected all the commands necessary. This script is entirely by me, so don't comment too hard, never did anything like this before. (But hey, it work's?)

#!/bin/bash
#
# DigiTemp action script. (Input data to RRD, make files for php to include, do graphics)
# Copyright 2005 by Tero Salminen
#<tero.salminen@oravaistemperature.com> www.oravaistemperature.com
# All Rights Reserved

# Get the latest values from SQL-database and define values for outside
# and inside temp's based on sensor's serialnumbers, then select latest value
sqlout=`mysql -u <USER> -p<PASSWORD> digitemp -N -e"SELECT celsius FROM digitemp WHERE \
serialnumber = '104D185800080034' ORDER BY dtkey DESC LIMIT 1;" | awk '{print $1}'`
sqlin=`mysql -u <USER> -p<PASSWORD> digitemp -N -e"SELECT celsius FROM digitemp WHERE \
serialnumber = '28C0274F00000052' ORDER BY dtkey DESC LIMIT 1;" | awk '{print $1}'`

# Update the RRDTool database with latest values
rrdtool update /log/temp/temperature.rrd N:$sqlout:$sqlin

# Fetch latest values (Both °C and °F) from SQL and put them into files for php
mysql -u <USER> -p<PASSWORD> digitemp -N -e"SELECT celsius FROM digitemp WHERE \
serialnumber = '104D185800080034' ORDER BY dtkey DESC LIMIT 1;"\
>/var/www/temps/data/1cnow_out
mysql -u <USER> -p<PASSWORD> digitemp -N -e"SELECT celsius FROM digitemp WHERE \
serialnumber = '28C0274F00000052' ORDER BY dtkey DESC LIMIT 1;"\
>/var/www/temps/data/1cnow_in
mysql -u <USER> -p<PASSWORD> digitemp -N -e"SELECT fahrenheit FROM digitemp \
WHERE serialnumber = '104D185800080034' ORDER BY dtkey DESC LIMIT 1;"\
>/var/www/temps/data/1fnow_out
mysql -u <USER> -p<PASSWORD> digitemp -N -e"SELECT fahrenheit FROM digitemp WHERE \
serialnumber = '28C0274F00000052' ORDER BY dtkey DESC LIMIT 1;"\
>/var/www/temps/data/1fnow_in

# Sleep for 10 seconds (rrdtool databaseupdate takes a few seconds)
sleep 10

# Do the graphics
# Way too simple approach to do 4 .png's
# to include in webpages
# TODO: make some variables...

rrdtool graph /var/www/temps/data/day.png \
-n DEFAULT:0:/usr/share/fonts/truetype/msttcorefonts/arial.ttf \
-c CANVAS#FFFFFF \
-c GRID#BBBBBB \
-c MGRID#BBBBBB \
-t "Temperature in Oravainen, Finland °C" \
-h 279 -w 575 \
-l 0 \
-i \
-E \
DEF:Outside=/etc/digitemp/log/temperature.rrd:out:AVERAGE \
DEF:Inside=/etc/digitemp/log/temperature.rrd:in:AVERAGE \
LINE2:Inside#3333CC:"Comp room\t" \
GPRINT:Inside:MAX:"Max\: %6.2lf°C\t" \
GPRINT:Inside:MIN:"Min\: %6.2lf°C\t" \
GPRINT:Inside:AVERAGE:"Average\: %6.2lf°C\t" \
GPRINT:Inside:LAST:"Current\: %6.2lf°C\\c" \
LINE2:Outside#FF0000:"Outside\t" \
GPRINT:Outside:MAX:"Max\: %6.2lf°C\t" \
GPRINT:Outside:MIN:"Min\: %6.2lf°C\t" \
GPRINT:Outside:AVERAGE:"Average\: %6.2lf°C\t" \
GPRINT:Outside:LAST:"Current\: %6.2lf°C\\c" \
COMMENT:""\\c \
COMMENT:"Copyright ? tero.salminen@oravaistemperature.com 2005\c"

rrdtool graph /var/www/temps/data/year.png \
-n DEFAULT:0:/usr/share/fonts/truetype/msttcorefonts/arial.ttf \
-c CANVAS#FFFFFF \
-c GRID#BBBBBB \
-c MGRID#BBBBBB \
-t "Temperature in Oravainen, Finland °C" \
-h 279 -w 575 \
-l 0 \
-E \
-i \
--lazy \
-s -1year \
DEF:Outside=/etc/digitemp/log/temperature.rrd:out:AVERAGE \
DEF:Outside2=/etc/digitemp/log/temperature.rrd:out:MAX \
DEF:Outside3=/etc/digitemp/log/temperature.rrd:out:MIN \
DEF:Inside=/etc/digitemp/log/temperature.rrd:in:AVERAGE \
DEF:Inside2=/etc/digitemp/log/temperature.rrd:in:MAX \
DEF:Inside3=/etc/digitemp/log/temperature.rrd:in:MIN \
LINE2:Inside#3333CC:"Computer room " \
GPRINT:Inside2:MAX:"Max\: %6.2lf°C\t" \
GPRINT:Inside3:MIN:"Min\: %6.2lf°C\t" \
GPRINT:Inside:AVERAGE:"Average\: %6.2lf°C\t" \
GPRINT:Inside:LAST:"Latest\: %6.2lf°C\\c" \
LINE2:Outside#FF0000:"Outside" \
LINE1:Outside2#FF9933:"Max" \
LINE1:Outside3#008800:"Min\t" \
GPRINT:Outside2:MAX:"Max\: %6.2lf°C\t" \
GPRINT:Outside3:MIN:"Min\: %6.2lf°C\t" \
GPRINT:Outside:AVERAGE:"Average\: %6.2lf°C\t" \
GPRINT:Outside:LAST:"Latest\: %6.2lf°C\\c" \
COMMENT:""\\c \
COMMENT:"Copyright ? tero.salminen@oravaistemperature.com 2005\c"

First in order is to get the latest temperature-values from MySQL and put them into RRDTool's database. After that I'm making four files with a single value in each of them, two for outside-temp's and two for inside-temp's, both in centigrade and fahrenheit. That results in four files. After that I had to put a small waiting period in script since RRDTool's database-updating takes a little time (few seconds) before I do the graphics. After this wait-part there's "rrdtool graph" commands that makes my day- and year graphs. (I left week and month-part's out here) The important switch is "-s -1year" in the second script, it tells the executable from where to start, ie "one year back from now" therefore the minus sign. I also included the end-switch "-e -5minute" because often the last value in the database is UNKN (unknown) and sometimes I got nasty spikes to the graphs. (The solution vas to put a very small diode to sensors feet to even the spikes, now they are very rare, even for the outside sensor on a 10 meter (~30 feet) cable) One feature worth mentioning is also the "--lazy" switch, it makes rrdtool to skip remaking graphics if there's no change in them, this is mainly for the longer time-span graphs. Also notice difference in "day" and "year"-graph commands, as You can see the month has "MIN" and "MAX" for the outside measurements, there's no point to put those for day, week and month since the variation from "AVERAGE" is so small. But hey, it looks cool? ;)

And last, make a simple .sh-script to run these two "main-script's in sequence, if I'd just put these two script's in crontab they would be run at the same times, and since I'd like to have the really latest values in my webpage and graphics I made a script and crontabbed that instead. The script itself is so basic a script can be:

#!/bin/bash
#
# DigiTemp crontab script.
# Copyright 2004 by Tero Salminen <tero.salminen@oravaistemperature.com> www.temperature.tk
# All Rights Reserved

/etc/digitemp/rrdb/log_temp_to_sql.pl
/etc/digitemp/rrdb/process_data.sh

Some notes

Now it's just downhill from here, make some .htm, .php or whatever and your done. Easy, huh? (Note: Always read the manuals: RRDTooland the readme that comes with Digitemp.) Oh, one more thing. I'm definetly not a coder, so don't be too upset about my code :)

Remeber, don't shoot the penquin!

To top of page