CurrentCost Update

For a while now, I’ve been using a Current Cost CC128 to monitor my electricity usage.

I hacked together a munin plugin to monitor both the electricty usage, and as the unit is in my lounge and coughs up temperature, it also provides the temperature reading.

For a number of reasons, the munin plugin doesn’t read the device directly. Only one device can open the serial port at a time, and there were a couple of things I wanted to do with the data, so didn’t want them fighting. I had a small piece of code grab the XML once a minute and dump it in a file instead.

Talking to a friend recently, they quite rightly pointed out (reminding me) that this method takes an instantaneous value for both the temperature and the electricity usage.

Whilst not a problem for the former as it won’t vary greatly within the 5 minute polling periods, the electricity usage clearly will. Think about boiling a kettle; if you were (un)? lucky enough to time the kettle just right, you could be between polls and not record the increased usage at all.

So, I’ve modified the way in which I record the data and write the files for the various things including munin to use.

As you’ll probably be aware, the CC128 outputs it’s data once every 6 seconds, so there’s clearly more data to make use of than just the single reading every 5 minutes.

I have a small daemon written in perl that listens on the serial port and every time it gets a full string of XML, it records the data to a database. Rather than re-write the munin plugin at the moment, along with anything else that uses the data file, I have a small bit of perl that runs from cron each minute that populates the expected XML into the file using averages of the values collected in the preceding 5 minute period. You could, of course, make this 95th percentile or whatever tickles your fancy.

So, the SQL table looks like this:

mysql> describe data;
+------------+------------------+------+-----+-------------------+-----------------------------+
| Field      | Type             | Null | Key | Default           | Extra                       |
+------------+------------------+------+-----+-------------------+-----------------------------+
| id         | int(10) unsigned | NO   | PRI | NULL              | auto_increment              |
| dsb        | int(10) unsigned | YES  |     | NULL              |                             |
| time       | timestamp        | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| tmpr       | decimal(6,4)     | YES  |     | NULL              |                             |
| sensor     | int(10) unsigned | YES  |     | NULL              |                             |
| unit_id    | int(10) unsigned | YES  |     | NULL              |                             |
| type       | int(10) unsigned | YES  |     | NULL              |                             |
| channel    | int(10) unsigned | YES  |     | NULL              |                             |
| data_units | varchar(128)     | YES  |     | NULL              |                             |
| data_value | int(10) unsigned | YES  |     | NULL              |                             |
+------------+------------------+------+-----+-------------------+-----------------------------+
create or replace view averages as
select sensor,dsb,unit_id,type,channel,data_units,avg(tmpr) as tmpr,avg(data_value) as value,now() as now,
from_unixtime(300 * floor(unix_timestamp()/300)-300) as start,from_unixtime(300 * floor(unix_timestamp()/300)) as end 
from data 
where time between from_unixtime(300 * floor(unix_timestamp()/300)-300) and from_unixtime(300 * floor(unix_timestamp()/300)) 
group by unit_id,sensor,channel;

I should credit http://larig.wordpress.com/2012/02/08/time-rounded-to-five-minutes-in-mysql/ for the sql to round time to the current 5 minute window.

The bit that writes the XML file looks like this:

#!/usr/bin/perl

use strict;

use DBI;

my $dsn = "DBI:mysql:database=<removed>;host=<removed>;port=3306";
my $dbh = DBI->connect($dsn, '<removed>', '<removed>');

my $sql = 'select * from averages';

my $sth = $dbh->prepare($sql) || die "cannot prepare sql\n$sql\n$DBI::errstr\n";

$sth->execute() || die "cannot execute sql\n$sql\n$DBI::errstr\n";

if($sth->rows > 0) {
	my $r;
	print "<msg>\n";
	while(my $row = $sth->fetchrow_hashref) {
		print "  <ch$row->{channel}>\n";
		print "    <$row->{data_units}>$row->{value}</$row->{data_units}>\n";
		print "  </ch$row->{channel}>\n";
		$r = $row;
	}
	print "  <sensor>$r->{sensor}</sensor>\n";
	print "  <id>$r->{unit_id}</id>\n";
	print "  <dsb>$r->{dsb}</dsb>\n";
	print "  <tmpr>$r->{tmpr}</tmpr>\n";
	print "  <type>$r->{type}</type>\n";
	print "  <time>$r->{now}</time>\n";
	print "</msg>\n";
} else {
	die "no rows returned!\n";
}

