aSql – Query Apache logfiles via SQL

asql is a simple console shell which allows you to run SQL queries against an Apache logfile, something that can be very useful -- especially at getting information which is not easily available via static logfile analysers.

When asql starts up it creates a temporary SQLite database to hold the contents of the parsed logfile(s) you might load. This temporary database may then be queried directly via SQL.

Install asql on Debian 8

Open the terminal and run the following command

sudo apt-get install asql

Using aSql

Once installed, either via the package or via the source download, please start the shell by typing "asql".

Once the shell starts you have several commands available to you, enter help for a complete list. The three most commonly used commands would be:

load

This will load an Apache logfile into the temporary database which is created at startup. Single files, or globs are accepted so you may run something like this:

load /var/log/apache2/acces*

To save time parsing the input files more than once you may saving the resulting database with save and later restore it.

select

This allows you to run an actual query.

show

This shows the table structure the logfile was parsed into.

aSql Examples

The following sample session provides a demonstration of typical usage of the shell, it demonstrates the use of the alias command which may be used to create persistent aliases:

asql v0.6 -- type ‘help' for help.

asql> load /home/skx/hg/engaging/logs/access.log

Loading: /home/skx/hg/engaging/logs/access.log

sasql> select COUNT(id) FROM logs

46

asql> alias hits SELECT COUNT(id) FROM logs

ALIAS hits SELECT COUNT(id) FROM logs

asql> alias ips SELECT DISTINCT(source) FROM logs;

ALIAS ips SELECT DISTINCT(source) FROM logs;

asql> hits

46

asql> alias

ALIAS hits SELECT COUNT(id) FROM logs

ALIAS ips SELECT DISTINCT(source) FROM logs;

Sponsored Link

Leave a comment

Your email address will not be published. Required fields are marked *