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

Thanks for the script...I was planning to write one to..but glad I 
didn't reinvent the wheel.

Cheers,
Vijay

Francesc Guasch wrote:

> Jin Zhao wrote:
>
>> Hi folks,
>>
>> Just played with mod_log_sql for a couple of hours and I'll say it's 
>> fantastic!!!
>>
>> Before introducing the module onto the production server, I want to 
>> do some analysis on the current logs. I havenot found any tool inside 
>> the downloaded mod_log_sql package to import current apache logs into 
>> the 
>
>
> I've been cleaning mine for a while, here it is.
>
>------------------------------------------------------------------------
>
>#!/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;
>
>  
>

__________________________________________________________________
Reminder: to unsubscribe, send email to <[email protected]>
with the words "unsubscribe mod_log_sql" in the body (w/o quotes).
The module homepage is http://www.grubbybaby.com/mod_log_sql/