Updated Splunk Dashboards and Powerwall 100% charged in February!

Over the last few months since I re-wrote my home monitoring application and started logging data to Splunk (in addition to the existing two locations) there’s been an update to Splunk that seemed to fix an issue I had with updating dashboard panels in near realtime. This is a huge benefit as the lack of realtime querying with the free version that I was using was one of the reasons I’d considered purchasing a Splunk Enterprise license! The update also included the dark theme which I first thought was a bit of a gimmick but has proved really useful as having a dark background for the quick glace dashboard means you can see the colours easier when reflected in the kitchen window.

So since the first draft dashboard I posted back in July, I’ve re-written the dashboards and started using a Splunk App so I can copy the source files out into bitbucket and make sure I have a copy and also logically group my dashboards away from the default search app content. My Splunk App is called Home Monitoring (as you’ll see below in the screenshots) after the server app and the logo is a small snippet from a picture of my solar array.

Below is a screenshot of the quick glance section of a dashboard titled realtime. Although it’s titled realtime (RT), the quick glance dashboard panels aren’t actually using RT searches as I found them to be less correct than using a search combined with “head 1” and limiting the search window to around 15 seconds.

On the quick glance section I have a line for the Powerwall figures, displaying current charge percentage (taking in to account the reserved 5%), load on the battery, the amount of power in kWh in the battery (an alternative way of displaying the percentage) and a rough restimate on how long until full or empty at current load. All of these figures come from the Powerwall APIs.

On the second line is the current household load, load on the grid (both from Powerwall API), import today (from the Arduino in the meter cupboard), generation from the solar array, total enery generated today by the solar array (both of the solar figures are from the inverter data) and mains voltage (from the Arduino in the meter cupboard).

The third line displays the current temperature of the hot water tank and whether it’s going up or down based on the figure before (from the Arduino in the hot water cupboard), the status of the immersion plug (from the server code which controlls the plug) and how much data we’ve used in the month on the 4G connection (from the EE status page).

And finally on the fourth line is the wind figures from the Arduino down the garden.

The colours on the page as mentioned earlier help to be able to see at a quick glance what the state of each section is – even reflected in the kitchen window when standing at the sink!

Below the quick glance section is a graph showing the Powerwall meters API data over 48 hours and charge percentage on the Z axis. The graph showing February 26 and 27th shows that we had incredibly sunny February days and didn’t use up all of the electricity stored in the Powerwalls so we hit fully charged (100%) two days in a row! This is quite a common scenario in the summer but completely unexpected in February.

Below the Powerwall meters graph is a graph displaying daily geneartion and import over the last 30 days. As can be seen in one of the graphs, Sunday 24th to Wednesday 27th February were lovely days where the solar panels achieved near perfect output for four days in a row!

The next screenshot shows the monthly generation and import graph which sits below the daily graph along with the wind speed graph over the last 48 hours. There is one further graph on the realtime dashboard page but it’s just out of shot. It displays the Hot water temperature over the last 48 hours.

All of these screenshots are from the same realtime dashboard but only the quick glance section displays on the screen at one time and you have to scroll down to see the rest of the graphs.

Tesla Powerwall and Rolec Car Charger

Last Friday we have our Tesla Powerwall installed and at the same time the electricians installed my Rolec car charger.  I was most excited about the Powerwall as I’ll hardly use the car charger and the Powerwall has an API which means I can add extra data to my home monitoring 😀

The whole process from emailing Tesla with some questions to getting the Powerwall has taken a while, mostly because we had to get some electrics fitted in the house before asking for a quote from Tesla.

The sign-up process is quick – fill in a brief form and pay a deposit by card.  The quote process after signing up is a lot more involved… You walk around your house taking lots of pictures and writing descriptions of where you’d like the Powerwall installed.  I’d prefer they sent out someone and I think it would have taken just as long for them to walk around my house assessing the various locations and Solar equipment as it took me to fill in the form – 2 and a half hours!  If you’re in the same boat, take pictures with a phone and fill the form in later on a laptop – you’ll save yourself a lot of hassle.

