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 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 utc_time()
{
$time = time();
$TimeToInflux = TimeToInflux($time);
$InfluxToTime = InfluxToTime($TimeToInflux);
echo "Today is: {$time} (" . date("Y-m-d H:i:s") . ")\n";
echo "Inserting time in influxdb in UTC : \"{$TimeToInflux}\"\n";
echo "Query time in influxdb in UTC : \"{$TimeToInflux}\" == {$InfluxToTime} (" . date("Y-m-d H:i:s", $InfluxToTime) . ")\n";
}
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 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 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);
// -----------------------------------------------------------------------------------------------------
}
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 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 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 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_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 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"]);
$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 build_last_hour()
{
$sock = new sockets();
$influx = new influx();
$data = $influx->QUERY_SQL("SELECT MAX(ZDATE) as MAX from access_hour");
$date_end = InfluxQueryFromUTC($data[0]->MAX);
echo "Date Start: {$date_end}\n";
$LastHour = InfluxQueryFromUTC(strtotime("-1 hour"));
$ThisHour = strtotime(date("Y-m-d H:00:00", $LastHour));
if ($date_end > 0) {
$WHERE_TIME = "WHERE time > '" . date("Y-m-d H:i:s", $date_end) . "' AND time < '" . date("Y-m-d H:i:s", $ThisHour) . "'";
$seconds = InfluxQueryFromUTC(time()) - strtotime(date("Y-m-d H:00:00", $date_end));
$diff = abs($ThisHour - $date_end) / 60;
if ($GLOBALS["VERBOSE"]) {
echo "Last date saved is " . date("Y-m-d H:i:s", $date_end) . "({$date_end}) To " . date("Y-m-d H:i:s", $ThisHour) . " ({$ThisHour}) {$diff}mn..\n";
}
if ($GLOBALS["VERBOSE"]) {
echo "{$WHERE_TIME}\n";
}
if ($diff < 60) {
if ($GLOBALS["VERBOSE"]) {
echo "{$diff}mn < 60 aborting...\n";
}
return;
}
} else {
$WHERE_TIME = "WHERE time < '" . date("Y-m-d H:i:s", $ThisHour) . "'";
}
if ($GLOBALS["VERBOSE"]) {
echo "Query From " . date("Y-m-d H:i:s", $date_end) . " to " . date("Y-m-d H:i:s", $ThisHour) . "\n";
}
$sql = "SELECT SIZE,RQS,FAMILYSITE,USERID,IPADDR,MAC,CATEGORY FROM access_log {$WHERE_TIME}";
if ($GLOBALS["VERBOSE"]) {
echo "{$sql}\n";
}
if ($GLOBALS["VERBOSE"]) {
echo "*******************************************\n";
}
if ($GLOBALS["VERBOSE"]) {
echo "\n";
}
$main = $influx->QUERY_SQL($sql);
$MAIN_ARRAY = array();
$catz = new mysql_catz();
if ($GLOBALS["VERBOSE"]) {
echo count($main) . " elements";
}
$xtime = 0;
foreach ($main as $row) {
$time = InfluxToTime($row->time);
$Time_hour = date("Y-m-d H:00:00", $time);
$size = intval($row->SIZE);
$RQS = intval($row->RQS);
$FAMILYSITE = $row->FAMILYSITE;
$CATEGORY = $row->CATEGORY;
if ($CATEGORY == null) {
$CATEGORY = $catz->GET_CATEGORIES($FAMILYSITE);
}
$USERID = $row->USERID;
$IPADDR = $row->IPADDR;
$MAC = $row->MAC;
//if($GLOBALS["VERBOSE"]){echo "$row->time] [$Time_hour] $FAMILYSITE {$size}Bytes, $CATEGORY $USERID/$IPADDR/$MAC\n";}
if ($size == 0) {
continue;
}
if ($RQS == 0) {
continue;
}
if ($time > $xtime) {
$xtime = $time;
}
$MD5KEY = md5("{$CATEGORY}{$USERID}{$FAMILYSITE}{$IPADDR}{$MAC}");
if (!isset($MAIN_ARRAY[$Time_hour][$MD5KEY])) {
$MAIN_ARRAY[$Time_hour][$MD5KEY]["FAMILYSITE"] = $FAMILYSITE;
$MAIN_ARRAY[$Time_hour][$MD5KEY]["CATEGORY"] = $CATEGORY;
$MAIN_ARRAY[$Time_hour][$MD5KEY]["USERID"] = $USERID;
$MAIN_ARRAY[$Time_hour][$MD5KEY]["IPADDR"] = $IPADDR;
$MAIN_ARRAY[$Time_hour][$MD5KEY]["MAC"] = $MAC;
$MAIN_ARRAY[$Time_hour][$MD5KEY]["size"] = $size;
$MAIN_ARRAY[$Time_hour][$MD5KEY]["hits"] = $RQS;
$MAIN_ARRAY[$Time_hour][$MD5KEY]["ZDATE"] = $time;
} else {
$MAIN_ARRAY[$Time_hour][$MD5KEY]["size"] = $MAIN_ARRAY[$Time_hour][$MD5KEY]["size"] + $size;
$MAIN_ARRAY[$Time_hour][$MD5KEY]["hits"] = $MAIN_ARRAY[$Time_hour][$MD5KEY]["hits"] + $RQS;
if ($time > $MAIN_ARRAY[$Time_hour][$MD5KEY]["ZDATE"]) {
$MAIN_ARRAY[$Time_hour][$MD5KEY]["ZDATE"] = $time;
}
}
}
$sq = new influx();
if (count($MAIN_ARRAY) == 0) {
if ($GLOBALS["VERBOSE"]) {
echo "No array....\n";
}
}
while (list($ztime, $array) = each($MAIN_ARRAY)) {
while (list($md5, $Tarray) = each($array)) {
$sdate = $ztime;
$USERID = $Tarray["USERID"];
$IPADDR = $Tarray["IPADDR"];
$MAC = $Tarray["MAC"];
$FAMILYSITE = $Tarray["FAMILYSITE"];
$CATEGORY = $Tarray["CATEGORY"];
$size = $Tarray["size"];
$RQS = $Tarray["hits"];
$ZDATE = $Tarray["ZDATE"];
if ($GLOBALS["VERBOSE"]) {
echo date("Y-m-d H:i:s", $ZDATE) . " {$USERID}/{$IPADDR}/{$MAC} -> [{$FAMILYSITE}/{$CATEGORY}] {$size}/{$RQS}\n";
}
$zArray = array();
$zArray["fields"]["time"] = $sdate;
$zArray["tags"]["CATEGORY"] = $CATEGORY;
$zArray["tags"]["USERID"] = $USERID;
$zArray["tags"]["IPADDR"] = $IPADDR;
$zArray["tags"]["MAC"] = $MAC;
$zArray["fields"]["SIZE"] = intval($size);
$zArray["tags"]["FAMILYSITE"] = $FAMILYSITE;
$zArray["fields"]["RQS"] = intval($RQS);
$zArray["fields"]["ZDATE"] = $ZDATE;
$sq->insert("access_hour", $zArray);
}
}
$data = $influx->QUERY_SQL("SELECT MAX(ZDATE) as MAX from access_hour");
$date_end = $data[0]->MAX;
if ($GLOBALS["VERBOSE"]) {
echo "Query was {$WHERE_TIME}\n";
}
if ($GLOBALS["VERBOSE"]) {
echo "Last time : {$xtime} " . date("Y-m-d H:i:s", $xtime) . "\n";
}
if ($GLOBALS["VERBOSE"]) {
echo "Last date saved is " . date("Y-m-d H:i:s", $date_end) . "({$date_end}) To " . date("Y-m-d H:i:s", $ThisHour) . " ({$ThisHour}) {$diff}mn..\n";
}
if ($GLOBALS["VERBOSE"]) {
echo "Last final access_hour saved date {$date_end} - " . date("Y-m-d H:i:s", $date_end) . "\n";
}
}