How to query the Apache server log using the MySQL statement _oracle

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

This time, we will analyze the Apache access log in an alternative way, and the tool we use is asql. aSQL is an open source tool that allows users to use SQL statements to query logs to show the same information in a more user-friendly format.
Apache Log Background Knowledge

Apache has two kinds of logs:

• Access log: stored in 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 path/var/log/apache2/error.log (Debian) or/var/log/httpd/error_log (Red Hat). The error log logs all errors reported by Apache Web server and the error condition. Error conditions include (not limited to) 403 (forbidden, which is usually reported when a request is denied access), 404 (not Found, reported when the resource is not present).

Although administrators can customize the level of detail for Apache access log by configuring Apache configuration files, in this article, we use the default configuration, as follows:

Copy Code code as follows:

Remote IP-Request time-Request Type-response code-requested URL-remote browser information (may contain operating system information)

So a typical Apache log entry is the following:

Copy Code code as follows:

192.168.0.101--[22/aug/2014:12:03:36-0300] "get/icons/unknown.gif http/1.1 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 the Apache error log? Because the error log entry mainly records special requests in Access log (you can customize them), you can get more information about the error situation in Access log (example 5 for more details).

In addition, the access log is a system-level log file to be described in advance. To analyze the log files for a virtual host, 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 written by Perl and requires the following two Perl modules: the DBI drive of SQLite and the GNU readline.
install aSQL on Debian, Ubuntu, and its derivative distributions

The use of aptitude,asql based on the Debian distribution and its dependencies are automatically installed.

  # Aptitude Install aSQL

Install 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?

From the dependencies in the above code you can see that asql transforms the unstructured plaintext of the Apache log into structured SQLite database information. The generated SQLite database can accept normal SQL query statements. The database can be generated from current and previous log files, which also include compression of 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 interaction interface.

Enter the commands that can be executed by the Help list:

First load all access logs in aSQL:

Copy Code code as follows:

aSQL > Load <apache-access-logs path >

For example, under Debian:

Copy Code code as follows:

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

Under the Centos/rhel:

Copy Code code as follows:

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 the aSQL. If you want to keep the database, you must first save it as a file. We'll explain how to do that later (refer to Example 3 and 4).

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

A hidden file named. asql, stored in the user's home directory, to record the command history entered by the user 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.
sample SQL query on aSQL

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

Example 1: lists the source/time and HTTP status codes requested in October 2014.

  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.

 SELECT source, SUM (size), as number from logs GROUP by source DESC;

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

Copy Code code as follows:

Save [FileName]

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

Example 4: load the database after reentry into aSQL.

Copy Code code as follows:

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).

  SELECT source, date, status, request from logs WHERE status= ' 403 ' ORDER by date

This example wants to show that although aSQL only analyzes access logs, we can display requests with error conditions by using the requested State field.
Summary:

We experienced how ASQL can help us analyze Apache log files and output the results in a friendly format. While you can achieve similar functionality by using command-line tools such as the pipe between cat and GREP,UNIQ,SORT,WC, aSQL demonstrates its powerful function as a Swiss Army knife, enabling us to pass standard SQL in our own needs Query statement to filter the log.

I hope this tutorial will help you.