How to query Apache server logs using MySQL statements: mysqlapache

How to query Apache server logs using MySQL statements: mysqlapache

Linux has a notable feature. Under normal circumstances, you can analyze system logs to understand what is happening in your system or what is happening. Indeed, system logs are the first-hand resource required by the system administrator 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. The tool we use is asql. Asql is an open-source tool that allows users to use SQL statements to query logs and display the same information in a more friendly format.
Apache log Background

Apache has two types of logs:

• Access log: stored in the path/var/log/apache2/access. log (Debian) or/var/log/httpd/access_log (Red Hat ). Access Log records all requests executed by Apache web server.
• Error log: stored in the path/var/log/apache2/error. log (Debian) or/var/log/httpd/error_log (Red Hat ). The Error log records all errors reported by the Apache web server and their situation. Errors include (Not limited to) 403 (Forbidden, usually reported when a request is denied) and 404 (Not found, reported when the requested resource does Not exist ).

Although the administrator 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:
Copy codeThe Code is as follows: remote IP address-request time-request type-response code-request URL-remote browser information (may contain operating system information)

Therefore, a typical Apache log entry looks like this:

Copy codeThe Code is as follows: 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? Because the error log entries mainly record special requests in the access log (you can customize them ), therefore, you can use the access log to obtain more information about the error (example 5 has more details ).

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

Asql is compiled by Perl and requires the following two Perl modules: DBI driver of SQLite and GNU readline.
Install asql On Debian, Ubuntu, and its derivative releases

Aptitude, asql, and their dependencies on the Debian-based release are automatically installed.

  # aptitude install asql

Install asql on Fedora, CentOS, and RHEL

On CentOS or RHEL, You need to enable EPEL repository and then run the following code. Run the following code in Fedora:

  # 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 asql work?

From the dependencies in the code above, you can see that asql converts unstructured plain Apache logs to structured SQLite database information. The generated SQLite database can accept normal SQL query statements. The database can be generated through the current and previous log files, including compressed and converted log files, such as access.log.X.gz or access_log.old.

First, start asql from the command line:

  # asql

You will enter the shell interaction interface built in asql.

Enter the executable commands in the help list:

First, load all access logs in asql:

Copy codeThe Code is as follows: asql> load <apache-access-logs path>

For example, in Debian:

Copy codeThe Code is as follows: asql> load/var/log/apache2/access .*

Under CentOS/RHEL:

Copy codeThe Code is as follows: asql> load/var/log/httpd/access_log *

After asql loads access logs, we can start database query. Note that the database generated after loading is "temporary" (temporary), which means 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 will introduce how to do this later (refer to example 3 and 4 ).

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

A hidden file named. asql is saved in the user's home directory, recording the command history you entered in asql shell. Therefore, you can use the arrow keys to view the command history and press ENTER to repeat the previous command.
Sample SQL query on asql

The following is a few examples of SQL queries using asql for Apache log files:

Example 1:Lists the source/time and HTTP status code of the request in January 1, October 2014.

  SELECT source, date, status FROM logs WHERE date >= '2014-10-01T00:00:00' ORDER BY source;

Example 2:The size of requests processed by a single client (bytes) is displayed from small to large ).

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

Example 3:In the current directory, save the database as [filename].

Copy codeThe Code is as follows: save [filename]

This avoids the processing time occupied by the load command for log syntax analysis.

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

Copy codeThe Code is as follows: restore [filename]

Example 5:Returns the error information recorded in the access log. In this example, all requests that return an HTTP status code of 403 (access forbidden) are displayed.

 

  SELECT source, date, status, request FROM logs WHERE status='403' ORDER BY date

In this example, we want to demonstrate that, although asql only analyzes access logs, we can still use the Request status domain to display requests with errors.
Summary:

We have experienced how asql helps us analyze Apache log files and output the results in a friendly format. Although you can also use command line tools (such as pipelines between cat and grep, uniq, sort and wc) to implement similar functions, in comparison, asql demonstrates its powerful functionality like a Swiss Army knife, allowing us to filter logs with standard SQL query statements as needed.

I hope this tutorial will help you.