Может кому надо ... (дополнение)
Доброго времени суток comp,
Если кому-то надо считать трафик национальный и зарубежный ....
Сори забыл самое главное
"Промышленно" эксплуатирую ~2месяца
Использовались материалы с http://www.uvsw.narod.ru/ (aka vasilisc)
File: squid2mysql
#!/usr/bin/perl
use Net::IPv4Addr qw(:all);
use DBI;
use DBI::DBD;
$mysqluser="имя пользователя"; # MySQL user name
$mysqlpass="пароль пользоватеся"; # MySQL user password
$mysqlserv="localhost"; # MySQL server
$mysqlport="3306"; # MySQL server port
$mysqldbas="kan_squid"; # MySQL database name
$backuplog="/var/log/squid/backup.log"; # Backup log if couldnt connect to MySQL
$dbh=DBI->connect("DBI:mysql:database=$mysqldbas;host=$mysqlserv;port=$mysqlport",$mysqluser,$mysqlpass,{AutoCommit=>1,RaiseError=>0,PrintError=>1})||&errorcon;
$sth=$dbh->prepare("INSERT INTO protocol(datework,time,code,status,bytes,url,userident,host,UA)
$sth=$dbh->VALUES(?,?,?,?,?,?,?,?,?)");
open(FBLOGRET,$backuplog);
close(FBLOGRET);
# Get ALL IP-Address from kan_squid.ipbase
my (@UALIST) = ();
$res=$dbh->prepare("select ipaddr from ipbase");
while (my $tmp_ip = $res->fetchrow_array()){
push(@UALIST, $tmp_ip);
}
while(<>) {
@lines=split(' ');
$lines[0]=~tr/./ /;
@_timestamp=split(' ',$lines[0]);
$cts=$_timestamp[0];
($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = gmtime($cts);
$year=$year+1900;
$mon=$mon+1;
$hour=$hour+2;
if ($hour>=24) {
$hour=$hour-24;
$mday=$mday+1;
}
$cdate="$year-$mon-$mday";
$ctime="$hour:$min:$sec";
$remotehost=$lines[2];
@codestatus=split('/',$lines[3]);
if ($codestatus[0] eq "TCP_HIT") { $_code=0; }
if ($codestatus[0] eq "TCP_MISS") { $_code=1; }
if ($codestatus[0] eq "TCP_REFRESH_HIT") { $_code=2; }
if ($codestatus[0] eq "TCP_REF_FAIL_HIT") { $_code=3; }
if ($codestatus[0] eq "TCP_REFRESH_MISS") { $_code=4; }
if ($codestatus[0] eq "TCP_CLIENT_REFRESH_MISS") { $_code=5; }
if ($codestatus[0] eq "TCP_IMS_HIT") { $_code=6; }
if ($codestatus[0] eq "TCP_SWAPFILE_MISS") { $_code=7; }
if ($codestatus[0] eq "TCP_NEGATIVE_HIT") { $_code=8; }
if ($codestatus[0] eq "TCP_MEM_HIT") { $_code=9; }
if ($codestatus[0] eq "TCP_DENIED") { $_code=10; }
if ($codestatus[0] eq "TCP_OFFLINE_HIT") { $_code=11; }
if ($codestatus[0] eq "UDP_HIT") { $_code=12; }
if ($codestatus[0] eq "UDP_MISS") { $_code=13; }
if ($codestatus[0] eq "UDP_DENIED") { $_code=14; }
if ($codestatus[0] eq "UDP_INVALID") { $_code=15; }
if ($codestatus[0] eq "UDP_MISS_NOFETCH") { $_code=16; }
if ($codestatus[0] eq "NONE") { $_code=17; }
$_status=$codestatus[1];
$objectsize=$lines[4];
$URLlink=$lines[6];
$username=$lines[7];
@peerstatus_host=split('/',$lines[8]);
$peerstatus=$peerstatus_host[0];
$peerhost=$peerstatus_host[1];
if (($_code ne "10")&& #TCP_DENIED
($username ne "-")&& #not register user
($_status ne "404")&& #Not Found
($_status ne "400")&& #Bad Request
($peerhost ne "-")) #uknown host
{
if ( !exists($iplist{$peerhost}) ){
$flag=0;
for (my $i=0; $i <= $#UALIST; $i++)
{
$islocal=ipv4_in_network($UALIST[$i],$peerhost);
if ( $islocal == 1) { $flag = 1 ;last; }
}
$iplist{$peerhost} = $flag;
}
else
{
$flag = $iplist{$peerhost};
}
$sth->execute($cdate,
$ctime,
$_code,
$_status,
$objectsize,
$URLlink,
$username,
$peerhost,
$flag ) || die "cannot transfer data";
$aff="SELECT * FROM download WHERE user='$username' AND dt='$cdate' and
isua=$flag";
$rows_affected=$dbh->do($aff);
if ($rows_affected < 1) {
$stn=$dbh->prepare("INSERT INTO download(user,dt,size,isua) VALUES
(?,?,?,?)");
$stn->execute($username,$cdate,0,$flag);
}
$aff="UPDATE download SET size=size+$objectsize WHERE user='$username'
AND dt='$cdate' AND isua=$flag";
$rows_affected=$dbh->do($aff);
}
}
exit 1;
sub errorconn {
while(<>) {
open(FBLOG,">>".$backuplog);
print FBLOG $_;
close(FBLOG);
}
die "cannot log to MySQL -- data buffered";
}
File: sqauth
#!/bin/sh
while read username password; do
status=`/usr/local/bin/mysql -u root -D kan_squid -e "SELECT username FROM
auth WHERE username='$username' AND
password=password('$password')"`
if [ "$status" = "" ]; then
echo "ERR"
else
curmonth=`date '+%Y-%m'`;
bitt=`/usr/local/bin/mysql -u root -D kan_squid -e "SELECT IFNULL((sum(bytes)
< maxmonthly),1) AS Shapka FROM
protocol, auth WHERE userident=username and userident='$username' and datework
like '$curmonth%' and ua=0;"|egrep "[0-2]"`;
if [ $bitt -eq 1 ]; then
echo "OK";
else
echo "ERR";
fi
fi
done
--
С наилучшими пожеланиями,
Колесник Андрей
mailto:kolesn***@e*****.ua,
ICQ: 208861095
