Category Archives: MySQL

Legba the Net-tracker

4 minutes, 15 seconds

Intro

I’d been meaning to learn how to write an app using something more than CSV files, but less than MariaDB, to store files – I’m thinking SQLite of course! Then along came the desire to have a simple way to track when a computer was on a network as a proxy for kids’ daily screen time. After all, the network is the computer, right?

While there’s so very many ways to solve detecting if a computer is online (more on this later), I thought it’d be fun to write a simple app that could correlate multiple IPs to a single person, and then give a histogram of minutes per day per person. Given this is just a proxy for screen time, it’s fine if it doesn’t have alerting, password protection or even a way to prevent going over the allotted time per day. The goal will be for any interested parties to see how long a device has been on for the current day. It’s then up to the family to have a discussion about what it means to go over your daily allotment.

Ok, let’s do this! We have a requirement to track computers being online and to write and read the results to a SQLite DB. I’ve been groovin’ on learning Python, so let’s double down and use that. I did some Wikipedia exploring and read about Papa Legba, and thought it made a mighty fine sounding name. Finally, after some nudging from a friend, we’ll package it up in Docker so it’s easy to try out and host in an isolated container.

Ping FTW

The first step to using Legba, is to define a list of users and which IPs they’ll be on. Very likely the best way to do this is to either use static IPs on your LAN clients, or have your DHCP server set the same IPs per MAC every time.

Then you’ll create a conf.py file copied from the conf.example.py file and fill it out. Here we see Jon and Habib have one IP each, where as Mohamed has 2:

trackme = {
    'Jon': ["192.168.1.82"],
    'Habib': ["192.168.1.12"],
    'Mohamed, ': ["192.168.1.240", "192.168.1.17"]
}

The code to track if a device is achieved via the subprocess module via a ping() function with just two lines that send a single ICMP packet:

# thanks https://stackoverflow.com/a/10402323
def ping(host):
    """ Ping a host on the network. Returns boolean """
    command = ["ping", "-c", "1", "-w1", host]
    return subprocess.run(args=command, stdout=subprocess.DEVNULL, stderr=subprocess.DEVNULL).returncode == 0

Back in the main() function, we then read in config, loop over each person and try and ping() each of their IPs. If we see them online, we write to the DB via record(). It ended up, just as I’d hoped, that Python’s SQLite libraries are robust and it’s just 6 lines to insert a row:

sql = ''' INSERT INTO status(name,state,date) VALUES(?,?,?) '''
cur = sqlite.cursor()
activity = (name, state, datetime.now())
cur.execute(sql, activity)
sqlite.commit()

return cur.lastrowid

Just before the end of the loop we call probably the most complex function of the lot output_stats_html(). This function is responsible for reading the day’s active users, getting each users activity by hour, the total for the day and finally output static HTML as well as a static JSON file that will get called via AJAX so the stats will auto-refresh.

At the end of the loop we sleep for 60 seconds. In theory if you had hundreds (thousands?!) of IPs to track and they were on connections with >500ms latency, it would take way longer than 60 seconds. Legba will not scale to this level. It’s currently been comfortably tested with 5-10 devices on a LAN where each device has ~20ms of latency.

A histogram is worth a 1000 words

After you’ve done a bit of a git clone with a lil pip3 install and fleshed out your own config.py and done a little systemd love, you’ll have some sweet sweet histograms! (Some keen eyed readers may note this histogram looks familiar ;)

It’s interesting to note that mobile devices, as seen withe “Adnon Cell”, are effectively on all the time. In this sense, Legba is not much use to track a cell phone. Meanwhile, Bobby Table’s desktop, Adnon’s Laptop and Chang’s Nintendo Switch all work as expected (NB – I didn’t actually test with a Switch).

Existing Solutions

I’ve been running this for solution for just about 4 months now. It’s been a great way for our family to have an open discussion about what it means to spend too much time on the computer and it’s been rock solid. Checking ls and select count(*) from status; I see my DB is 23MB and has 487,069 rows.

Given the simplicity of this app, could this DB and rows be easily stored and retrieved elsewhere? When I wrote the app, I didn’t care – I just wanted to write it for the fun of writing it! However, I was listening to episode 171 of Late Night Linux and they mentioned how utilitarian Telegraf is. It struck me that, indeed, if you had a Telegraf, InfluxDB and Grafana (aka “the TIG stack”) already set up, it would be pretty trivial to capture these same stats. I would do this by setting up a centralized instance of Telegraf and either use the built in Ping plugin, or possibly the more extensible [[inputs.exec]] input type. With the latter, you could even re-use parts of Legba to pretty trivially input the data to InfluxDB. Then, it would be equally trivial, to slice up the ping counts per hour, per user and have a slick dashboard. Just food for thought!

