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