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();
}
}
include_once dirname(__FILE__) . '/framework/class.settings.inc';
include_once dirname(__FILE__) . '/ressources/class.freeweb.inc';
include_once dirname(__FILE__) . '/ressources/class.system.network.inc';
if ($GLOBALS["VERBOSE"]) {
echo "starting include functions done..\n";
}
@mkdir("/root/properties", 0755, true);
$ldap = new clladp();
$q = new mysql();
$suffix = $ldap->suffix;
$arr = array("uid");
$sr = @ldap_search($ldap->ldap_connection, "dc=organizations,{$suffix}", '(objectclass=userAccount)', $arr);
if ($sr) {
$hash = ldap_get_entries($ldap->ldap_connection, $sr);
for ($i = 0; $i < $hash["count"]; $i++) {
$user = $hash[$i]["uid"][0];
$sql = "select val_string from properties where properties.tag=0x6771 and hierarchyid=(select hierarchy_id from stores where user_name='{$user}');";
$ligne = mysql_fetch_array($q->QUERY_SQL($sql, "zarafa"));
$val_string = x_mysql_escape_string2($ligne["val_string"]);
$cmd = "update properties set val_string='{$val_string}' where properties.tag=0x6771 and hierarchyid=(select hierarchy_id from stores where user_name='{$user}');";
@file_put_contents("/root/properties/{$user}.sql", $cmd);
$f[] = $cmd;
}
}
@file_put_contents("/root/properties/ALL.sql", @implode("\n", $f));
function x_mysql_escape_string2($line)
{
$search = array("\\", "", "\n", "\r", "", "'", '"');
$replace = array("\\\\", "\\0", "\\n", "\\r", "\\Z", "\\'", '\\"');
return str_replace($search, $replace, $line);
}
