Importing Apache Access logs

Developed In: bash — Contributed by: Shantanu Oak

Change the apache config file to save the access log in a specified format and then import the text file into database.

Shantanu Oak
bash 
  1. # cat /etc/httpd/conf/httpd.conf | grep LogFormat
  2. #Old Format to be changed to the new format
  3. #LogFormat "%h %l %u %t \"%r\" %>s %b \"%{Referer}i\" \"%{User-Agent}i\"" combined
  4. LogFormat "\"%h\" \"%l\" \"%u\" \"%{%Y-%m-%d %H:%M:%S}t\" \"%r\" \"%>s\" \"%b\" \"%{Referer}i\" \"%{User-Agent}i\"" combined
  5.  
  6. use db_Name;
  7. CREATE TABLE `access_log` (
  8.   `ip` varchar(100) NOT NULL default '',
  9.   `RFC` varchar(100) default NULL,
  10.   `user` varchar(100) default NULL,
  11.   `mytime` datetime default NULL,
  12.   `request` varchar(100) default NULL,
  13.   `response` int(11) NOT NULL default '0',
  14.   `bytes` bigint(20) NOT NULL default '0',
  15.   `referer` varchar(100) default NULL,
  16.   `browser` varchar(500) default NULL,
  17.   UNIQUE KEY `mygroup` (`ip`,`RFC`,`user`,`mytime`,`request`)
  18. );
  19.  
  20. mysqlimport --ignore --fields-terminated-by ' ' --fields-enclosed-by '"' test /var/log/httpd/access_log
  21.  

Current Tags

oksoft 

You must be logged in to tag this tool

On Nov 12, 2009 @ 11:04, rashoodollison said:

The reason he asked the question was that our Apache log file that day was over 10 times the size of the file from the previous day. It sure looked like the server was getting hammered.

affordable degree | online phd degrees

On Nov 12, 2009 @ 11:03, rashoodollison said:

Strata can easily handle the data size, but the format is enough to give any software fits. So, we wrote a quick Apache log parser extension that makes it really simple to just point the software to your Apache log and import it. The resulting table is nicely formatted and everything is ready to go.

university degree online | online degree program | buy degree

On Oct 23, 2009 @ 13:29, Shantanu Oak said:

LOAD DATA INFILE '/var/log/httpd/access_log' IGNORE INTO TABLE access_log FIELDS TERMINATED BY ' ' ENCLOSED BY '"'

# The load data infile statement that is generated automatically.

Votes

Not yet rated.
You must be logged in to vote.

Watches

1 members are watching this tool
You must be logged in to track this tool.

Provide Feedback

Please note:
HTML will be purified, but we allow for a number of HTML tags so that you have the flexibility to decorate your comment text to some extent. The comments allow the following HTML tags:

strong, b, em, blockquote, a, code, pre

To put code into your comment, simply encapsulate your code with
[code language="XXX"][/code], where XXX is any common language, for instance "PHP", "SQL", "C", etc.

You must be logged in to comment