Differences

This shows you the differences between two versions of the page.

Link to this comparison view

apache2mysql [2012/10/17 00:05] (current)
Line 1: Line 1:
 +======= Insert apache log summaries into mysql database =======
 +===== 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)
 +
 +===== Compability =====
 +Tested on FreeBSD 6.4 and FreeBSD 8.0.
 +
 +===== Requirements =====
 +
 +==== Apache ====
 +
 +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.
 +
 +<file txt httpd.conf>​
 +LogFormat "%t %v %h %{JSESSIONID}C %{SSL_PROTOCOL}x %{SSL_CIPHER}x \"​%r\"​ %>s %D %b \"​%{Referer}i\"​ \"​%{User-Agent}i\""​ timelog
 +</​file>​
 +
 +==== MySQL ====
 +
 +Requires a destination mysql table
 +<file sql create_database.sql>​
 +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;​
 +</​file>​
 +
 +===== Syntax =====
 +
 +<​code>​
 +$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
 +</​code>​
 +
 +===== SQL Examples =====
 +
 +get statistics averaged by day instead of hour.
 +
 +<file sql dayavg.sql>​
 +SELECT DATE(datetime) as date,​url,​avg(avgsize) as avgsize,​avg(avgtime) as avgtime, sum(hits) as hits FROM front group by date,url;
 +</​file>​
 +
 +
 +===== Download =====
 +
 +Latest version 1.0 {{files:​apache2mysql:​apache2mysql.sh}}
 +
 +
 +===== Changelog =====
 +=== 1.0 ===
 +       o Initial public release