function parse_realtime_hash()
{
@mkdir("/var/log/squid/mysql-rtcaches", 0755, true);
@mkdir("/var/log/squid/mysql-rttime", 0755, true);
$GLOBALS["TablePrimaireHour"] = array();
$GLOBALS["TABLES_PRIMAIRES_SEARCHWORDS"] = array();
$GLOBALS["MacResolvFrfomIP"] = null;
$GLOBALS["MacResolvInterface"] = null;
$WORKDIR = "/var/log/squid/mysql-rthash";
$GLOBALS["MacResolvInterface"] = trim(@file_get_contents("/etc/artica-postfix/settings/Daemons/MacResolvInterface"));
$GLOBALS["EnableMacAddressFilter"] = trim(@file_get_contents("/etc/artica-postfix/settings/Daemons/EnableMacAddressFilter"));
if (!is_numeric($GLOBALS["EnableMacAddressFilter"])) {
$GLOBALS["EnableMacAddressFilter"] = 1;
}
$EnableRemoteSyslogStatsAppliance = trim(@file_get_contents("/etc/artica-postfix/settings/Daemons/EnableRemoteSyslogStatsAppliance"));
$DisableArticaProxyStatistics = trim(@file_get_contents("/etc/artica-postfix/settings/Daemons/DisableArticaProxyStatistics"));
$EnableRemoteStatisticsAppliance = trim(@file_get_contents("/etc/artica-postfix/settings/Daemons/EnableRemoteStatisticsAppliance"));
$SquidActHasReverse = trim(@file_get_contents("/etc/artica-postfix/settings/Daemons/SquidActHasReverse"));
if (!is_numeric($EnableRemoteStatisticsAppliance)) {
$EnableRemoteStatisticsAppliance = 0;
}
if (!is_numeric($DisableArticaProxyStatistics)) {
$DisableArticaProxyStatistics = 0;
}
if (!is_numeric($EnableRemoteSyslogStatsAppliance)) {
$EnableRemoteSyslogStatsAppliance = 0;
}
if (!is_numeric($SquidActHasReverse)) {
$SquidActHasReverse = 0;
}
@mkdir($WORKDIR, 0755, true);
chown($WORKDIR, "squid");
chgrp($WORKDIR, "squid");
if (!($handle = opendir("/var/log/squid/mysql-rthash"))) {
return;
}
$GLOBALS["LOG_HOSTNAME"] = false;
$EnableProxyLogHostnames = intval(trim(@file_get_contents("/etc/artica-postfix/settings/Daemons/EnableProxyLogHostnames")));
if ($EnableProxyLogHostnames == 1) {
$GLOBALS["LOG_HOSTNAME"] = true;
}
$GLOBALS["IPCACHE"] = unserialize(@file_get_contents("/etc/squid3/IPCACHE.db"));
$GLOBALS["SitenameResolved"] = unserialize(@file_get_contents("/etc/squid3/SitenameResolved.db"));
$GLOBALS["GetFamilySites"] = unserialize(@file_get_contents("/etc/squid3/GetFamilySites.db"));
$GLOBALS["USERSDB"] = unserialize(@file_get_contents("/etc/squid3/usersMacs.db"));
$GLOBALS["KEYUSERS"] = unserialize(@file_get_contents("/etc/squid3/KEYUSERS.db"));
$GLOBALS["CACHEARP"] = unserialize(@file_get_contents("/etc/squid3/CACHEARP.db"));
if ($GLOBALS["MacResolvInterface"] != null) {
$GLOBALS["MacResolvFrfomIP"] = ethToIp();
}
$GLOBALS["UserAgents"] = array();
$q = new mysql_squid_builder();
$logfileD = new logfile_daemon();
$IpClass = new IP();
$CountDeFiles = 0;
$AA = 0;
$countDeFiles = 0;
while (false !== ($filename = readdir($handle))) {
if ($filename == ".") {
continue;
}
if ($filename == "..") {
continue;
}
$filepath = "{$WORKDIR}/{$filename}";
events("parse_realtime_hash():: Scanning {$WORKDIR}/{$filename}");
$content = unserialize(@file_get_contents($filepath));
$CountDeFiles++;
@unlink($filepath);
while (list($SUFFIX_TABLE, $Arrayz) = each($content)) {
while (list($index, $rows) = each($Arrayz)) {
$AA++;
$cached = 0;
$hostname = null;
$SUFFIX_DATE = $SUFFIX_TABLE;
$key = null;
$xtime = $rows["TIME"];
$sitename = $rows["SITENAME"];
$mac = $rows["MAC"];
$uid = $rows["UID"];
$ipaddr = $rows["IPADDR"];
if (isset($rows["HOSTNAME"])) {
$hostname = $rows["HOSTNAME"];
}
$SquidCode = $rows["SQUID_CODE"];
$SIZE = $rows["SIZE"];
$uri = $rows["URI"];
$zMD5 = md5(serialize($rows));
$UserAgent = $rows["USERAGENT"];
$code_error = $rows["HTTP_CODE"];
if ($IpClass->isValid($uid)) {
$uid = null;
}
$RESPONSE_TIME = $rows["RESPONSE_TIME"];
if ($GLOBALS["VERBOSE"]) {
echo "Scanning {$SUFFIX_DATE} {$xtime} {$ipaddr} {$sitename}\n";
}
if (isset($GLOBALS["ZMD5"][$zMD5])) {
events("{$uri} - md5 = {$zMD5} is the same !!!");
}
$GLOBALS["ZMD5"][$zMD5] = true;
if ($mac == null) {
if ($GLOBALS["EnableMacAddressFilter"] == 1) {
$mac = IpToMac($ipaddr);
}
}
if ($uid == null) {
if ($mac != null) {
if (isset($GLOBALS["USERSDB"]["MACS"][$mac])) {
$uid = $GLOBALS["USERSDB"]["MACS"][$mac]["UID"];
}
}
}
if (strpos(" {$sitename}", "www.") > 0) {
if (preg_match("#^www\\.(.+)#", $sitename, $re)) {
$sitename = $re[1];
}
}
if ($IpClass->isValid($sitename)) {
if (!isset($GLOBALS["SitenameResolved"][$sitename])) {
$GLOBALS["SitenameResolved"][$sitename] = gethostbyaddr2($sitename);
}
if ($GLOBALS["SitenameResolved"][$sitename] != null) {
$sitename = $GLOBALS["SitenameResolved"][$sitename];
}
}
if (!isset($GLOBALS["GetFamilySites"][$sitename])) {
$GLOBALS["GetFamilySites"][$sitename] = x_GetFamilySites($sitename);
if ($GLOBALS["GetFamilySites"][$sitename] == null) {
$GLOBALS["GetFamilySites"][$sitename] = $sitename;
}
}
$familysite = $GLOBALS["GetFamilySites"][$sitename];
if ($familysite == "localhost") {
continue;
}
if ($uid != null) {
$key = "uid";
}
if ($key == null) {
if ($mac != null) {
$key = "MAC";
}
}
if ($key == null) {
if ($ipaddr != null) {
$key = "ipaddr";
}
}
if ($key == null) {
continue;
}
$hour = date("H", $xtime);
$date = date("Y-m-d H:i:s", $xtime);
if ($GLOBALS["VERBOSE"]) {
echo "Date: {$date}: {$familysite} {$uid}/{$ipaddr}\n";
}
$uri = trim($uri);
if ($uri == null) {
continue;
}
if ($uid == null) {
$uid = x_MacToUid($mac);
}
if ($uid == null) {
$uid = x_IpToUid($ipaddr);
}
if ($hostname == null) {
$hostname = x_MacToHost($mac);
}
if ($hostname == null) {
$hostname = x_IpToHost($ipaddr);
}
if (trim($hostname) == null) {
if ($GLOBALS["LOG_HOSTNAME"]) {
$hostname = gethostbyaddr2($ipaddr);
}
}
if (preg_match("#(.+?):(.+)#", $SquidCode, $re)) {
$SquidCode = $re[1];
}
if ($logfileD->CACHEDORNOT($SquidCode)) {
$cached = 1;
}
if ($GLOBALS["VERBOSE"]) {
echo "[" . __LINE__ . "]: Uri <{$uri}> Squid code={$SquidCode} cached={$cached} Client = {$uid}/{$mac}/{$hostname} [{$ipaddr}] , Size={$SIZE} bytes\n";
}
//events("$familysite - Squid code=$SquidCode cached=$cached Client = $uid/$mac/$hostname [$ipaddr] , Size=$SIZE bytes");
$KeyUser = md5($uid . $hostname . $ipaddr . $mac . $UserAgent);
$UserAgent = x_mysql_escape_string2($UserAgent);
if (!isset($GLOBALS["KEYUSERS"][$KeyUser])) {
$GLOBALS["UserAutDB"][] = "('{$KeyUser}','{$mac}','{$ipaddr}','{$uid}','{$hostname}','{$UserAgent}')";
//$sql="INSERT IGNORE INTO UserAutDB (zmd5,MAC,ipaddr,uid,hostname,UserAgent) VALUES ('$KeyUser','$mac','$ipaddr','$uid','$hostname','$UserAgent')";
}
if ($UserAgent != null) {
$GLOBALS["UserAgents"][] = "('{$UserAgent}')";
}
$catz = new mysql_catz();
$category = x_mysql_escape_string2($catz->GetMemoryCache($sitename, true));
events("RTTHASH:: {$sitename} Category = `{$category}`");
$TablePrimaireHour = "squidhour_" . $SUFFIX_DATE;
$TableSizeHours = "sizehour_" . $SUFFIX_DATE;
$TableCacheHours = "cachehour_" . $SUFFIX_DATE;
$tableYoutube = "youtubehours_" . $SUFFIX_DATE;
$tableSearchWords = "searchwords_" . $SUFFIX_DATE;
$sitename = x_mysql_escape_string2($sitename);
$uri = substr($uri, 0, 254);
$uri = x_mysql_escape_string2($uri);
$uriT = x_mysql_escape_string2($uri);
$hostname = x_mysql_escape_string2($hostname);
$TYPE = $logfileD->codeToString($code_error);
$REASON = $TYPE;
if ($mac != null) {
$GLOBALS["macscan"][] = "('{$mac}','{$ipaddr}')";
}
$GLOBALS["TablePrimaireHour"][$TablePrimaireHour][] = "('{$sitename}','{$uriT}','{$TYPE}','{$REASON}','{$ipaddr}','{$hostname}','{$date}','{$zMD5}','{$uid}','{$SIZE}','{$cached}','{$mac}','{$category}')";
//$sql="INSERT IGNORE INTO `$TableSizeHours` (`zDate`,`size`,`cached`) VALUES('$date','$SIZE','$cached')";
$GLOBALS["TABLES_PRIMAIRES_SIZEHOUR"][$TableSizeHours][] = "('{$date}','{$SIZE}','{$cached}')";
if ($SIZE > 0) {
$GLOBALS["TABLES_PRIMAIRES_CACHEHOUR"][$TableCacheHours][] = "('{$date}','{$SIZE}','{$cached}','{$familysite}')";
}
if (strpos(" {$uri}", "youtube") > 0) {
$VIDEOID = $logfileD->GetYoutubeID($uri);
if ($VIDEOID != null) {
events("YOUTUBE:: {$date}: {$ipaddr} {$uid} {$mac} [{$VIDEOID}]");
$sql = "INSERT IGNORE INTO `{$tableYoutube}`\n\t\t\t\t\t(`zDate`,`ipaddr`,`hostname`,`uid`,`MAC` ,`account`,`youtubeid`)\n\t\t\t\t\tVALUES ('{$date}','{$ipaddr}','','{$uid}','{$mac}','0','{$VIDEOID}')";
$rand = rand(100, 65000);
@file_put_contents("/var/log/squid/mysql-queue/YoutubeRTT." . time() . ".{$rand}.sql", $sql);
}
}
$SearchWords = $logfileD->SearchWords($uri);
if (is_array($SearchWords)) {
$words = x_mysql_escape_string2($SearchWords["WORDS"]);
$GLOBALS["TABLES_PRIMAIRES_SEARCHWORDS"][$tableSearchWords][] = "('{$zMD5}','{$sitename}','{$date}','{$ipaddr}','{$hostname}','{$uid}','{$mac}','0','{$familysite}','{$words}')";
}
//
$timekey = date('YmdH', $xtime);
$stime = date("Y-m-d H:i:s", $xtime);
$table = "quotatemp_{$timekey}";
$keyr2 = md5("{$stime}{$date}{$uid}{$ipaddr}{$mac}{$sitename}");
$GLOBALS["TABLES_PRIMAIRES_QUOTATEMP"][$table][] = "('{$stime}','{$keyr2}','{$ipaddr}','{$familysite}','{$familysite}','{$uid}','{$mac}','{$SIZE}')";
}
}
}
events("{$WORKDIR} -> {$AA} elements scanned");
if (count($GLOBALS["UserAgents"]) > 0) {
$q = new mysql_squid_builder();
$q->QUERY_SQL("INSERT IGNORE INTO `UserAgents` (`pattern`) VALUES " . @implode(",", $GLOBALS["UserAgents"]));
$GLOBALS["UserAgents"] = array();
}
if ($CountDeFiles > 0) {
$GLOBALS["PARSE_SECOND_TIME"] = true;
events(__FUNCTION__ . "():: {$CountDeFiles} parsed files");
@file_put_contents("/etc/squid3/IPCACHE.db", serialize($GLOBALS["IPCACHE"]));
@file_put_contents("/etc/squid3/SitenameResolved.db", serialize($GLOBALS["SitenameResolved"]));
@file_put_contents("/etc/squid3/GetFamilySites.db", serialize($GLOBALS["GetFamilySites"]));
@file_put_contents("/etc/squid3/KEYUSERS.db", unserialize($GLOBALS["KEYUSERS"]));
@file_put_contents("/etc/squid3/CACHEARP.db", serialize($GLOBALS["CACHEARP"]));
PurgeMemory();
empty_TablePrimaireHour();
}
}
function testssocks()
{
$GLOBALS["VERBOSE"] = true;
include_once dirname(__FILE__) . '/ressources/class.mysql.catz.inc';
$catz = new mysql_catz();
echo "google.fr -> " . $catz->GetMemoryCache("google.fr", true) . "\n";
echo "google.com -> " . $catz->GetMemoryCache("google.com", true) . "\n";
}
function main_items()
{
$tpl = new templates();
$MyPage = CurrentPageName();
$q = new mysql_squid_builder();
$date = date("YmdH");
$table = "RTTH_{$date}";
if ($q->COUNT_ROWS($table) == 0) {
json_error_show("No data");
}
$catz = new mysql_catz();
$search = '%';
$table = "(SELECT SUM(size) as size,ipaddr,uid,MAC,sitename FROM {$table} GROUP BY ipaddr,uid,MAC,sitename) as t";
$page = 1;
if (isset($_POST["sortname"])) {
if ($_POST["sortname"] != null) {
$ORDER = "ORDER BY {$_POST["sortname"]} {$_POST["sortorder"]}";
}
}
if (isset($_POST['page'])) {
$page = $_POST['page'];
}
$searchstring = string_to_flexquery();
if ($searchstring != null) {
$sql = "SELECT COUNT(*) as TCOUNT FROM {$table} WHERE 1 {$searchstring}";
$ligne = mysql_fetch_array($q->QUERY_SQL($sql));
$total = $ligne["TCOUNT"];
if (!$q->ok) {
json_error_show("{$q->mysql_error}");
}
} else {
$sql = "SELECT COUNT(*) as TCOUNT FROM {$table}";
$ligne = mysql_fetch_array($q->QUERY_SQL($sql));
$total = $ligne["TCOUNT"];
if (!$q->ok) {
json_error_show("{$q->mysql_error}");
}
}
if (isset($_POST['rp'])) {
$rp = $_POST['rp'];
}
if (!is_numeric($rp)) {
$rp = 50;
}
$pageStart = ($page - 1) * $rp;
$limitSql = "LIMIT {$pageStart}, {$rp}";
$sql = "SELECT * FROM {$table} WHERE 1 {$searchstring} {$ORDER} {$limitSql}";
writelogs($sql, __FUNCTION__, __FILE__, __LINE__);
$results = $q->QUERY_SQL($sql);
if (!$q->ok) {
json_error_show("{$q->mysql_error}");
}
$data = array();
$data['page'] = $page;
$data['total'] = $total;
$data['rows'] = array();
if (mysql_num_rows($results) == 0) {
$data['rows'][] = array('id' => $ligne[time()], 'cell' => array($sql, "", "", ""));
json_encode($data);
return;
}
while ($ligne = mysql_fetch_assoc($results)) {
$category = null;
$ligne["size"] = FormatBytes($ligne["size"] / 1024);
$category = $catz->GetMemoryCache($ligne["sitename"], true);
if ($category != null) {
$category = "<br><strong style='color:#D2904A;font-size:12px'>({$category})</strong>";
}
$data['rows'][] = array('id' => md5($ligne["pattern"]), 'cell' => array("<span style='font-size:14px;'>{$ligne["sitename"]}{$category}</span>", "<span style='font-size:14px;'>{$ligne["ipaddr"]}</span>", "<span style='font-size:14px;'>{$ligne["uid"]}</span>", "<span style='font-size:14px;'>{$ligne["MAC"]}</span>", "<span style='font-size:14px;'>{$ligne["size"]}</span>"));
}
echo json_encode($data);
}
