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); }