The asql command allows you to load up your apache logs into a temporary SQLite database and query the log files as you would any normal SQL database. It provides a both interactive prompt and the ability to be used as a script interface.
If you’re running Ubuntu you can install asql with the following command:
Getting started with ASQL
You can start up asql interactive mode by simply typing asql
From the prompt type help for more info
First up we want to load a log file. To do this we can run load /path/to/my.log Lets try this now.
You can also use wildcards like /var/log/apache2/access.log* asql will also automatically load any files compressed with *.gz and bzip2.
It will load all the log files into a table called logs
Now lets try our first query
You can see the fields available to you by running show
Lets look at some more practical queries.
This request is in the docs, it shows all the clients connecting to your webserver and the size of files/requests that they have downloaded in total.
Find the top 10 client addresses by hits
Find the largest 20 HTML files requested
Show average hits per hour
You get the idea.
Feel free to experiment, and don’t forget to look at man asql as there are some useful examples in the docs. Post any queries you think would be useful in the comments.