Hello Vishal,
Here is the script I use to dump to a MySql database. The syntax
should be quite similar for PostgreSQL. This is a PHP hack of the squidalyser
PERL script. I had to stop using squidalyser because it would not communicate
with MySql 4.1.X. I set this as an hourly cron job. Obviously you must
coordinate this with your log rotation scheme. Hope this helps you out.
<?php
$s1 = time();
$cn = mysqli_connect("localhost","sqluser","sqlpassword","squidalyser");
If (!$cn) {
printf("Connect failed: %s\n",mysqli_connect_errno());
exit();
}
$result = mysqli_query($cn,"SELECT MAX(time) FROM logfile");
$row = mysqli_fetch_row($result);
$mt = $row[0];
mysqli_free_result($result);
$my = substr($mt,0,4);
$mM = substr($mt,5,2);
$md = substr($mt,8,2);
$mh = substr($mt,11,2);
$mm = substr($mt,14,2);
$ms = substr($mt,17,2);
$maxtm = mktime($mh,$mm,$ms,$mM,$md,$my);
$f = gzopen("/usr/local/squid/logs/access.log","rb");
$i=0;
$buffer = gzgets($f);
while (!gzeof($f)) {
$buffer = preg_split("/\s+/",gzgets($f));
$remotehost = mysqli_real_escape_string($cn,$buffer[2]);
$rfc931 = mysqli_real_escape_string($cn,$buffer[7]);
$authuser = mysqli_real_escape_string($cn,$buffer[7]);
$request = mysqli_real_escape_string($cn,$buffer[6]);
$status = split("/",$buffer[3]);
$bytes = $buffer[4];
$time = $buffer[0];
If ($time>$maxtm) {
$s = "INSERT INTO logfile
(id,remotehost,rfc931,authuser,request,status,bytes,time) ";
$s .=
"VALUES(null,'$remotehost','$rfc931','$authuser','$request',$status[1],$bytes
,FROM_UNIXTIME($time))";
if(mysqli_query($cn,$s) === TRUE) {
} else {
printf("%s - ERROR: %s\n",$i,mysqli_error($cn));
}
$c = count($buffer);
$i++;
}
}
$s = "DELETE FROM logfile WHERE time<(DATE_SUB(NOW(), INTERVAL 91 DAY))";
if(!mysqli_query($cn,$s) === TRUE) {
printf("\nUnable to purge old records\nMySql Error -
%s",mysqli_error($cn));
}
mysqli_close($cn);
gzclose($f);
$s2 = time();
$total = $s2 - $s1;
printf("\nParsed %s lines in %s seconds.\n",$i,$total);
?>
Brian E. Conklin, MCP+I, MCSE
Director of Information Services
Mason General Hospital
http://www.masongeneral.com
-----Original Message-----
From: Vishal Kashyap @ [SaiHertz] [mailto:vishalonlist@xxxxxxxxx]
Sent: Saturday, February 19, 2005 11:09 AM
To: squid-users@xxxxxxxxxxxxxxx
Subject: [squid-users] Logging into PostgreSQL
Dear all,
This question has been asked a lot but their stants no answer to it in the
archives,
Is their a way by which I can log the user usage of squid into a PostgreSQL
server, Please do reply with any pointers and suggestions
--
With Best Regards,
Vishal Kashyap.
http://vishalkashyap.tk
Mason General Hospital
901 Mt. View Drive
PO Box 1668
Shelton, WA 98584
http://www.masongeneral.com
(360) 426-1611
This message is intended for the sole use of the individual and entity
to whom it is addressed and may contain information that is privileged,
confidential and exempt from disclosure under applicable law. If you
are not the addressee nor authorized to receive for the addressee, you
are hereby notified that you may not use, copy, disclose or distribute
to anyone this message or any information contained in the message. If
you have received this message in error, please immediately notify the
sender and delete the message.
Thank you.
