Home Monitoring Re-Write Number Four!

Since getting the Tesla Powerwall installed, our trusted Wattson has not been able to display correct figures as it can’t tell if we are importing or exporting until the Powerwall is full.  The Wattson displays a relatively static value of +150W indicating that we’re importing, yet the data from the various other devices in the house contradicts that figure.

So it’s time to say goodbye to Wattson and hand it on to a neighbour and hope they get some use out of it.

Wattson’s demise is a great excuse to upgrade to a tablet and display a lot more information than just whether we’re importing or exporting, so I’ve gone out and bought a Samsung Galaxy Tab A from JL to replace Wattson.

In order to display more information on the tablet, I needed to re-write the home monitoring application and start graphing the data at home rather than relying on PVOutput.  PVOutput is a great website, but it’s limited to a 5 minute picture of what’s going on and I’ve run out of fields to upload data, even though I donate to get extra fields! Wattson has gotten us used to being able to see what’s going on instantly rather than waiting for a snapshot 5 minutes later.

The second re-write I did of the home monitoring application in 2015 has been running well for a few years, but despite what I wrote back then about it being maintainable, it was a pain to add in a new datasource and it was written in my least favourite framework – Mule.

Since then I’ve tried re-writing it in Node.js, but that code was less than elegant and not tested at all… It also relied on a heavy weight MySQL database which I wanted to avoid if possible. HSQLDB may be a bit basic, but it’s served me well for many years and allows me to make changes to the files in a text editor if required.

I did learn something valuable from the Node.js re-write – consolidate the five tables I had before into one large table. I’ve changed the following five tables

to a single table for ease of storing the data and to save space.

The previous database file size was 640MB (note that’s more than 200MB per year as I blogged about the database being 400MB only last year) vs. the new single table layout file size of 240MB. Every field in the database except the composite primary keys are nullable. This allows the data to be stored into the table in any order, after all I can’t guarantee which Arduino will send it’s data first.

The next step was to work out how to convert the database from the original layout to the new layout without having my pc running at 100% for over 2 hours (the first time I loaded the data from the old tables to the new table, this is exactly what happened!). The trick was to not insert based on a select union, but to use the HSQLDB merge functionality. The two hour ETL turned into a three minute ETL. This much improved ETL time allows me to take a copy of the old database (the in use one) at any time, transform it and check the new app is compatible with the schema and can write data into the new layout correctly.

As I’ve mentioned above, the new application is no longer based on Mule and instead is a Spring Boot app.   The home monitoring application receives input using Spring MVC controllers and persists the data to the database against the date and time (rounded to the minute).

At the service layer, there’s also three separate scheduled services, one for uploading PVOutput data once a minute, one for requesting the EE addons status page and scraping the data every hour and one for calling the Tesla Powerwall API every five seconds.

EE addons status page scraping I hear you say… “what’s that for?”  We no longer have fixed line internet and rely on EE 4G internet, which is great until we run out of data two days before the end of the month!  The EE addons status page displays how much data you have used, how much is remaining and how long until the next period.  Since I’ve now got the option to display a lot of different data on the tablet, it seemed sensible to display the EE data allowance too!

For anyone interested in doing something similar, here’s a class I’ve written to read the HTML and trim it to extract the right bits of information. The fields aren’t accessible as I don’t store the information – I simply pass it straight to Splunk via toString.

package uk.co.vsf.home.monitoring.service.ee;

import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.builder.ReflectionToStringBuilder;
import static org.apache.commons.lang3.StringUtils.*;

public class EeDataStatus {

	private static final String ALLOWANCE_LEFT = "allowance__left";
	private static final String ALLOWANCE_TIMESPAN = "allowance__timespan";
	private static final String BOLD_END = "</b>";
	private static final String BOLD_START = "<b>";
	private static final String SPAN_END = "</span>";
	private static final String SPAN_START = "<span>";
	private static final String DOUBLE_SPACE = "  ";

	private final String allowance;
	private final String remaining;
	private final String timeRemaining;

	public EeDataStatus(final String response) {
		String allowance = response.substring(response.indexOf(ALLOWANCE_LEFT) + ALLOWANCE_LEFT.length());
		allowance = allowance.substring(0, allowance.indexOf(SPAN_END));

		Pattern pattern = Pattern.compile("(\\d+.*\\d*GB)");
		Matcher matcher = pattern.matcher(allowance);

		matcher.find();
		this.remaining = matcher.group();
		matcher.find();
		this.allowance = matcher.group();

		String timespan = response.substring(response.indexOf(ALLOWANCE_TIMESPAN) + ALLOWANCE_TIMESPAN.length());
		timespan = timespan.substring(0, timespan.indexOf(SPAN_END));
		timespan = timespan.substring(timespan.indexOf(SPAN_START) + SPAN_START.length());
		timespan = timespan.replaceAll(BOLD_END, EMPTY).replaceAll(BOLD_START, EMPTY);
		timespan = timespan.replaceAll(CR, EMPTY);
		timespan = timespan.replaceAll(LF, EMPTY);
		timespan = timespan.replaceAll(DOUBLE_SPACE, SPACE);
		timespan = StringUtils.trim(timespan);
		this.timeRemaining = timespan;
	}

