Proxy logging to mysql

RouterOS General
1

Does anyone have any reports on the best plugin / method for logging squid usage to a mysql server? Ive seen a few perl scripts but always end up with 100cpu utilisation. Doesn’t have to be squid to be honest, just an automated way to get proxy log into mysql in real time.

Many thanks

2

Perl script which can be run in the background. Parses the squid log file in real time and insert the entries into mySQL. Logfile formats and the like in squid needs to be double checked. It’s been a while since I last used this…

-- SNIP --
#!/usr/bin/perl

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 --
3

Thank you Chris, looks spot on.

I’ll test later today.