I wrote this in a hurry a few days ago. It's not elegant and actually quite ugly, but it does work. Note that I only keep a few fields in the database (IP address, timestamp, URL requested, the response code, and the size). Modify it to suit your needs.
j. -- Jeremy L. Gaddis <[EMAIL PROTECTED]> #!/usr/bin/perl # # $Id: log2sql.pl, v1.0 2002/01/26 07:25:47 jeremy Exp $ # # Author: Jeremy Gaddis <[EMAIL PROTECTED]> # # Released under the terms of the GNU Public License (GPL). my $version = "1.0"; # General Configuration my $LOGFILE = "access.log"; my $serverName = "localhost"; my $serverPort = "3306"; my $serverUser = "username"; my $serverPass = "password"; my $serverDB = "apache"; # Main stuff use DBI; use DBI::DBD; use Time::ParseDate; $| = 1; #print "log2mysql $version - Jeremy Gaddis <jeremy\@gaddis.org>\n\n"; my $start = localtime; chomp($start); print "[$start] Starting up... done.\n"; my $start = localtime; chomp($start); print "[$start] Connecting to '$serverDB' on $serverName:$serverPort... "; my $dbh = DBI->connect("DBI:mysql:database=$serverDB;host=$serverName;po rt=$serverPort",$serverUser,$serverPass); print "done.\n"; my $start = localtime; chomp($start); print "[$start] Opening log file... "; open(LOGFILE, "<access.log"); print "done.\n"; my $line = ""; my ($totalcount) = 0; my ($id, $host, $ident, $remoteuser, $datetime, $method, $request, $httpversion, $response, $size, $referrer, $agent) = ("", "", "", "", "", "", "", "", "", "", "", ""); my $sql_fmt = "INSERT INTO logs VALUES ( NULL, '%s', '%s', '%s', '%s', %d )"; my $start = localtime; chomp($start); print "[$start] Importing logs... "; while(defined($line = <LOGFILE>)) { chomp($line); $totalcount++; ($host, $ident, $remoteuser, $datetime, $request, $response, $size, $ref errer, $agent) = $line =~ m!(.*?) (.*?) (.*?) \[(.*?)\] "(.*?)" (.*?) (.*?) "(.* ?)" "(.*?)"!gi; $datetime =~ s/-0500//; $datetime =~ s/\//:/g; $request =~ s/(HTTP.*?$)//; chomp($request); if ($size eq '-') { $size = 0; } # do funky date stuff here @datearray = split(/:/, $datetime); $day = @datearray[0]; $month = @datearray[1]; $year = @datearray[2]; $hour = @datearray[3]; $minute = @datearray[4]; $second = @datearray[5]; if ($month eq 'Jan') { $month = 1; } if ($month eq 'Feb') { $month = 2; } if ($month eq 'Mar') { $month = 3; } if ($month eq 'Apr') { $month = 4; } if ($month eq 'May') { $month = 5; } if ($month eq 'Jun') { $month = 6; } if ($month eq 'Jul') { $month = 7; } if ($month eq 'Aug') { $month = 8; } if ($month eq 'Sep') { $month = 9; } if ($month eq 'Oct') { $month = 10; } if ($month eq 'Nov') { $month = 11; } if ($month eq 'Dec') { $month = 12; } $newdate = "$year-$month-$day $hour:$minute:$second"; $sql = sprintf($sql_fmt, $host, $newdate, $request, $response, $size); $dbh->do($sql) or die "*** ERROR ***"; } print "done.\n"; my $start = localtime; chomp($start); print "[$start] Closing log files... "; close(LOGFILE); print "done.\n"; my $start = localtime; chomp($start); print "[$start] Disconnecting from database... "; $dbh->disconnect; print "done.\n"; print "\nTotal records imported: $totalcount\n\n"; -----Original Message----- From: Andreas Rabus [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 29, 2002 7:32 AM To: Debian ISP List (E-Mail) Subject: Hi, i'm looking for a web-log analyzer for potato and multiple virtual hosts. webalizer keeps breaking (didn't create stats for some days, and then starts again...), analog is ugly, ... and all need plain text log. I'd like to put the log in a database (mysql, postgresql or s.th.) and run some more sophisticated stats opver that data, like url of waht regexp are visited how often, form where, etc. I found s.th calle "Le Visitéur" (URL not handy at the moment...) some time ago, but that's it. Has anybody else found a program that can do this for me? thanks, ar Andreas Rabus entity38 AG Theresienstraße 29 80333 München Tel +49 (89) 286772-27 Fax +49 (89) 286772-21 ISDN +49 (89) 286772-30 ICQ #132675697 [EMAIL PROTECTED] www.entity38.de -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED] -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED]