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.
