IPv6 Geolocation in Piwik

zac

Note: As of Matomo 3.5.0 or so, none of the below is needed any more as Matomo now supports the Geo IP 2 databases natively. If you followed these instructions, be sure to remove the cron script below – and configure automatic archival of Matomo reports.

 

Piwik is a fantastic and free web analytics tool that unfortunately has very limited support for IPv6 Geolocation information. This has been requested time and time and time again – and with a little effort, it’s pretty easy to solve.

First, a script to download the GeoLite2 databases, seeing as the database downloader in Piwik only works with the older GeoLite databases – I have this saved as /etc/cron.monthly/piwik. Update the variables to suit your install:

#!/bin/sh
# Script to download GeoLite2 databases for Piwik

# Variables
PIWIKDIR="/var/www/piwik/misc"
PIWIKUSER="www-data"

# Get files
cd $PIWIKDIR
wget http://geolite.maxmind.com/download/geoip/database/GeoLite2-City.tar.gz
wget http://geolite.maxmind.com/download/geoip/database/GeoLite2-Country.tar.gz

# Untar city database, clean up
tar zxvf GeoLite2-City.tar.gz --strip-components=1 --wildcards GeoLite2-City*/GeoLite2-City.mmdb
rm GeoLite2-City.tar.gz
chown $PIWIKUSER.$PIWIKUSER GeoLite2-City.mmdb

# Untar country database, clean up
tar zxvf GeoLite2-Country.tar.gz --strip-components=1 --wildcards GeoLite2-Country*/GeoLite2-Country.mmdb
rm GeoLite2-Country.tar.gz
chown $PIWIKUSER.$PIWIKUSER GeoLite2-Country.mmdb

Run the script once to ensure it works – you should end up with two files in Piwik’s misc directory.

Next, install and activate the GeoIP2 plugin:

Log in to Piwik as an admin user, then click ⚙ to access the Settings menu. Click Plugins on the left, then click the link for installing plugins from the Marketplace.

Find Geo IP 2 from the list, click Install and then Activate.

Finally, click Geolocation on the left, then make sure Geo IP 2 is selected as the Location Provider (update and save if necessary). Over on the right, it should have correctly identified the country you’re in, as well as mentioning that this GeoIP implementation has access to city and country databases.

Note that Piwik’s System Check will warn you about File Integrity (because of the new database files) and Geolocation (because of the non-standard GeoIP provider) after configuring Geo IP 2 – this is perfectly normal.

Quick Base Formula URL for multiple actions

zac

In Quick Base, you can create buttons using Formula URL fields to trigger multiple actions – however, it’s complicated. A reminder for myself in case I ever need to set something like this up again – this formula adds a record to a child table, updates a field on the current table and immediately re-displays the form in “view” mode using a series of URL encoded ‘chunks’ that are all strung together at the end:

var text AddChildRecord =
URLRoot() & "db/" [_DBID_XXX]
& "?a=API_AddRecord&_fid_nn="& URLEncode([Record ID#])
& "&_fid_nn="& URLEncode("Add Text Here")
& "&_fid_nn="& URLEncode(User())
& "&_fid_nn="& URLEncode(Today())
& "&apptoken=9jabnbdh53n9gd4kwhxa7s8gn1";

Because we’re using the API_AddRecord function to add a record to a child table, you have to specify the DBID. To find the DBID alias, go into the Settings for the child table, then select Advanced Settings and scroll to the bottom of the page. Record ID will need to be updated to reflect what records are called in that table (unsure whether this needs to be passed through the URLEncode function or not – but it works).

The &_fid_nn items are form fields (nn is the field ID); to find these, go to Settings for the child table, then select Fields under table structure. Hover your mouse over the field name to see the field ID.

The last line is only required if app tokens are enabled for the Quick Base app.

var text EditRecord =
URLRoot() & "db/" & Dbid() & "?act=API_EditRecord&rid=" & [Record ID#]
& "&_fid_nn=" & URLEncode("Yes")
& "&apptoken=9jabnbdh53n9gd4kwhxa7s8gn1";

Editing a record in the existing table using the API_EditRecord function is much easier. Use the Dbid() function to specify the current table, specify the changes you want and add an apptoken if needed.

var text Display =
URLRoot() & "db/" & Dbid()
& "?a=dr&rid=" & [Record ID#];

This displays the record again using the same form. Here’s a breakdown of what each bit means:

URLRoot()Go to https://mycompany.quickbase.com/
& “db/”Add the “db/” you see in all URLs
& Dbid()Add the Table ID you are currently on
& “?a=dr&rid=”This says “Action = Display Record and Record ID = …”
& [Record ID#]Specify the Record ID to display
$AddChildRecord
& "&rdr=" & URLEncode($EditRecord)
& URLEncode("&rdr=" & URLEncode($Display))

String everything together – note the nested URLEncode functions and the lack of a semicolon at the end.

Perl CGI.pm and Unicode

zac

Because I always forget how to handle Unicode input/output in Perl .. a few notes to help jog my memory for next time:

Remember to use both CGI.pm and Encode.pm:

use CGI qw(:standard);
use Encode qw(decode encode);

Handle webform input with Unicode characters:

my $input = decode('utf8', param('input'));

Prepare scalar variables to cope with Unicode characters:

my $output = encode('utf8', $input);

Declare the correct Content-Type when creating HTML:

print header(-type => 'text/html', -charset => 'utf-8');
print start_html(-title => 'Page with Unicode', -encoding => 'utf-8');

Now you can print your Unicode variables without error:

print div({-id => 'output'}, $output);