	@Override
	public String toString() {
		return new ReflectionToStringBuilder(this).toString();
	}
}

When I tried writing the home monitoring application in Node.js I gave Prometheus a go to see whether that would be a good tool for graphing at home.  It worked well when graphing small sets of data, but when I tried to graph over a years worth of data, it either errored because there was too much data coming back from the query, or took a vast amount of time to refresh the graph.  It’s possible I wasn’t using the tool correctly, but I decided it wasn’t for me in this use case because of the inability to graph large amounts of data and because it’s not as intuitive as the graphing tool I’ve chosen to go with.

So what graphing tool have I chosen?  Splunk 🙂

I chose Splunk for a number of reasons:

  1. I’ll be sending less than 500MB to Splunk a day, so it’s free 😀
  2. It’s incredibly intuitive to search through data in Splunk, so I should be able to give my dad a basic lesson and he can create graphs for himself. I had considered the ELK stack, but the searching language isn’t quite as intuitive…
  3. Splunk doesn’t care about the schema of the data you throw at it.  This makes it easy to work with as I can add/remove fields when required and not have to change a schema.

Writing the data to Splunk uses the ToStringBuilder JSON format and a Log4j socket appender.  The ToStringBuilder format is configured at bootup via the following component.

package uk.co.vsf.home.monitoring;

import org.apache.commons.lang3.builder.ToStringBuilder;
import org.apache.commons.lang3.builder.ToStringStyle;
import org.springframework.stereotype.Component;

@Component
public class ToStringBuilderStyleComponent {

	public ToStringBuilderStyleComponent() {
		ToStringBuilder.setDefaultStyle(ToStringStyle.JSON_STYLE);
	}
}

And I chose the Log4j socket appender because it doesn’t require the use of tokens to talk to Splunk.

<?xml version="1.0" encoding="UTF-8"?>
<Configuration status="warn">
    <Appenders>
        <Socket name="socket" host="SERVER NAME" port="9500">
            <PatternLayout pattern="%m%n"/>
        </Socket>
        <Console name="STDOUT" target="SYSTEM_OUT">
        </Console>
    </Appenders>
    <Loggers>
        <Logger name="uk.co.vsf.home.monitoring" level="info" additivity="false">
            <AppenderRef ref="socket" />
            <AppenderRef ref="STDOUT" />
        </Logger>

	...

</Configuration>

Bringing it all together, we’ve gone from Wattson which displayed only one figure – house load – as shown in the (albeit not great) picture below:

To this 😀

And this complicated device/application diagram

Hopefully this incarnation of the home monitoring application will last a few years, but I suspect I’ll be re-writing it all again at some point 🙂

References
Tesla Powerwall 2 API https://github.com/vloschiavo/powerwall2/
Log4j2 Socket Appender https://logging.apache.org/log4j/2.x/manual/appenders.html#SocketAppender

Chromecast Ate My Internet Data Allowance!

In January I bought a Chromecast so that friends and family could cast videos onto the main living room TV. It’s a handy little device and when idle it displays lovely background images of nature, space and various other things. I don’t know why, but I never considered where it gets those lovely background screen saver images from…

As I previously have mentioned, I have a capped Internet data allowance of 64GB per month. This month (11th March to 11th April) we ran out of data two days early for the first time this year. It’s incredibly painful having to wait a couple of days until the Internet comes back on 🙁 Yes I know – I need to get out more!

I logged into my home router to see who’d gobbled the most data this month and give them a bit of a shouting at only to discover that the top user for the month was the Chromecast, beating even me to top data user!!

Chromecast was using 700MB+ per day IDLE!

Luckily I managed to find a web page explaining how to tame the Chromecast and restrict it’s data usage by giving it access to a Google photo album with 1px images.

I found that I needed more than two of these images and that each had to be a slightly different colour or Google photos was clever enough to not upload duplicates. The backgrounds can be seen in my shared album here: https://photos.app.goo.gl/GPf1UFMRN5Rw1Xaq2. I also found that you have to make sure that when you disable the other sources of pictures that you untick all the items within the category before marking the category as “off”.

