How to query Apache logs with SQL statements on Linux

Linux has a notable feature, and under normal circumstances, you can use the log to analyze the system log to see what is going on in your system, or what is happening. Indeed, system logs are the first-hand resources that system administrators need to solve system and application problems. In this article, we will focus on Apache access logs generated by Apache HTTP Web server.

This time, we will analyze Apache access logs in an alternative way, and the tools we use are asql. aSQL is an open source tool that allows users to query logs using SQL statements to present the same information in a more friendly format.

Apache Log Background knowledge

Apache has two types of logs:

• Access log: stored in Path/var/log/apache2/access.log (Debian) or/var/log/httpd/access_log (Red Hat). Access log logs all requests performed by Apache Web server.
• Error log: stored in Path/var/log/apache2/error.log (Debian) or/var/log/httpd/error_log (Red Hat). Error log records all the errors reported by Apache Web server and the error conditions. The Error situation includes (not limited to) 403 (Forbidden, which is typically reported when a request is denied access), 404 (not Found, which is reported when the request resource does not exist).

Although administrators can customize the details of Apache access log by configuring the Apache configuration file, in this article we will use the default configuration as follows:

So a typical Apache log entry would look like this:

But what about Apache error log? Because the error log entry primarily records the special request in Access log (which you can customize), you can get more information about the error situation by using Access log (example 5 for more details).

In addition, access log is a system-level log file, as described in advance. To analyze the log files of the virtual hosts, you need to check their corresponding configuration files (for example: in/etc/apache2/sites-available/[virtual host name] (Debian)).

Installing aSQL on Linux

aSQL is written by Perl and requires the following two Perl modules: SQLite's DBI driver and GNU ReadLine.

Install aSQL on Debian, Ubuntu and its derivative distributions

Using aptitude,asql based on the Debian distribution and its dependencies will be automatically installed.

# Aptitude Install aSQL

Installing the aSQL on the Fedora,centos,rhel

On CentOS or RHEL, you need to enable EPEL repository, and then run the following code. In Fedora, run the following code 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

How does the aSQL work?

As you can see from the dependencies in the above code, aSQL transforms the unstructured plaintext Apache log into the structured SQLite database information. The generated SQLite database can accept normal SQL query statements. The database can be generated from the current and previous log files, which also include compressing the converted log files, similar to access.log.x.gz or Access_log.old.

First, start aSQL from the command line:

# aSQL

You will enter the aSQL built-in shell interface.

Enter the commands that the Help list can perform:

First load all the access logs in aSQL:

For example, under Debian:

aSQL > load/var/log/apache2/access.*

Under the Centos/rhel:

aSQL > load/var/log/httpd/access_log*

When aSQL completes loading the access log, we can start the database query. Note that the database generated after loading is "temporary" (temporary), meaning that the database will be cleared when you exit aSQL. If you want to keep the database, you must first save it as a file. We'll show you how to do this later (see Example 3 and 4).

The resulting database has a table named logs. Enter the following command to list the fields provided in the logs table:

A hidden file named. aSQL, saved in the user's home directory, records the command history that the user entered in the aSQL shell. So you can use the arrow keys to navigate through the command history and press ENTER to repeat the previous command.

Example SQL query on aSQL

Here are a few examples of using aSQL to run SQL queries against Apache log files:

Example 1: Lists the requested source/time and HTTP status code in October 2014.

View Source print? SELECT  source,  date , status  FROM  logs  WHERE  date  >=  ‘2014-10-01T00:00:00‘  ORDER  BY  source;

Example 2: Small to large displays the request size (bytes) processed by a single client.

View Source print? SELECT  source,  SUM ( size ),  AS  NUMBER  FROM  logs  GROUP  BY  source  ORDER  BY  Number  DESC ;

Example 3: Save the database as [filename] in the current directory.

Save [FileName]

This avoids the processing time used by the load command to parse the log.

Example 4: Load the database after re-entering aSQL.

restore [filename]

Example 5: Returns the error condition recorded in the access log. In this example, we will show all requests that return an HTTP status code of 403 (Access Forbidden).

View Source print? SELECT  source,  date , status, request  FROM  logs  WHERE  status= ‘403‘  ORDER  BY  date

This example wants to show that although aSQL only parses the access log, we can still display requests that have an error condition by using the requested State field.

Summary:

We experienced how aSQL helped us analyze Apache log files and output them in a friendly format. While you can achieve similar functionality by using command-line tools such as the one between cat and GREP,UNIQ,SORT,WC, this asql shows the powerful features of the Swiss Army knife, allowing us to use standard SQL Query statements to filter the log.

Transferred from: http://www.php100.com/html/it/mobile/2014/1216/8065.html

How to query Apache logs with SQL statements on Linux