[squid-users] Re: Squid + MySQL ?

>Maxime Gaudreault ha scritto:
>> Hi
>>
>> I'm looking for a solution to save bandwidth and bandwidth saving into a MySQL database to display some stats on a web page.
>>

I just parse the logs real-time and insert them into mysql. The below can run as a background process, will tail the log
and automatically insert every log entry into a DB in real time. It is aware of log rotations and the like, so it should
never need to be restarted (touch wood).

Once the data is in the DB, it's a simple matter of some simple queries...

-- SNIP --
#!/usr/bin/perl
###############################################################################
### Squid Log Traffic Accounting ###
### (c)2005 Chris Knipe <savage_at_savage.za.org> ###
###############################################################################
### Version 1.1a: ###
### 2005-05-02 - Initial Coding began. ###
###############################################################################
use File::Tail;
use Mysql;
use strict;
use warnings;

###############################################################################
### Constants & Variables ###
###############################################################################
use constant DBHost => "dbhost";
use constant DBName => "dbname";
use constant DBUser => "dbuser";
use constant DBPass => "dbpass";
use constant LogFile => "/var/log/squid/access.log";

###############################################################################
### Code Starts ###
###############################################################################
my ($File, $Line) = undef;
$File = File::Tail->new(name=>LogFile, maxinterval=>5, interval =>1, adjustafter=>7);
while (defined($Line = $File->read)) {
   my $GlobalDB = Mysql->connect(DBHost, DBName, DBUser, DBPass);
   $GlobalDB->{'GlobalDB'}->{'PrintError'} = 0;
   my ($When, $ElapseTime, $ClientAddress, $HTTPCode, $Size, $Method, $URL, $Ident, $HierarchyData , $ContentType) = split (/\s+/, $Line);
   my ($Timestamp, $null) = split(/\./, $When);
   my $SQL = $GlobalDB->query("SELECT EntryID FROM PrePaidSquidLogs WHERE Timestamp=" . $GlobalDB->quote($Timestamp) . " AND ClientAddress=" . $GlobalDB->quote($ClientAddress) . " AND Size=" . $GlobalDB->quote($Size) . " AND URL="
. $GlobalDB->quote($URL) . " AND Ident=" . $GlobalDB->quote($Ident));
   if ($SQL->numrows != 1) {
     $GlobalDB->query("INSERT DELAYED INTO SquidLogs (Timestamp, ElapseTime, ClientAddress, HTTPCode, Size, Method, URL, Ident, HierarchyData, ContentType) VALUES (" . $GlobalDB->quote($Timestamp) . ", " .
$GlobalDB->quote($ElapseTime) . ", " . $GlobalDB->quote($ClientAddress) . ", " . $GlobalDB->quote($HTTPCode) . ", " . $GlobalDB->quote($Size) . "," . $GlobalDB->quote($Method) . ", " . $GlobalDB->quote($URL) . ", " .
$GlobalDB->quote($Ident) . ", " . $GlobalDB->quote($HierarchyData) . ", " . $GlobalDB->quote($ContentType) . ")");
   }
}

-- SNIP --

--
Chris.