The effect of using the new 1px album images was instant and hopefully I won’t end up running out of data two days early next month 🙂

(Chromecast limited at 9pm Thursday 12th April)

Three Months in to 4G Only Internet

Last year I cancelled my ADSL connection and went 4G only on my EE 64GB contract.  It’s been an interesting three months, so I thought I’d post this info in case anyone else was considering going 4G only.

Background to why I went 4G only

In August 2017, I noticed that the ADSL connection had been causing problems with my internet access.  I was running a 4G contract with EE and ADSL with Zen load balanced through a pfsense server with secure traffic going ADSL and everything else via 4G.  Connections to websites were taking an age, or not loading at all.  I happen to have a thinkbroadband quality monitor on the line and checked it only to see that the ADSL connection had a red patch at that exact moment.

While I was looking at the quality meter, I went back through the last months’ worth of graphs and saw that on one particular day there was a large (more than 4 hours) outage of internet which explained why I’d thought the internet was slow a few weeks back too…

What ensued was a series of calls backwards and forwards to the Zen technical support as I knew that nothing on my side had changed.  The wiring in the house has previously been minimised to only the master socket with ADSL filter plate, one cordless telephone base station plugged directly into the filter plate and one ADSL modem cable.

The first call to the technical support was effectively a wait and see… a few days later the same thing happened so I called back and was asked to try a different modem – well unless you’ve got a lot of money, you don’t tend to buy two of everything, so after 30 minutes on the phone saying it wasn’t my kit, I asked for Zen to send me a modem which I’d post back… they were having none of it – apparently they don’t have spare kit to send out – not amused!

The issue dragged on and on and I managed to beg and borrow a modem off a colleague at the middle of August and that didn’t fix the issue, but by confirming the fault existed with the different modem, Zen FINALLY (It shouldn’t have taken this long) agreed reluctantly to an engineer (note reluctantly – I was not impressed at all with the Zen technical support on this case issue).

The engineer came on the 25th August, the line dropped just as he picked up the telephone, but he was unable to find any issue with internal or external wiring.

In the next month, the line would drop a number of times more, but no significant outages – I decided to wait and see, plus I wanted the ADSL connection for my holiday in late September/early October, so I could VPN back to home.

A week in to my holiday, I was a little bored surfing the internet one night and checked the thinkbroadband quality graphs again – to my horror, the ADSL connection had been off for a large proportion of my holiday so far!!






In a moment of anger, I emailed the Zen accounts department and told them I’d like to cancel.

“Since my broadband is incredibly unreliable at the moment, I want to cancel my broadband.  I will fall back to using my 4G connection permanently which is an awful lot more reliable at the moment!  I’ve had two or more months of unreliable broadband, which at over £20 per month for poor internet isn’t sustainable.”

Zen accounts sent a rely back within an hour confirming my request to cancel and on the 23rd October, my ADSL connection was stopped.

1st Month on 4G

The first month on 4G was an interesting (and expensive) learning curve.  I’d just come home from holiday and needed to re-install my laptop – hard drive failure.  This meant I downloaded far more that month than normal and we ended up using the 64GB of data on the contract 5 days before the next period!!

CRAP!

I can’t live without internet for 5 days, so I purchased a 1GB top-up for £5 and thought I’d see if that would last the 5 days.  Well it didn’t… it lasted precisely 47 minutes!! £5 had gone – poof!

I reluctantly paid for an additional 10GB at £15 and told the household to limit their internet video watching or there will be an internet blackout when the next data purchase runs out.

Having saved £20 odd pounds per month not having an ADSL connection, in the first month I’d already spent £20 for extra data on the 4G connection!

The Following two months on 4G

…Have been ok, we’ve not had any overspend on extra data (thank goodness!) but when my contact is up next month, I’ll be pushing EE hard for some extra data or two lines for the price of one.

Would I have cancelled my ADSL connection with hindsight?

Yes – Zen pissed me off big time with their technical support.

I’m pretty certain my modem was fine since the line dropped with a colleague’s modem too.  I paid them £21.40 per month for years and I expected more support for the money – heck If I knew I’d have no support, I should have gone with a cheaper provider!

It has been rough on 4G only, but now we know how quickly data can run out, we’re a little more aware of when a website is loading a video at the bottom of a page.

I would only recommend going 4G only if you have enough data on your contract – 64GB sounds like a lot, but when watching 1080p videos on YouTube or if you have Netflix, its potentially going to be tight each month.

I’ll certainly be getting another internet connection as soon as they fibre the village I live in -ConnectedCounties, you’ve promised it, so now you need to deliver on that promise!