Re: Any script/tool to import apache logs from files into mysql?

#!/usr/bin/perl -w

=pod

    insert_access_log.pl 
		
		CopyRight 2003, Francesc Guasch - Ortiz

    This program is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation; either version 2 of the License, or
    (at your option) any later version.

    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.

    GPL License: http://www.fsf.org/licenses/gpl.txt


=cut

use strict;
use Carp;
use Date::Parse;
use DBI;
use Getopt::Long;
use POSIX qw(strftime);

###########################################################################
# 
#  GLOBALS

my $DEBUG=0;
my $LOG_FILE="/var/log/httpd/access_log";
my $TABLE="access_log";
my $HOSTNAME;
my $DATABASE= 'apachelogs';
my $SHOW_PROGRESS=1;
my $LAST_TIME=0;

my $USAGE = $0;
$USAGE =~ s#.*/##;
$USAGE.=" --hostname=DB_SERVER [--database=$DATABASE] [--table=$TABLE] [--log-file=$LOG_FILE] [--show-progress=$SHOW_PROGRESS] [--debug] [--help]";


{
	my $help;
	GetOptions(
					  'debug' => \$DEBUG,
				 'log-file=s' => \$LOG_FILE,
				    'table=s' => \$TABLE,
				 'database=s' => \$DATABASE,
				 'hostname=s' => \$HOSTNAME,
			'show-progress=s' => \$SHOW_PROGRESS,
			'help' => \$help
	);
	if ($help) {
		print "$USAGE\n";
		exit(0);
	}
}

die "$USAGE\n"
	unless defined $HOSTNAME && defined $DATABASE;

my $DB="DBI:mysql:database=$DATABASE;hostname=$HOSTNAME";
my $dbh;

#Date::Parse->language($LANGUAGE) 
#	or die "I Can't parse dates in language $LANGUAGE\n";

sub fix_date {
	my $datetime = shift;
	confess("Undefined datetime") unless defined $datetime;
	my ($date,$time) = $datetime =~ /(.*?):(.*) .*/;
	return "$date $time";
}

sub show_progress {
	my $date = shift;
	my $time = time;
	return if $time-$LAST_TIME <60;
	$LAST_TIME=time;
	print "$date\n";

}
sub duplicate {
	my ($uri,$host,$time) = @_;
	my $query="SELECT time_stamp FROM $TABLE".
		" WHERE request_uri=$uri AND remote_host=$host ".
		"	AND time_stamp=$time";
	my $sth;
	eval {
		$sth = $dbh->prepare($query);
	};
	warn "$@ $query\n" if $@;
	$sth->execute;
	my ($found) = $sth->fetchrow;
	$sth->finish;
	warn "duplicate @_\n" if $found && $DEBUG>1;
	return ($found or 0);
}
sub match_log {
	my $line= shift;
	die "missing log_line" unless defined $line;
	my @data= $line =~
		/(.*?)				# 0 ip
		\s.*?				# - ident
		\s(.*?)				# 1 user
		\s\[(.*?)\]			# 2 time
		\s"(\w+)?			# 3 request_method
		\s?(.*?)"			# 4 request_uri
		\s(-|\d+)			# 5 status
		\s(-|\d+)			# 6 bytes sent
		\s"(.*?)"			# 7 referer
		\s"(.*?)"			# 8 agent
		/x;
	unless (defined $data[2]) {
		warn "Can't parse $line\n";
		return;
	}
	$data[2] = fix_date($data[2]);
	show_progress($data[2]);
	my $time = str2time($data[2])
		or die "I can't parse $data[2]";
	check_date($data[2],$time);
	$data[2]=$time;
	$data[4] =~ s/\s.*//;

	for (0..$#data) { 
		$data[$_] = '-'  unless defined $data[$_];
		$data[$_] =~ s/\\/\\\\/g;
		$data[$_] =~ s/'/\\'/g;
		$data[$_]="'$data[$_]'" 
			unless $data[$_] =~ /^\d+$/;
	}
#	return if duplicate($data[4],$data[0],$data[2]);
	my $query = "INSERT INTO $TABLE ".
		"(remote_host,remote_user".
		" ,time_stamp,request_method,request_uri,status,bytes_sent".
		" ,referer,agent)".
		" values(".join(",",@data).")\n";
	eval {
		$dbh->do($query);
	};
	warn "$@ $query\n" if $@;
	warn "$line\n$query\n\n" if $DEBUG>1;
}

sub check_date {
	my ($date,$seconds) = @_;
 	my @date = localtime($seconds);
	my ($sec,$min,$hour,$mday,$mon,$year) = @date;
	$year += 1900;
	my $str = strftime("%d/%b/%Y %H:%M:%S",localtime($seconds));
	die "$str\n$date\n" unless uc($str) eq uc($date);
}

sub dump_data {
	my $cont=0;
	open LOG,"<$LOG_FILE" or die "$! $LOG_FILE\n";
	my $line;
	while ($line=<LOG>) {
		match_log($line);
	}
	close LOG;
}

####################################################################

$dbh = DBI->connect($DB,undef,undef,{RaiseError => 1, PrintError => 0})
	or die $DBI::errstr;

dump_data();

$dbh->disconnect;