Last updated on September 9, 2020 by Gabriel Cánepa
One of the distinguishing features of Linux is that, under normal circumstances, you should be able to know what is happening and has happened on your system by analyzing one or more system logs. Indeed, system logs are the first resource a system administrator tends to look to while troubleshooting system or application issues. In this article, we will focus on the Apache access log files generated by Apache HTTP server. We will explore an alternative way of analyzing Apache access logs using
asql, an open-source tool that allows one to run SQL queries against the logs in order to view the same information in a more friendly format.
There are two kinds of Apache logs:
/var/log/apache2/access.log(for Debian) or
/var/log/httpd/access_log(for Red Hat). Contains records of every request served by an Apache web server.
/var/log/apache2/error.log(for Debian) or
/var/log/httpd/error_log(for Red Hat). Contains records of all error conditions reported by an Apache web server. Error conditions include, but are not limited to,
403(Forbidden, usually returned after a valid request missing access credentials or insufficient read permissions), and
404(Not found, returned when the requested resource does not exist).
Although the verbosity of Apache access log file can be customized through Apache's configuration files, we will assume the default format in this article, which is as follows:
Remote IP - Request date - Request type - Response code - Requested resource - Remote browser (may also include operating system)
So a typical Apache log entry looks like:
192.168.0.101 - - [22/Aug/2014:12:03:36 -0300] "GET /icons/unknown.gif HTTP/1.1" 200 519 "http://192.168.0.10/test/projects/read_json/" "Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:30.0) Gecko/20100101 Firefox/30.0"
But what about Apache error log? Since error log entries dealing with particular requests have corresponding entries in the access log (which you can customize), you can use the access log file to obtain more information about error conditions (refer to example 5 for more details).
That being said, please note that access log is a system-wide log file. To find the log files of virtual hosts, you may also need to check their corresponding configuration files (e.g., within
/etc/apache2/sites-available/[virtual host name] on Debian).
asql is written in Perl, and requires two Perl modules: a DBI driver for SQLite and GNU readline.
asqlon Debian, Ubuntu or their derivatives
asql and its dependencies will automatically be installed with
aptitude on Debian-based distributions.
# aptitude install asql
asqlon Fedora, CentOS or RHEL
On CentOS or RHEL, you will need to enable EPEL repository first, and then run the commands below. On Fedora, proceed to these commands directly.
# sudo yum install perl-DBD-SQLite perl-Term-ReadLine-Gnu # wget http://www.steve.org.uk/Software/asql/asql-1.7.tar.gz # tar xvfvz asql-1.7.tar.gz # cd asql # make install
As you can guess from the dependencies listed above,
asql converts unstructured plain-text Apache log files into a structured SQLite database, which can be queried using standard SQL commands. This database can be populated with the contents of current and past log files - including compressed rotated logs such as
asql from the command line with the following command
You will be entering
asql's built-in shell interface.
help to list the available commands in the
We will begin by loading all the access logs in
asql, which can be done with:
asql> load </path/to/apache-access-logs>
In case of Debian, the following command will do:
asql> load /var/log/apache2/access.*
In case of CentOS/RHEL, use this command instead:
asql> load /var/log/httpd/access_log*
asql finishes loading access logs, we can start querying the database. Note that the database created after loading is temporary, meaning that if you exit the
asql shell, the database will be lost. If you want to preserve the database, you have to save it to a file first. We will see how to do that later (refer to examples
The database contains a table named
logs. The available fields in the
logs table can be displayed using the
.asql hidden file, which is stored in each user's home directory, records the history of the commands that were typed by the user in an
asql shell. Thus, you can browse through it using the arrow keys, and repeat previous commands by just pressing
ENTER when you find the right one.
Here a few examples of running SQL queries against Apache log files with
Listing the request sources / dates and HTTP status codes returned during the month of October 2014.
SELECT source, date, status FROM logs WHERE date >= '2014-10-01T00:00:00' ORDER BY source;
Displaying the total size (in bytes) of requests served per client in descending order.
SELECT source,SUM(size) AS Number FROM logs GROUP BY source ORDER BY Number DESC;
Saving the database to [filename] in the current working directory.
This allows us to avoid the need for waiting while the log parsing is performed with the
load command as shown earlier.
Restoring the database in a new
asql session after exiting the current one.
Returning error conditions logged in the access file. In this example, we will display all the requests that returned a
403 (access forbidden) HTTP code.
SELECT source,date,status,request FROM logs WHERE status='403' ORDER BY date
This goes to show that although
asql only analyzes access logs, we can use the status field of a request to display requests with error conditions.
We have seen how
asql can help us analyze Apache logs and present the results in a user friendly output format. Although you could obtain similar results by using command line utilities such as
cat in conjunction with
wc (to name a few), in comparison
asql represents a Swiss army knife due to the fact that it allows us to use standard SQL syntax to filter the logs according to our needs.
Feel free to leave your questions or comments below. Hope it helps.
Please note that this article is published by Xmodulo.com under a Creative Commons Attribution-ShareAlike 3.0 Unported License. If you would like to use the whole or any part of this article, you need to cite this web page at Xmodulo.com as the original source.