RE: Re: Squid + MySQL ?

["Maxime Gaudreault" <mgaudreault@xxxxxxxxxxxxxxx>]

Cool that looks good

Can you give your MySQL DB schema ?

-----Original Message-----
From: Chris Knipe [mailto:savage@xxxxxxxxxxxxx] 
Sent: July-30-09 11:32 AM
To: squid-users@xxxxxxxxxxxxxxx
Subject:  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@xxxxxxxxxxxxx>
###
########################################################################
#######
### 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.