$sth->finish;
$dbh->disconnect;

…and lastly, the bit that grabs the stuff and chucks it into the database looks like this (and also writes a backward compatible version of the XML file with the instantaneous values…:

#!/usr/bin/perl

$|++;

use strict;

use File::Copy qw/move/;
use Device::SerialPort qw/:PARAM :STAT 0.07/;
use XML::Simple;
use DBI;
use Data::Dumper;

my $debug = $ENV{DEBUG} || 0;

use Proc::PID::File;
if(Proc::PID::File->running()) {
	print "Already running\n" if $debug;
	exit;
}

my $port = $ENV{port} || '/dev/ttyUSB0';
my $baud = $ENV{baud} || 57600;

my $debug = $ENV{DEBUG} || 0;

my $cc = Device::SerialPort->new($port) || die "unable to open port: $!\n";
$cc->baudrate($baud);
$cc->read_char_time(0);
$cc->read_const_time(1000);
$cc->write_settings;

my $dsn = "DBI:mysql:database=<removed>;host=<removed>;port=3306";
my $dbh = DBI->connect($dsn, '<removed>', '<removed>');
my $sql = 'insert into data (dsb,tmpr,sensor,unit_id,type,channel,data_units,data_value) values (?,?,?,?,?,?,?,?)';
my $sth = $dbh->prepare($sql) || die "cannot prepare sql\n$sql\n$DBI::errstr\n";

while(1) {
	my $timeout = 10;
	my $chars = 0;
	my $buffer;
	LOOP: while($timeout > 0) {
		my($count, $saw) = $cc->read(255);
		if($count > 0) {
			$chars += $count;
			$buffer .= $saw;
			chomp $buffer;
			print "added\n$saw\nnow have\n$buffer\n" if $debug;
			if($buffer =~ m|<msg>.*?</msg>|) {
				print "Buffer contains XML\n\n" if $debug;
				do_xml($buffer);
				last LOOP;
			}
			elsif($buffer =~ m|</msg>|) {
				print "Caught the end of the XML\n" if $debug;
				undef $buffer;
			}
		} else {
			print "." if $debug;
			$timeout--;
		}
	
		print "Waited $timeout and never saw what I was looking for\n" if $timeout == 0 && $debug;
	
	}
}

sub do_xml {
	my $xmls = shift;
	if($xmls =~ "<hist>.*?</hist>") {
		return;
	} else {
		$XML::Simple::PREFERRED_PARSER = 'XML::Parser';
		my $xml;
		eval { $xml = XMLin($xmls); };
		return undef if $@;
		print Dumper($xml) if $debug;
		for(keys %$xml) {
			if(m/^ch(\d+)$/) {
				my $channel = $1;
				my $name = 'ch'.$channel;
				for(keys %{$xml->{$name}}) {
					my $unit = $_;
					my $value = $xml->{$name}->{$unit};
					printf("dsb[%s] tmpr[%s] sensor[%s] id[%s] type[%s] ch[%s] units[%s] val[%s]\n",$xml->{dsb},$xml->{tmpr},$xml->{sensor},$xml->{id},$xml->{type},$channel,$unit,$value) if $debug;
					$sth->execute($xml->{dsb},$xml->{tmpr},$xml->{sensor},$xml->{id},$xml->{type},$channel,$unit,$value) || die "cannot execute sql\n$sql\n$DBI::errstr\n";
				}
			}
		}
		open(XML,'>/tmp/currentcost.snapshot.xml.tmp') || die "cannot open file: $!\n";
		print XML $xmls || die "cannot write to file: $!\n";
		close XML || die "cannot close file: $!\n";
		move('/tmp/currentcost.snapshot.xml.tmp', '/tmp/currentcost.snapshot.xml') || die "unable to move file: $!\n";
	}
}

I restart that from cron periodically, incase it dies. If it’s already running, it simply exits silently. It’s a quick hacky way to make it restart in the event something horrible happens. I did at least put the XML parse in an eval in case it barfs 🙂

As usual, this blog post is as much for my personal notes of how I did it than anything else. Some of the code if awful, but it’s not life or death and so I’ve often been lazy with it. There’s no doubt this could be done prettier and less hacky if I had more time 🙂

Cost Effective Temperature Monitoring

I’d been thinking about some temperature monitoring at home for a while, particularly in the nursery, so I can see if it’s getting too hot or cold in there while I’m asleep.

When my Raspberry Pi arrived, I decided to put it to work.

temp-day

After quite a bit of googling & reading, I found that the one wire file system stuff was easy to install, and had been done by others (See references below).

Whilst it’s possible to go and buy the individual components, and I’m quite capable with a soldering iron, I have to admit to taking the lazy route and purchased some bits from Sheepwalk Electronics

I wanted to monitor temperature in the garage, outside, the loft, the front bedroom, and the small bedroom on the back of the house (currently, the nursery).

I also monitor temperature in the lounge, but that comes from the Current Cost unit that I also monitor electricity usage with.

So, I’d need a host adaptor, and a sensor for each of the places to be monitored.

The Sheepwalk sensor modules are made up, or kit form, and include RJ45 sockets to ease putting together a network with standard cat5 cable. The sensors can either be parasitic and pull power from the bus, or can have power supplied. I’ve set mine up as parasitic, and it’s working OK so far.

One wire is best suited to a linear network of devices strung together, but seems quite robust, and is working in a star arrangement.

I purchased Sheepwalk’s SWE2+SWE0 pack which comes with an SWE2 (basically, a sensor plus 6 RJ45 sockets) and four SWE0 (a basic sensor on the end of 2m of cat5). I also purchased a USB host adaptor, and the RJ11 to RJ45 cable, although this would be simple to make up. Lastly, I picked up some RJ45 couplers, as they’re a good price and I’d be sure to need some!

I installed Raspbian (the official/recommended linux distro for the Raspberry Pi) on the SD card and booted the Raspberry. I gave it a static IP on my network so I could probe it from the Munin instance later on without fear of the IP changing.

After inserting the USB adaptor, I installed OWFS

kdyson@rpi1 ~ $ apt-get install owfs

There’s no init script for owfs itself, only for the supporting services, but Neil Baldwin’s page that I’d been reading had one, which saved me the hassle of writing one.

With that started, I plugged in the RJ11 to RJ45 lead, and plugged the SWE2 into the end. A fresh ls -al /mnt/1wire/ showed the new sensor, and

kdyson@rpi1 ~ $ cat /mnt/1wire/28.873AC4030000/temperature
31.5625
kdyson@rpi1 ~ $

I tested the rest of the sensors, before hooking them up where I wanted them. I was a little concerned that the cable length might be a problem to the furthest sensor – the sensor in the loft is about 25m of cable from the SWE2, and is strung off the SWE1 that provides the “Front Bedroom” sensor.

Lastly, I hacked up a munin plugin. All I needed was the munin-node package (apt-get install munin-node) as my remote munin instance would be collecting the data and producing the graphs (it’s already doing things like the already mentioned temperature in the lounge, electricity usage, and more mundane things like the SNR & sync rate of my ADSL line from my router).

So, in summary:

  • Raspberry Pi + SD Card + Raspbian + OWFS + Munin-Node
    • USB Host Adaptor + RJ11 to RJ45 Lead
      • SWE2 (Garage)
        • SWE0 (Garage Outside)
        • SWE1 (Front Bedroom)
          • SWE0 (Loft)
        • SWE0 (Small Bedroom)

Code Snippets

First up is the munin plugin. I took a chunk of inspiration from http://err.no/personal/blog/2010/Nov/02 but the perl OWNet module was playing up for me, and given it was 10pm, I took the lazy route and hacked it about to just look around the file system. You can tell how lazy I was, because you can see I used glob() instead of opendir() etc. I’m not proud of it, but it’s working.

You’d need to drop this either directly in /etc/munin/plugins or someplace else and symlink it to there. I did the latter.

#!/usr/bin/perl

#%# family=auto
#%# capabilities=autoconf

use strict;
use warnings;
use utf8;

my $debug = 0;

chdir "/mnt/1wire/bus.0";

my @buses = grep { /bus./ } glob("*");

print "got buses ".join(", ", @buses)."\n" if $debug;

my %sensors;
my %sensor_names;
for my $bus (@buses) {
  for my $sensor (glob($bus."/*")) {
    print "got sensor $sensor\n" if $debug;
    my $p = $sensor;
    $sensor =~ s|^bus.\d+/||;
    my $sensor_name = $sensor;
    $sensor_name =~ s|\.||;
    next if $sensor =~ /(interface|alarm|simultaneous)/;
    next unless $sensor =~ /^28\./;
    $sensors{$sensor} = $p;
    $sensor_names{$sensor} = $sensor_name;
  }
}

my %labels;
if(open(A, '/etc/owfs-aliases')) {
  while(<a>) {
    chomp;
    print "got label $_\n" if $debug;
    my($s,$l) = m/^([\da-zA-Z\.]+)\s*=\s*(.+)$/;
    $labels{$s} = $l;
  }
  close A;
}

use Data::Dumper;
print Dumper(\%sensors)."\n" if $debug;
print Dumper(\%labels)."\n" if $debug;

if (defined $ARGV[0]) {
  if ($ARGV[0] eq 'autoconf') {
    if (-d "/mnt/1wire/bus.0") {
      print "yes\n";
      exit 0;
    }
    print "no\n";
    exit 1;
  } elsif ($ARGV[0] eq 'config') {
    print "graph_title Temperature\n";
    print "graph_args --base 1000\n";
    print "graph_vlabel Temp in °C\n";
    print "graph_category sensors\n";
    print "graph_info This graph shows the temperature in degrees Celsius of the sensors on the network.\n";
    print "$sensor_names{$_}.label $labels{$_}\n" foreach (keys %sensors);
    exit 0;
  }
}

for my $sensor (keys %sensors) {
  print "sensor is $sensor\n" if $debug;
  if ($0 =~ /ow_([\w\.]+)/) {
    print "1 is $1\n" if $debug;
    next unless ($1 eq $sensor || $1 eq $labels{$sensor});
  }
  my $file = "/mnt/1wire/bus.0/".$sensors{$sensor}."/temperature";
  my $temp = `cat $file`;
  chomp $temp;
  printf "%s.value %.4f\n", $sensor_names{$sensor}, $temp;
}

exit 0;

Next up, the aliases file /etc/owfs-aliases – again, it was late, owfs aliases didn’t appear to be working quite how I was expecting, and so I hacked the plugin to use this file:

28.5086C4030000 = Front_Bedroom
28.7D61C4030000 = Garage
28.684EC4030000 = Garage_Outside
28.873AC4030000 = Loft
28.C786C4030000 = Small_Bedroom

References

http://err.no/personal/blog/2010/Nov/02
http://neilbaldwin.net/blog/weather/raspberry-pi-data-logger/

IPv6 Privacy Extensions

Introduction

If you’ve switched on IPv6, whether via a tunnel, or natively, your machine is likely to have stepped out from behind NAT and now has a globally routable address.

Whilst NAT is not security, and inbound connections to your machine are likely behind a firewall, that doesn’t change one thing: in many cases your IPv6 address is made up automatically by auto-configuration.

It’s made up of two parts, the last 64 bits are put together partly from the MAC address of your network interface, and the rest comes from the network prefix.

This makes your machine globally identifiable, and therefore, trackable by third parties, such as web sites.

To this end, RFC3041, superseded by RFC 4941 defines privacy extensions.

When enabled, your machine still has the auto-configuration address, but now also has a randomised additional address that changes periodically and is used for outbound connections.

On many linux distributions this is disabled by default. Windows XP is the same. Windows Vista enables it by default, and I believe newer versions of Windows also enable it by default.

I don’t have access to any Windows Server installations with IPv6, so I’m unsure if the server editions do this too.

I mention servers, as you probably don’t want this on a server. Imagine, for example, a mail server making an outbound connection from a random and short lived IP address. It’s unlikely to have a valid PTR, for example, and many, not all I grant you, but many MTAs will not like that.

Enabling it on Linux

Enabling it on linux distributions is quite straight forward:

as root:

echo 2 > /proc/sys/net/ipv6/conf/all/use_tempaddr

You can automate this at boot in the normal way; edit /etc/sysctl.conf and add the line:
net.ipv6.conf.all.use_tempaddr=2
You can swap all for a specific IPv6 enabled interface, such as eth0 if you require.

Enabling it on OS X

OS X has it disabled by default. I believe you can add to, or create /etc/sysctl.conf with the following:

net.ipv6.conf.all.use_tempaddr=1

Don’t quote me on that last one; I’ve not tested it!

[edit 21/Mar/2017]: MacOS Sierra seems to have it enabled by default…