From bdb5c0e5ea921d274b797f3b9b311b713eb0b792 Mon Sep 17 00:00:00 2001 From: Edward Rudd Date: Thu, 12 Feb 2004 03:44:12 +0000 Subject: moved create_tables.sql and make_combined_log.pl into contrib directory. Added contrib directory to build system. Updated new maintainer information in AUTHORS,LICENSE, and manual.xml --- contrib/Makefile.in | 2 +- contrib/create_tables.sql | 51 +++++++++++++++++ contrib/make_combined_log.pl | 131 +++++++++++++++++++++++++++++++++++++++++++ 3 files changed, 183 insertions(+), 1 deletion(-) create mode 100644 contrib/create_tables.sql create mode 100644 contrib/make_combined_log.pl (limited to 'contrib') diff --git a/contrib/Makefile.in b/contrib/Makefile.in index c40a711..7a020d9 100644 --- a/contrib/Makefile.in +++ b/contrib/Makefile.in @@ -3,7 +3,7 @@ # Modify these top variables. SUBDIRS = -EXTRA_DIST = README mysql_import_combined_log.pl +EXTRA_DIST = README create_tables.sql make_combined_log.pl #Don't modify anything below here diff --git a/contrib/create_tables.sql b/contrib/create_tables.sql new file mode 100644 index 0000000..0fe0f4b --- /dev/null +++ b/contrib/create_tables.sql @@ -0,0 +1,51 @@ +create table access_log ( + id char(19) , + agent varchar(255) , + bytes_sent int unsigned , + child_pid smallint unsigned, + cookie varchar(255), + machine_id varchar(25), + request_file varchar(255), + referer varchar(255) , + remote_host varchar(50) , + remote_logname varchar(50) , + remote_user varchar(50) , + request_duration smallint unsigned , + request_line varchar(255), + request_method varchar(10) , + request_protocol varchar(10) , + request_time char(28), + request_uri varchar(255), + request_args varchar(255), + server_port smallint unsigned, + ssl_cipher varchar(25), + ssl_keysize smallint unsigned, + ssl_maxkeysize smallint unsigned, + status smallint unsigned , + time_stamp int unsigned , + virtual_host varchar(255) +); + +create table notes ( + id char(19), + item varchar(80), + val varchar(80) +); + +create table headers_in ( + id char(19), + item varchar(80), + val varchar(80) +); + +create table headers_out ( + id char(19), + item varchar(80), + val varchar(80) +); + +create table cookies ( + id char(19), + item varchar(80), + val varchar(80) +); diff --git a/contrib/make_combined_log.pl b/contrib/make_combined_log.pl new file mode 100644 index 0000000..00786de --- /dev/null +++ b/contrib/make_combined_log.pl @@ -0,0 +1,131 @@ +#!/usr/bin/perl + +# $Id: make_combined_log.pl,v 1.1 2004/02/12 03:44:12 urkle Exp $ +# +# make_combined_log.pl +# +# Usage: make_combined_log +# +# This perl script extracts the httpd access data from a MySQL database +# and formats it properly for parsing by 3rd-party log analysis tools. +# +# The script is intended to be run out by cron. Its commandline arguments tell +# it how many days' worth of access records to extract, and which virtual_host +# you are interested in (because many people log several virthosts to one MySQL +# db.) This permits you to run it daily, weekly, every 9 days -- whatever you +# decide. +# +# Note: By "days" I mean "chunks of 24 hours prior to the moment this script is +# run." So if you run it at 4:34 p.m. on the 12th, it will go back through 4:34 +# p.m. on the 11th. +# +# Known issues: +# * Because GET and POST are not discriminated in the MySQL log, we'll just +# assume that all requests are GETs. This should have negligible effect +# on any analysis software. This could be remedied IF you stored the full +# HTTP request in your database instead of just the URI, but that's going to +# cost you a LOT of space really quickly... +# +# * Because this is somewhat of a quick hack it doesn't do the most robust +# error checking in the world. Run it by hand to confirm your usage before +# putting it in crontab. + +$| = 1; + +use DBI; + +# Remember, $#ARGV is parameters minus one... +if ($#ARGV != 1) { + die "Incorrect usage, please read the perl source code for correct usage." +} + +$days = $ARGV[0]; +$virthost = $ARGV[1]; + +# +# Set up the proper variables to permit database access +# +$serverName = "your.dbhost.com"; +$serverPort = "3306"; +$serverUser = "someuser"; +$serverPass = "somepass"; +$serverTbl = "acc_log_tbl"; +$serverDb = "apache"; + +# +# Other constants +# +$st_tz = "-0800"; +$dt_tz = "-0700"; + +$now = time(); +$start = $now - (86400 * $days); + +# +# Connect and fetch the records +# +$dbh = DBI->connect("DBI:mysql:database=$serverDb;host=$serverName;port=$serverPort",$serverUser,$serverPass); +if (not $dbh) { + die "Unable to connect to the database. Please check your connection variables. (Bad password? Incorrect perms?)"; +} + +$records = $dbh->prepare("select remote_host,remote_user,request_uri,time_stamp,status,bytes_sent,referer,agent,request_method,request_protocol from `$serverTbl` where virtual_host='$virthost' and time_stamp >= $start order by time_stamp"); +$records->execute; +if (not $records) { + die "No such table or the select returned no records." +} + +#Right +#ariston.netcraft.com - - [14/Nov/2001:05:13:39 -0800] "GET / HTTP/1.0" 200 502 "-" "Mozilla/4.08 [en] (Win98; I)" +#ariston.netcraft.com - - [14/Nov/2001:05:13:39 -0800] "GET / HTTP/1.0" 200 502 "-" "Mozilla/4.08 [en] (Win98; I)" + +#Bad +#ariston.netcraft.com - - [2001-11-14 05:13:39 -0800] "GET / HTTP/1.1" 200 502 "-" "Mozilla/4.08 [en] (Win98; I)" +#ariston.netcraft.com - - [2001-11-14 05:13:39 -0800] "GET / HTTP/1.1" 200 502 "-" "Mozilla/4.08 [en] (Win98; I)" + + +# +# Pull out the data row by row and format it +# +while (@data = $records->fetchrow_array) { + ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime($data[3]); + $year=$year+1900; + + # Create format for leading-zero formatting + if ($mday < 10) { $mday = "0$mday"; } + if ($mon < 10) { $mon = "0$mon"; } + if ($hour < 10) { $hour = "0$hour"; } + if ($min < 10) { $min = "0$min"; } + if ($sec < 10) { $sec = "0$sec"; } + + # Convert numeric month to string month + for ($mon) { + if (/00/) { $mon = "Jan";} + elsif (/01/) { $mon = "Feb";} + elsif (/02/) { $mon = "Mar";} + elsif (/03/) { $mon = "Apr";} + elsif (/04/) { $mon = "May";} + elsif (/05/) { $mon = "Jun";} + elsif (/06/) { $mon = "Jul";} + elsif (/07/) { $mon = "Aug";} + elsif (/08/) { $mon = "Sep";} + elsif (/09/) { $mon = "Oct";} + elsif (/10/) { $mon = "Nov";} + elsif (/11/) { $mon = "Dec";} + } + + # Create the output + print "$data[0] $data[1] - [$mday/$mon/$year:$hour:$min:$sec "; + if ($isdst) { + print "$dt_tz\] "; + } else { + print "$st_tz\] "; + } + print "\"$data[8] $data[2] $data[9]\" $data[4] $data[5] \"$data[6]\" \"$data[7]\"\n"; +} + +# +# Done +# +$records->finish; + -- cgit