6 files changed, 489 insertions, 0 deletions

diff --git a/contrib/.cvsignore b/contrib/.cvsignore
new file mode 100644
index 0000000..f3c7a7c
--- /dev/null
+++ b/contrib/.cvsignore
@@ -0,0 +1 @@
+Makefile
diff --git a/contrib/Makefile.in b/contrib/Makefile.in
new file mode 100644
index 0000000..c2bd203
--- /dev/null
+++ b/contrib/Makefile.in
@@ -0,0 +1,36 @@
+# @configure_input@
+
+# Modify these top variables.
+SUBDIRS = 
+
+EXTRA_DIST = README \
+                create_tables.sql \
+                make_combined_log.pl \
+                mysql_import_combined_log.pl
+
+#Don't modify anything below here
+
+srcdir = @abs_srcdir@
+builddir = @abs_builddir@
+
+STD_DIST = Makefile.in
+
+DISTFILES = $(STD_DIST) $(EXTRA_DIST)
+
+all:
+
+install:
+
+update:
+
+clean:
+
+distclean: clean
+        $(RM) Makefile
+
+local-dist: $(DISTFILES)
+        mkdir -p $(DESTDIR)
+        cp -dp --parents $(DISTFILES) $(DESTDIR)
+
+.PHONY: include all-subdirs update-subdirs install-subdirs \
+        clean-subdirs distclean-subdirs dist
diff --git a/contrib/README b/contrib/README
new file mode 100644
index 0000000..bf0e65f
--- /dev/null
+++ b/contrib/README
@@ -0,0 +1,38 @@
+This directory contains contributed scripts/programs/utilites for mod_log_sql.
+
+* create_tables.sql
+
+This is the create table SQL commands to create the access, headers_in, 
+headers_out, cookies, and notes tables in the MySQL database.
+Use it like this.
+mysql -u user -h host -p apachelogdatabase < create_tables.sql
+Where:
+        user is the username to log in as,
+        host is the hostname the server is on, 
+        apachelogdatabase is the database to put the tables into
+        -p will have mysql ask you for a password for the user
+
+*make_combined_log.pl
+
+This perl script will extract the data from mod_log_sql's tables in the 
+database and export a standard Apache combined log file. Use this to run
+logs through a program like webalizer.
+
+You must edit the perl script to configure variables before you run it.
+Usage:
+./make_combined_log.pl days virtualhost
+Where:
+        days is the number of days to fetch (starting from now and going back 
+                in time)
+        virtualhost is the name of the virtualhost to retrieve
+
+Example:
+        ./make_combined_log.pl 2 example.com
+
+*mysql_import_combined_log.pl
+
+This is a perl script written by Aaron Jenson that imports a combined log file
+from apache into a SQL database table.. You can use this script to import logs
+from a webserver you are converting over from the standard Apache log system to
+mod_log_sql.  A Usage statement can be fetch by running the program with no 
+parameters or with --help or -?.
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..2c55271
--- /dev/null
+++ b/contrib/make_combined_log.pl
@@ -0,0 +1,139 @@
+#!/usr/bin/perl
+
+# $Id: make_combined_log.pl,v 1.2 2004/02/12 23:32:55 urkle Exp $
+#
+# make_combined_log.pl
+#
+# Usage: make_combined_log <days> <virtual host>
+#
+# 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;
+
+# Remove the # in front of this line when you have 
+# edited the variables below.
+#$has_edited_source = 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";
+
+if (!defined($has_edited_source)) {
+        print "Please edit this file and configure it first.\n";
+        print "This program is $0\n";
+        exit 1;
+}
+# Remember, $#ARGV is parameters minus one...
+if ($#ARGV != 1) {
+        print "Usage $0 days virtualhost\n";
+        exit 1;
+}
+
+$days     = $ARGV[0];
+$virthost = $ARGV[1];
+
+#
+# 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;
diff --git a/contrib/mysql_import_combined_log.pl b/contrib/mysql_import_combined_log.pl
new file mode 100644
index 0000000..bffec20
--- /dev/null
+++ b/contrib/mysql_import_combined_log.pl
@@ -0,0 +1,224 @@
+#!/usr/bin/perl -w
+# $Id: mysql_import_combined_log.pl,v 1.4 2004/02/21 18:09:50 urkle Exp $
+# Written by Aaron Jenson.
+# Original source: http://www.visualprose.com/software.php
+# Updated to work under Perl 5.6.1 by Edward Rudd
+use strict;
+use Getopt::Long qw(:config bundling);
+use DBI;
+use Date::Parse;
+
+my %options = ();
+my $i = 0;
+my $sql = '';
+my $valuesSql = '';
+my $line = '';
+my $dbh = 0;
+my $sth = 0;
+my @parts = ();
+my $part;
+my $TIMESTAMP = 3;
+my $REQUEST_LINE = 4;
+my @cols = (
+        'remote_host',                  ## 0
+        'remote_logname',               ## 1
+        'remote_user',                  ## 2
+        'request_time',                 ## 3.string
+        'time_stamp',                   ## 3.posix
+        'request_line',                 ## 5
+        'request_method',               ## 6
+        'request_uri',                  ## 7
+        'request_args',                 ## 8
+        'request_protocol',             ## 9
+        'status',                               ## 10
+        'bytes_sent',                   ## 11
+        'referer',                              ## 12
+        'agent'                                 ## 13
+);
+my $col = '';
+
+GetOptions (\%options,
+                "version" => sub { VERSION_MESSAGE(); exit 0; },
+                "help|?" => sub { HELP_MESSAGE(); exit 0; },
+                "host|h=s",
+                "database|d=s",
+                "table|t=s",
+                "username|u=s",
+                "password|p=s",
+                "logfile|f=s");
+
+$options{host} ||= 'localhost';
+$options{database} ||= '';
+$options{username} ||= '';
+$options{password} ||= '';
+$options{logfile} ||= '';
+
+if( ! $options{database} )
+{
+        HELP_MESSAGE();
+        print "Must supply a database to connect to.\n";
+        exit 1;
+}
+
+if( ! $options{table} )
+{
+        HELP_MESSAGE();
+        print "Must supply table name.\n";
+        exit 1;
+}
+
+if( $options{logfile} )
+{
+        if( ! -e $options{logfile} )
+        {
+                print  "File '$options{logfile}' doesn't exist.\n";
+                exit 1;
+        }
+        open(STDIN, "<$options{logfile}") || die "Can't open $options{logfile} for reading.";
+}
+
+$dbh = Connect();
+if (! $dbh) {
+        exit 1;
+}
+
+$sql = "INSERT INTO $options{table} (";
+foreach $col (@cols)
+{
+        $sql .= "$col," if( $col );
+}
+chop($sql);
+$sql .= ') VALUES (';
+my ($linecount,$insertcount) = (0,0);
+while($line = <STDIN>)
+{
+        $linecount++;
+        @parts = SplitLogLine( $line );
+        next if( $parts[$TIMESTAMP+1] == 0 );
+        $valuesSql = '';
+        for( $i = 0; $i < @cols; ++$i )
+        {
+                $parts[$i] =~ s/\\/\\\\/g;
+                $parts[$i] =~ s/'/\\'/g;
+                $valuesSql .= "'$parts[$i]'," if( $cols[$i] );
+        }
+        chop($valuesSql);
+
+        $sth  = $dbh->prepare("$sql$valuesSql)");
+        if( ! $sth->execute() )
+        {
+                print "Unable to perform specified query.\n$sql$valuesSql\n" . $sth->errstr() . "\n";
+        } else {
+                $insertcount++;
+        }
+        $sth->finish();
+}
+print "Parsed $linecount Log lines\n";
+print "Inserted $insertcount records\n";
+print "to table '$options{table}' in database '$options{database}' on '$options{host}'\n";
+
+# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 
+# Connects to a MySQL database and returns the connection.
+# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 
+sub Connect
+{
+        my $dsn = "DBI:mysql:$options{database};hostname=$options{host}";
+        return DBI->connect( $dsn, $options{username}, $options{password} );
+}
+
+
+# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 
+# Splits up a log line into its parts.
+# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 
+sub SplitLogLine
+{
+        my $line = shift;
+        my $i = 0;
+        my $inQuote = 0;
+        my $char = '';
+        my $part = '';
+        my @parts = ();
+        my $count = 0;
+        chomp($line);
+        for( $i = 0; $i < length($line); ++$i )
+        {
+                $char = substr($line, $i, 1);
+                if( $char eq ' ' && ! $inQuote )
+                {
+                        ## print "Found part $part.\n";
+                        if( $count == $TIMESTAMP )
+                        {
+                                push(@parts, "[".$part."]");
+                                $part = str2time($part);
+                        }
+                        push(@parts, $part);
+                        if( $count == $REQUEST_LINE )
+                        {
+                                my @request = split(/[ ?]/, $part);
+                                push(@parts, $request[0]);
+                                push(@parts, $request[1]);
+                                if( $request[3] )
+                                {
+                                        push(@parts, $request[2]);
+                                        push(@parts, $request[3]);
+                                }
+                                else
+                                {
+                                        push(@parts, '');
+                                        push(@parts, $request[2]);
+                                }
+                                $count += 5;
+                        }
+                        else
+                        {
+                                ++$count;
+                        }
+                        $part = '';
+                }
+                elsif( $char eq '"' || $char eq '[' || $char eq ']' )
+                {
+                        $inQuote = !$inQuote;
+                }
+                else
+                {
+                        $part .= $char;
+                }
+        }
+        push(@parts,$part) if $part;
+
+        return @parts;
+}
+
+
+# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 
+# Prints the usage/help message for this program.
+# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 
+sub HELP_MESSAGE
+{
+        print<<EOF;
+Imports an Apache combined log into a MySQL database.
+Usage: mysql_import_combined_log.pl -d <database name> -t <table name> [-h <hostname>] [-u <username>] [-p <password>] [-f <filename]
+ --host|-h <host name>         The host to connect to.  Default is localhost.
+ --database|-d <database name> The database to use.  Required.
+ --username|-u <username>      The user to connect as.
+ --password|-p <password>      The user's password.
+ --table|-t <table name>       The name of the table in which to insert data.
+ --logfile|-f <file name>      The file to read from.  If not given, data is read from stdin.
+ --help|-?                     Print out this help message.
+ --version                     Print out the version of this software.
+EOF
+}
+
+
+
+# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 
+# Prints the version information for this program
+# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 
+sub VERSION_MESSAGE
+{
+        print "mysql_import_combined_log.pl version 1.2\n";
+        print "Version 1.0 Written by Aaron Jenson.\n";
+        print "Update to work with perl 5.6.1 by Edward Rudd\n";
+}
+
+1;