SYNOPSIS:

I wrote this tool to get loadtimes over the course of a day from apachelogs into a SQL database for reporting, graphing and further analysis.

Uses awk, and mysqlimport for maximum performance
performance: 600.000 lines with stats on 28 separate urls imported in 40 seconds on 1 cpu virtual machine (X5590)
tested on FreeBSD 6.4 and FreeBSD 8.0

REQUIREMENTS:

httpd.conf

LogFormat "%t %v %h %{JSESSIONID}C %{SSL_PROTOCOL}x %{SSL_CIPHER}x \"%r\" %>s %D %b \"%{Referer}i\" \"%{User-Agent}i\"" timelog

mysql

CREATE TABLE IF NOT EXISTS tablename ( datetime datetime NOT NULL, hostname varchar(32) NOT NULL, url varchar(200) NOT NULL, avgsize float NOT NULL, avgtime float NOT NULL, hits int(11) NOT NULL, PRIMARY KEY (datetime,hostname,url) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

SYNTAX:

$path/apache2mysql.sh [debug] <listfile> <logfile> [logfile]

 debug: specify for verbose output
 listfile: list of urls to monitor see example here
 logfile: path to apachelog

SQL EXAMPLES:

get statistics averaged by day instead of hour.

SELECT DATE(datetime) as date,url,avg(avgsize) as avgsize,avg(avgtime) as avgtime, sum(hits) as hits FROM front group by date,url;

Latest version 1.0 apache2mysql.sh
Changelog:

1.0:
       o Initial public release