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)
Requires custom logformat as specified here note: this was created for java, hence the JSESSIONID, if you have a session id as a cookiefield replace JSESSIONID with your cookie field.
LogFormat "%t %v %h %{JSESSIONID}C %{SSL_PROTOCOL}x %{SSL_CIPHER}x \"%r\" %>s %D %b \"%{Referer}i\" \"%{User-Agent}i\"" timelog
Requires a destination mysql table
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;
$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
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
o Initial public release