Hey,
I’ve modified proxylizer daemon to support squid log files. Here is php code to do that:
#!/usr/bin/php
<?php
/****************************************************************************
* *
* MikroTik Proxylizer, Web-proxy log analyzer *
* Copyright (C) 2009 MikroTik *
* *
* This program is free software: you can redistribute it and/or modify *
* it under the terms of the GNU General Public License as published by *
* the Free Software Foundation, either version 3 of the License, or *
* (at your option) any later version. *
* *
* This program is distributed in the hope that it will be useful, *
* but WITHOUT ANY WARRANTY; without even the implied warranty of *
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the *
* GNU General Public License for more details. *
* *
* You should have received a copy of the GNU General Public License *
* along with this program. If not, see <http://www.gnu.org/licenses/>. *
* *
****************************************************************************/
/****************************************************************************
SQUID support added by ayufan (ayufan(at)osk-net(dot)pl)
****************************************************************************/
if (isset($_SERVER['REQUEST_URI'])) return;
// DB_URL format:
// DB_TYPE://DB_USER:DB_PASSWORD@DB_HOSTNAME/DB_NAME
define("DB_URL", "mysql://proxylizer:proxylizer@localhost/proxylizer");
define("HOST_IP", "127.0.0.1");
define("STARTED_FROM_INDEX", 2);
define("DEBUG", 0);
define("IP_DIGIT", "(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)");
define("IP_REGEXP", "^" . IP_DIGIT . "\\." . IP_DIGIT . "\\." . IP_DIGIT . "\\." . IP_DIGIT . "$");
chdir(dirname($argv[0]));
$host = ip2long(HOST_IP);//exec("hostname");
include('DB.php');
set_time_limit(0);
ob_implicit_flush();
// tell PEAR to write no backtrace
$skiptrace = &PEAR::getStaticProperty('PEAR_Error', 'skiptrace');
$skiptrace = true;
if (!($db = connectDB())) {
echo date("Y-m-d H:i | ") . "Couldn't connect to database!!!\n";
exit(2);
}
while (($line = fgets(STDIN)) !== FALSE) {
$line = str_replace("\n", "", $line);
$line = ereg_replace("[ \t]+", " ", $line);
$rawlog = explode(" ", $line);
if(count($rawlog) != 10) {
echo date("Y-m-d H:i | ") . "Invalid Squid line!!!\n";
continue;
}
$date = date("Y-m-d", intval($rawlog[0]));
$time = date("H:i", intval($rawlog[0]));
$IP = eregIP($rawlog[2]);
if($IP === FALSE) {
echo date("Y-m-d H:i | ") . "Invalid IP address!!!\n";
continue;
}
$IP = ip2long($rawlog[2]);
parseURL($rawlog[6]);
parseDomain($fulldomain);
parseActionSquid($rawlog[3]);
parseCacheSquid($rawlog[3]);
insertLine($db);
}
$db->disconnect();
function eregIP ($ip) {
if (ereg(IP_REGEXP, $ip)) {
return $ip;
} else {
return false;
}
}
function connectDB() {
$options = array(
'debug' => 2,
'portability' => DB_PORTABILITY_ALL,
);
do {
$db = & DB::connect(DB_URL, $options);
if (PEAR::isError($db)) {
echo "/1/ ";
echo date("Y-m-d H:i | ") . "Code " . $errcode=$db->getcode() . " ";
echo $db->getMessage() . "\n";
if ($errcode == DB_ERROR_ACCESS_VIOLATION || $errcode == DB_ERROR_NOSUCHDB) {
return false;
}
return false;
} else {
return $db;
}
} while (true);
}
function insertLine (& $db) {
global $subdomain, $domain, $topdomain, $domainid , $cofig_const ;
$query = "INSERT INTO domain(subdomain, domain, topdomain)
VALUES ('{$subdomain}', '{$domain}', '{$topdomain}')";
do {
$insertdata = & $db->query($query);
$iserror = false;
if (PEAR::isError($insertdata)) {
$msg = $insertdata->getMessage() . "\n";
$errcode = $insertdata->getCode();
if ($errcode = DB_ERROR_CONSTRAINT || $errcode = DB_ERROR_ALREADY_EXISTS) {
$testquery = "SELECT ID from domain WHERE subdomain = '{$subdomain}'
and domain = '{$domain}' and topdomain = '{$topdomain}'";
// domain exists
$res =& $db->query($testquery);
if (PEAR::isError($res)) {
$errorcode = $res->getCode();
if ($errorcode == DB_ERROR_ACCESS_VIOLATION || $errcode == DB_ERROR_NOSUCHDB ||
$errcode == DB_ERROR_NODBSELECTED || $errcode == DB_ERROR ||
$errcode == DB_ERROR_NODBSELECTED) {
// domain exists but connection lost
echo "/2/ ";
echo date("Y-m-d H:i | ") . "CODE: " . $res->getCode() . " ";
echo $res->getMessage() . " \n";
$iserror = true;
$db->disconnect();
$db = connectDB();
} else {
$iserror = false;
}
} else {
// domain exists, id selected
$row =& $res->fetchRow();
$domainid = $row['0'];
insertWebproxyTable($db);
$iserror = false;
}
} elseif ($errcode == DB_ERROR_ACCESS_VIOLATION || $errcode == DB_ERROR_NOSUCHDB ||
$errcode == DB_ERROR_NODBSELECTED || $errcode == DB_ERROR ||
$errcode == DB_ERROR_NODBSELECTED) {
// no connection, etc
echo "/3/ ";
echo date("Y-m-d H:i | ") . "CODE: " . $res->getCode() . " ";
echo $res->getMessage() . " \n";
$iserror = false;
$db->disconnect();
$db = connectDB();
} else {
echo "/4/ ";
echo date("Y-m-d H:i | ") . $insertdata->getMessage() . "\n";
}
} else {
// domain inserted
$querymaxid = "SELECT max(ID) FROM domain";
$res =& $db->query($querymaxid);
if (PEAR::isError($res)) {
echo "/5/ ";
echo date("Y-m-d H:i | ") . "CODE: " . $res->getCode() . " ";
echo $res->getMessage() . "\n";
} else {
$row =& $res->fetchRow();
$domainid = $row['0'];
insertWebproxyTable($db);
$iserror = false;
}
}
} while ($iserror ==true);
}
function insertWebproxyTable (& $db) {
global $domainid, $host, $date, $time, $IP, $domainid, $path, $action, $cache, $iserror, $line;
$query = "INSERT INTO webproxylog (host, event_date, event_time, IP, domain_id, path, action, cache)"
. " VALUES ('{$host}', '{$date}', '{$time}', "
. "'{$IP}', '{$domainid}', '{$path}', '{$action}', '{$cache}')";
$insertdata = & $db->query($query);
if (PEAR::isError($insertdata)) {
echo "/6/ ";
echo date("Y-m-d H:i | ") . $insertdata->getMessage() . "\n";
$iserror = false;
} else if (DEBUG) {
$query = "SELECT MAX(id) FROM webproxylog";
$res = & $db->query($query);
if (!PEAR::isError($res)) {
$row = $res->fetchRow();
if ($row) {
$logID = $row[0];
$msg = str_replace("'", "''", $line);
$query = "INSERT INTO msg (logid, msg) VALUES ({$logID}, '{$msg}')";
$res = & $db->query($query);
}
}
}
}
function parseDomain($d) {
global $subdomain, $domain, $topdomain;
$is_ipv4 = ip2long($d);
if ($is_ipv4 !== false) {
$subdomain = "";
$domain = $d;
$topdomain = "";
} else {
$posoflastdot = strrpos($d, '.');
if ($posoflastdot === false) {
$subdomain = "";
$domain = $d;
$topdomain = "";
} else {
$topdomain = substr($d, $posoflastdot + 1);
$rawdomain = substr($d, 0, $posoflastdot);
$posofprelastdot = strrpos($rawdomain, '.');
if ($posofprelastdot !== false) {
$domain = substr($rawdomain, $posofprelastdot + 1);
$subdomain = substr($rawdomain, 0, $posofprelastdot);
} else {
$domain = $rawdomain;
$subdomain = "";
}
}
}
}
function parseURL($u) {
global $fulldomain, $path;
$posofquestion = strpos($u, '?');
if ($posofquestion != false) {
$u = substr($u, 0, $posofquestion);
}
$posofcolonslash = strpos($u, '://');
$address = substr($u, $posofcolonslash + 3);
$posofslash = strpos($address, '/');
if ($posofslash != false) {
$fulldomain = substr($address, 0, $posofslash);
$path = substr($address, $posofslash + 1);
} else {
$fulldomain = $address;
$path = "";
}
}
function parseAction($a) {
global $action;
$action = $a == 'action=allow' ? 1 : 0;
}
function parseActionSquid($a) {
global $action;
$action = (strpos($a, "DENIED") !== FALSE);
}
function parseCache($l) {
global $cache;
$c = substr($l, -9);
$cache = $c == 'cache=HIT' ? 1 : 0;
}
function parseCacheSquid($l) {
global $cache;
$cache = (strpos($l, "HIT") !== FALSE);
}
?>
Simply copy above code and paste it to squidproxylizer.php file (you can use nano to do that). After that configure DB_URL and DB_HOST in source. My version reads logs from STDIN.
Simplest usage is to redirect squid logs to script:
tail -f /var/log/squid/access.log | php squidproxylizer.php
In Debian you can add it to crontab with nice daemon solution. Add following line which automatically starts squidproxylizer to root’s crontab: crontab -e -u root (Of course tune script and log location)
* * * * * tail -n 0 -f /var/log/squid/access.log | /sbin/start-stop-daemon --start --name squidproxylizer --quiet --chuid proxy --startas /srv/squidproxylizer.php 1>/dev/null 2>/dev/null &
When daemon starts you can stop it for one minute with following command:
start-stop-daemon --stop --name squidproxylizer
It’s test version and works nice for me.
I’m looking for feedback.
ayufan
Someday maybe i’ll add 