Otherwise, I hope some else than me gives Legba a try!

Howto: Sympa 6.1 on Ubuntu 16.04

3 minutes, 6 seconds

Recently I was tasked at work to get an instance of Sympa set up. Their docs are a bit scattered, but I found a promising post on debian.org which suggested I could get away with an apt-get install instead of needing to compile from source. Well, it turns out I did get it working, but only after a lot of trial and error. Given that some one else might be trying to do this, and because I had to document the exact steps for work, here’s a handy dandy blog post which I hope will help some one trying to do the same thing.

Good news for those looking to do this for Sympa 6.2 (latest at time of publishing), I have a post on how to do this exact thing on the soon to be released Ubuntu 17.04 with Sympa 6.2.  Stay tuned!

Assumptions/Prerequisites

This post assumes you have root on your box.  It assumes you have Apache2 installed.  It assumes you’re running a stock Ubuntu 16.04 install.  It assumes you want to run Sympa on your server.  It also assumes you’ll be using Postfix as the lists MTA. It assumes you have a DNS entry (A record) for the server.  As well it assumes you also have an MX record pointing to the A record or no MX record so the MX defaults to the A record.  If this doesn’t apply to you, caveat emptor!

To recap, that’s:

  • Apache 2 installed and working
  • Postfix as MTA
  • Ubuntu 16.04 server
  • Existing DNS entry
  • Run all commands as root

I also was using this server solely to serve Sympa mail and web traffic so if you have a multi-tenant/multi-use server, it may be more complicated.

Steps

