Re: can apache log to MySQL ?

[Craig Sanders <cas@taz.net.au>]

On Thu, May 11, 2000 at 07:23:45PM +0800, Chad A. Adlawan wrote:
> > another way to do it is to write a small perl (or whatever) program
> > which reads apache log entries on stdin and injects them into a database
> > - should be less than 20 lines of perl using DBI.
> 
>    but the problem is, i cant code in PERL :-(
> 
>    any good souls out there who have the free time to write that 20
>    lines of code ? :-)


here's a first stab at it. note, it's completely untested and might even
work. i've used exactly the same database table as in Apache::DBILogger,
so anything that works with that should also work with this.


add the following to httpd.conf:

# use :*: as field delimter because it's extremely unlikely to appear in a URL
# or Referrer or User-Agent.  send time in ISO format.
LogFormat "%v:*:%b:*:%u:*:%f:*:%h:*:%a:*:%s:*:%{%Y-%m-%d %H:%M:%S+%z}t:*:%{Content-type:}o:*:%U:*:%{Referer}i:*:%{User-Agent}i:*:%{cookie}n" dbilog
CustomLog "|/usr/local/bin/dbi-logger.pl" dbilog


create /usr/local/bin/dbilogger.pl:

---cut here---
#! /usr/bin/perl

# dbilogger.pl
# Copyright Craig Sanders <cas@taz.net.au> 2000
# This script is licensed under the terms of the GNU GPL.
#
# Revision History:
# 2000-05-11:  first version
#
# TODO:
# needs error checking.

use DBI;

# configuration variables
$dbhost='localhost';
$dbname='DATABASE';
$dbuser='USER';
$dbpass='PASSWORD';

$table='requests';

# main program
$dsn = "DBI:Pg:dbname=$dbname;host=$dbhost";

$dbh = DBI->connect($dsn, $dbuser, $dbpass) || 
	die "database error: $DBI::errstr" ;

$sth = $dbh->prepare("INSERT INTO $table
                      VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)");

while (<>) {
	@line = split /:*:/ ;

	# might need some extra code in here to clean up the data before
	# inserting it into the database.  or maybe not.

	$sth->execute(@line) ;
}
---cut here---


create the table and index in postgres:

CREATE TABLE requests (
  server varchar(127) DEFAULT '',
  bytes mediumint(9) DEFAULT '0',
  user varchar(15) DEFAULT '',
  filename varchar(200) DEFAULT '',
  remotehost varchar(150) DEFAULT '',
  remoteip varchar(15) DEFAULT '',
  status smallint(6) DEFAULT '0',
  timeserved datetime DEFAULT now(),
  contenttype varchar(50) DEFAULT '',
  urlpath varchar(200) DEFAULT '',
  referer varchar(250) DEFAULT '',
  useragent varchar(250) DEFAULT '',
  usertrack varchar(100) DEFAULT '',
);
CREATE INDEX server_idx ON requests (server);
CREATE INDEX timeserved_idx ON requests (timeserved);

you'll also need to set up a username and password, and create a
database for the table to go in. and modify the script above to use
them.


> PS. i recently found out that you can actually use mysql itself to
> read a datafile and inject it to some table. that can be done by:
>
> LOAD DATA INFILE '/var/log/apache/access.log' INTO TABLE table_name
> FIELDS TERMINATED BY ' ' OPTIONALLY ENCLOSED BY '"';
>
> since apache logs "date/time" (%t in apaches LogFormat) inside an "["
> and an "]", i replaced all "[]"'s w/ an '"' ... everything seemed to
> have worked very well and very smoothly until i found out that some
> User-Agent logs contains something like ""Mozilla/4.6 "en" (Win95;
> I)"" ... the "en" thingy confused that MySQL directive :-( ... any
> inputs anyone ???

convert the extra " characters to \" *before* you convert [ and ] to ". or
convert them to single quotes.

i.e.   s/"/\\"/g
or     s/"/'/g

craig

--
craig sanders