function tests() { $influx = new influx(); $sql = "SELECT SUM(SIZE) as size FROM MAIN_SIZE WHERE time > 1434913322s GROUP BY time(10m) ORDER BY ASC"; $influx->debug = true; $main = $influx->QUERY_SQL($sql); }
function start() { $sock = new sockets(); $SquidPerformance = intval($sock->GET_INFO("SquidPerformance")); if ($SquidPerformance > 1) { if ($GLOBALS["VERBOSE"]) { echo "Squid Performance disabled</br>"; } return; } $influx = new influx(); $now = strtotime(date("Y-m-d H:00:00")); $sql = "select sum(SIZE) as size from MAIN_SIZE group by time(5m) where time > {$now}"; $main = $influx->QUERY_SQL($sql); foreach ($main as $row) { $time = $row->time; $min = date("i", $time); $size = $row->size / 1024; if ($GLOBALS["VERBOSE"]) { echo "{$min} -> {$size}<bR>\n"; } $size = $size / 1024; $xdata[] = $min; $ydata[] = $size; } $page = CurrentPageName(); $time = time(); if (count($xdata) > 1) { krsort($xdata); krsort($ydata); $array["xdata"] = $xdata; $array["ydata"] = $ydata; $data = urlencode(base64_encode(serialize($array))); $f1[] = "<div style='width:665px;height:240px' id='SQUIDRTT-{$time}-2'></div>"; $f2[] = "function FDeux{$time}(){\n\t\t\tLoadjsSilent('{$page}?rtt-hour=yes&container=SQUIDRTT-{$time}-2&data={$data}',false);\n\t\t}\n\t\tsetTimeout(\"FDeux{$time}()\",500);"; } $html = @implode("\n", $f1) . "<script>" . @implode("\n", $f2) . "</script>"; echo $html; }
function USERAGENTS() { echo __FUNCTION__ . "\n"; $now = InfluxQueryFromUTC(strtotime("-4 hour")); $MAIN = array(); $xdata = array(); $ydata = array(); $influx = new influx(); $sock = new sockets(); $UserAgentsStatistics = intval($sock->GET_INFO("UserAgentsStatistics")); if ($UserAgentsStatistics == 0) { return; } $sql = "SELECT MAC,RQS,SIZE,UID,USERAGENT FROM useragents WHERE time>{$now}s"; echo __FUNCTION__ . ": QUERY\n"; $main = $influx->QUERY_SQL($sql); echo __FUNCTION__ . ": PARSING\n"; foreach ($main as $row) { $SIZE = intval($row->SIZE); $RQS = intval($row->RQS); $UID = $row->UID; $MAC = $row->MAC; $USERAGENT = $row->USERAGENT; if ($MAC == null) { if ($UID == null) { continue; } } $md5 = md5("{$UID}{$USERAGENT}{$MAC}"); if (!isset($TMAIN[$md5])) { $TMAIN[$md5]["UID"] = $UID; $TMAIN[$md5]["USERAGENT"] = $USERAGENT; $TMAIN[$md5]["MAC"] = $MAC; $TMAIN[$md5]["RQS"] = $RQS; $TMAIN[$md5]["SIZE"] = $SIZE; } else { $TMAIN[$md5]["SIZE"] = $TMAIN[$md5]["SIZE"] + $SIZE; $TMAIN[$md5]["RQS"] = $TMAIN[$md5]["RQS"] + $RQS; } } while (list($md5, $array) = each($TMAIN)) { $USERAGENT = trim($array["USERAGENT"]); $MAC = $array["MAC"]; $RQS = $array["RQS"]; $SIZE = $array["SIZE"]; $UID = $array["UID"]; $USERAGENT = mysql_escape_string2($USERAGENT); if ($GLOBALS["VERBOSE"]) { echo "('{$USERAGENT}','{$SIZE}','{$RQS}','{$MAC}','{$UID}')\n"; } $f[] = "('{$USERAGENT}','{$SIZE}','{$RQS}','{$MAC}','{$UID}')"; } if (count($f) > 0) { $q = new mysql_squid_builder(); $q->QUERY_SQL("CREATE TABLE IF NOT EXISTS USERAGENTS4H (\n\t\t\t\t`hits` BIGINT UNSIGNED, \n\t\t\t\t`size` BIGINT UNSIGNED,\n\t\t\t\t`USERAGENT` VARCHAR(128) NOT NULL ,\n\t\t\t\t`UID` VARCHAR(128) NOT NULL,\n\t\t\t\t`MAC` VARCHAR(128) NOT NULL,\n\t\t\t\tKEY `hits` (`hits`), \n\t\t\t\tKEY `size` (`size`),\n\t\t\t\tKEY `UID` (`UID`),\n\t\t\t\tKEY `MAC` (`MAC`),\n\t\t\t\tKEY `USERAGENT` (`USERAGENT`)\n\t\t\t\t) ENGINE=MYISAM"); $q->QUERY_SQL("TRUNCATE TABLE USERAGENTS4H"); $q->QUERY_SQL("INSERT IGNORE INTO USERAGENTS4H (USERAGENT,size,hits,MAC,UID) VALUES " . @implode(",", $f)); } }
function start_week() { $unix = new unix(); $hostname = $unix->hostname_g(); $now = InfluxQueryFromUTC(strtotime("-7 day")); $today = date("Y-m-d", $now) . " 00:00:00"; $sql = "SELECT SUM(TX) as TX, SUM(RX) as RX,ETH FROM ethrxtx WHERE proxyname='{$hostname}' AND time >'{$today}' group by time(4h),ETH"; $influx = new influx(); echo "{$sql}\n"; $main = $influx->QUERY_SQL($sql); $c = 0; $f = array(); foreach ($main as $row) { $time = date("Y-m-d H:i:s", InfluxToTime($row->time)); $ETH = $row->ETH; if ($ETH == "lo") { continue; } $RX = $row->RX; $TX = $row->TX; $f[] = "('{$time}','{$ETH}','{$RX}','{$TX}')"; } if (count($f) == 0) { return; } $q = new mysql(); if ($q->TABLE_EXISTS("RXTX_WEEK", "artica_events")) { $q->QUERY_SQL("TRUNCATE TABLE `RXTX_WEEK`", "artica_events"); } $sql = "CREATE TABLE IF NOT EXISTS `RXTX_WEEK`\n\t(`ZDATE` DATETIME,\n\t`RX` INT UNSIGNED NOT NULL DEFAULT 1,\n\t`TX` INT UNSIGNED NOT NULL DEFAULT 1,\n\t`ETH` VARCHAR(60),\n\tKEY `ZDATE`(`ZDATE`),\n\tKEY `RX`(`RX`),\n\tKEY `TX`(`TX`),\n\tKEY `ETH`(`ETH`) ) ENGINE = MYISAM;"; $q->QUERY_SQL($sql, "artica_events"); if (!$q->ok) { return; } $q->QUERY_SQL("INSERT IGNORE INTO RXTX_WEEK (ZDATE,ETH,RX,TX) VALUES " . @implode(",", $f), "artica_events"); }
function cpustats() { $xdata = array(); $ydata = array(); $unix = new unix(); $hostname = $unix->hostname_g(); $filecache = dirname(__FILE__) . "/ressources/logs/web/cpustatsH.db"; $filecache_load = dirname(__FILE__) . "/ressources/logs/web/INTERFACE_LOAD_AVGH.db"; $filecache_mem = dirname(__FILE__) . "/ressources/logs/web/INTERFACE_LOAD_AVG2H.db"; $now = InfluxQueryFromUTC(strtotime("-24 hour")); $influx = new influx(); $sql = "SELECT MEAN(CPU_STATS) as cpu,MEAN(LOAD_AVG) as load,MEAN(MEM_STATS) as memory FROM SYSTEM where proxyname='{$hostname}' and time > {$now}s GROUP BY time(10m) ORDER BY ASC"; if ($GLOBALS["VERBOSE"]) { echo "{$sql}\n"; } $main = $influx->QUERY_SQL($sql); foreach ($main as $row) { $time = InfluxToTime($row->time); if (!is_numeric($row->cpu)) { continue; } if (!is_numeric($row->load)) { continue; } $min = date("l H:i", $time) . "mn"; $xdata[] = $min; $ydata[] = round($row->cpu, 2); $ydataL[] = round($row->load, 2); $ydataM[] = round($row->memory, 2); if ($GLOBALS["VERBOSE"]) { echo "{$min} -> {$row->cpu} | {$row->load} | {$row->memory}\n"; } } if (count($xdata) > 1) { $ARRAY = array($xdata, $ydata); $ARRAYL = array($xdata, $ydataL); $ARRAYM = array($xdata, $ydataM); if ($GLOBALS["VERBOSE"]) { echo "-> {$filecache}\n"; } @file_put_contents($filecache, serialize($ARRAY)); @file_put_contents($filecache_load, serialize($ARRAYL)); @file_put_contents($filecache_mem, serialize($ARRAYM)); @chmod($filecache, 0755); @chmod($filecache_load, 0755); @chmod($filecache_mem, 0755); } $xdata = array(); $ydata = array(); $ydataL = array(); $ydataM = array(); $filecache = dirname(__FILE__) . "/ressources/logs/web/cpustats.db"; $filecache_load = dirname(__FILE__) . "/ressources/logs/web/INTERFACE_LOAD_AVG.db"; $filecache_mem = dirname(__FILE__) . "/ressources/logs/web/INTERFACE_LOAD_AVG2.db"; $now = InfluxQueryFromUTC(strtotime("-168 hour")); $influx = new influx(); $sql = "SELECT MEAN(CPU_STATS) as cpu,MEAN(LOAD_AVG) as load,MEAN(MEM_STATS) as memory FROM SYSTEM where proxyname='{$hostname}' and time > {$now}s GROUP BY time(1h) ORDER BY ASC"; if ($GLOBALS["VERBOSE"]) { echo "{$sql}\n"; } $main = $influx->QUERY_SQL($sql); foreach ($main as $row) { $time = InfluxToTime($row->time); if (!is_numeric($row->cpu)) { continue; } if (!is_numeric($row->load)) { continue; } $min = date("l H:00", $time); $xdata[] = $min; $ydata[] = round($row->cpu, 2); $ydataL[] = round($row->load, 2); $ydataM[] = round($row->memory / 1024, 2); if ($GLOBALS["VERBOSE"]) { echo "{$min} -> {$row->cpu} | {$row->load} | {$row->memory}\n"; } } if (count($xdata) > 1) { $ARRAY = array($xdata, $ydata); $ARRAYL = array($xdata, $ydataL); $ARRAYM = array($xdata, $ydataM); if ($GLOBALS["VERBOSE"]) { echo "-> {$filecache}\n"; } @file_put_contents($filecache, serialize($ARRAY)); @file_put_contents($filecache_load, serialize($ARRAYL)); @file_put_contents($filecache_mem, serialize($ARRAYM)); @chmod($filecache, 0755); @chmod($filecache_load, 0755); @chmod($filecache_mem, 0755); } }
function mQuotaWebFilter() { $unix = new unix(); $pidfile = "/etc/artica-postfix/pids/exec.squid.stats.hours.php.mQuotaWebFilter.pid"; $timefile = "/etc/artica-postfix/pids/exec.squid.stats.hours.php.mQuotaWebFilter.time"; $sock = new sockets(); $SquidPerformance = intval($sock->GET_INFO("SquidPerformance")); if ($SquidPerformance > 1) { die; } $pid = $unix->get_pid_from_file($pidfile); if (!$GLOBALS["FORCE"]) { if ($pid < 100) { $pid = null; } $unix = new unix(); if ($unix->process_exists($pid, basename(__FILE__))) { $rpcessTime = $unix->PROCCESS_TIME_MIN($pid); if ($GLOBALS["VERBOSE"]) { echo "Already executed pid {$pid} since {$rpcessTime}Mn\n"; } if ($rpcessTime < 10) { return; } $unix->KILL_PROCESS($pid, 9); } $timeexec = $unix->file_time_min($timefile); if ($timeexec < 30) { return; } $mypid = getmypid(); @file_put_contents($pidfile, $mypid); } build_last_hour(); $ipClass = new IP(); if ($GLOBALS["VERBOSE"]) { echo "Time File: {$timefile}\n"; } $ARRAY["TIME_BUILD"] = time(); $q = new mysql_squid_builder(); $influx = new influx(); $date_from = InfluxQueryFromUTC(strtotime("-1 hour")); $sql = "SELECT SUM(SIZE) as size,USERID,IPADDR,MAC,CATEGORY FROM access_log GROUP BY time(1h) ,USERID,IPADDR,MAC,CATEGORY WHERE time > {$date_from}s"; if ($GLOBALS["VERBOSE"]) { echo "{$sql}\n"; } $main = $influx->QUERY_SQL($sql); foreach ($main as $row) { $CATEGORY = $row->CATEGORY; $USERID = strtolower($row->USERID); $IPADDR = $row->IPADDR; $MAC = $row->MAC; $size = intval($row->size); if ($size == 0) { continue; } if ($CATEGORY != null) { if (!isset($ARRAY["categories"][$CATEGORY]["HOUR"][$USERID])) { $ARRAY["categories"][$CATEGORY]["HOUR"][$USERID] = $size; } else { $size_old = intval($ARRAY["categories"][$CATEGORY]["HOUR"][$USERID]); $size_old = $size_old + $size; $ARRAY["categories"][$CATEGORY]["HOUR"][$USERID] = $size_old; } if (!isset($ARRAY["categories"][$CATEGORY]["HOUR"][$IPADDR])) { $ARRAY["categories"][$CATEGORY]["HOUR"][$IPADDR] = $size; } else { $size_old = $ARRAY["categories"][$CATEGORY]["HOUR"][$IPADDR]; $size_old = $size_old + $size; if ($size_old == 0) { echo "Warning {$CATEGORY}/{$IPADDR} {$ARRAY["categories"][$CATEGORY]["HOUR"][$IPADDR]} + {$size} = 0\n"; } $ARRAY["categories"][$CATEGORY]["HOUR"][$IPADDR] = $size_old; } if (!isset($ARRAY["categories"][$CATEGORY]["HOUR"][$MAC])) { $ARRAY["categories"][$CATEGORY]["HOUR"][$MAC] = $size; } else { $size_old = $ARRAY["categories"][$CATEGORY]["HOUR"][$MAC]; $size_old = $size_old + $size; $ARRAY["categories"][$CATEGORY]["HOUR"][$MAC] = $size_old; } } if (!isset($ARRAY["UID"][$USERID]["HOUR"])) { $ARRAY["UID"][$USERID]["HOUR"] = $size; } else { $size_old = $ARRAY["UID"][$USERID]["HOUR"]; $size_old = $size_old + $size; $ARRAY["UID"][$USERID]["HOUR"] = $size_old; } if (!isset($ARRAY["IPADDR"][$IPADDR]["HOUR"])) { $ARRAY["IPADDR"][$IPADDR]["HOUR"] = $size; } else { $size_old = $ARRAY["IPADDR"][$IPADDR]["HOUR"]; $size_old = $size_old + $size; $ARRAY["IPADDR"][$IPADDR]["HOUR"] = $size_old; } if ($ipClass->IsvalidMAC($MAC)) { if (!isset($ARRAY["MAC"][$MAC]["HOUR"])) { $ARRAY["MAC"][$MAC]["HOUR"] = $size; } else { $size_old = $ARRAY["MAC"][$MAC]["HOUR"]; $size_old = $size_old + $size; $ARRAY["MAC"][$MAC]["HOUR"] = $size_old; } } } //----------------------------------------------------------------------------------------------- $date_from = InfluxQueryFromUTC(strtotime("-1 day")); $sql = "SELECT SUM(SIZE) as size,USERID,IPADDR,MAC,CATEGORY FROM access_hour GROUP BY time(1d) ,USERID,IPADDR,MAC,CATEGORY WHERE time > {$date_from}s"; if ($GLOBALS["VERBOSE"]) { echo "{$sql}\n"; } $main = $influx->QUERY_SQL($sql); foreach ($main as $row) { $CATEGORY = $row->CATEGORY; $USERID = strtolower($row->USERID); $IPADDR = $row->IPADDR; $MAC = $row->MAC; $size = intval($row->size); if ($size == 0) { continue; } if ($CATEGORY != null) { if (!isset($ARRAY["categories"][$CATEGORY]["DAY"][$USERID])) { $ARRAY["categories"][$CATEGORY]["DAY"][$USERID] = $size; } else { $size_old = intval($ARRAY["categories"][$CATEGORY]["DAY"][$USERID]); $size_old = $size_old + $size; $ARRAY["categories"][$CATEGORY]["DAY"][$USERID] = $size_old; } if (!isset($ARRAY["categories"][$CATEGORY]["DAY"][$IPADDR])) { $ARRAY["categories"][$CATEGORY]["DAY"][$IPADDR] = $size; } else { $size_old = $ARRAY["categories"][$CATEGORY]["DAY"][$IPADDR]; $size_old = $size_old + $size; $ARRAY["categories"][$CATEGORY]["DAY"][$IPADDR] = $size_old; } if (!isset($ARRAY["categories"][$CATEGORY]["DAY"][$MAC])) { $ARRAY["categories"][$CATEGORY]["DAY"][$MAC] = $size; } else { $size_old = $ARRAY["categories"][$CATEGORY]["DAY"][$MAC]; $size_old = $size_old + $size; $ARRAY["categories"][$CATEGORY]["DAY"][$MAC] = $size_old; } } if (!isset($ARRAY["UID"][$USERID]["DAY"])) { $ARRAY["UID"][$USERID]["DAY"] = $size; } else { $size_old = $ARRAY["UID"][$USERID]["DAY"]; $size_old = $size_old + $size; $ARRAY["UID"][$USERID]["DAY"] = $size_old; } if (!isset($ARRAY["IPADDR"][$IPADDR]["DAY"])) { $ARRAY["IPADDR"][$IPADDR]["DAY"] = $size; } else { $size_old = $ARRAY["IPADDR"][$IPADDR]["DAY"]; $size_old = $size_old + $size; $ARRAY["IPADDR"][$IPADDR]["DAY"] = $size_old; } if ($ipClass->IsvalidMAC($MAC)) { if (!isset($ARRAY["MAC"][$MAC]["DAY"])) { $ARRAY["MAC"][$MAC]["DAY"] = $size; } else { $size_old = $ARRAY["MAC"][$MAC]["DAY"]; $size_old = $size_old + $size; $ARRAY["MAC"][$MAC]["DAY"] = $size_old; } } } //----------------------------------------------------------------------------------------------- $influx = new influx(); $date_from = strtotime("-1 week"); $sql = "SELECT SUM(SIZE) as size,USERID,IPADDR,MAC,CATEGORY FROM access_hour GROUP BY time(1w) ,USERID,IPADDR,MAC,CATEGORY WHERE time > {$date_from}s"; $main = $influx->QUERY_SQL($sql); foreach ($main as $row) { $CATEGORY = $row->CATEGORY; $USERID = strtolower($row->USERID); $IPADDR = $row->IPADDR; $MAC = $row->MAC; $size = intval($row->size); if ($size == 0) { continue; } if ($CATEGORY != null) { if (!isset($ARRAY["categories"][$CATEGORY]["WEEK"][$USERID])) { $ARRAY["categories"][$CATEGORY]["WEEK"][$USERID] = $size; } else { $size_old = $ARRAY["categories"][$CATEGORY]["WEEK"][$USERID]; $size_old = $size_old + $size; $ARRAY["categories"][$CATEGORY]["WEEK"][$USERID] = $size_old; } if (!isset($ARRAY["categories"][$CATEGORY]["WEEK"][$IPADDR])) { $ARRAY["categories"][$CATEGORY]["WEEK"][$IPADDR] = $size; } else { $size_old = $ARRAY["categories"][$CATEGORY]["WEEK"][$IPADDR]; $size_old = $size_old + $size; $ARRAY["categories"][$CATEGORY]["WEEK"][$IPADDR] = $size_old; } if (!isset($ARRAY["categories"][$CATEGORY]["WEEK"][$MAC])) { $ARRAY["categories"][$CATEGORY]["WEEK"][$MAC] = $size; } else { $size_old = $ARRAY["categories"][$CATEGORY]["WEEK"][$MAC]; $size_old = $size_old + $size; $ARRAY["categories"][$CATEGORY]["WEEK"][$MAC] = $size_old; } } if ($USERID != null) { if (!isset($ARRAY["UID"][$USERID]["WEEK"])) { $ARRAY["UID"][$USERID]["WEEK"] = $size; } else { $size_old = $ARRAY["UID"][$USERID]["WEEK"]; $size_old = $size_old + $size; $ARRAY["UID"][$USERID]["WEEK"] = $size_old; } } if ($ipClass->isValid($IPADDR)) { if (!isset($ARRAY["IPADDR"][$IPADDR]["WEEK"])) { $ARRAY["IPADDR"][$IPADDR]["IPADDR"] = $size; } else { $size_old = $ARRAY["IPADDR"][$IPADDR]["WEEK"]; $size_old = $size_old + $size; $ARRAY["IPADDR"][$IPADDR]["WEEK"] = $size_old; } } if ($ipClass->IsvalidMAC($MAC)) { if (!isset($ARRAY["MAC"][$MAC]["WEEK"])) { $ARRAY["MAC"][$MAC]["WEEK"] = $size; } else { $size_old = $ARRAY["MAC"][$MAC]["WEEK"]; $size_old = $size_old + $size; $ARRAY["MAC"][$MAC]["WEEK"] = $size_old; } } } //----------------------------------------------------------------------------------------------- if ($GLOBALS["VERBOSE"]) { print_r($ARRAY); } @unlink("/usr/share/squid3/CurrentSizesUsers.db"); @file_put_contents("/usr/share/squid3/CurrentSizesUsers.db", serialize($ARRAY)); @file_put_contents("/usr/share/artica-postfix/ressources/logs/web/CurrentSizesUsers.db", serialize($ARRAY)); @chmod("/usr/share/artica-postfix/ressources/logs/web/CurrentSizesUsers.db", 0755); }
function GRAB_DATAS($ligne, $md5) { $GLOBALS["zMD5"] = $md5; $params = unserialize($ligne["params"]); $influx = new influx(); $from = InfluxQueryFromUTC($params["FROM"]); $to = InfluxQueryFromUTC($params["TO"]); $interval = $params["INTERVAL"]; $q = new mysql_squid_builder(); $q->QUERY_SQL("DROP TABLE `tmp_{$md5}user`"); $sql = "CREATE TABLE IF NOT EXISTS `tmp_{$md5}user`\n\t(`ZDATE` DATETIME,\n\t`SIZE` INT UNSIGNED NOT NULL DEFAULT 1,\n\t`RQS` INT UNSIGNED NOT NULL DEFAULT 1,\n\t`CATEGORY` VARCHAR(60),\n\t`FAMILYSITE` VARCHAR(128),\n\t`USERID` VARCHAR(60),\n\t`IPADDR` VARCHAR(60),\n\t`MAC` VARCHAR(60),\n\tKEY `ZDATE`(`ZDATE`),\n\tKEY `CATEGORY`(`CATEGORY`),\n\tKEY `FAMILYSITE`(`FAMILYSITE`),\n\tKEY `USERID`(`USERID`),\n\tKEY `IPADDR`(`IPADDR`),\n\tKEY `MAC`(`MAC`)) ENGINE = MYISAM;"; $q->QUERY_SQL($sql); if (!$q->ok) { echo "********** FAILED **********\n"; echo $q->mysql_error . "\n"; build_progress("{step} {insert_data}: MySQL engine, {failed}", 110); return false; } $FIELDS["MAC"] = "MAC"; $FIELDS["IPADDR"] = "IPADDR"; $FIELDS["USERID"] = "USERID"; $sql = "SELECT SIZE,FAMILYSITE,RQS,CATEGORY,MAC,IPADDR,USERID FROM access_log WHERE (time >'" . date("Y-m-d H:i:s", $from) . "' and time < '" . date("Y-m-d H:i:s", $to) . "')"; if (isset($params["USER"])) { while (list($field, $size) = each($FIELDS)) { $FINAL_FIELDS[] = $field; } $sql = "SELECT SIZE,FAMILYSITE,RQS,CATEGORY," . @implode(",", $FINAL_FIELDS) . " FROM access_log WHERE (time >'" . date("Y-m-d H:i:s", $from) . "' and time < '" . date("Y-m-d H:i:s", $to) . "')"; } echo "{$sql}\n"; build_progress("{step} {waiting_data}: BigData engine, (websites) {please_wait}", 6); $GLOBALS["CSV1"][] = array("date", "website", "uid", "ipaddr", "mac", "SizeBytes", "SizeText", "hits"); $main = $influx->QUERY_SQL($sql); echo "MAIN(1): " . count($main) . " items\n"; if (count($main) < 2) { $sql = "SELECT SIZE,FAMILYSITE,RQS," . @implode(",", $FINAL_FIELDS) . " FROM access_log WHERE (time >'" . date("Y-m-d H:i:s", $from) . "' and time < '" . date("Y-m-d H:i:s", $to) . "')"; $main = $influx->QUERY_SQL($sql); echo "MAIN(2): " . count($main) . " items\n"; } $c = 0; foreach ($main as $row) { $time = InfluxToTime($row->time); $SIZE = intval($row->SIZE); if ($SIZE == 0) { continue; } $RQS = intval($row->RQS); $CATEGORY = mysql_escape_string2($row->CATEGORY); $FAMILYSITE = mysql_escape_string2($row->FAMILYSITE); $MAC = mysql_escape_string2($row->MAC); $IPADDR = mysql_escape_string2($row->IPADDR); $USERID = mysql_escape_string2($row->USERID); $DATE = date("Y-m-d H:00:00", $time); //if($GLOBALS["VERBOSE"]){echo "$DATE','$SIZE','$RQS','$CATEGORY','$FAMILYSITE','$USERID','$IPADDR','$MAC'\n";} $f[] = "('{$DATE}','{$SIZE}','{$RQS}','{$CATEGORY}','{$FAMILYSITE}','{$USERID}','{$IPADDR}','{$MAC}')"; $SIZE_TEXT = FormatBytes($SIZE / 1024); $GLOBALS["CSV1"][] = array($DATE, $FAMILYSITE, $USERID, $IPADDR, $MAC, $SIZE, $SIZE_TEXT, $RQS); $c++; if (count($f) > 500) { $q->QUERY_SQL("INSERT IGNORE INTO `tmp_{$md5}user` (`ZDATE`,`SIZE`,`RQS`,`CATEGORY`,`FAMILYSITE`,`USERID`,`IPADDR`,`MAC`)\n\t\t\tVALUES " . @implode(",", $f)); $f = array(); if (!$q->ok) { echo "********** FAILED **********\n"; echo $q->mysql_error . "\n"; build_progress("{step} {insert_data}: MySQL engine, {failed}", 110); return false; } } } if (count($f) > 0) { $q->QUERY_SQL("INSERT IGNORE INTO `tmp_{$md5}user` (`ZDATE`,`SIZE`,`RQS`,`CATEGORY`,`FAMILYSITE`,`USERID`,`IPADDR`,`MAC`) VALUES " . @implode(",", $f)); if (!$q->ok) { echo "********** FAILED **********\n"; echo $q->mysql_error . "\n"; build_progress("{step} {insert_data}: MySQL engine, {failed}", 110); return false; } } if ($c == 0) { echo "MAIN_ARRAY is null....\n"; return false; } return true; }
function xqueries() { $page = CurrentPageName(); $tpl = new templates(); $influx = new influx(); $sql = "SELECT MAX(ZDATE) AS MAX FROM access_log"; $main = $influx->QUERY_SQL($sql); $MAX = $main[0]->MAX; $LastEntry = $tpl->time_to_date($MAX, true); if ($GLOBALS["VERBOSE"]) { echo "<p style='color:blue'>{$MAX} -> {$LastEntry}</p>"; } $from_gmt = $tpl->time_to_date($MAX - 300, true); $from = QueryToUTC($MAX - 300); $fromTime = date("Y-m-d H:i:s", $from); $ToTime = date("Y-m-d H:i:s", QueryToUTC($MAX)); $sql = "SELECT * from access_log WHERE time > '{$fromTime}' AND time < '{$ToTime}'"; //echo "<hr>$sql</HR>"; $main = null; $influx2 = new influx(); $QUERY2 = $influx2->QUERY_SQL($sql); $color = null; $ipClass = new IP(); $q = new mysql_squid_builder(); $c = 0; $D = 0; foreach ($QUERY2 as $row) { $USER = trim($row->USERID); $IPADDR = trim($row->IPADDR); $MAC = trim($row->MAC); if ($row->SIZE == 0) { continue; } if (is_numeric($USER)) { continue; } $RQS = $row->RQS; $time = InfluxToTime($row->time); $DATEKEY = date("H:00", $time); $KEYMD5 = md5("{$USER}{$IPADDR}{$MAC}"); $c = $c + $RQS; $D = $D + $row->SIZE; if (!isset($MAIN[$DATEKEY][$KEYMD5])) { $MAIN[$DATEKEY][$KEYMD5]["USER"] = $USER; $MAIN[$DATEKEY][$KEYMD5]["IPADDR"] = $IPADDR; $MAIN[$DATEKEY][$KEYMD5]["MAC"] = $MAC; $MAIN[$DATEKEY][$KEYMD5]["SIZE"] = $row->SIZE; $MAIN[$DATEKEY][$KEYMD5]["RQS"] = $RQS; } else { $MAIN[$DATEKEY][$KEYMD5]["SIZE"] = $MAIN[$DATEKEY][$KEYMD5]["SIZE"] + $row->SIZE; $MAIN[$DATEKEY][$KEYMD5]["RQS"] = $MAIN[$DATEKEY][$KEYMD5]["RQS"] + $RQS; } } $D = FormatBytes($D / 1024); $requests = $tpl->javascript_parse_text("{requests}"); $last_entry_on = $tpl->javascript_parse_text("{last_entry_on}"); $since = $tpl->_ENGINE_parse_body("{since}"); $html[] = "\n\t\n\t<div style='width:98%' class=form>\n\t<div style='margin-top:5px;font-size:16px;text-align:right;margin-bottom:15px;font-weight:bold'>\n\t\t{$since} 5mn ({$c} {$requests} / {$D}) UTC:" . $tpl->time_to_date($from, true) . " - GMT {$from_gmt} / {$last_entry_on}: {$LastEntry}</div>"; $html[] = "\n\t\t\n\t<table style='width:100%'>"; $html[] = $tpl->_ENGINE_parse_body("<tr>\n\t\t\t<th style='font-size:18px'>{time}</th>\n\t\t\t<th style='font-size:18px'>{MAC}</th>\n\t\t\t<th style='font-size:18px'>{ipaddr}</th>\n\t\t\t<th style='font-size:18px'>{uid}</th>\n\t\t\t<th style='font-size:18px'>{requests}</th>\n\t\t\t<th style='font-size:18px'>{size}</th>\n\t\t\t</tr>\n\t\t\t"); while (list($time, $SUBARRAY) = each($MAIN)) { while (list($KEYMD5, $BIGARRAY) = each($SUBARRAY)) { if ($color == null) { $color = "#F2F0F1"; } else { $color = null; } $MAC = $BIGARRAY["MAC"]; $RQS = $BIGARRAY["RQS"]; $SIZE = $BIGARRAY["SIZE"]; $USER = $BIGARRAY["USER"]; $IPADDR = $BIGARRAY["IPADDR"]; $MAC_link = null; if ($SIZE > 1024) { $size = FormatBytes($SIZE / 1024); } else { $size = "{$SIZE}Bytes"; } $RQS = FormatNumber($RQS); if ($ipClass->IsvalidMAC($MAC)) { $MAC_link = "<a href=\"javascript:blur();\"\n\t\t\tOnClick=\"javascript:Loadjs('squid.nodes.php?node-infos-js=yes&MAC=" . urlencode($MAC) . "');\"\n\t\t\tstyle='font-size:16px;text-decoration:underline;font-weight:bold'>\t\t\n\t\t\t"; if (trim($USER) == null) { $USER = $q->MacToUid($MAC); } } $html[] = "<tr style='background-color:{$color}'>"; $html[] = "<td style='font-size:16px;width:50px;padding:10px;font-weight:bold'>{$time}</td>"; $html[] = "<td style='font-size:16px;width:50px;padding:10px;font-weight:bold'>{$MAC_link}{$MAC}</a></td>"; $html[] = "<td style='font-size:16px;width:50px;padding:10px;font-weight:bold'>{$IPADDR}</td>"; $html[] = "<td style='font-size:16px;width:50px;padding:10px;font-weight:bold'>{$USER}</td>"; $html[] = "<td style='font-size:16px;width:50px;text-align:right;padding:10px' nowrap>{$RQS}</td>"; $html[] = "<td style='font-size:16px;width:50px;text-align:right;padding:10px' nowrap>{$size}</td>"; $html[] = "</tr>"; } } $html[] = "</table>"; $html[] = "</div>"; $html[] = "\n\t<script>\n\t\tfunction FollowerRefresh(){\n\t\t\tif(!document.getElementById('ZRTRQUESTS_COMPTER')){ return;}\n\t\t\tvar compter=parseInt(document.getElementById('ZRTRQUESTS_COMPTER').value);\n\t\t\tif(compter<10){\n\t\t\t\tcompter=compter+1;\n\t\t\t\tdocument.getElementById('ZRTRQUESTS_COMPTER').value=compter;\n\t\t\t\tsetTimeout(\"FollowerRefresh()\",1000);\n\t\t\t\treturn;\n\t\t\t}\n\t\t\t\n\t\t\tdocument.getElementById('ZRTRQUESTS_COMPTER').value=0;\n\t\t\tif(!document.getElementById('proxy-follower-table')){ return;}\n\t\t\tLoadAjaxSilent('proxy-follower-table','{$page}?follow=yes&t={$_GET["t"]}');\n\t\t}\n\t\t\t\n\t\t\t\n\tsetTimeout(\"FollowerRefresh()\",1000);\n\t</script>"; echo @implode("\n", $html); }
function GRAB_DATAS($ligne, $md5) { $GLOBALS["zMD5"] = $md5; $params = unserialize($ligne["params"]); $influx = new influx(); $from = InfluxQueryFromUTC($params["FROM"]); $to = InfluxQueryFromUTC($params["TO"]); $interval = $params["INTERVAL"]; $user = $params["USER"]; $md5_table = "{$md5}sites"; echo "FLOW: FROM {$from} to {$to} {$interval} user:{$user}\n"; $sql = "SELECT SIZE,FAMILYSITE FROM access_log WHERE time >'" . date("Y-m-d H:i:s", $from) . "' and time < '" . date("Y-m-d H:i:s", $to) . "'"; echo "{$sql}\n"; build_progress("{step} {waiting_data}: BigData engine, (websites) {please_wait}", 6); $main = $influx->QUERY_SQL($sql); foreach ($main as $row) { $time = InfluxToTime($row->time); $SIZE = intval($row->SIZE); $FAMILYSITE = $row->FAMILYSITE; $Hour = date("Y-m-d H:00:00", $time); if ($SIZE == 0) { continue; } if (!isset($MAIN_ARRAY[$Hour][$FAMILYSITE])) { $MAIN_ARRAY[$Hour][$FAMILYSITE]["SIZE"] = $SIZE; } else { $MAIN_ARRAY[$Hour][$FAMILYSITE]["SIZE"] = $MAIN_ARRAY[$Hour][$FAMILYSITE]["SIZE"] + $SIZE; } } if (count($MAIN_ARRAY) == 0) { echo "MAIN_ARRAY is null....\n"; return false; } echo "MAIN_ARRAY (1) = " . count($MAIN_ARRAY) . "\n"; build_progress("{step} {insert_data}: MySQL engine, {please_wait}", 8); $f = array(); $GLOBALS["CSV1"][] = array("Date", "Websites", "SizeBytes"); $sql = "CREATE TABLE IF NOT EXISTS `{$md5}sites` \n\t(`zDate` DATETIME,`familysite` VARCHAR(128),`size` INT UNSIGNED NOT NULL DEFAULT 1,\n\tKEY `familysite`(`familysite`),\n\tKEY `zDate`(`zDate`),\n\tKEY `size`(`size`)\n\t) ENGINE = MYISAM;"; $q = new mysql_squid_builder(); $q->QUERY_SQL($sql); if (!$q->ok) { echo $q->mysql_error; REMOVE_TABLES($md5); return false; } while (list($curhour, $array) = each($MAIN_ARRAY)) { while (list($FAMILYSITE, $Tarray) = each($array)) { $SIZE = $Tarray["SIZE"]; $c = 0; $f[] = "('{$curhour}','{$FAMILYSITE}','{$SIZE}')"; $GLOBALS["CSV1"][] = array($curhour, $FAMILYSITE, $SIZE); if (count($f) > 500) { $q->QUERY_SQL("INSERT IGNORE INTO `{$md5}sites` (zDate,familysite,size) VALUES " . @implode(",", $f)); if (!$q->ok) { echo $q->mysql_error; REMOVE_TABLES($md5); return false; } $f = array(); } } } if (count($f) > 0) { $q->QUERY_SQL("INSERT IGNORE INTO `{$md5}sites` (zDate,familysite,size) VALUES " . @implode(",", $f)); $f = array(); } echo "Websites {$c} items inserted to MySQL\n"; $sql = "CREATE TABLE IF NOT EXISTS `{$md5}users`\n\t(`user` VARCHAR(128),`size` INT UNSIGNED NOT NULL DEFAULT 1, KEY `user`(`user`), KEY `size`(`size`)\n\t) ENGINE = MYISAM;"; $q = new mysql_squid_builder(); $q->QUERY_SQL($sql); if (!$q->ok) { echo $q->mysql_error; REMOVE_TABLES($md5); return false; } $sql = "SELECT SIZE,{$user} FROM access_log WHERE time >'" . date("Y-m-d H:i:s", $from) . "' and time < '" . date("Y-m-d H:i:s", $to) . "'"; echo "{$sql}\n"; build_progress("{step} {waiting_data}: BigData engine, (websites) {please_wait}", 8); $main = $influx->QUERY_SQL($sql); $MAIN_ARRAY = array(); $c = 0; foreach ($main as $row) { $SIZE = intval($row->SIZE); $USER = $row->{$user}; if ($SIZE == 0) { continue; } if (!isset($MAIN_ARRAY[$USER])) { $MAIN_ARRAY[$USER] = $SIZE; } else { $MAIN_ARRAY[$USER] = $MAIN_ARRAY[$USER] + $SIZE; } } echo "MAIN_ARRAY (2) = " . count($MAIN_ARRAY) . "\n"; $c = 0; $GLOBALS["CSV2"][] = array("member", "SizeBytes"); while (list($USER, $SIZE) = each($MAIN_ARRAY)) { $GLOBALS["CSV2"][] = array($USER, $SIZE); $f[] = "('{$USER}','{$SIZE}')"; $c++; if (count($f) > 500) { $q->QUERY_SQL("INSERT IGNORE INTO `{$md5}users` (user,size) VALUES " . @implode(",", $f)); $f = array(); } } if (count($f) > 0) { $q->QUERY_SQL("INSERT IGNORE INTO `{$md5}users` (user,size) VALUES " . @implode(",", $f)); $f = array(); } echo "Members {$c} items inserted to MySQL\n"; return true; }
function node_infos_realtime_list() { $page = CurrentPageName(); $tpl = new templates(); $sock = new sockets(); $influx = new influx(); $from = strtotime('-1 hour'); if (isset($_POST['page'])) { $page = $_POST['page']; } if (isset($_POST['rp'])) { $rp = $_POST['rp']; } $ip = new IP(); if ($ip->isIPAddress($_GET["ipaddr"])) { $Select = "IPADDR"; $FORCE_FILTER = " IPADDR='{$_GET["ipaddr"]}'"; } if ($ip->IsvalidMAC($_GET["MAC"])) { $Select = "MAC"; $FORCE_FILTER = " MAC='{$_GET["MAC"]}'"; } $sql = "SELECT * FROM access_log where time >{$from}s and {$FORCE_FILTER} ORDER BY time DESC LIMIT {$rp}"; $data = array(); $data['page'] = $page; $data['total'] = 0; $data['rows'] = array(); $today = date("Y-m-d"); $tcp = new IP(); $main = $influx->QUERY_SQL($sql); $c = 0; foreach ($main as $row) { $color = "black"; $return_code_text = null; $ff = array(); $color = "black"; $uri = $row->SITE; $xtimelog = null; $date = date("H:i:s", InfluxToTime($row->time)); $mac = $row->MAC; $ip = $row->IPADDR; $user = $row->uid; $size = $row->SIZE; $rqs = $row->RQS; $ident = array(); $md = md5(serialize($row)); $c++; $spanON = "<span style='color:{$color};font-size:16px'>"; $spanOFF = "</span>"; $cached_text = null; $size = FormatBytes($size / 1024); $data['rows'][] = array('id' => $md, 'cell' => array("{$spanON}{$date}{$spanOFF}", "{$spanON}{$uri}{$spanOFF}", "{$spanON}{$rqs}{$spanOFF}", "{$spanON}{$size}{$spanOFF}")); } $data['total'] = $c; echo json_encode($data); }
function GRAB_DATAS($ligne, $md5) { $GLOBALS["zMD5"] = $md5; $params = unserialize($ligne["params"]); $influx = new influx(); $from = InfluxQueryFromUTC($params["FROM"]); $to = InfluxQueryFromUTC($params["TO"]); $interval = $params["INTERVAL"]; $USER_FIELD = $params["USER"]; $md5_table = md5(__FUNCTION__ . "." . "{$from}{$to}"); $searchsites = trim($params["searchsites"]); $searchuser = trim($params["searchuser"]); $searchsites_sql = null; $searchuser_sql = null; if ($searchsites == "*") { $searchsites = null; } if ($searchuser == "*") { $searchuser = null; } if ($searchsites != null) { $searchsites_sql = str_replace("*", ".*", $searchsites); $searchsites_sql = " AND FAMILYSITE =~ /{$searchsites_sql}/"; } if ($searchuser != null) { $searchuser_sql = str_replace("*", ".*", $searchuser); $searchuser_sql = " AND {$USER_FIELD} =~ /{$searchuser_sql}/"; } $SRF["USERID"] = true; $SRF["IPADDR"] = true; $SRF["MAC"] = true; unset($SRF[$USER_FIELD]); while (list($A, $P) = each($SRF)) { $srg[] = $A; } $users_fiels = @implode(",", $srg); $Z[] = "SELECT SIZE,RQS,FAMILYSITE,{$USER_FIELD},{$users_fiels} FROM access_log"; $Z[] = "WHERE (time >'" . date("Y-m-d H:i:s", $from) . "' and time < '" . date("Y-m-d H:i:s", $to) . "')"; if ($searchsites_sql != null) { $Z[] = "{$searchsites_sql}"; } if ($searchuser_sql != null) { $Z[] = "{$searchuser_sql}"; } $sql = @implode(" ", $Z); echo "{$sql}\n"; build_progress("{step} {waiting_data}: BigData engine, (websites) {please_wait}", 6); $main = $influx->QUERY_SQL($sql); $GLOBALS["CSV1"][] = array("date", "website", "member", "ipaddr", "mac", "SizeBytes", "SizeText", "hits"); foreach ($main as $row) { $time = InfluxToTime($row->time); $USER = $row->USERID; $IPADDR = $row->IPADDR; $MAC = $row->MAC; $SIZE = intval($row->SIZE); $RQS = intval($row->RQS); $FAMILYSITE = $row->FAMILYSITE; if (trim($FAMILYSITE) == null) { continue; } if (trim($IPADDR) == null) { continue; } $HOURLY = date("Y-m-d H:00:00", $time); $MDKey = md5("{$FAMILYSITE}{$USER}{$IPADDR}{$MAC}"); if ($SIZE == 0) { continue; } $TIME_TEXT = date("Y-m-d H:i:s", $time); $SizeText = FormatBytes($SIZE / 1024, true); $GLOBALS["CSV1"][] = array($TIME_TEXT, $FAMILYSITE, $USER, $IPADDR, $MAC, $SIZE, $SizeText, $RQS); if (!isset($MAIN_ARRAY[$HOURLY][$MDKey])) { $MAIN_ARRAY[$HOURLY][$MDKey]["FAMILYSITE"] = $FAMILYSITE; $MAIN_ARRAY[$HOURLY][$MDKey]["USER"] = $USER; $MAIN_ARRAY[$HOURLY][$MDKey]["MAC"] = $MAC; $MAIN_ARRAY[$HOURLY][$MDKey]["IPADDR"] = $IPADDR; $MAIN_ARRAY[$HOURLY][$MDKey]["RQS"] = $RQS; $MAIN_ARRAY[$HOURLY][$MDKey]["SIZE"] = $SIZE; } else { $MAIN_ARRAY[$HOURLY][$MDKey]["RQS"] = $MAIN_ARRAY[$HOURLY][$MDKey]["RQS"] + $RQS; $MAIN_ARRAY[$HOURLY][$MDKey]["SIZE"] = $MAIN_ARRAY[$HOURLY][$MDKey]["SIZE"] + $SIZE; } } if (count($MAIN_ARRAY) == 0) { echo "MAIN_ARRAY is null....\n"; return false; } echo "MAIN_ARRAY (1) = " . count($MAIN_ARRAY) . "\n"; build_progress("{step} {insert_data}: MySQL engine, {please_wait}", 8); $f = array(); $sql = "CREATE TABLE IF NOT EXISTS `{$md5}user` (\n\t`USERID` VARCHAR(128),\n\t`MAC` VARCHAR(90),\n\t`IPADDR` VARCHAR(90),\n\t`familysite` VARCHAR(128),\n\t`zDate` DATETIME,\n\t`size` INT UNSIGNED NOT NULL DEFAULT 1,\n\t`hits` INT UNSIGNED NOT NULL DEFAULT 1,\n\tKEY `USERID`(`USERID`),\n\tKEY `MAC`(`MAC`),\n\tKEY `IPADDR`(`IPADDR`),\n\tKEY `hits`(`hits`),\n\tKEY `familysite`(`familysite`),\n\tKEY `size`(`size`)\n\t) ENGINE = MYISAM;"; $q = new mysql_squid_builder(); $q->QUERY_SQL($sql); if (!$q->ok) { echo $q->mysql_error; REMOVE_TABLES($md5); return false; } $c = 0; while (list($TIME, $SUBARRAY) = each($MAIN_ARRAY)) { while (list($MDKey, $array) = each($SUBARRAY)) { $USER = $array["USER"]; $HITS = $array["RQS"]; $SIZE = $array["SIZE"]; $MAC = $array["MAC"]; $IPADDR = $array["IPADDR"]; $FAMILYSITE = $array["FAMILYSITE"]; if (trim($FAMILYSITE) == null) { continue; } if (trim($IPADDR) == null) { continue; } $c++; $SIZE_LOGS = $SIZE; $f[] = "('{$TIME}','{$FAMILYSITE}','{$USER}','{$MAC}','{$IPADDR}','{$SIZE}','{$HITS}')"; echo "('{$TIME}','{$FAMILYSITE}','{$USER}','{$SIZE_LOGS}','{$HITS}')\n"; if (count($f) > 500) { $q->QUERY_SQL("INSERT IGNORE INTO `{$md5}user` (zDate,familysite,USERID,MAC,IPADDR,size,hits) VALUES " . @implode(",", $f)); if (!$q->ok) { echo $q->mysql_error; REMOVE_TABLES($md5); return false; } $f = array(); } } } if (count($f) > 0) { $q->QUERY_SQL("INSERT IGNORE INTO `{$md5}user` (zDate,familysite,USERID,MAC,IPADDR,size,hits) VALUES " . @implode(",", $f)); $f = array(); } echo "{$c} items inserted to MySQL\n"; return true; }
function InfluxDBPassword() { $sock = new sockets(); $InfluxDBPassword = $sock->GET_INFO("InfluxDBPassword"); $influx = new influx(); $data = $influx->QUERY_SQL("SHOW USERS"); foreach ($data as $row) { $ARRAY["USER"] = $row->user; } if ($InfluxDBPassword != null) { if (isset($ARRAY["root"])) { $influx->QUERY_SQL("SET PASSWORD FOR root = '{$InfluxDBPassword}'"); } else { $influx->QUERY_SQL("CREATE USER root WITH PASSWORD '{$InfluxDBPassword}' WITH ALL PRIVILEGES"); } } else { if (isset($ARRAY["root"])) { $influx->QUERY_SQL("DROP USER root"); } } }
function BANDWIDTH_MONITOR() { $sock = new sockets(); $SquidPerformance = intval($sock->GET_INFO("SquidPerformance")); if ($SquidPerformance > 1) { return; } $watchdog = new squid_watchdog(); $MonitConfig = $watchdog->MonitConfig; if ($MonitConfig["CHECK_BANDWITDH"] == 0) { return; } $CHECK_BANDWITDH_INTERVAL = $MonitConfig["CHECK_BANDWITDH_INTERVAL"]; $influx = new influx(); if (!is_numeric($CHECK_BANDWITDH_INTERVAL)) { $CHECK_BANDWITDH_INTERVAL = 5; } $olddate = strtotime("-{$CHECK_BANDWITDH_INTERVAL} minutes", time()); $CHECK_BANDWITDH_SIZE = intval($MonitConfig["CHECK_BANDWITDH_INTERVAL"]); $sql = "select sum(SIZE) as size from access_log where time > {$olddate}s"; $main = $influx->QUERY_SQL($sql); foreach ($main as $row) { $size = $row->size / 1024; $size = round($size / 1024, 2); if ($GLOBALS["VERBOSE"]) { echo "Since " . date("Y-m-d H:i:s", $olddate) . "- Size: {$size}MB\n"; } } if ($GLOBALS["VERBOSE"]) { echo "{$size}MB must be higher than {$CHECK_BANDWITDH_SIZE}MB\n"; } if ($size < $CHECK_BANDWITDH_SIZE) { return; } $EXCEED_SIZE = $size; $REPORT[] = "Report bandwidth usage since: " . date("{l} {F} d H:i:s", $olddate); $ipclass = new IP(); $sql = "select sum(SIZE) as size,IPADDR,MAC,USERID from access_log group by IPADDR,MAC,USERID where time > {$olddate}s"; $main = $influx->QUERY_SQL($sql); foreach ($main as $row) { $users2 = array(); $size = $row->size / 1024; $size = round($size / 1024, 2); if ($size == 0) { continue; } if ($size < 1) { continue; } if ($CHECK_BANDWITDH_SIZE > 1) { if ($size < 2) { continue; } } $IPADDR = $row->IPADDR; $users2[] = $IPADDR; $MAC = trim($row->MAC); $USERID = trim($row->USERID); if ($USERID != null) { $users2[] = $USERID; } if ($ipclass->IsvalidMAC($MAC)) { $users2[] = $MAC; } $REPORT[] = "User: "******", ", $users2) . " {$size}MB used"; if ($GLOBALS["VERBOSE"]) { echo "Since " . date("Y-m-d H:i:s", $olddate) . "- {$IPADDR},{$MAC},{$USERID} Size: {$size}MB\n"; } } $catz = new mysql_catz(); $sql = "select sum(SIZE) as size,FAMILYSITE from access_log group by FAMILYSITE where time > {$olddate}s"; $main = $influx->QUERY_SQL($sql); foreach ($main as $row) { $size = $row->size / 1024; $size = round($size / 1024, 2); if ($size == 0) { continue; } if ($size < 1) { continue; } $FAMILYSITE = $row->FAMILYSITE; $category = $catz->GET_CATEGORIES($FAMILYSITE); if ($category != null) { $category_text = " (category:{$category})"; } $REPORT[] = "Web site: {$FAMILYSITE} {$size}MB used{$category_text}"; } squid_admin_mysql(0, "Bandwidth usage {$EXCEED_SIZE}MB exceed {$CHECK_BANDWITDH_SIZE}MB", @implode("\n", $REPORT), __FILE__, __LINE__); }
function GRAB_DATAS($ligne, $md5) { $GLOBALS["zMD5"] = $md5; $params = unserialize($ligne["params"]); $influx = new influx(); $from = InfluxQueryFromUTC($params["FROM"]); $to = InfluxQueryFromUTC($params["TO"]); $interval = $params["INTERVAL"]; $USER_FIELD = $params["USER"]; $md5_table = md5(__FUNCTION__ . "." . "{$from}{$to}"); $searchsites = trim($params["searchsites"]); $searchuser = trim($params["searchuser"]); $categories = trim($params["categories"]); $searchsites_sql = null; $searchuser_sql = null; if ($categories == "*") { $categories = null; } if ($searchuser == "*") { $searchuser = null; } if ($searchuser != null) { $searchuser_sql = str_replace("*", ".*", $searchuser); if ($searchuser_sql != null) { $searchuser_sql = " AND {$USER_FIELD} =~ /{$searchuser_sql}/"; } } $SRF["USERID"] = true; $SRF["IPADDR"] = true; $SRF["MAC"] = true; unset($SRF[$USER_FIELD]); while (list($A, $P) = each($SRF)) { $srg[] = $A; } $users_fiels = @implode(",", $srg); if ($searchuser != null) { $whereuser = "******"; } $q = new mysql_squid_builder(); $Z[] = "SELECT RQS,rulename,category,hostname,website,client FROM webfilter"; $Z[] = "WHERE (time >'" . date("Y-m-d H:i:s", $from) . "' and time < '" . date("Y-m-d H:i:s", $to) . "'){$whereuser}"; $sql = @implode(" ", $Z); echo "{$sql}\n"; build_progress("{step} {waiting_data}: BigData engine, (websites) {please_wait}", 6); $main = $influx->QUERY_SQL($sql); $GLOBALS["CSV1"][] = array("date", "rulename", "website", "category", "client", "uid"); foreach ($main as $row) { $time = InfluxToTime($row->time); $rulename = $row->rulename; $category = $row->category; $uid = $row->hostname; $website = $row->website; $client = $row->client; $HOURLY = date("Y-m-d H:00:00", $time); $MDKey = md5("{$rulename}{$category}{$uid}{$website}{$client}"); $TIME_TEXT = date("Y-m-d H:i:s", $time); $GLOBALS["CSV1"][] = array($TIME_TEXT, $rulename, $website, $category, $client, $uid); if (!isset($MAIN_ARRAY[$HOURLY][$MDKey])) { $MAIN_ARRAY[$HOURLY][$MDKey]["RULENAME"] = $rulename; $MAIN_ARRAY[$HOURLY][$MDKey]["WEBSITE"] = $website; $MAIN_ARRAY[$HOURLY][$MDKey]["CATEGORY"] = $category; $MAIN_ARRAY[$HOURLY][$MDKey]["CLIENT"] = $client; $MAIN_ARRAY[$HOURLY][$MDKey]["UID"] = $uid; $MAIN_ARRAY[$HOURLY][$MDKey]["RQS"] = 1; } else { $MAIN_ARRAY[$HOURLY][$MDKey]["RQS"] = $MAIN_ARRAY[$HOURLY][$MDKey]["RQS"] + 1; } } if (count($MAIN_ARRAY) == 0) { echo "MAIN_ARRAY is null....\n"; return false; } echo "MAIN_ARRAY (1) = " . count($MAIN_ARRAY) . "\n"; build_progress("{step} {insert_data}: MySQL engine, {please_wait}", 8); $f = array(); $sql = "CREATE TABLE IF NOT EXISTS `{$md5}user` (\n\t`RULENAME` VARCHAR(128),\n\t`WEBSITE` VARCHAR(128),\n\t`CATEGORY` VARCHAR(90),\n\t`CLIENT` VARCHAR(128),\n\t`UID` VARCHAR(90),\n\t`zDate` DATETIME,\n\t`hits` INT UNSIGNED NOT NULL DEFAULT 1,\n\tKEY `RULENAME`(`RULENAME`),\n\tKEY `WEBSITE`(`WEBSITE`),\n\tKEY `CATEGORY`(`CATEGORY`),\n\tKEY `CLIENT`(`CLIENT`),\n\tKEY `UID`(`UID`),\n\tKEY `zDate`(`zDate`),\n\tKEY `hits`(`hits`)\n\t) ENGINE = MYISAM;"; $q = new mysql_squid_builder(); $q->QUERY_SQL($sql); if (!$q->ok) { echo $q->mysql_error; REMOVE_TABLES($md5); return false; } $c = 0; while (list($TIME, $SUBARRAY) = each($MAIN_ARRAY)) { while (list($MDKey, $array) = each($SUBARRAY)) { $RULENAME = $array["RULENAME"]; $HITS = $array["RQS"]; $WEBSITE = $array["WEBSITE"]; $CATEGORY = $array["CATEGORY"]; $CLIENT = $array["CLIENT"]; $UID = $array["UID"]; $zDate = $TIME; $c++; $f[] = "('{$RULENAME}','{$WEBSITE}','{$CATEGORY}','{$CLIENT}','{$UID}','{$zDate}','{$HITS}')"; if (count($f) > 500) { $q->QUERY_SQL("INSERT IGNORE INTO `{$md5}user` (RULENAME,WEBSITE,CATEGORY,CLIENT,UID,zDate,hits) VALUES " . @implode(",", $f)); if (!$q->ok) { echo $q->mysql_error; REMOVE_TABLES($md5); return false; } $f = array(); } } } if (count($f) > 0) { $q->QUERY_SQL("INSERT IGNORE INTO `{$md5}user`(RULENAME,WEBSITE,CATEGORY,CLIENT,UID,zDate,hits) VALUES " . @implode(",", $f)); $f = array(); } echo "{$c} items inserted to MySQL\n"; return true; }
function query_influx($sql) { $GLOBALS["VERBOSE"] = true; $GLOBALS["DEBUG_INFLUX_VERBOSE"] = true; $influx = new influx(); $influx->ROOT_DUMP_ALL_DATABASES(); $main = $influx->QUERY_SQL($sql); foreach ($main as $row) { echo "TIME: " . date("Y-m-d H:i:s", $row->time) . "\n"; echo "SIZE: {$row->size}\n"; var_dump($row, $row->time); } echo "today is " . strtotime(date("Y-m-d H:i:s")) . "\n"; }
function GenerateGraph($nopid = false) { if (!is_file("/etc/artica-postfix/settings/Daemons/EnableDNSPerfs")) { @file_put_contents("/etc/artica-postfix/settings/Daemons/EnableDNSPerfs", 1); } $unix = new unix(); $pidtime = "/etc/artica-postfix/pids/" . basename(__FILE__) . "." . __FUNCTION__ . ".time"; $time = $unix->file_time_min($pidtime); if (!$GLOBALS["FORCE"]) { if ($time < 60) { return; } } @unlink($pidtime); @file_put_contents($pidtime, time()); $q = new mysql(); $EnableDNSPerfs = intval(@file_get_contents("/etc/artica-postfix/settings/Daemons/EnableDNSPerfs")); if ($EnableDNSPerfs == 0) { $q->QUERY_SQL("DROP TABLE dashboard_dnsperf_day", "artica_events"); die; } $q = new mysql(); $q->QUERY_SQL("DROP TABLE dashboard_dnsperf_day", "artica_events"); $q = new postgres_sql(); $q->QUERY_SQL("DROP TABLE dashboard_dnsperf_day"); $q->QUERY_SQL("CREATE TABLE IF NOT EXISTS dashboard_dnsperf_day (time timestamp,DNS VARCHAR(128), percent FLOAT(5), response FLOAT(5) )"); $q->create_index("dashboard_dnsperf_day", "ikey", array("time", "dns", "percent", "response")); $hostname = $unix->hostname_g(); $now = strtotime("-24 hour"); $q = new postgres_sql(); $sql = "SELECT AVG(PERCENT) as PERCENT,AVG(RESPONSE) as RESPONSE,DNS FROM dnsperfs where PROXYNAME='{$hostname}' and time > {$now}s GROUP BY DNS,time(10m) ORDER BY ASC"; $results = $q->QUERY_SQL($sql); while ($ligne = @pg_fetch_assoc($results)) { $zDate = $ligne["zDate"]; $PERCENT = $ligne["PERCENT"]; if (!is_numeric($PERCENT)) { continue; } $RESPONSE = $ligne["RESPONSE"]; $DNS = $ligne["DNS"]; $f[] = "('{$zDate}','{$DNS}','{$RESPONSE}','{$PERCENT}')"; } if (count($f) > 0) { print_r($f); $q->QUERY_SQL("INSERT INTO dashboard_dnsperf_day (time,dns,response,percent) \n\t\t\t\tVALUES " . @implode(",", $f), "artica_events"); } $hostname = $unix->hostname_g(); $now = date("Y-m-d H:i:s", strtotime("-30 day")); $influx = new influx(); $sql = "SELECT MEAN(PERCENT) as PERCENT,MEAN(RESPONSE) as RESPONSE,\n\tDNS FROM dnsperfs where PROXYNAME='{$hostname}' and time > '{$now}' GROUP BY DNS,time(1d) ORDER BY ASC"; $main = $influx->QUERY_SQL($sql); $f = array(); foreach ($main as $row) { $time = InfluxToTime($row->time); if (!is_numeric($row->PERCENT)) { continue; } $PERCENT = $row->PERCENT; $RESPONSE = $row->RESPONSE; $DNS = $row->DNS; $zDate = date("Y-m-d", $time); $f[] = "('{$zDate}','{$DNS}','{$RESPONSE}','{$PERCENT}')"; } $sql = "SELECT AVG(PERCENT) as PERCENT FROM dashboard_dnsperf_day"; $ligne = @mysql_fetch_array($q->QUERY_SQL($sql, "artica_events")); $sock = new sockets(); $sock->SET_INFO("DashBoardDNSPerfsStats", $ligne["PERCENT"]); $q->QUERY_SQL("DROP TABLE dashboard_dnsperf_month", "artica_events"); $q->QUERY_SQL("CREATE TABLE IF NOT EXISTS dashboard_dnsperf_month (`TIME` timestamp,`DNS` VARCHAR(128),`PERCENT` FLOAT(5),`RESPONSE` FLOAT(5)"); $q->create_index("dashboard_dnsperf_month", "ikey", array("time", "dns", "percent", "response")); if (count($f) > 0) { print_r($f); $q->QUERY_SQL("INSERT INTO dashboard_dnsperf_month (`TIME`,`DNS`,`RESPONSE`,`PERCENT`)\n\t\t\t\tVALUES " . @implode(",", $f), "artica_events"); } }
function list1() { $page = CurrentPageName(); $tpl = new templates(); $influx = new influx(); $q = new mysql_squid_builder(); $USER_FIELD = $_GET["user"]; $search = $_GET["search"]; if ($search == null) { $search = "*"; } $from = strtotime("{$_GET["date1"]} {$_GET["time1"]}"); if (!isset($_GET["date1"])) { $from = strtotime("-1 hour"); } if (isset($_POST["qtype"])) { if ($_POST["query"] != null) { $search = str_replace("*", ".*", $_POST["query"]); $search_query = "AND {$_POST["qtype"]} =~ /{$search}/"; } } $to = strtotime("{$_GET["date2"]} {$_GET["time2"]}"); $md5_table = md5("{$from}{$to}{$USER_FIELD}"); $_SESSION["SQUID_STATS_MEMBER_SEARCH"] = $search; $Maxlines = $_GET["Maxlines"]; $_SESSION["SQUID_STATS_MAX_LINES"] = $Maxlines; if ($search != "*") { //$search_query="AND (SITE =~ /$search/ or $USER_FIELD =~ /$search/)"; } $totext = "and time < {$to}s"; if (!isset($_POST["rp"])) { $_POST["rp"] = 100; } if ($from == $to) { $totext = null; } $totext = null; $sql = "SELECT * from access_log WHERE time > {$from}s {$totext} {$search_query} ORDER BY ASC LIMIT {$_POST["rp"]}"; $main = $influx->QUERY_SQL($sql); $data = array(); $data['page'] = 1; $data['total'] = 0; $data['rows'] = array(); $c = 0; $fontsize = "18px"; $color = null; $ipClass = new IP(); foreach ($main as $row) { $USER = trim($row->USERID); if ($row->SIZE == 0) { continue; } $time = date("H:i:00", InfluxToTime($row->time)); $CURTIME = strtotime($time); $CATEGORY = $row->CATEGORY; $SITE = $row->SITE; $RQS = $row->RQS; $MAC_link = null; $MAC = $row->MAC; $IPADDR = $row->IPADDR; $USER = trim($row->USERID); $SIZE = $row->SIZE; $MD5 = md5("{$time}{$MAC}{$IPADDR}{$USER}{$SITE}"); if (!isset($MAIN[$CURTIME][$MD5])) { $MAIN[$CURTIME][$MD5]["TIME"] = $time; $MAIN[$CURTIME][$MD5]["RQS"] = $RQS; $MAIN[$CURTIME][$MD5]["MAC"] = $MAC; $MAIN[$CURTIME][$MD5]["IPADDR"] = $IPADDR; $MAIN[$CURTIME][$MD5]["USER"] = $USER; $MAIN[$CURTIME][$MD5]["SIZE"] = $SIZE; $MAIN[$CURTIME][$MD5]["SITE"] = $SITE; $MAIN[$CURTIME][$MD5]["CATEGORY"] = $CATEGORY; } else { $MAIN[$CURTIME][$MD5]["RQS"] = $MAIN[$MD5]["RQS"] + $RQS; $MAIN[$CURTIME][$MD5]["SIZE"] = $MAIN[$MD5]["SIZE"] + $SIZE; $MAIN[$CURTIME][$MD5]["CATEGORY"] = $CATEGORY; } } krsort($MAIN); while (list($curtime, $array0) = each($MAIN)) { while (list($MDKey, $array) = each($array0)) { $USER = trim($array["USER"]); if ($color == null) { $color = "#F2F0F1"; } else { $color = null; } $time = $array["TIME"]; $RQS = $array["RQS"]; $MAC_link = null; $SIZE = $array["SIZE"]; if ($SIZE > 1024) { $size = FormatBytes($SIZE / 1024); } else { $size = "{$SIZE} Bytes"; } $MAC = $array["MAC"]; $IPADDR = $array["IPADDR"]; $CATEGORY = $array["CATEGORY"]; $SITE = $array["SITE"]; $RQS = FormatNumber($RQS); $c++; if ($ipClass->IsvalidMAC($MAC)) { $MAC_link = "<a href=\"javascript:blur();\"\n\t\t\tOnClick=\"javascript:Loadjs('squid.nodes.php?node-infos-js=yes&MAC=" . urlencode($MAC) . "');\"\n\t\t\tstyle='font-size:{$fontsize};text-decoration:underline'>\t\t\n\t\t\t"; } if ($ipClass->isValid($SITE)) { $SITE = "<a href=\"https://db-ip.com/{$SITE}\" style='text-decoration:underline;color:black' target=_new>{$SITE}</a>"; } if ($c > $_POST["rp"]) { break; } $data['rows'][] = array('id' => $c, 'cell' => array("<span style='font-size:{$fontsize}'>{$time}</a></span>", "<span style='font-size:{$fontsize}'>{$SITE}</a></span>", "<span style='font-size:{$fontsize}'>{$CATEGORY}</a></span>", "<span style='font-size:{$fontsize}'>{$MAC_link}{$MAC}</a></span>", "<span style='font-size:{$fontsize}'>{$IPADDR}</a></span>", "<span style='font-size:{$fontsize}'>{$USER}</a></span>", "<span style='font-size:{$fontsize}'>{$RQS}</a></span>", "<span style='font-size:{$fontsize}'>{$size}</a></span>")); } } $data['total'] = $c; echo json_encode($data); return; }
function parse() { $TimeFile = "/etc/artica-postfix/pids/exec.squid.interface-size.php.time"; $pidfile = "/etc/artica-postfix/pids/exec.squid.interface-size.php.pid"; $unix = new unix(); $sock = new sockets(); $EnableKerbAuth = intval($sock->GET_INFO("EnableKerbAuth")); $ActiveDirectoryEmergency = intval($sock->GET_INFO("ActiveDirectoryEmergency")); if ($ActiveDirectoryEmergency == 1) { $EnableKerbAuth = 0; } $pid = @file_get_contents($pidfile); if ($pid < 100) { $pid = null; } if ($unix->process_exists($pid, basename(__FILE__))) { $timepid = $unix->PROCCESS_TIME_MIN($pid); if ($GLOBALS["VERBOSE"]) { echo "{$pid} already executed since {$timepid}Mn\n"; } if (!$GLOBALS["FORCE"]) { if ($timepid < 14) { return; } $kill = $unix->find_program("kill"); unix_system_kill_force($pid); } } @file_put_contents($pidfile, getmypid()); if (!$GLOBALS["FORCE"]) { if (!$GLOBALS["VERBOSE"]) { $time = $unix->file_time_min($TimeFile); if ($time < 14) { echo "Current {$time}Mn, require at least 14mn\n"; return; } } } @unlink($TimeFile); @file_put_contents($TimeFile, time()); $sock = new sockets(); $SquidPerformance = intval($sock->GET_INFO("SquidPerformance")); events("Proxy performance set to {$SquidPerformance}"); build_progress("{refresh_dashboard_values}", 10); system_values(); $php = $unix->LOCATE_PHP5_BIN(); build_progress("{refresh_dashboard_values}", 11); $dateint = InfluxQueryFromUTC(strtotime("-48 hours")); $date = date("Y-m-d H:00:00", $dateint); $qSimple = new mysql(); $sql = "SELECT COUNT(ID) as tcount FROM squid_admin_mysql WHERE severity=0 AND zDate>'{$date}'"; $ligne = mysql_fetch_array($qSimple->QUERY_SQL($sql, "artica_events")); @file_put_contents("{$GLOBALS["BASEDIR"]}/WATCHDOG_COUNT_EVENTS", $ligne["tcount"]); @chmod("{$GLOBALS["BASEDIR"]}/WATCHDOG_COUNT_EVENTS", 0777); if ($SquidPerformance > 1) { if (is_file("/etc/cron.d/artica-stats-hourly")) { @unlink("/etc/cron.d/artica-stats-hourly"); system("/etc/init.d/cron reload"); } build_progress("{statistics_are_disabled}", 110); die; } if (!is_file("/etc/cron.d/artica-stats-hourly")) { @unlink("/etc/cron.d/artica-stats-hourly"); } @mkdir("/usr/share/artica-postfix/ressources/interface-cache", 0755, true); $t1 = time(); $q = new mysql_squid_builder(); $tables[] = "dashboard_size_day"; $tables[] = "dashboard_countwebsite_day"; $tables[] = "dashboard_countuser_day"; $tables[] = "dashboard_user_day"; $tables[] = "dashboard_notcached"; $tables[] = "dashboard_cached"; $tables[] = "dashboard_blocked_day"; while (list($num, $table) = each($array)) { if (!$q->TABLE_EXISTS($table)) { events("Table: {$table} is not yet ready..."); continue; } $NUM = $q->COUNT_ROWS($table); events("Table: {$table} {$NUM} rows"); } build_progress("{calculate_cache_rate}", 12); CachedOrNot(); squidhour_clean(); $t1 = time(); $influx = new influx(); $now = InfluxQueryFromUTC(strtotime("-24 hour")); build_progress("{refresh_dashboard_values}", 13); // ----------------------------------------------------------------------------------------------------- $COUNT_DE_SNI_CERTS_TIME = 1000000; $COUNT_DE_SNI_CERTS = "{$GLOBALS["BASEDIR"]}/COUNT_DE_SNI_CERTS"; if (is_file($COUNT_DE_SNI_CERTS)) { $COUNT_DE_SNI_CERTS_TIME = $unix->file_time_min($COUNT_DE_SNI_CERTS); } if ($GLOBALS["VERBOSE"]) { echo "COUNT_DE_SNI_CERTS_TIME = {$COUNT_DE_SNI_CERTS_TIME}\n"; $COUNT_DE_SNI_CERTS_TIME = 999999; } if ($COUNT_DE_SNI_CERTS_TIME > 60) { $sql = "SELECT COUNT(website) as tcount,website FROM sni_certs WHERE time > {$now}s GROUP BY time(24h),website"; $f = array(); echo $sql . "\n"; $main = $influx->QUERY_SQL($sql); foreach ($main as $row) { $website = $row->website; $count = intval($row->tcount); if ($GLOBALS["VERBOSE"]) { echo "SNI: {$website} -> {$count}\n"; } $f[] = "('{$count}','{$website}')"; } if (count($f) > 0) { $q = new mysql_squid_builder(); $q->QUERY_SQL("CREATE TABLE IF NOT EXISTS sni_certs (`hits` BIGINT UNSIGNED, `websites` VARCHAR(128) NOT NULL PRIMARY KEY, KEY `hits` (`hits`) ) ENGINE=MYISAM"); $q->QUERY_SQL("TRUNCATE TABLE sni_certs"); $q->QUERY_SQL("INSERT IGNORE INTO sni_certs (hits,websites) VALUES " . @implode(",", $f)); @unlink($COUNT_DE_SNI_CERTS); @file_put_contents($COUNT_DE_SNI_CERTS, count($f)); } } build_progress("{refresh_dashboard_values}", 14); $NETS = $unix->NETWORK_ALL_INTERFACES(); // ----------------------------------------------------------------------------------------------------- while (list($Interface, $array) = each($NETS)) { $sql = "SELECT SUM(RX) as size FROM ethrxtx WHERE time > {$now}s AND ETH='{$Interface}' GROUP BY time(10m) ORDER BY ASC"; if ($GLOBALS["VERBOSE"]) { echo "\n*****\n{$sql}\n******\n"; } $MAIN = array(); $xdata = array(); $ydata = array(); $main = $influx->QUERY_SQL($sql); foreach ($main as $row) { $time = InfluxToTime($row->time); $min = date("H:i", $time); $size = intval($row->size) / 1024; if ($GLOBALS["VERBOSE"]) { echo "({$time}): ethrxtx {$Interface}:RX: {$min} -> {$size}\n"; } $size = $size / 1024; if (round($size) == 0) { continue; } $xdata[] = $min; $ydata[] = round($size); } $MAIN["xdata"] = $xdata; $MAIN["ydata"] = $ydata; @file_put_contents("{$GLOBALS["BASEDIR"]}/FLUX_{$Interface}_RX", serialize($MAIN)); if (count($xdata) < 2) { @unlink("{$GLOBALS["BASEDIR"]}/FLUX_{$Interface}_RX"); } $sql = "SELECT SUM(TX) as size FROM ethrxtx WHERE time > {$now}s AND ETH='{$Interface}' GROUP BY time(10m) ORDER BY ASC"; $MAIN = array(); $xdata = array(); $ydata = array(); build_progress("{refresh_dashboard_values}", 15); $main = $influx->QUERY_SQL($sql); foreach ($main as $row) { $time = InfluxToTime($row->time); $min = date("H:i", $time); $size = intval($row->size) / 1024; $size = $size / 1024; if ($size == 0) { continue; } if ($GLOBALS["VERBOSE"]) { echo "ethrxtx {$Interface}:TX: {$min} -> {$size}\n"; } $xdata[] = $min; $ydata[] = round($size); } $MAIN["xdata"] = $xdata; $MAIN["ydata"] = $ydata; @file_put_contents("{$GLOBALS["BASEDIR"]}/FLUX_{$Interface}_TX", serialize($MAIN)); if (count($xdata) < 2) { @unlink("{$GLOBALS["BASEDIR"]}/FLUX_{$Interface}_TX"); } } // ----------------------------------------------------------------------------------------------------- build_progress("{cleaning_databases}", 16); squidhour_clean(); build_progress("{refresh_dashboard_values}", 17); FLUX_RQS(); build_progress("{refresh_dashboard_values}", 18); build_progress("{refresh_dashboard_values}", 19); USERAGENTS(); build_progress("{calculate_dates}", 20); MAX_MIN(); backup_size(); build_progress("{refresh_dashboard_values}", 21); WEBFILTERING(); build_progress("{refresh_dashboard_values}", 22); $f = array(); // ----------------------------------------------------------------------------------------------------- $q = new mysql_squid_builder(); $ligne = @mysql_fetch_array($q->QUERY_SQL("SELECT COUNT(*) as tcount FROM proxy_ports WHERE enabled=1 AND transparent=1 AND Tproxy=1")); if ($q->ok) { @file_put_contents("{$GLOBALS["BASEDIR"]}/COUNT_DE_TRANSPARENT", intval($ligne["tcount"])); } // ----------------------------------------------------------------------------------------------------- build_progress("{refresh_dashboard_values}", 51); $MAIN = array(); $xdata = array(); $ydata = array(); $f = array(); // ----------------------------------------------------------------------------------------------------- // Calcul des caches en cours. $SquidCacheLevel = $sock->GET_INFO("SquidCacheLevel"); if (!is_numeric($SquidCacheLevel)) { $SquidCacheLevel = 4; } if ($SquidCacheLevel == 0) { @file_put_contents("{$GLOBALS["BASEDIR"]}/COUNT_DE_CACHES", 0); } build_progress("{refresh_dashboard_values}", 52); $q = new mysql(); $sql = "SELECT cache_size,cache_type FROM squid_caches_center WHERE remove=0"; $xsize = 0; $results = $q->QUERY_SQL($sql, "artica_backup"); while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) { $cache_size = $ligne["cache_size"]; $cache_type = $ligne["cache_type"]; if ($cache_type == "Cachenull") { continue; } $xsize = $xsize + $cache_size; } if ($GLOBALS["VERBOSE"]) { echo "COUNT_DE_CACHES: {$xsize}MB\n"; } @file_put_contents("{$GLOBALS["BASEDIR"]}/COUNT_DE_CACHES", $xsize); if ($GLOBALS["PROGRESS"]) { build_progress("{refresh_dashboard_values}", 90); system("{$php} /usr/share/artica-postfix/exec.status.php --all --verbose"); } build_progress("{refresh_dashboard_values} {done}", 100); // ----------------------------------------------------------------------------------------------------- }
$ipaddr = gethostbyaddr($ipaddr); $time = time(); $q = new influx(); $line = "{$time}:::{$user}:::Infected:::Kaspersky-Antivirus:::{$public_ip}:::Security issue:::THREAT {$VirusName} DETECTED:::{$Clienthostname}:::{$www}:::{$local_ip}"; $q->insert_ufdb($line); } else { events("{$InfectedPath} -> no match", __FUNCTION__, __FILE__, __LINE__); } $sock = new sockets(); $sock->getFrameWork("system.php?parse-blocked=yes"); $SquidAutoblock = $sock->GET_INFO("SquidAutoblock"); events("SquidAutoblock={$SquidAutoblock}", __FUNCTION__, __FILE__, __LINE__); if ($sock->GET_INFO("SquidAutoblock") == 1) { $InfectedPath = str_replace(basename($InfectedPath), "", $InfectedPath); $sql = "INSERT INTO squid_block(uri,task_type,zDate)\n\tVALUES('{$InfectedPath}','autoblock {$VirusName}',NOW());"; $q->QUERY_SQL($sql, "artica_backup"); $sock->getFrameWork("cmd.php?squidnewbee=yes"); } function events($text, $function, $file, $line) { writelogs($text, $function, $file, $line); $pid = @getmypid(); $date = @date("H:i:s"); $logFile = "/var/log/kaspersky/kav4proxy/kav4proxy.threats.log"; $size = @filesize($logFile); if ($size > 1000000) { @unlink($logFile); } $f = @fopen($logFile, 'a'); @fwrite($f, "{$date} [{$pid}]:: " . basename(__FILE__) . "::{$line}:: {$text}\n"); @fclose($f);
function GRAB_DATAS($ligne, $md5) { $GLOBALS["zMD5"] = $md5; $params = unserialize($ligne["params"]); $influx = new influx(); $to = InfluxQueryFromUTC($params["TO"]); $from = InfluxQueryFromUTC($params["FROM"]); $interval = $params["INTERVAL"]; $user = $params["USER"]; $md5_table = "{$md5}sites"; $search = $params["SEARCH"]; $USER_FIELD = $params["USER"]; echo "FLOW: FROM {$from} to {$to} {$interval} user:{$user} {$search}\n"; if ($search == "*") { $search = null; } if ($search != null) { $search = str_replace("*", ".*", $search); $SSEARCH = " AND ({$USER_FIELD}=~ /{$search}/)"; } $sql = "SELECT {$user},SIZE FROM access_log WHERE (time >'" . date("Y-m-d H:i:s", $from) . "' and time < '" . date("Y-m-d H:i:s", $to) . "'){$SSEARCH}"; echo "{$sql}\n"; build_progress("{step} {waiting_data}: BigData engine, (websites) {please_wait}", 6); $main = $influx->QUERY_SQL($sql); foreach ($main as $row) { $time = InfluxToTime($row->time); $SIZE = intval($row->SIZE); $USER = $row->{$USER_FIELD}; if ($SIZE == 0) { continue; } if (!isset($MAIN_ARRAY[$USER])) { $MAIN_ARRAY[$USER] = $SIZE; } else { $MAIN_ARRAY[$USER] = $MAIN_ARRAY[$USER] + $SIZE; } } if (count($MAIN_ARRAY) == 0) { echo "MAIN_ARRAY is null....\n"; return false; } echo "MAIN_ARRAY (1) = " . count($MAIN_ARRAY) . "\n"; build_progress("{step} {insert_data}: MySQL engine, {please_wait}", 8); $f = array(); $GLOBALS["CSV1"][] = array("member", "SizeBytes"); $sql = "CREATE TABLE IF NOT EXISTS `{$md5}user` \n\t(`user` VARCHAR(128),`size` INT UNSIGNED NOT NULL DEFAULT 1,\n\tKEY `user`(`user`),\n\tKEY `size`(`size`)\n\t) ENGINE = MYISAM;"; $q = new mysql_squid_builder(); $q->QUERY_SQL($sql); if (!$q->ok) { echo $q->mysql_error; REMOVE_TABLES($md5); return false; } while (list($USER, $SIZE) = each($MAIN_ARRAY)) { $c = 0; $f[] = "('{$USER}','{$SIZE}')"; if ($GLOBALS["VERBOSE"]) { echo "{$USER} -> {$SIZE}\n"; } $GLOBALS["CSV1"][] = array($USER, $SIZE); if (count($f) > 500) { $q->QUERY_SQL("INSERT IGNORE INTO `{$md5}user` (user,size) VALUES " . @implode(",", $f)); if (!$q->ok) { echo $q->mysql_error; REMOVE_TABLES($md5); return false; } $f = array(); } } if (count($f) > 0) { $q->QUERY_SQL("INSERT IGNORE INTO `{$md5}user` (user,size) VALUES " . @implode(",", $f)); $f = array(); } echo "Members {$c} items inserted to MySQL\n"; return true; }
function memstats() { $time = time(); $page = CurrentPageName(); $influx = new influx(); $q = new mysql(); $_SESSION["SQUID_STATS_MEM_DATE1"] = $_GET["date1"]; $_SESSION["SQUID_STATS_MEM_TIME1"] = $_GET["time1"]; $_SESSION["SQUID_STATS_MEM_DATE2"] = $_GET["date2"]; $_SESSION["SQUID_STATS_MEM_TIME2"] = $_GET["time2"]; $from = strtotime("{$_GET["date1"]} {$_GET["time1"]}"); $to = strtotime("{$_GET["date2"]} {$_GET["time2"]}"); $md5 = md5("{$_GET["interval"]}{$from}{$to}"); $sql = "SELECT MEM_STATS FROM SYSTEM WHERE time > {$from}s and time < {$to}s GROUP BY time({$_GET["interval"]})"; $main = $influx->QUERY_SQL($sql); echo "// {$sql}"; $per["1m"] = "Y-m-d H:i:00"; $per["5m"] = "Y-m-d H:i:00"; $per["10m"] = "Y-m-d H:i:00"; $per["15m"] = "Y-m-d H:i:00"; $per["30m"] = "Y-m-d H:i:00"; $per["1h"] = "Y-m-d H:00"; $per["1d"] = "Y-m-d"; foreach ($main as $row) { $time = $row->time; $min = date($per[$_GET["interval"]], $time); $f[] = "('{$min}','{$row->MEM_STATS}')"; } $temptable = "tmp_{$md5}"; $sql = "CREATE TABLE IF NOT EXISTS `{$temptable}` (\n\t`zDate` DATETIME PRIMARY KEY,\n\t`size` INT UNSIGNED NOT NULL DEFAULT 1,\n\tKEY `size`(`size`)\n\t) ENGINE = MYISAM;"; $q->QUERY_SQL($sql, "artica_backup"); if (!$q->ok) { echo "//{$q->mysql_error}\n"; } $q->QUERY_SQL("INSERT IGNORE INTO `{$temptable}` (`zDate`,`size`) VALUES " . @implode(",", $f), "artica_backup"); $results = $q->QUERY_SQL("SELECT AVG(size) as MEM_STATS,zDate FROM {$temptable} GROUP BY zDate ORDER BY zDate", "artica_backup"); while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) { $time = strtotime($ligne["zDate"]); $xdata[] = date("H:i", $time); $ydata[] = $ligne["MEM_STATS"] / 1024; } $q->QUERY_SQL("DROP TABLE {$temptable}", "artica_backup"); $page = CurrentPageName(); $time = time(); $title = "{server_memory_consumption} (GB)"; $timetext = $_GET["interval"]; $highcharts = new highcharts(); $highcharts->container = $_GET["container"]; $highcharts->xAxis = $xdata; $highcharts->Title = $title; //$highcharts->subtitle="<a href=\"javascript:blur();\" OnClick=\"javascript:Loadjs('squid.rtt.php')\" style='font-size:16px;text-decoration:underline'>{realtime_flow}</a>"; $highcharts->TitleFontSize = "14px"; $highcharts->AxisFontsize = "12px"; $highcharts->yAxisTtitle = "GB"; $highcharts->xAxis_labels = true; $highcharts->LegendSuffix = "GB"; $highcharts->xAxisTtitle = $timetext; $highcharts->datas = array("{size}" => $ydata); echo $highcharts->BuildChart(); }
function xtstart() { $unix = new unix(); $sock = new sockets(); $pidfile = "/etc/artica-postfix/pids/" . basename(__FILE__) . "." . __FUNCTION__ . ".pid"; $weektime = "/etc/artica-postfix/pids/" . basename(__FILE__) . "." . __FUNCTION__ . ".week.time"; // /etc/artica-postfix/pids/exec.squid.rotate.php.build.time $sock = new sockets(); $unix = new unix(); $pid = $unix->get_pid_from_file($pidfile); if ($unix->process_exists($pid)) { echo "Already PID {$pid} is running\n"; die; } @file_put_contents($pidfile, getmypid()); $influx = new influx(); $UserAgentsStatistics = intval($sock->GET_INFO("UserAgentsStatistics")); $EnableKerbAuth = intval($sock->GET_INFO("EnableKerbAuth")); $USERMM = "MAC"; if ($EnableKerbAuth == 1) { $USERMM = "USERID"; } $time = strtotime('5 minutes ago'); $time_week = strtotime("-7 days"); if ($UserAgentsStatistics == 1) { $sql = "SELECT SUM(SIZE) as size,COUNT(RQS) as hits,USERAGENT,MAC,uid FROM useragents WHERE time> {$time}s GROUP BY time(10m),USERAGENT,MAC,uid ORDER BY ASC"; if ($GLOBALS["VERBOSE"]) { echo "{$sql}\n"; } $main = $influx->QUERY_SQL($sql); foreach ($main as $row) { $USERAGENT = $row->USERAGENT; $MAC = $row->MAC; $uid = $row->uid; $size = intval($row->size); $hits = intval($row->hits); if ($USERAGENT == null) { continue; } if ($size == null) { continue; } if ($size == 0) { continue; } $USERAGENT = mysql_escape_string2($USERAGENT); if ($GLOBALS["VERBOSE"]) { echo "MAIN_AGT: {$USERAGENT} -> {$size}\n"; } $uid = mysql_escape_string2($uid); $f[] = "('{$size}','{$hits}','{$USERAGENT}','{$MAC}','{$uid}')"; } $q = new mysql_squid_builder(); if (!$q->TABLE_EXISTS("current_useragnt10m")) { $q->QUERY_SQL("CREATE TABLE IF NOT EXISTS current_useragnt10m (\n\t\t\t\t`hits` BIGINT UNSIGNED,\n\t\t\t\t`size` BIGINT UNSIGNED,\n\t\t\t\t`USERAGENT` VARCHAR(256) NOT NULL,\n\t\t\t\t`MAC` VARCHAR(90) NOT NULL,\n\t\t\t\t`uid` VARCHAR(128) NOT NULL\n\t\t\t\t\t\n\t\t\t\t) ENGINE=MYISAM\n\t\t\t\t"); } if (!$q->TABLE_EXISTS("week_useragnt")) { $q->QUERY_SQL("CREATE TABLE IF NOT EXISTS week_useragnt (\n\t\t\t\t`hits` BIGINT UNSIGNED,\n\t\t\t\t`size` BIGINT UNSIGNED,\n\t\t\t\t`USERAGENT` VARCHAR(256) NOT NULL,\n\t\t\t\t`MAC` VARCHAR(90) NOT NULL,\n\t\t\t\t`uid` VARCHAR(128) NOT NULL\n\t\t\t\n\t\t\t\t) ENGINE=MYISAM\n\t\t\t\t"); } if (count($f) > 0) { $q->QUERY_SQL("TRUNCATE TABLE current_useragnt10m"); $q->QUERY_SQL("INSERT IGNORE INTO current_useragnt10m (hits,size,USERAGENT,MAC,uid) VALUES " . @implode(",", $f)); $f = array(); } $xtime = $unix->file_time_min($weektime); if ($GLOBALS["VERBOSE"]) { $xtime = 10000000; } if ($xtime > 360) { $sql = "SELECT SUM(SIZE) as size,COUNT(RQS) as hits,USERAGENT,MAC,uid FROM useragents WHERE time> {$time_week}s GROUP BY time(7d),USERAGENT,MAC,uid ORDER BY ASC"; if ($GLOBALS["VERBOSE"]) { echo "{$sql}\n"; } $main = $influx->QUERY_SQL($sql); $f = array(); foreach ($main as $row) { $USERAGENT = $row->USERAGENT; $MAC = $row->MAC; $uid = $row->uid; $size = intval($row->size); $hits = intval($row->hits); if ($USERAGENT == null) { continue; } if ($size == null) { continue; } if ($size == 0) { continue; } $USERAGENT = mysql_escape_string2($USERAGENT); if ($GLOBALS["VERBOSE"]) { echo "MAIN_AGT: {$USERAGENT} -> {$size}\n"; } $uid = mysql_escape_string2($uid); $f[] = "('{$size}','{$hits}','{$USERAGENT}','{$MAC}','{$uid}')"; } if (count($f) > 0) { $q->QUERY_SQL("TRUNCATE TABLE useragents"); $q->QUERY_SQL("INSERT IGNORE INTO useragents (hits,size,USERAGENT,MAC,uid) VALUES " . @implode(",", $f)); $f = array(); } @unlink($weektime); @file_put_contents($weektime, time()); } } $sql = "SELECT SIZE,RQS,{$USERMM} from access_log WHERE time> {$time}s"; if ($GLOBALS["VERBOSE"]) { echo "{$sql}\n"; } $MAIN = array(); $xdata = array(); $ydata = array(); $f = array(); $influx = new influx(); $main = $influx->QUERY_SQL($sql); $MEMBERS_COUNT = 0; foreach ($main as $row) { $USER = $row->{$USERMM}; $size = intval($row->SIZE); $hits = intval($row->RQS); if ($USER == null) { continue; } if ($size == 0) { continue; } if (!isset($XMAIN[$USER])) { $XMAIN[$USER]["SIZE"] = $size; $XMAIN[$USER]["HITS"] = $hits; } else { $XMAIN[$USER]["SIZE"] = $XMAIN[$USER]["SIZE"] + $size; $XMAIN[$USER]["HITS"] = $XMAIN[$USER]["HITS"] + $hits; } } while (list($USER, $ARRAY) = each($XMAIN)) { if ($GLOBALS["VERBOSE"]) { echo "{$USER} {$ARRAY["SIZE"]}/{$ARRAY["HITS"]}\n"; } $f[] = "('{$USER}','{$ARRAY["SIZE"]}','{$ARRAY["HITS"]}')"; } if ($GLOBALS["VERBOSE"]) { echo "RESULTS=" . count($f) . " members...\n"; } if (count($f) == 0) { return; } $q = new mysql_squid_builder(); if (!$q->TABLE_EXISTS("current_members10m")) { $q->QUERY_SQL("CREATE TABLE IF NOT EXISTS current_members10m (\n\t\t\t\t`hits` BIGINT UNSIGNED,\n\t\t\t\t`size` BIGINT UNSIGNED,\n\t\t\t\t`member` VARCHAR(128) NOT NULL PRIMARY KEY) ENGINE=MYISAM\n\t\t\t\t"); } if (count($f) > 0) { $q->QUERY_SQL("TRUNCATE TABLE current_members10m"); $q->QUERY_SQL("INSERT IGNORE INTO current_members10m (member,hits,size) VALUES " . @implode(",", $f)); $MEMBERS_COUNT = $q->COUNT_ROWS("current_members10m"); @file_put_contents("{$GLOBALS["BASEDIR"]}/MEMBERS_COUNT10M", $MEMBERS_COUNT); } $array["tags"]["proxyname"] = $unix->hostname_g(); $array["fields"]["members"] = $MEMBERS_COUNT; $influx->insert("members_count", $array); $f = array(); }