These steps assume you’re going to install Sympa on list.example.com.  There’s no reason you couldn’t use example.com instead.

  1. Install sympa:
    apt-get install -y sympa
  2. When prompted during this install:
    1. Choose a good mysql root password and enter it when prompted
    2. Please select the mail server configuration type that best meets your needs: Internet Site
    3. System mail name: list.example.com
    4. Which Web Server(s) are you running?: apache 2
    5. Database type to be used by sympa: mysql
    6. MySQL application password for sympa: <blank> (will assign random one)
  3. Sympa 6.1 has a present! It ships with a bug.  Fix the regex on line 126 of /usr/share/sympa/lib/SympaSession.pm so it looks like this (note “{” is now “\{” ):
    if ($cookie and $cookie =~ /^\d\{,16}$/) {
  4. Edit /etc/sympa/wwsympa.conf to change line 81 to 1 instead of 0:
     use_fast_cgi 1

    If you don’t do this step, you’ll see full HTML pages show up in /var/logs/syslog and only 500 errors in the browser :(

    lists.example.com should show the sympa UI, w00t!

  5. Ensure Sympa starts at boot:
    update-rc.d sympa defaults
    update-rc.d sympa enable
  6. ensure postfix is updated in /etc/postfix/main.cf edit these values to match:
    myhostname = lists.example.com
    smtpd_tls_cert_file=/full/path/to/cert/apache/uses.pem
    smtpd_tls_key_file=/full/path/to/key/apache/uses.pem
    alias_maps = hash:/etc/aliases,hash:/etc/mail/sympa/aliases
    alias_database = hash:/etc/aliases,hash:/etc/mail/sympa/aliases
    mydestination = $myhostname, lists.example.com, , localhost
    relay_domains = $mydestination, lists.example.com
  7. Update /etc/sympa/sympa.conf so that these values match:
    listmaster email1@example.com,other_here@domain.com
    domain lists.example.com
    wwsympa_url https://lists.example.com/wws

     

  8. update /etc/sympa/wwsympa.conf so that these values match:
    default_home lists
    create_list intranet

    The “intranet” value will prevent some one from signing up and requesting a list with any approval.

  9. add default aliases for sympa at the top of /etc/mail/sympa/aliases:
    ## main sympa aliases
    sympa: "| /usr/lib/sympa/bin/queue sympa@lists.example.com"
    listmaster: "| /usr/lib/sympa/bin/queue sympa@lists.example.com"
    bounce+*: "| /usr/lib/sympa/bin/bouncequeue sympa@lists.example.com"
    sympa-request: email1@example.com
    sympa-owner: email1@example.com
  10. reboot and rebuild aliases:
    newaliases
    reboot

Sympa should now be up and running at lists.example.com!  All mail and and out should work so you can run your own list server. Please report any problems so I can keep this post updated and accurate – thanks!

tableMaker: Open Source PHP MySQL CRUD GUI library (Updated!)

1 minute, 56 seconds

I have to admit, one of the utter joys of my job is that they encourage me to open source software I write at my day job.  After looking high and low for a PHP framework or library to do a basic MySQL CRUD GUI, I gave up.  While phpMyAdmin is the longstanding champion for full featured DB administration, it’s way to complicated for an end user looking to just add a row right quick.  There seems to be an amazing project called CrudKit (great name!), but it has this one, massive blocking “feature”:

prevents usage in MVC frameworks
commit 047807d01f

This is, literally, what I was trying to do. I spent a some time seeing how hard it would be to contribute to CrudKit to get the feature I wanted working.  I ultimately decided that a bespoke solution would more quickly achieve my desired goals.  That said, if you do want a stand alone app, do check out CrudKit.

While I suspect it could use some rewrites to not have silly-long arrays passed as arguments, I’m quite happy with my results: tableMaker. This guy takes this PHP:

$tm = new tableManager(DB_SERVER, DB_USER, DB_PASS, DATABASE, TABLE);
$rowsArray = $tm->getRowsFromTable();
print $tm->getHtmlFromRows($rowsArray, "/edit?table={$tm->table}&id=");

And turns it into this HTML:

If you want to render a fully functional edit form with dynamic client side error handling and table sensitive validation rules, just run this PHP:

$row = $tm->getRowFromTable($_GET['id']);
print $tm->getAddEditHtml($row, 'edit', "/save?table={$tm->table}");

Which will output this responsive, nice looking HTML:

Two big features of tableMaker are it’s simplicity and it’s security*. Yes table maker can do whiz bang client side sorting, but it also can output tidy, HTML compliant tables.  Yes, we can make your browser download 100k+ of web fonts just to render an “X” when you have an error in your form, but it can also do with out all that noise – implementers choice!  Security wise, tableMaker abstracts away all the complexity while ensuring there’s simply no way for you to expose yourself to a SQL injection attack. (* We need some nonces).

Along they way in making this, I see all the cool kids are using Composer.  I’ve earmarked this guy for my next project!

I’m really happy to have been paid to write this library; I’d be even happier if some else on started using it! I’d about piss my pants with glee if some opened a PR ;)

Update 3/18/2017Issue #3 on tableManager has been closed – CSRF protection in place!  Go Nonce, go!

Rogue MySQL queries

1 minute, 32 seconds

Do you you ever have those moments where you’re trying out a query in MySQL and realize after you executed it that it’s going to kill your database server? If you’re like me, you’re often working in a LAMP stack in which case you’ve executed the query via a web page. The first thing you do is hit “esc” to stop the page from loading. MySQL doesn’t head the “full stop” call from Apache, if issued. If you’re silly and you did this on some sort production machine, you don’t exactly want to restart Apache or even worse, restart MySQL which can often take a while.

Enter show processlist; and kill PID! Yeah, mysql has it’s on version of “ps” and “kill -9”. In our case, this is extremely handy because it saves us the headache of rebooting MySQL and taking the DB offline for a minute. Connect to your DB as root and type:

mysql> show processlist;
+------+------+-----------+------+---------+------+----------------------+--------------+
| Id   | User | Host      | db   | Command | Time | State                | Info         |
+------+------+-----------+------+---------+------+----------------------+--------------+
| 1693 | root | localhost | rei  | Query   |   75 | Copying to tmp table | SELECT     di| 
| 1695 | root | localhost | NULL | Query   |    0 | NULL                 | show processl| 
+------+------+-----------+------+---------+------+----------------------+--------------+

Indeed this one query is killing the CPU:

top - 08:57:50 up 30 days, 20:59,  2 users,  load average: 0.53, 0.14, 0.04
Tasks:  72 total,   2 running,  70 sleeping,   0 stopped,   0 zombie
Cpu(s): 20.1% us, 30.1% sy,  0.0% ni, 49.8% id,  0.0% wa,  0.0% hi,  0.0% si
Mem:   2074628k total,  2010740k used,    63888k free,   142648k buffers
Swap:   524280k total,      192k used,   524088k free,  1549692k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                                                                                              
 3119 mysql     16   0  549m 115m 5076 S 99.8  5.7   9:53.40 mysqld                                                                                                                                

All we have to do is kill it and the box is back to idle. Sweet!

mysql> kill 1693;
Query OK, 0 rows affected (0.00 sec)

mysql> show processlist;
+------+------+-----------+------+---------+------+-------+------------------+
| Id   | User | Host      | db   | Command | Time | State | Info             |
+------+------+-----------+------+---------+------+-------+------------------+
| 1695 | root | localhost | NULL | Query   |    0 | NULL  | show processlist | 
+------+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)