2009/10/11 Making your Flukso log to a database/RRD tool



Click here for a couple of pictures of the installation of a flukso device.

  • login via ethernet port on the flukso with ssh: username root and password root as well
  • edit /etc/config/firewall: set wan input to 'ACCEPT':
      config 'zone'
            option 'name' 'wan'
            option 'input' 'ACCEPT'
            option 'output' 'ACCEPT'
            option 'forward' 'REJECT'
            option 'masq' '1'
    
    this enables you to login to the flukso via the wireless interface as well
  • /etc/config/system, add a line log_ip pointing to your syslog server
    config 'system'
    	option 'timezone' 'UTC'
    	option 'hostname' 'flukso'
    	option 'device' '...'
    	option 'key' '...'
    	option 'version' '101'
    	option 'log_ip' '192.168.64.1'
    
    192.168.64.1 is the address of the system running rsyslog
  • invoke "uci commit" and reboot the flukso This will send lines like this to the syslog server:
    Oct 11 13:42:11 192.168.62.15 flukso: sites/all/modules/logger/rrdtool update sites/all/modules/logger/data/base/somevalue.rrd 1255268234:1036 1255268275:1043 1255268281:1044 1255268338:1054 1255268344:1055 1255268395:1063 1255268401:1064 125
    Oct 11 13:42:30 192.168.62.15 flukso: received message from /dev/ttyS0: ADC0 sample value: 28
    Oct 11 13:42:31 192.168.62.15 flukso: received pulse from /dev/ttyS0: somevalue:0000001086
    Oct 11 13:42:37 192.168.62.15 flukso: received message from /dev/ttyS0: ADC0 sample value: 28
    Oct 11 13:42:37 192.168.62.15 flukso: received pulse from /dev/ttyS0: somevalue:0000001087
    Oct 11 13:42:44 192.168.62.15 flukso: received message from /dev/ttyS0: ADC0 sample value: 28
    Oct 11 13:42:44 192.168.62.15 flukso: received pulse from /dev/ttyS0: somevalue:0000001088
    Oct 11 13:42:51 192.168.62.15 flukso: received message from /dev/ttyS0: ADC0 sample value: 28
    Oct 11 13:42:51 192.168.62.15 flukso: received pulse from /dev/ttyS0: somevalue:0000001089
    
    The values starting here with 1255 are timestamps (epoch values), the value behind to colon is the measured Watts. These ADC0 samples are in steps of 0.05 watt. E.g. 28 is 560 watt.
  • create a fifo to which rsyslog will write its messages
    mkdir -p /usr/local/var
    mkfifo /usr/local/var/rsyslog.all
    
  • add to rsyslog.conf:
    *.*             |/usr/local/var/rsyslog.all
    
  • enable remote UDP/TCP rsyslog messages:
    # provides UDP syslog reception
    $ModLoad imudp
    $UDPServerRun 514
    # provides TCP syslog reception
    $ModLoad imtcp
    $InputTCPServerRun 514
    
  • restart rsyslogd
    /etc/init.d/rsyslog restart
    
  • create the script /usr/local/bin/get_flukso (don't forget to run chmod +x /usr/local/bin/get_flukso)
    #! /bin/sh
    
    grep 'flukso: received message from.* ADC0 sample value:' /usr/local/var/rsyslog.all | /usr/local/bin/get_flukso.pl
    

Log to a MySQL database

  • create a database/table in mysql:
    CREATE DATABASE flukso;
    USE flukso;
    CREATE TABLE flukso (ts DATETIME NOT NULL, value INT NOT NULL, PRIMARY KEY(ts));
    GRANT INSERT,SELECT ON flukso.* TO flukso@localhost IDENTIFIED BY 'flukso';
    FLUSH PRIVILEGES;
    
    I'm assuming here that the flukso-script runs on the same pc as the database
  • create the script /usr/local/bin/get_flukso.pl (don't forget to run chmod +x /usr/local/bin/get_flukso.pl)
    #! /usr/bin/perl -w
    
    use Time::ParseDate;
    use DBI;
    
    $db_type = 'mysql';
    $db_host = 'localhost';
    $db_user = 'flukso';
    $db_pass = 'flukso';
    $db_db   = 'flukso';
    $db_table= 'flukso';
    
    $query = "INSERT INTO $db_table(ts, value) VALUES(FROM_UNIXTIME(?), ?)";
    $dbh = DBI->connect("dbi:$db_type:database=$db_db:host=$db_host", $db_user, $db_pass, { RaiseError => 0, AutoCommit => 1 }) or die 'cannot connect to server' . $DBI::errstr;
    $sth = $dbh -> prepare($query);
    
    while(<>)
    {
    
    	/^([^ ]+ +[0-9]+ +[^ ]+).* ([0-9]+)$/;
    	$ts = parsedate($1);
    	$value = $2;
    
    	$sth -> execute($ts, $value);
    }
    
  • run:
    /usr/local/bin/get_flukso &
    
    you could add it to /etc/rc.local (Debian systems) BEFORE the line with 'exit 0' (which is on the end of that file)
  • Please note that the timestamp is in UTC and you need to multiply the value with 19,84 first to get something usable. E.g.:
    mysql> select ts, avg(value / 0.05) from flukso group by year(ts), month(ts), day(ts), hour(ts);
    +---------------------+-------------------+
    | ts                  | avg(value / 0.05) |
    +---------------------+-------------------+
    | 2009-10-11 14:35:25 |      703.95683453 |
    | 2009-10-11 15:00:04 |     1727.78947368 |
    +---------------------+-------------------+
    2 rows in set (0.00 sec)
    

To a RRD(-tool) graph

  • First create a RRD datafile:
    mkdir /var/flukso
    rrdtool create /var/flukso/flukso_data.rrd DS:watt:GAUGE:60:U:U \
            RRA:AVERAGE:0.5:1:3200 \
            RRA:AVERAGE:0.5:6:3200 \
            RRA:AVERAGE:0.5:36:3200 \
            RRA:AVERAGE:0.5:144:3200 \
            RRA:AVERAGE:0.5:1008:3200 \
            RRA:AVERAGE:0.5:4320:3200 \
            RRA:AVERAGE:0.5:52560:3200 \
            RRA:AVERAGE:0.5:525600:3200 \
            RRA:MIN:0.5:1:3200 \
            RRA:MIN:0.5:6:3200 \
            RRA:MIN:0.5:36:3200 \
            RRA:MIN:0.5:144:3200 \
            RRA:MIN:0.5:1008:3200 \
            RRA:MIN:0.5:4320:3200 \
            RRA:MIN:0.5:52560:3200 \
            RRA:MIN:0.5:525600:3200 \
            RRA:MAX:0.5:1:3200 \
            RRA:MAX:0.5:6:3200 \
            RRA:MAX:0.5:36:3200 \
            RRA:MAX:0.5:144:3200 \
            RRA:MAX:0.5:1008:3200 \
            RRA:MAX:0.5:4320:3200 \
            RRA:MAX:0.5:52560:3200 \
            RRA:MAX:0.5:525600:3200
    
  • Create the script /usr/local/bin/get_flukso.pl (don't forget to run chmod +x /usr/local/bin/get_flukso.pl)
    #! /usr/bin/perl -w
    
    use Time::ParseDate;
    
    while(<>)
    {
    
            /^([^ ]+ [0-9]+ [^ ]+).* ([0-9]+)$/;
            $ts = parsedate($1);
            $value = $2;
    
            $cvalue = $value * 19.84;
            system("/usr/bin/rrdtool update /var/flukso/flukso_data.rrd N:$cvalue");
    }
    
  • Create the script /usr/local/bin/draw_flukso:
    #! /bin/sh
    
    /usr/bin/rrdtool graph /var/www/flukso-day.png -a PNG --width 480 --start -1d \
            DEF:watt=/var/flukso/flukso_data.rrd:watt:AVERAGE \
            DEF:wattmin=/var/flukso/flukso_data.rrd:watt:MIN \
            DEF:wattmax=/var/flukso/flukso_data.rrd:watt:MAX \
            LINE1:watt#FF0000:"watt" \
            LINE1:wattmin#00FF00:"watt min" \
            LINE1:wattmax#0000FF:"watt max" \
            'GPRINT:watt:LAST:last\: %lf' \
            'GPRINT:watt:MIN:min\: %lf' \
            'GPRINT:watt:MAX:max\: %lf' \
            > /dev/null
    
    This script will generate an image in /var/www which normally is the root of your webserver.
  • Add /usr/local/bin/draw_flukso to cron:
    0,30 * * * *	/usr/local/bin/draw_flukso
    
  • run:
    /usr/local/bin/get_flukso &
    
    you could add it to /etc/rc.local (Debian systems) BEFORE the line with 'exit 0' (which is on the end of that file)







contact form Winnen in de Staatsloterij! disclaimer