RE:

["Jeremy L. Gaddis" <jeremy@gaddis.org>]

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     <jeremy@gaddis.org>


#!/usr/bin/perl
#
# $Id: log2sql.pl, v1.0 2002/01/26 07:25:47 jeremy Exp $
#
# Author: Jeremy Gaddis <jeremy@gaddis.org>
#
# 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:andreas.rabus@entity38.de]
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

    Andreas.Rabus@entity38.de
    www.entity38.de



--
To UNSUBSCRIBE, email to debian-isp-request@lists.debian.org
with a subject of "unsubscribe". Trouble? Contact
listmaster@lists.debian.org