using Programming;

A Blog about some of the intrinsics related to programming and how one can get the best out of various languages.

Getting libvirt networking in place without too much pain

Automating libvirt networking with minimal pain

A few weeks ago I started a project to experiment with kvm / libvirt / qemu virtualization on CentOS 8.2. Getting qemu and libvirt all setup was relatively easy, and went pretty painlessly. After that, setting up network bridges went relatively well also.

However, when it got to networking, things got a little less pleasant.

Non-bridged Networking

First and foremost, the project I'm working on treats the hypervisor as the firewall. It does minimal routing to ensure that the VM's are adequately separated from the main network. Documentation on this was limited, to say the least. I won't go into the setup details, but you basically create virtual interface bridges via the virsh net-define from an XML file.

Next, the hard part: creating firewall rules to allow certain services on VM's to be accessed.

This proved a much more challenging task than I thought. All the information out there made this extremely daunting and non-trivial.

As it turns out, the recommended solutions are comparatively over-engineered to what I'm going to propose.

Setting up the qemu hook

First and foremost: when libvirt starts, stops, and migrates a VM it calls the qemu hook which is, by default, in /etc/libvirt/hooks. There are two parameters for this hook we're going to care about: ${1} which is the "domain" or "virtual machine", and ${2} which is the "action" being performed.

For our situation, we care about three actions: start, stopped, and reconnect. For reconnect, we're simply going to stop then start.

VM / Networking Structure

My setup puts all my VM info in /home/kvm, there are three folders there:

  • /home/kvm/images: all my ISO's;
  • /home/kvm/disks: all of the disk images for my VM's;
  • /home/kvm/networks: all of the scripts for the VM's of which we should do network operations;

I chose this structure because it's easy to remember.

Now the biggest problem with libvirt networking is that it resets firewall rules in iptables regularly. Any time the daemon restarts, if I recall correctly. As a result, if we create iptables rules there is no guarantee they will remain after any sort of state-change.

The libvirt hook

So, my solution was to put a network script for each VM in /home/kvm/networks, named after the VM, that allows me to add/remove the appropriate iptables rules when the VM starts and stops.

To do so, I created a very simple qemu hook:

#!/bin/bash

DOMAIN=${1}
ACTION=${2}
BASE_PATH="/home/kvm/networks/$DOMAIN"

if test -f $BASE_PATH; then
    if [ $ACTION = "stopped" ] || [ $ACTION = "reconnect" ]; then
        $BASE_PATH stopped
    fi
    if [ $ACTION = "start" ] || [ $ACTION = "reconnect" ]; then
        $BASE_PATH start
    fi
fi

This hook simply calls /home/kvm/networks/VM_NAME with either start or stopped, depending on what is happening.

Next, we'll look at a sample VM I have a script for.

Important: after creating this hook, restart libvirtd via service libvirtd restart. Otherwise, libvirt will not pick up on your new script.

The VM Script

The script for each VM is extremely simple, I define the public / external IP, the private / internal IP, and the ports I want to forward, in the format protocol:port, with commas between each combination:

#!/bin/bash
PUBLIC_IP=192.168.0.2
PRIVATE_IP=10.1.1.2
PORTS=tcp:80,tcp:443
/home/kvm/networks/modify_network $PUBLIC_IP $PRIVATE_IP $PORTS ${1}

The PUBLIC_IP is an external IP, this IP should be assigned to a physical interface on the server.

We then call another script, /home/kvm/networks/modify_network, which has the root of the iptables rules. We also pass ${1} which is either start or stopped (because that's how qemu calls it).

The hard part: the iptables rules

Ok, so maybe this isn't that hard. Again, we have a relatively simple script:

#!/bin/bash
PUBLIC_IP=${1}
PRIVATE_IP=${2}
readarray -d , -t PORTS <<< "${3}"

if [ ${4} = "start" ]; then
    /sbin/iptables -I FORWARD -m state -d $PRIVATE_IP/32 --state NEW,RELATED,ESTABLISHED -j ACCEPT
    for (( n=0; n < ${#PORTS[*]}; n++ )); do
        readarray -d : -t PORT_PARTS <<< "${PORTS[n]//[$'\t\r\n']}"
        /sbin/iptables -t nat -I PREROUTING -p ${PORT_PARTS[0]} --dport ${PORT_PARTS[1]} -d $PUBLIC_IP -j DNAT --to $PRIVATE_IP
    done
fi

if [ ${4} = "stopped" ]; then
    /sbin/iptables -D FORWARD -m state -d $PRIVATE_IP/32 --state NEW,RELATED,ESTABLISHED -j ACCEPT
    for (( n=0; n < ${#PORTS[*]}; n++ )); do
        readarray -d : -t PORT_PARTS <<< "${PORTS[n]//[$'\t\r\n']}"
        /sbin/iptables -t nat -D PREROUTING -p ${PORT_PARTS[0]} --dport ${PORT_PARTS[1]} -d $PUBLIC_IP -j DNAT --to $PRIVATE_IP
    done
fi

This one is, perhaps, the most complex so far, but only because I wanted comma-specified ports.

The readarray -d , -t PORTS <<< "${3}" section reads the third parameter, then splits it on commas into an array called PORTS.

Then, if the fourth parameter is start, we do an /sbin/iptables -I FORWARD insert with the private IP. This is actually extremely important, as libvirt by default only allows for RELATED,ESTABLISHED connections by default, and we need to allow NEW connections as well. I spent probably three or four days fighting this rule specifically.

Next, we use the for to loop through all the items in the PORTS array. (The ${#PORTS[*]} counts the number of elements in the array.) The readarray -d : -t PORT_PARTS <<< "${PORTS[n]//[$'\t\r\n']}" line does two things: the part of the line after the carets (<<<) removes whitespace, then the readarray section again splits the string, this time on colons.

We then do an /sbin/iptables -t nat -I that opens the protocol and port to the public and private IP combination we had.

Lastly, our stopped block does the opposite: it removes each rule we previously added to ensure that no errant ports are left open if the VM is killed.

Verify with iptables

Lastly, it's helpful to verify the iptables -L result:

Chain FORWARD (policy ACCEPT)
target     prot opt source               destination
ACCEPT     all  --  anywhere             10.1.1.2             state NEW,RELATED,ESTABLISHED
ACCEPT     all  --  anywhere             10.1.1.0/24          ctstate RELATED,ESTABLISHED
ACCEPT     all  --  10.1.1.0/24          anywhere
ACCEPT     all  --  anywhere             anywhere
REJECT     all  --  anywhere             anywhere             reject-with icmp-port-unreachable
REJECT     all  --  anywhere             anywhere             reject-with icmp-port-unreachable

I left the default rules in place, but you'll notice that the second rule, which allows connections from anywhere to 10.1.1.0/24, only allows RELATED,ESTABLISHED, which means that if you are opening an internal service users won't be able to connect unless we add our NEW,RELATED,ESTABLISHED rule, as there's no previous connection to associate yet.

We'll also verify the iptables -t nat -L result:

Chain PREROUTING (policy ACCEPT)
target     prot opt source               destination
DNAT       tcp  --  anywhere             192.168.0.2          tcp dpt:https to:10.1.1.2
DNAT       tcp  --  anywhere             192.168.0.2          tcp dpt:http to:10.1.1.2

These both show that our rules were created.

The End

This all is basically the result of my two+ weeks of researching this issue. During my research I came across dozens of articles that had much more complex solutions, but in the end my requirements (NAT'ing specific ports to specific VM's) actually simplified my solution.

Building a Rashberry Pi Graphing Machine

Building the software for a Rasberry Pi Weather Instrument System

Per the usual I recently tweeted about a thing, which was a Raspberry Pi Weather Measurement System, in particular I tweeted a picture of the graphs (and asked an expert in statistics / data-science for an assist on how to best model the data), and said expert actually responded with some helpful information.

Of course, shortly afterward someone asked me how to create said thing, so that's what this blog-post is about: how did I create the graphing system there?

Now, because you might not have a Raspberry Pi of your own to play with, I'm going to demonstrate with a blank Debian install (because my Raspberry Pi OS is Rasbian), and we'll go through the whole thing pretty quick as far as setup.

For me, I use Oracle (is it Oracle now? It was Sun when I started using it) VirtualBox, which is a freaking awesome piece of software. I then setup a Debian VM, get Debian installed with a minimal graphical config (XFCE), and then we can get started.

Step 1: Install MariaDB and Apache with Python

The first step to getting our setup going is to install MariaDB / MySQL. For this, a simple command is necessary (if using Debian 9.3 you'll want to su - then use the command instead of sudo with the command, as it seems like sudo isn't always included by default):

sudo apt-get install mariadb-server

(If it gives you crap about inserting a CD ROM, you can sudo nano /etc/apt/sources.list, then put a # as the first character on the first line without it, then CTRL + O, press [Return], then CTRL + X.) My MariaDB did not prompt for a password (it installed with a root user with no password), but if yours prompts you for a password just pick something you can remember (we'll need it later).

Next, we want to install Apache / httpd, and set it up for Python hosting. This is, once again, a pretty simple command:

sudo apt-get install apache2

Then set it up for Python, I won't detail the commands but they're pretty simple:

sudo a2dismod mpm_event
sudo a2enmod mpm_prefork cgi

Modify the /etc/apache2/sites-enabled/000-default.conf:

sudo nano /etc/apache2/sites-enabled/000-default.conf

In that config file, you want to add an Options line with +ExecCGI as the argument inside the Directory, which you should create if it doesn't exist (added mine right after the <VirtualHost *:80> line. You also want to set the index.

<Directory /var/www/html>
    Options + ExecCGI
    DirectoryIndex index.py
</Directory>

Next we want to AddHandler cgi-script .py after the directory lines:

AddHandler cgi-script .py

Finally:

sudo service apache2 restart

Step 2: Create a database

Assuming you setup MySQL / MariaDB, you should be able to login:

mysql -u ... -p

Where ... is your username (probably root, I'm insecure). If you don't have a password set, omit the -p bit. We create a database:

CREATE DATABASE WhateverNameYouReallyLike;
USE WhateverNameYouReallyLike;

Then we'll create a table (you can make as many as you need, I always make one per sensor):

CREATE TABLE TemperatureLog
(
    Logged datetime NOT NULL,
    Sample int(2) NOT NULL,
    Value float(11, 3) NOT NULL,
    PRIMARY KEY (Logged, Sample)
);

You can make as many as you need, I have 6, because I have that many sensor values.

Finally, make a user:

CREATE USER 'SomeUsername' IDENTIFIED BY 'SomePassword';
GRANT ALL ON WhateverNameYouReallyLike.* TO 'SomeUsername';

Step 3: Log to the Database

This part I won't detail much, we'll build a sample cron job that adds random data, not ideal but it'll get our graphing to work like we want.

We won't use truly random data, we'll actually use a formula with a little bit of a random skew, so that we see the graph adjust slightly but also to allow us to see how our tick settings and such work.

First we'll install pymysql, which isn't available by default:

sudo apt-get install python-pymysql

Then, create a test.py or something, with the following contents:

#!/usr/bin/python
from datetime import datetime
from time import sleep
from random import randint

import sys
import pymysql

SAMPLES = 5
DELAY = 2
DATE_FORMAT = "%Y-%m-%d %H:%M:%S"

def build_query(table, date, sample, value):
    template = """INSERT INTO {table} (Logged, Sample, Value) VALUES ('{logged}', {sample}. {value})"""
    return template.format(table=table, logged=date, sample=sample, value=value)

if __name__ == '__main__':
    date_val = datetime.utcnow()
    date = date_val.strftime(DATE_FORMAT)
    conn = pymysql.connect (
        db='WhateverNameYouLike',
        user='SomeUsername',
        passwd='SomePassword',
        host='localhost')
    cursor = conn.cursor()

    for sample in range(SAMPLES):
        value = date_val.hour * 60 + date_val.minute
        value = abs(1800 - value) / 100.0
        temp = value + (randint(0, 9) / 100.0)
        cursor.execute(build_query("TemperatureLog", date, sample, temp))
        print "Temp: {0}".format(temp)
    conn.commit()

Now the values themselves don't really matter, but they should range from 0 to 18, then back down. (0 at noon, 18 at midnight), that way we see a nice curve.

Next step is to setup a cronjob, usually cron is installed by default but if it's not there are articles on the internet about it.

We'll make a /etc/cron.d/test for ours, and fill it with:

SHELL=/bin/sh
* * * * * pi /home/pi/test.py

Chances are you are on a Raspberry Pi, so pi will be after the five asterisks, then /home/py/test.py is the path to your python file.

This will run every minute, so we'll let it run while we work on the next bit.

Step 4: Setup graphing (this is the fun part)

We want to get our Apache server setup to host some python, HTML and Javascript which allows us to view live-graphs.

We'll test Apache quick with an index.py:

#!/usr/bin/python
print "Content-Type: text/html"
print ""
print "Test"

If well-and-good you should see "Test" printed to your screen.

Now things get fun.

For graphing, I always use [d3.js], which is a really powerful and robust bit of JavaScript, it does a lot of stuff, far more than I know, so I just trust it for the most part (probably bad but oh well). You'll want to download the .zip, and pull all the files into a d3 folder in your /var/www/html.

You also probably want jQuery, I'm using 1.12.4 min, and whatever design stuff you want (bootstrap, etc.).

We want to make our index.py pretty simple:

#!/usr/bin/python
# Header must always be printed first
print "Content-type: text/html"
print ""

if __name__ == "__main__":
    with open("index.html") as template:
        print template.read()

That's it, we'll use index.html to hold all our stuff. (Realistically, we could have left the document root of Apache as index.html and just used it, but I had it setup this way because I did a couple weird things in the index.py.) The nice thing here is this still proves your python works.

Next, we want to define a time.py, which will be a sort of API:

#!/usr/bin/python
from datetime import datetime, timedelta
import cgi

DEFAULT_DATE_FORMAT = "%Y-%m-%d %H:%M:%S"

# Header must always be printed first
print "Content-type: text/html"
print ""

if __name__ == "__main__":
    args = cgi.FieldStorage()
    date_format = DEFAULT_DATE_FORMAT
    if "DateFormat" in args:
        date_format = args["DateFormat"].value

    if "Next" in args or "All" in args:
        utctime = datetime.utcnow()
        print (utctime + timedelta(seconds = 60 - utctime.second)).strftime(date_format)
   if "NextIn" in args or "All" in args:
        utctime = datetime.utcnow()
        print str(60 - utctime.second) + " seconds"
    if (not "Next" in args and not "NextIn" in args) or "All" in args:
        print datetime.utcnow().strftime(date_format)

Alright, so we're going to use this to display the system time and the countdown to the next thing. You can test it directly, it should print your current system time (in UTC).

Next I'll give you my stylesheet, it's pretty basic:

body { 
    background: #202020;
    color: #efefef;
    width: 100%;
    height: 100%;
    overflow: hidden;
}
html { height: 100%; }

.graphBlock {
    max-height: 100%:
    max-width: 100%;
    height: 100%;
    width: 100%;
    text-align: center;
}

svg { display: inline-block; }
.graphBlock svg { width: 100%; }
.graphBlock.split svg { width: 49%; }
.right { float: right; }
.axis line { stroke: #303030; }
.axis.y path, .axis.y .tick:nth-child(2) line { stroke: #efefef; }
.axis path { stroke: #303030; }
svg text { fill: #efefef; }

.header, .current {
    background: #07507F;
    padding: 5px;
}

.current { border-bottom: 1px solid #30303; }
select, input { color: #202020; }

Alright, so the stylesheet is done. Things only gett more interesting from here. We have a graph.js file to define:

function buildGraph(svgTitle, table, title, unit, params, cb) {
    var svg = d3.select(svgTitle);
    var margin = {top: 20, right: 20, bottom: 30, left: 40};
    var vb = svg.attr("viewBox").split(" ");
    var width = +vb[2] - margin.left - margin.right;
    var height = +vb[3] - margin.top - margin.bottom;
    var g = svg
        .append("g")
        .attr("transform", "translate(" + margin.left + "," + margin.top + ")")
        .attr("width", width);

    var parseTime = d3.timeParse("%Y-%m-%d %H:%M:%S");

    var x = d3.scaleTime().rangeRound([0, width]);
    var y = d3.scaleLinear().rangeRound([height, 0]);

    var line = d3.line()
        .curve(d3.curveMonotoneX)
        .x(function(d) { return x(d.Logged); })
        .y(function(d) { return y(d.Value); });

    var area = d3.area()
        .curve(d3.curveMonotoneX)
        .x(function(d) { return x(d.Logged); })
        .y1(function(d) { return y(d.Value); });

    d3.tsv("history.py?LogType=" + table + "&" + params, function(d) {
        d.Logged = parseTime(d.Logged);
        d.Value = +d.Value;
        return d;
    }, function(error, data) {
        if (error) throw error;

        x.domain(d3.extent(data, function(d) { return d.Logged; }));
        var yS = y.domain(d3.extent(data, function(d) { return d.Value; })).nice();

        area.y0(y(yS.domain()[0]));

        g.append("g")
            .attr("class", "x axis")
            .attr("transform", "translate(0," + height + ")")
            .call(d3.axisBottom(x).tickSize(-height))
            .select(".domain");

        g.append("g")
            .attr("class", "y axis")
            .call(d3.axisLeft(y).tickSize(-width))
            .append("text")
            .attr("fill", "#000")
            .attr("transform", "rotate(-90)")
            .attr("y", 6)
            .attr("x", -4)
            .attr("dy", "0.71em")
            .attr("text-anchor", "em")
            .text(title);

        g.select(".y")
            .select(".domain")
            .attr("transform", "translate(-1,0)");

        g.append("path")
            .datum(data)
            .attr("fill", "none")
            .attr("stroke", "steelblue")
            .attr("stroke-linejoin", "round")
            .attr("stroke-linecap", "round")
            .attr("stroke-width", 1.5)
            .attr("d", line);

        g.append("path")
            .datum(data)
            .attr("fill", "steelblue")
            .attr("fill-opacity", "0.25")
            .attr("stroke", "none")
            .attr("d", area);

        g.append("text")
            .attr("fill", "#000")
            .attr("y", "4")
            .attr("x", width - 4)
            .attr("dy", "0.71em")
            .attr("font-size", "11")
            .attr("text-anchor", "end")
            .text(parseFloat(Math.round(data[data.length - 1].Value * 100) / 100).toFixed(2) + unit);
        cb();
    });
}

function updateGraph(svgTitle, table, title, unit, params, cb) {
    var svg = d3.select(svgTitle);
    var margin = {top: 20, right: 20, bottom: 30, left: 40};
    var vb = svg.attr("viewBox").split(" ");
    var width = +vb[2] - margin.left - margin.right;
    var height = +vb[3] - margin.top - margin.bottom;
    var g = svg.select("g");

    var parseTime = d3.timeParse("%Y-%m-%d %H:%M:%S");

    var x = d3.scaleTime().rangeRound([0, width]);
    var y = d3.scaleLinear().rangeRound([height, 0]);

    var line = d3.line()
        .curve(d3.curveMonotoneX)
        .x(function(d) { return x(d.Logged); })
        .y(function(d) { return y(d.Value); });

    var area = d3.area()
        .curve(d3.curveMonotoneX)
        .x(function(d) { return x(d.Logged); })
        .y1(function(d) { return y(d.Value); });

    d3.tsv("history.py?LogType=" + table + "&" + params, function(d) {
        d.Logged = parseTime(d.Logged);
        d.Value = +d.Value;
        return d;
    }, function(error, data) {
        if (error) throw error;

        x.domain(d3.extent(data, function(d) { return d.Logged; }));
        var yS = y.domain(d3.extent(data, function(d) { return d.Value; })).nice();

        area.y0(y(yS.domain()[0]));

        g.selectAll("path").remove();
        g.selectAll("g").remove();
        g.selectAll("text").remove();

        g.append("g")
            .attr("class", "x axis")
            .attr("transform", "translate(0," + height + ")")
            .call(d3.axisBottom(x).tickSize(-height))
            .select(".domain");

        g.append("g")
            .attr("class", "y axis")
            .call(d3.axisLeft(y).tickSize(-width))
            .append("text")
            .attr("fill", "#000")
            .attr("transform", "rotate(-90)")
            .attr("y", 6)
            .attr("x", -4)
            .attr("dy", "0.71em")
            .attr("text-anchor", "em")
            .text(title);

        g.select(".y")
            .select(".domain")
            .attr("transform", "translate(-1,0)");

        g.append("path")
            .datum(data)
            .attr("fill", "none")
            .attr("stroke", "steelblue")
            .attr("stroke-linejoin", "round")
            .attr("stroke-linecap", "round")
            .attr("stroke-width", 1.5)
            .attr("d", line);

        g.append("path")
            .datum(data)
            .attr("fill", "steelblue")
            .attr("fill-opacity", "0.25")
            .attr("stroke", "none")
            .attr("d", area);

        g.append("text")
            .attr("fill", "#000")
            .attr("y", "4")
            .attr("x", width - 4)
            .attr("dy", "0.71em")
            .attr("font-size", "11")
            .attr("text-anchor", "end")
            .text(parseFloat(Math.round(data[data.length - 1].Value * 100) / 100).toFixed(2) + unit);
        cb();
    });
}

This is the D3 bit, it basically build an individual graph from the provided parameters.

The last two python files are for the history (which was in the blue field in my screenshot) and for the latest, which gathers the data-points for D3:

#!/usr/bin/python
from datetime import datetime, timedelta

import cgi
import pymysql

DATE_FORMAT = "%Y-%m-%d %H:%M:%S"
DEFAULT_DAY_LIMIT = 7
DEFAULT_MINUTE_GROUPING = 3

if __name__ == '__main__':
    print "Content-type: text/html"
    print

    args = cgi.FieldStorage()
    day_limit = DEFAULT_DAY_LIMIT

    if "Days" in args:
        day_limit = float(args["Days"].value)

    start_date = (datetime.utcnow() - timedelta(days = day_limit)).strftime(DATE_FORMAT)
    end_date = datetime.utcnow().strftime(DATE_FORMAT)
    minute_group = DEFAULT_MINUTE_GROUPING

    if "MinuteGroup" in args:
        minute_group = float(args["MinuteGroup"].value)

    table = "TemperatureLog"

    formatter = lambda c: c * 1.8 + 32

    if "LogType" in args:
        log_type = args["LogType"].value
        if log_type == "Humidity":
            table = "HumidityLog"
            formatter = lambda x: x
        elif log_type == "BmpTemp":
            table = "BmpTempLog"
            formatter = lambda c: c * 1.8 + 32
        elif log_type == "Pressure":
            table = "PressureLog"
            formatter = lambda p: p * 0.0002953
        elif log_type == "Altitude":
            table = "AltitudeLog"
            formatter = lambda m: m * 3.28084
        elif log_type == "SeaPressure":
            table = "SeaPressureLog"
            formatter = lambda p: p * 0.0002953

    if "Start" in args:
        start_date = args["Start"].value
    if "End" in args:
        end_date = args["End"].value

    conn = pymysql.connect(
        db='WhateverNameYouReallyLike',
        user='SomeUsername',
        passwd='SomePassword',
        host='localhost')
    cursor = conn.cursor()

    cursor.execute("""
        SELECT
            Logged, AVG(Value)
        FROM
            {0}
        WHERE
            Logged >= '{1}' AND Logged <= '{2}'
        GROUP BY
            ROUND(UNIX_TIMESTAMP(Logged)/({3} * 60));""".format(table, start_date, end_date, minute_group))

    didfirst = 0
    print "Logged\tValue"
    for row in cursor.fetchall():
        didfirst = 1
        print "{0}\t{1}".format(row[0], formatter(row[1]))

We're going to call this file history.py. The last python file is latest.py:

#!/usr/bin/python
from datetime import datetime, timedelta

import cgi
import pymysql

DATE_FORMAT = "%Y-%m-%d %H:%M:%S"
DEFAULT_DAY_LIMIT = 7
DEFAULT_MINUTE_GROUPING = 1

if __name__ == '__main__':
    print "Content-type: text/html"
    print

    args = cgi.FieldStorage()
    start_date = (datetime.utcnow() - timedelta(days = DEFAULT_DAY_LIMIT)).strftime(DATE_FORMAT)
    end_date = datetime.utcnow().strftime(DATE_FORMAT)
    minute_group = DEFAULT_MINUTE_GROUPING
    gather = []

    gather.append(("TemperatureLog", lambda c: c * 1.8 + 32))

    if "Start" in args:
        start_date = args["Start"].value
    if "End" in args:
        end_date = args["End"].value

    conn = pymysql.connect(
        db='WhateverNameYouReallyLike',
        user='SomeUsername',
        passwd='SomePassword',
        host='localhost')
    cursor = conn.cursor()

    for table, formatter in gather:
        cursor.execute("""
            SELECT
                Logged, AVG(Value)
            FROM
                {0}
            WHERE
                Logged >= '{1}' AND Logged <= '{2}'
            GROUP BY
                ROUND(UNIX_TIMESTAMP(Logged)/({3} * 60))
            ORDER BY
                Logged DESC
            LIMIT
                1;""".format(table, start_date, end_date, minute_group))

        for row in cursor.fetchall():
            print "{2}\t{0}\t{1}".format(row[0], formatter(row[1]), table[:-3])

So now there's only one file left, which is our index.html. I guess let's lay it out:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
        <link href="style.css" rel="stylesheet" />
        <script src='d3/d3.min.js'></script>
        <script src='graphs.js'></script>
</head>
<body>
    <div class="header">
        Last refresh: <span class="lastRefresh"></span>;
                    Pi time: <span class="piTime"></span>;
        Next sensor pull: <span class="nextRefresh"></span> (<span class="nextRefreshSeconds"></span>)
        <div class="right">
            Graph Timeline:
            <select id="days">
                <option value="0.041666667">1 hour</option>
                <option value="0.125">3 hours</option>
                <option value="0.25">6 hours</option>
                <option value="0.5">12 hours</option>
                <option value="1">1 day</option>
                <option value="2">2 days</option>
                <option value="3">3 days</option>
                <option value="4">4 days</option>
                <option value="5">5 days</option>
                <option value="6">6 days</option>
                <option value="7" selected>1 week</option>
                <option value="14">2 weeks</option>
                <option value="28">4 weeks</option>
                <option value="30">1 month</option>
                <option value="60">2 months</option>
                <option value="90">3 months</option>
            </select>
        </div>
    </div>
    <div class="current">
        Current readings (<span id="logValue"></span>):
        <span class="">
            Temp 1:
            <span id="temperatureValue"></span>F
        </span>
        <div class="right">
            Ticks:
            <select id="minuteGroup">
                <option value="0.083333">5 seconds</option>
                <option value="0.166667">10 seconds</option>
                <option value="0.25">15 seconds</option>
                <option value="0.5">30 seconds</option>
                <option value="1" selected>1 minute</option>
                <option value="2">2 minutes</option>
                <option value="3">3 minutes</option>
                <option value="5">5 minutes</option>
                <option value="10">10 minutes</option>
                <option value="15">15 minutes</option>
                <option value="30">30 minutes</option>
                <option value="60">1 hour</option>
                <option value="120">2 hours</option>
                <option value="180">3 hours</option>
                <option value="240">4 hours</option>
                <option value="360">6 hours</option>
                <option value="720">12 hours</option>
                <option value="1440">1 day</option>
            </select>
        </div>
        <br />
    </div>
    <div class='graphBlock split'>
        <svg class='temp' viewBox='0 0 1 1'></svg>
    </div>
    <script src='jquery.1.12.4.min.js'></script>

    <script>
        var loading = 0;

        Number.prototype.padLeft = function(base, chr) {
        var len = (String(base || 10).length - String(this).length) + 1;
            return len > 0 ? new Array(len).join(chr || '0') + this : this;
        }

        function round(value, decimals) {
            return Number(Math.round(value + 'e' + decimals) + 'e-' + decimals);
        }

        function graphDone() {
            loading -= 1;
        }

        function updateGraphs(callback) {
            if (loading > 0) { return; }

            clearInterval(graphInt);
            if (callback == null) {
                callback = updateGraph;
            }

            var days = "Days=" + $("#days").val() + "&MinuteGroup=" + $("#minuteGroup").val();
            loading = 1;

            var subHeight = $(".header").outerHeight() + $(".current").outerHeight();
            var vb = "0 0 ";

            if ($(".graphBlock").hasClass("split")) {
                vb += $(".graphBlock").outerWidth() / 2;
                vb += " ";
                vb += ($(".graphBlock").outerHeight() - subHeight) / 3;
            } else {
                vb += $(".graphBlock").outerWidth() / 1;
                vb += " ";
                vb += ($(".graphBlock").outerHeight() - subHeight) / 6;
            }

            $('svg').each(function () { $(this)[0].setAttribute('viewBox', vb); });
            callback('.temp', 'Temperature', 'Temp (F)', "ºF", days, graphDone);

            var d = new Date;
            var dFormat = [d.getUTCFullYear(), (d.getUTCMonth() + 1).padLeft(), d.getUTCDate().padLeft()].join('-');
            dFormat += " ";
            dFormat += [d.getUTCHours().padLeft(), d.getUTCMinutes().padLeft(), d.getUTCSeconds().padLeft()].join(':');
            $('.lastRefresh').html(dFormat);
            graphInt = setInterval(updateGraphs, 5000);
        }

        function updateTimes() {
            clearInterval(timeInt);
            var xmlHttp = new XMLHttpRequest();
            xmlHttp.onreadystatechange = function() {
                if (xmlHttp.readyState == 4 && xmlHttp.status == 200) {
                    var text = xmlHttp.responseText.trim().split("\n");
                    $(".nextRefresh").html(text[0]);
                    $(".nextRefreshSeconds").html(text[1]);
                    $(".piTime").html(text[2]);
                    timeInt = setInterval(updateTimes, 500);
                }
            }
            xmlHttp.open("GET", "time.py?All=true", true);
            xmlHttp.send(null);
        }

        function updateCurrents() {
            clearInterval(currentInt);
            var xmlHttp = new XMLHttpRequest();
            xmlHttp.onreadystatechange = function() {
                if (xmlHttp.readyState == 4 && xmlHttp.status == 200) {
                    var text = xmlHttp.responseText.trim().split("\n");
                    text.forEach(function(line) {
                        var items = line.split("\t");
                        switch (items[0]) {
                            case "Temperature":
                                $("#temperatureValue").html(round(items[2], 2));
                                break;
                        }
                        $("#logValue").html(items[1]);
                    });
                    currentInt = setInterval(updateCurrents, 1000);
                }
            }
            xmlHttp.open("GET", "latest.py", true);
            xmlHttp.send(null);
        }

        var graphInt = setInterval(updateGraphs, 5000);
        var timeInt = setInterval(updateTimes, 500);
        var currentInt = setInterval(updateCurrents, 1000);
        updateGraphs(buildGraph);
        updateTimes();
        updateCurrents();
    </script>
</body>
</html>

Yeah it's long, yeah it's ugly, but it works. :)

Alright, so if you put all this together you should have a graphing utility, that automatically refreshes (the setInterval lines do that, the 5000, 1000 and 500 are milliseconds), and the cron-job will keep data moving in.

The graphs update every 5 seconds or so, so you should be able to change the "Ticks" or "Graph Timeline" setting and see a very quick reaction. You will also see that all of this is setup for adding new tables (with the same structure) for new graphs / sensors.