Play with website logs using SQL.

Cattoy loads website logs into an SQLite virtual table where they can be mined with SQL queries.

• sqlite3 compiled with support for external modules. The stock CentOS sqlite package meets this requirement.

• A website that follows EuPathDB's file naming and location conventions so that the log file can be located for a given hostname. Alternatively the access_log.so module can be manually loaded into an sqlite3 session and a virtual table manually created for an apache log file.

• The expected log format is NCSA combined with the addition of %D.

  %h %l %u %t "%r" %>s %b "%{Referer}i" "%{User-agent}i" %D

Invoke the cattoy script with the desired website hostname.

cattoy <hostname>

Invoke desired SQL queries against the access_log table.

cattoy> SELECT url, status
   ...> FROM access_log
   ...> WHERE remote_host = '208.65.89.219'
   ...> AND request like '%gbrowse%'
   ...> AND method = 'POST';
/cgi-bin/gbrowse/trichdb/|200
/cgi-bin/gbrowse/trichdb/|200
/cgi-bin/gbrowse/trichdb/|200
/cgi-bin/gbrowse/trichdb/|200

See SQLite documentation for supported SQL syntax.

The columns of theaccess_log table can be listed using the table_info pragma.

cattoy> PRAGMA table_info(access_log);

0|remote_host|TEXT|0||0
1|remote_user|TEXT|0||0
2|time|TEXT|0||0
3|request|TEXT|0||0
4|status|INTEGER|0||0
5|bytes|INTEGER|0||0
...

If you want to forego the cattoy script, the access_log.so module can be manually loaded into an sqlite3 session and a virtual table manually created for an apache log file.

Start the CLI client

$ sqlite3

Load the module,

sqlite> .load httpd.so

Create a virtual table. Gzip compressed logs are supported.

sqlite> create virtual table access_log using weblog("/var/log/httpd/dev.trichdb.org/access_log-20140101.gz");

It currently only supports the Apache HTTPD access and error logs. Support for Tomcat's catalina and WDK application logs are future desires.

The sqlite libraries are needed to build the source. On RHEL derivatives, these are provided by the sqlite-devel RPM.

$ make

This should generate access_log.so and error_log.so files. The cattoy shell script will load this into an sqlite3 session.