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);

		this.remaining = matcher.group();
		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;

	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;

public class ToStringBuilderStyleComponent {

	public ToStringBuilderStyleComponent() {

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">
        <Socket name="socket" host="SERVER NAME" port="9500">
            <PatternLayout pattern="%m%n"/>
        <Console name="STDOUT" target="SYSTEM_OUT">
        <Logger name="uk.co.vsf.home.monitoring" level="info" additivity="false">
            <AppenderRef ref="socket" />
            <AppenderRef ref="STDOUT" />



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 🙂

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

Elasticsearch, Logstash and Kibana (E.L.K.) on Docker – Part 1 Logstash

When I set up my new server back in May, I decided to try out Elasticsearch, Logstash and Kibana (E.L.K.) on it against the Aggregator (my PVOutput aggregating and uploading application) logs. It took me most of a day to get it installed, but because I followed a guide somewhere on the internet I can’t remember how it was all configured..!

I was searching Docker Hub the other day and thought… “I wonder if they have Docker images for E.L.K.?”

Luckily for me, they have taken the time to create Docker instances and it’s a good excuse to uninstall E.L.K. on my server and re-do it using Docker images!

I started off by trying out the basic Logstash example given on Docker Hub which worked fine and the decided to try to get the image to receive my log files via the Log4j SocketAppender.  No matter how I tried to get the existing Aggregator application to send the logs (SocketAppender or even via Docker volume), I could not get it to work…

So back to the drawing board!  Time to get a simple Java application up and running to try things out with… and that’s how the Spring Boot Web Example was born.

I started out with a basic Logstash config which had previously worked, using the log4j input, but found out later that Log4j and Log4j2 have incompatibilities and an addon would be needed if that input is used.

logstash.conf (1st attempt)

input {
  log4j {
    port => 9999
filter {

output {
  stdout {}

But I don’t want to install another plugin… so I tried out various other “methods” (a.k.a. trial and error…)  and eventually found that if you use the input type tcp, you can send data to it using the Log4j2 SocketAppender, providing the Layout isn’t SerializedLayout.

logstash.conf (2nd attempt with tcp input and json codec)

input {
  tcp {
    port => 9999
    codec => json

filter {

output {
  stdout {}

The above logstash.conf was combined with the SocketAppender and JSONLayout combo in the log4j2.xml config file

<Socket name="socket" host="pompeii" port="13456" reconnectionDelayMillis="5000">
    <JSONLayout complete="true" compact="false" eventEol="true" />

But I still couldn’t get it to produce the results I was after until it dawned on me that perhaps I should change the problem around… If I just throw standard log strings at it, maybe I can break them up or format them into something that’s easier for Logstash to consume!

So I then ended up with the tcp input and line codec and decided that if I send key value paired log messages at logstash, I could use the kv filter. I’ve now ended up with…

input {
  tcp {
    port => 9999
    codec => line

filter {
  kv {
    source => "message"
    recursive => "true"

output {
  stdout {codec => rubydebug}

And changed the log4j2.xml config to use a pattern layout that works better with the kv filter

log4j2.xml (the full file can be found in the spring-boot-example in the log4j2.xml.tcp file)

<?xml version="1.0" encoding="UTF-8"?>
<Configuration status="warn" strict="true" monitorInterval="30">
        <Property name="defaultpattern">logdate=(%d{ISO8601}) thread=(%thread)) level=(%level) loggerclass=(%logger{36}) message=(%msg)%n</Property>
    <Filter type="ThresholdFilter" level="trace"/>
            <Socket name="socket" host="pompeii" port="13456" reconnectionDelayMillis="5000">
                <Layout type="PatternLayout" pattern="${defaultpattern}" />
            <Logger name="uk.co.vsf" level="info" additivity="false">
                <AppenderRef ref="STDOUT"/>
                <AppenderRef ref="File"/>
                <AppenderRef ref="socket"/>

The above Log4j2 config file specifies a patter that the key value (kv) filter will read easily, realising where each value ends because the values are wrapped with brackets. The logstash config file also specifies the out codec rubydebug as I found out (the hard way) that having debug on gives you an awful log of help when trying out config changes!

Putting it all together and running Logstash in Docker is probably the easiest part! To run logstash I have a run script which has the following command

docker run -p 13456:9999 -it --rm -v "$PWD":/config-dir logstash -f /config-dir/logstash.conf

It’s exposing the Logstash host container port 9999 as host port 13456 and loads in the custom logstash.conf file.

At present Logstash doesn’t send the received messages anywhere, but it will log the input to the console. So here’s an example of calling the get users by id service.


And the stdout from Logstash.


That’s it for now, but (hopefully) in the next part, I plan to hook Logstash up to Elasticsearch in docker!

Mini Spring Boot Web Application

After a couple of years of reading about Spring Boot and never having actually tried it out, I thought now was the time to build a little example to find out how easy it is and also because I need a simple app for my E.L.K. project (more on that later!).

There’s a very good intro to get people off the ground from Spring on their website https://spring.io/guides/gs/spring-boot/ which I read through and then tweaked.

The source code for the rest of this blog article can be found here https://github.com/vls29/spring-boot-example.

For the web application I’ve used Maven, but instead of following the guide exactly as the guide extended the spring boot starter parent pom, I decided to find out what I might need to get the project to run by using only the poms/jars that I thought would be essential.

As such the pom doesn’t have a parent and references only the relevant spring boot related poms and jars:

  • spring-boot-starter-actuator to bring in the “…production grade services…” and see what they do!
  • spring-boot-starter-web to bring in all the Spring MVC and embedded tomcat goodies
  • and spring-boot-starter-log4j2 to add in logging which I’m going to need for the E.L.K. project

There’s also two plugins, the maven compiler plugin to make sure it compiles at Java version 8 and the spring-boot-maven-plugin as the guide mentioned that it bundles the jar with the included maven dependencies (note if you’re not extending the spring boot super pom, you’ll need an execution goal for the spring-boot-maven-plugin otherwise nothing will happen).

The Application and HelloWorldController are based around the example from the getting started guide, but the UserController is one I’ve added.  It has a noddy example service which receives a path variable long representing the id of the user to retrieve (although in the example it doesn’t matter what value!).  It constructs a static user and returns the user object to the consumer.  Spring MVC then uses Jackson to marshal the object to JSON.

The final file is then a Run Config for Eclipse which executes the “spring-boot:run” goal and starts up the app from Eclipse.

The result is a very simple service that can retrieve our fake user as shown in the image below.


I was pleasently surprised how quick it was to create a service and all the documentation available to set it up.

That’s it for now, but there will be more on E.L.K. soon!