Once the quote form is filled in it goes off to your local installer (mine was 50 odd miles away) who takes a look and tells Tesla how much for their part installing the Powerwall will be.  I found this bit a little odd as we’d given various locations in the house where the Powerwall could go, but we weren’t told where it would go, so ended up asking for our installers details to contact him directly.

After you’re happy with the quote (cost of Powerwall + Installation) your installer then sends a request to the DNO who decides whether you can output from the Powerwall at 3.68 or 5kW.  This bit is the slowest part in the whole process and took around 8 weeks…!  Luckily our DNO was happy for us to output at 5kW from the battery (apologies if this isn’t the correct technical speak!  I’m no good with electrickery).

Mark Cawood (of Cawoods Electricians) and his team arrived around 8:30am on Friday morning.  You don’t get a sense of the size or weight of the Powerwall until you see it in person being lifted out the back of a van!

By 1:30pm the Powerwall and charging point were installed and the Powerwall just needed configuring.  I was surprised the process of configuring the Powerwall was so complicated and problematic.  It took around 30 minutes of trying various connections (wifi, ethernet and 3G) and filling the same form in 5 times to get the Powerwall correctly configured and setup.

By 2:30pm Mark and the guys had cleared up, given us a full demo of how to use the car charger, including some useful tips on how to store the charge cable, made sure we had access on our mobiles to the Tesla app and our Powerwall data and said their goodbyes.

I’d highly recommend Mark and the team if you’re looking to get a Powerwall or solar panels installed.  They were very friendly, tidied up and did a neat job of all the cable runs, answered loads(!) of questions we had, made sure we were able to see our Powerwall on the various devices we own and made sure I knew how to use the car charger.

Since installing the Powerwall we’ve used a grand total of 23 units, far less than our normal 60 odd units.  We’ve even had two complete 100% self-powered days where the solar and Powerwall have provided 100% of our power requirements for the day.

As for the car charger – I haven’t used it yet – still reads 0.26kWh consumed!  I’m trying to spend as little as possible on fuel during the spring-summer months since charging at work is free 😉

Home Monitoring Upgrade (Part 2) – HSQLDB to MySQL

As mentioned in my previous post (see http://blog.v-s-f.co.uk/2017/04/home-monitoring-upgrade/), the first task I have is to migrate from HSQLDB to MySQL.

Because the system logs data every minute while I have power in the house and I want to minimise downtime when I actually have a fully working upgrade, I’ve experimented with a copy of the live HSQL database.

Once I’d copied it from my server to my laptop I then attempted to view the data in notepad++ – yeah, not a particularly smart move! NP++ cannot handle files over about 100M. It also turns out (having more’d the .data file) that the data is not in a readable state. I had a performance issue with a select query a long time back and changed the table to cached.

So, luckily at work a colleague had introduced us all to a great database tool called SQL Workbench. It can work with most databases and unlike SQuirreL, it doesn’t crash when looking at the work DB2 database.

Using SQL Workbench, I’ve then created a script file which creates the new consolidated table and loads data from the old tables in to the new + drops the old tables. The end result is a 122,398KB HSQLDB script file which is human readable.

Next step was to get MySQL running on my server. Instead of installing it directly though, there’s a Docker image available.

My first few attempts at inserting the data from the HSQLDB file in to the MySQL database were less than impressive! One of the attempts had the server running flat out (100% cpu) for over an hour when I finally decided that it was probably not going to complete the import this year and nuked it!

So having learnt a few lessons about not using single row inserts(!), but batching them in to multi-row inserts of 100,000 and a few MySQL deafult parameter increases (although I’m not sure if these are necessary as the batch inserts seemed to make most difference), I was finally able to import the data. It still took a few minutes from running the MySQL container to it being available – but that’s significantly better than running for hours importing the data!!

Now that I have the necessary scripts and knowledge to migrate the data, the next part is re-writing the application that receives the Arduino data, uploads to PVOutput and serves the hot water display Arduino.