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 BUILD_REPORT($md5) { build_progress("{building_query}", 5); $unix = new unix(); $q = new mysql_squid_builder(); $ligne = mysql_fetch_array($q->QUERY_SQL("SELECT * FROM reports_cache WHERE `zmd5`='{$md5}'")); $params = unserialize($ligne["params"]); $influx = new influx(); $from = InfluxQueryFromUTC($params["FROM"]); $to = InfluxQueryFromUTC($params["TO"]); $interval = $params["INTERVAL"]; $md5_table = $md5; $searchsites_sql = null; $USER_FIELD = $params["USER"]; $SEARCH = $params["SEARCH"]; if ($SEARCH != null) { $searchuser_sql = str_replace("*", "%", $SEARCH); if (strpos(" {$SEARCH}", "%") > 0) { $searchuser_sql = " HAVING {$USER_FIELD} LIKE '{$SEARCH}'"; } else { $searchuser_sql = " HAVING `{$USER_FIELD}`='{$SEARCH}'"; } } if (!GRAB_DATAS($ligne, $md5)) { build_progress("{unable_to_query_to_bigdata}", 110); return; } $q = new mysql_squid_builder(); //zDate,familysite,user,size,hits $sql = "SELECT SUM(SIZE) as SIZE, SUM(RQS) as RQS,{$USER_FIELD},`ZDATE` FROM `tmp_{$md5}user` \n\tGROUP BY {$USER_FIELD},`ZDATE` {$searchuser_sql} ORDER BY `ZDATE`"; echo "********** SQL **********\n"; echo $sql . "\n"; echo "********** SQL **********\n"; $results = $q->QUERY_SQL($sql); if (!$q->ok) { echo "{$sql}\n{$q->mysql_error}\n"; build_progress("MySQL error [" . __LINE__ . "]", 110); return; } $c = 0; while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) { $time = $ligne["ZDATE"]; $c++; if (intval($ligne["SIZE"]) == 0) { continue; } $size = $ligne["SIZE"] / 1024; $size = round($size / 1024, 2); $xdata[] = $ligne["ZDATE"]; $ydata[] = $size; } echo "Building chronology\n"; $sql = "SELECT SUM(SIZE) as SIZE, SUM(RQS) as RQS,`ZDATE`,FAMILYSITE,{$USER_FIELD}\n\tFROM `tmp_{$md5}user` GROUP BY FAMILYSITE,{$USER_FIELD},ZDATE {$searchuser_sql} ORDER BY ZDATE"; echo "********** SQL **********\n"; echo $sql . "\n"; echo "********** SQL **********\n"; $results = $q->QUERY_SQL($sql); if (!$q->ok) { echo "{$sql}\n{$q->mysql_error}\n"; build_progress("MySQL error [" . __LINE__ . "]", 110); return; } $c = 0; $MAIN["CSV"][] = array("time", "bytes", "site", "hits"); while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) { $c++; $time = $ligne["ZDATE"]; if ($GLOBALS["VERBOSE"]) { echo "CHRONOS: {$ligne["FAMILYSITE"]} {$time}'\n"; } $MAIN["CRONOS"][] = array("TIME" => $time, "BYTES" => $ligne["SIZE"], "SITE" => $ligne["FAMILYSITE"], "RQS" => $ligne["RQS"]); $MAIN["CSV"][] = array("{$time}", "{$ligne["SIZE"]}", "{$ligne["FAMILYSITE"]}", "{$ligne["RQS"]}"); } $MAIN["GRAPH1"]["xdata"] = $xdata; $MAIN["GRAPH1"]["ydata"] = $ydata; if ($c == 0) { build_progress("{chronology}: {failed} C == 0", 110); return; } //---------------------------------------------------------------------------------------------------------- $results = $q->QUERY_SQL("SELECT USERID as uid,IPADDR as ipaddr,MAC as mac FROM `tmp_{$md5}user` GROUP BY uid,ipaddr,mac"); build_progress("{parsing_data} (2)", 25); while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) { $IDENT[] = array("IPADDR" => $ligne["ipaddr"], "USERID" => $ligne["uid"], "MAC" => $ligne["mac"]); } //---------------------------------------------------------------------------------------------------------- $sql = "SELECT SUM(SIZE) as SIZE,FAMILYSITE,{$USER_FIELD} FROM `tmp_{$md5}user` \n\tGROUP BY FAMILYSITE,{$USER_FIELD} {$searchuser_sql} ORDER BY SIZE DESC"; echo "********** SQL **********\n"; echo $sql . "\n"; echo "********** SQL **********\n"; $results = $q->QUERY_SQL($sql); build_progress("{parsing_data} (2)", 30); if (!$q->ok) { echo "{$sql}\n{$q->mysql_error}\n"; build_progress("MySQL error [" . __LINE__ . "]", 110); return; } $tt = 0; while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) { $size = $ligne["SIZE"]; $FAMILYSITE = $ligne["FAMILYSITE"]; $size = $size / 1024; $PieData2[$FAMILYSITE] = $size; if ($GLOBALS["VERBOSE"]) { echo "PIE DATA: {$FAMILYSITE}' -> {$size}\n"; } if ($tt < 11) { $MAIN["GRAPH2"]["PIEDATA"][$FAMILYSITE] = $size; $MAIN["GRAPH2"]["TABLE"][$FAMILYSITE] = $size; } $tt++; $MAIN["FAMS"][$FAMILYSITE] = $ligne["SIZE"]; } //---------------------------------------------------------------------------------------------------------- echo "MD5:{$GLOBALS["zMD5"]}\n"; REMOVE_TABLES($GLOBALS["zMD5"]); $encoded_data = base64_encode(serialize($MAIN)); $datasize = strlen($encoded_data); echo "Saving " . strlen($encoded_data) . " bytes...\n"; $q->QUERY_SQL("UPDATE reports_cache SET `builded`=1,`values`='{$encoded_data}',`values_size`='{$datasize}' WHERE `zmd5`='{$GLOBALS["zMD5"]}'"); if (!$q->ok) { echo $q->mysql_error . "\n"; build_progress("MySQL {failed}", 110); return; } build_progress("{success}", 100); }
function BUILD_REPORT($md5) { build_progress("{building_query}", 5); $unix = new unix(); $q = new mysql_squid_builder(); $ligne = mysql_fetch_array($q->QUERY_SQL("SELECT * FROM reports_cache WHERE `zmd5`='{$md5}'")); $params = unserialize($ligne["params"]); $influx = new influx(); $mintime = strtotime("2008-01-01 00:00:00"); $params["TO"] = intval($params["TO"]); $params["FROM"] = abs(intval($params["FROM"])); if ($params["FROM"] < $mintime) { $params["FROM"] = strtotime(date("Y-m-d 00:00:00")); } $params["TO"] = intval($params["TO"]); if ($params["TO"] < $mintime) { $params["TO"] = time(); } $influx = new influx(); $to = InfluxQueryFromUTC($params["TO"]); $from = InfluxQueryFromUTC($params["FROM"]); $interval = $params["INTERVAL"]; $user = $params["USER"]; $md5_table = $md5; if (!GRAB_DATAS($ligne, $md5)) { build_progress("{unable_to_query_to_bigdata}", 110); return false; } $q = new postgres_sql(); $q->QUERY_SQL("COPY (SELECT * from \"{$md5}report\") To '/tmp/{$md5}report.csv' with CSV HEADER;"); $values_size = @filesize("/tmp/{$md5}report.csv"); $values = mysql_escape_string2(@file_get_contents("/tmp/{$md5}report.csv")); echo "MD5:{$GLOBALS["zMD5"]} {$values_size}Bytes " . FormatBytes($values_size / 1024) . "\n"; $q = new mysql_squid_builder(); $q->QUERY_SQL("UPDATE reports_cache SET `builded`=1,`values`='{$values}',`values_size`='{$values_size}' WHERE `zmd5`='{$GLOBALS["zMD5"]}'"); if (!$q->ok) { echo $q->mysql_error . "\n"; build_progress("PostGreSQL {failed}", 110); return; } build_progress("{success}", 100); }
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 (!is_file("/etc/artica-postfix/settings/Daemons/SQUIDEnable")) { @file_put_contents("/etc/artica-postfix/settings/Daemons/SQUIDEnable", 1); } 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); build_progress("{refresh_dashboard_values} APACHE_STATISTICS (2)", 11); APACHE_STATISTICS(); build_progress("{refresh_dashboard_values} NETWORK_INTERFACES_RXTX (3)", 11); NETWORK_INTERFACES_RXTX(); build_progress("{refresh_dashboard_values} COUNT_OF_SURICATA (4)", 11); COUNT_OF_SURICATA(); build_progress("{refresh_dashboard_values} NOT_CATEGORIZED (5)", 11); NOT_CATEGORIZED(); $SQUIDEnable = intval(@file_get_contents("/etc/artica-postfix/settings/Daemons/SQUIDEnable")); if ($SQUIDEnable == 0) { build_progress("{done}", 100); return; } 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($tables)) { 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); // ----------------------------------------------------------------------------------------------------- build_progress("{refresh_dashboard_values}", 14); // ----------------------------------------------------------------------------------------------------- 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 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 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 BUILD_REPORT($md5) { build_progress("{building_query}", 5); $unix = new unix(); $q = new mysql_squid_builder(); $ligne = mysql_fetch_array($q->QUERY_SQL("SELECT * FROM reports_cache WHERE `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; if (!GRAB_DATAS($ligne, $md5)) { build_progress("{unable_to_query_to_bigdata}", 110); } $q = new mysql_squid_builder(); $per["10m"] = "DATE_FORMAT(zDate,'%m-%d %Hh') as tdate"; $per["1h"] = "DATE_FORMAT(zDate,'%m-%d %Hh') as tdate"; $per["1d"] = "DATE_FORMAT(zDate,'%m-%d') as tdate"; $per["1w"] = "DATE_FORMAT(zDate,'%U') as tdate"; $per["30d"] = "DATE_FORMAT(zDate,'%m') as tdate"; $datformat = $per[$interval]; $results = $q->QUERY_SQL("SELECT user,size FROM `{$md5}user` ORDER BY size DESC"); build_progress("{parsing_data} (2)", 25); $c = 0; while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) { $USER = $ligne["user"]; $SIZE = $ligne["size"]; $BIGDATA[$USER] = $SIZE; $c++; } build_progress("{$c} {rows}", 8); echo "{$c} rows....\n"; REMOVE_TABLES($md5); $encoded_data = base64_encode(serialize($BIGDATA)); $datasize = strlen($encoded_data); echo "Saving " . strlen($encoded_data) . " bytes...\n"; $q->QUERY_SQL("UPDATE reports_cache SET `builded`=1,`values`='{$encoded_data}',`values_size`='{$datasize}' WHERE `zmd5`='{$GLOBALS["zMD5"]}'"); if (!$q->ok) { echo $q->mysql_error . "\n"; build_progress("MySQL {failed}", 110); return; } build_progress("{success}", 100); }
function xsFLOW($md5) { build_progress("{building_query}", 5); $unix = new unix(); $q = new mysql_squid_builder(); $ligne = mysql_fetch_array($q->QUERY_SQL("SELECT * FROM reports_cache WHERE `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; if (!GRAB_DATAS($ligne, $md5)) { build_progress("{unable_to_query_to_bigdata}", 110); } $q = new mysql_squid_builder(); $per["10m"] = "DATE_FORMAT(zDate,'%m-%d %Hh') as tdate"; $per["1h"] = "DATE_FORMAT(zDate,'%m-%d %Hh') as tdate"; $per["1d"] = "DATE_FORMAT(zDate,'%m-%d') as tdate"; $per["1w"] = "DATE_FORMAT(zDate,'%U') as tdate"; $per["30d"] = "DATE_FORMAT(zDate,'%m') as tdate"; $datformat = $per[$interval]; $sql = "SELECT SUM(size) as size,{$datformat} FROM `{$md5}sites` GROUP BY tdate ORDER BY tdate"; echo "{$sql}\n"; $results = $q->QUERY_SQL($sql); build_progress("{parsing_data} (2)", 25); $c = 0; while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) { $size = $ligne["size"] / 1024; $size = round($size / 1024); if ($GLOBALS["VERBOSE"]) { echo "{$ligne["tdate"]} = {$size}MB\n"; } if ($size == 0) { continue; } $c++; $xdata[] = $ligne["tdate"]; $ydata[] = $size; } build_progress("{$c} {rows}", 8); echo "{$c} rows....\n"; if (count($xdata) < 2) { $q->QUERY_SQL("DROP TABLE `{$md5}sites`"); build_progress("{$c} {rows} ({only})", 110); REMOVE_TABLES($md5); return; } $time = time(); $MAIN["GRAPH1"]["xdata"] = $xdata; $MAIN["GRAPH1"]["ydata"] = $ydata; $xdata = array(); $ydata = array(); $sql = "SELECT SUM(size) as size,familysite FROM `{$md5}sites` GROUP BY familysite ORDER BY size DESC LIMIT 0,10"; echo "{$sql}\n"; $results = $q->QUERY_SQL($sql); build_progress("{parsing_data} (2)", 30); $c = 0; while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) { $size = $ligne["size"]; $FAMILYSITE = $ligne["familysite"]; $PieData2[$FAMILYSITE] = $size; $size = $size / 1024; $size = round($size / 1024, 2); $PieData[$FAMILYSITE] = $size; $c++; } $MAIN["GRAPH2"]["PIEDATA"] = $PieData; $MAIN["GRAPH2"]["TABLE"] = $PieData2; build_progress("{saving}", 50); if ($GLOBALS["zMD5"] == null) { build_progress("MD5 - > NULL {failed}", 110); return; } build_progress("{$c} {rows}", 60); build_progress("{building_query} {$user} (3)", 70); $results = $q->QUERY_SQL("SELECT user,size FROM `{$md5}users` ORDER BY size DESC LIMIT 0,20"); build_progress("{parsing_data} {$user} (3)", 90); while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) { $size = $ligne["size"]; $FAMILYSITE = $ligne["user"]; $PieData4[$FAMILYSITE] = $size; $size = $size / 1024; $size = round($size / 1024, 2); $PieData3[$FAMILYSITE] = $size; } $MAIN["GRAPH3"]["PIEDATA"] = $PieData3; $MAIN["GRAPH3"]["TABLE"] = $PieData4; $MAIN["GRAPH3"]["TYPE"] = $user; $MAIN["CSV1"] = $GLOBALS["CSV1"]; $MAIN["CSV2"] = $GLOBALS["CSV2"]; echo "MD5:{$GLOBALS["zMD5"]}\n"; REMOVE_TABLES($md5); $encoded_data = base64_encode(serialize($MAIN)); $datasize = strlen($encoded_data); echo "Saving " . strlen($encoded_data) . " bytes...\n"; $q->QUERY_SQL("UPDATE reports_cache SET `builded`=1,`values`='{$encoded_data}',`values_size`='{$datasize}' WHERE `zmd5`='{$GLOBALS["zMD5"]}'"); if (!$q->ok) { echo $q->mysql_error . "\n"; build_progress("MySQL {failed}", 110); return; } build_progress("{success}", 100); }
function BUILD_REPORT($md5) { build_progress("{building_query}", 5); $unix = new unix(); $q = new mysql_squid_builder(); $ligne = mysql_fetch_array($q->QUERY_SQL("SELECT * FROM reports_cache WHERE `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; if (!GRAB_DATAS($ligne, $md5)) { build_progress("{unable_to_query_to_bigdata}", 110); return; } $q = new mysql_squid_builder(); $per["10m"] = "DATE_FORMAT(zDate,'%m-%d %Hh') as tdate"; $per["1h"] = "DATE_FORMAT(zDate,'%m-%d %Hh') as tdate"; $per["1d"] = "DATE_FORMAT(zDate,'%m-%d') as tdate"; $per["1w"] = "DATE_FORMAT(zDate,'%U') as tdate"; $per["30d"] = "DATE_FORMAT(zDate,'%m') as tdate"; $datformat = $per[$interval]; //---------------------------------------------------------------------------------------------------------- $results = $q->QUERY_SQL("SELECT SUM(size) as size,familysite FROM `{$md5}user` GROUP BY familysite ORDER BY size DESC LIMIT 0,10"); build_progress("{parsing_data} (2)", 25); $c = 0; while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) { $size = $ligne["size"]; $size = round($size / 1024); $c++; $FAMILYSITE = $ligne["familysite"]; $TOP_WEBSITES_SIZE[$FAMILYSITE] = $size; } build_progress("{$c} {rows}", 30); //---------------------------------------------------------------------------------------------------------- $results = $q->QUERY_SQL("SELECT SUM(hits) as hits,familysite FROM `{$md5}user` GROUP BY familysite ORDER BY hits DESC LIMIT 0,10"); build_progress("{parsing_data} (2)", 25); while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) { $hits = $ligne["hits"]; $FAMILYSITE = $ligne["familysite"]; $TOP_WEBSITES_HITS[$FAMILYSITE] = $hits; } //---------------------------------------------------------------------------------------------------------- $results = $q->QUERY_SQL("SELECT SUM(size) as size,{$user} FROM `{$md5}user` GROUP BY {$user} ORDER BY size DESC LIMIT 0,10"); build_progress("{parsing_data} (2)", 25); while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) { $size = $ligne["size"]; $size = round($size / 1024); $USER = $ligne[$user]; echo "USER: {$USER} (" . FormatBytes($size) . ")\n"; $TOP_WEBSITES_MEMBERS[$USER] = $size; } //---------------------------------------------------------------------------------------------------------- $MAIN["CSV"] = $GLOBALS["CSV1"]; $MAIN["TOP_WEBSITES_SIZE"] = $TOP_WEBSITES_SIZE; $MAIN["TOP_WEBSITES_MEMBERS"] = $TOP_WEBSITES_MEMBERS; $MAIN["TOP_WEBSITES_HITS"] = $TOP_WEBSITES_HITS; echo "MD5:{$GLOBALS["zMD5"]}\n"; REMOVE_TABLES($GLOBALS["zMD5"]); $encoded_data = base64_encode(serialize($MAIN)); $datasize = strlen($encoded_data); echo "Saving " . strlen($encoded_data) . " bytes...\n"; $q->QUERY_SQL("UPDATE reports_cache SET `builded`=1,`values`='{$encoded_data}',`values_size`='{$datasize}' WHERE `zmd5`='{$GLOBALS["zMD5"]}'"); if (!$q->ok) { echo $q->mysql_error . "\n"; build_progress("MySQL {failed}", 110); return; } build_progress("{success}", 100); }
function BUILD_REPORT($md5) { build_progress("{building_query}", 5); $unix = new unix(); $q = new mysql_squid_builder(); $ligne = mysql_fetch_array($q->QUERY_SQL("SELECT * FROM reports_cache WHERE `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; if (!GRAB_DATAS($ligne, $md5)) { build_progress("{unable_to_query_to_bigdata}", 110); return; } $q = new mysql_squid_builder(); $per["10m"] = "DATE_FORMAT(zDate,'%m-%d %Hh') as tdate"; $per["1h"] = "DATE_FORMAT(zDate,'%m-%d %Hh') as tdate"; $per["1d"] = "DATE_FORMAT(zDate,'%m-%d') as tdate"; $per["1w"] = "DATE_FORMAT(zDate,'%U') as tdate"; $per["30d"] = "DATE_FORMAT(zDate,'%m') as tdate"; $datformat = $per[$interval]; $md5_table = "`{$md5}user`"; //---------------------------------------------------------------------------------------------------------- $sql = "SELECT SUM(hits) as hits,UID,CLIENT FROM {$md5_table} GROUP BY UID,CLIENT ORDER by hits DESC LIMIT 0,15"; $results = $q->QUERY_SQL($sql); build_progress("{parsing_data} (2)", 15); $c = 0; while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) { $c++; $MAIN["GRAPH1"][$ligne["UID"] . "/" . $ligne["CLIENT"]] = $ligne["hits"]; } build_progress("{$c} {rows}", 30); //---------------------------------------------------------------------------------------------------------- $sql = "SELECT SUM(hits) as hits,CATEGORY FROM {$md5_table} GROUP BY CATEGORY ORDER by hits DESC LIMIT 0,15"; $results = $q->QUERY_SQL($sql); build_progress("{parsing_data} (2)", 15); while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) { $MAIN["GRAPH2"][$ligne["CATEGORY"]] = $ligne["hits"]; } //---------------------------------------------------------------------------------------------------------- $sql = "SELECT SUM(hits) as hits,RULENAME FROM {$md5_table} GROUP BY RULENAME ORDER by hits DESC LIMIT 0,15"; $results = $q->QUERY_SQL($sql); build_progress("{parsing_data} (2)", 15); while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) { $MAIN["GRAPH3"][$ligne["RULENAME"]] = $ligne["hits"]; } //---------------------------------------------------------------------------------------------------------- $sql = "SELECT SUM(hits) as hits,WEBSITE FROM {$md5_table} GROUP BY WEBSITE ORDER by hits DESC LIMIT 0,15"; $results = $q->QUERY_SQL($sql); build_progress("{parsing_data} (2)", 15); while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) { $MAIN["GRAPH4"][$ligne["WEBSITE"]] = $ligne["hits"]; } //---------------------------------------------------------------------------------------------------------- $MAIN["csv"] = $GLOBALS["CSV1"]; echo "MD5:{$GLOBALS["zMD5"]}\n"; REMOVE_TABLES($GLOBALS["zMD5"]); $encoded_data = base64_encode(serialize($MAIN)); $datasize = strlen($encoded_data); echo "Saving " . strlen($encoded_data) . " bytes...\n"; $q->QUERY_SQL("UPDATE reports_cache SET `builded`=1,`values`='{$encoded_data}',`values_size`='{$datasize}' WHERE `zmd5`='{$GLOBALS["zMD5"]}'"); if (!$q->ok) { echo $q->mysql_error . "\n"; build_progress("MySQL {failed}", 110); return; } build_progress("{success}", 100); }
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 BUILD_REPORT($md5) { build_progress("{building_query}", 5); $unix = new unix(); $q = new mysql_squid_builder(); $ligne = mysql_fetch_array($q->QUERY_SQL("SELECT * FROM reports_cache WHERE `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; if (!GRAB_DATAS($ligne, $md5)) { build_progress("{unable_to_query_to_bigdata}", 110); return; } $q = new mysql_squid_builder(); $per["10m"] = "DATE_FORMAT(zDate,'%m-%d %Hh') as tdate"; $per["1h"] = "DATE_FORMAT(zDate,'%m-%d %Hh') as tdate"; $per["1d"] = "DATE_FORMAT(zDate,'%m-%d') as tdate"; $per["1w"] = "DATE_FORMAT(zDate,'%U') as tdate"; $per["30d"] = "DATE_FORMAT(zDate,'%m') as tdate"; $datformat = $per[$interval]; $md5_table = "`{$md5}user`"; //---------------------------------------------------------------------------------------------------------- $sql = "SELECT {$datformat},SUM(size) as size FROM {$md5_table} GROUP BY tdate ORDER by tdate"; build_progress("{parsing_data} (1)", 7); $results = $q->QUERY_SQL($sql); if (!$q->ok) { echo $q->mysql_error . "\n"; build_progress("{parsing_data} (1) {failed}", 110); REMOVE_TABLES($md5); die; } $c = 0; while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) { $ligne["size"] = round($ligne["size"] / 1024); $MAIN["GRAPH0"]["ydata"][] = $ligne["size"]; $MAIN["GRAPH0"]["xdata"][] = $ligne["tdate"]; $c++; } build_progress("{$c} {rows}", 30); //---------------------------------------------------------------------------------------------------------- $sql = "SELECT {$datformat},SUM(hits) as hits FROM {$md5_table} GROUP BY zdate ORDER by tdate"; build_progress("{parsing_data}", 35); $results = $q->QUERY_SQL($sql); if (!$q->ok) { echo $q->mysql_error . "\n"; build_progress("{parsing_data} {failed}", 110); REMOVE_TABLES($md5); die; } if (mysql_num_rows($results) == 0) { build_progress("{parsing_data} {no_data}", 7); echo $sql . "\n"; sleep(10); REMOVE_TABLES($md5); build_progress("{parsing_data} {failed}", 110); } while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) { $MAIN["GRAPH1"]["ydata"][] = $ligne["hits"]; $MAIN["GRAPH1"]["xdata"][] = $ligne["tdate"]; } //---------------------------------------------------------------------------------------------------------- $sql = "SELECT SUM(size) as size,familysite FROM {$md5_table} GROUP BY familysite ORDER by size DESC LIMIT 0,15"; $results = $q->QUERY_SQL($sql); if (!$q->ok) { echo $q->mysql_error . "\n"; build_progress("{parsing_data} {failed}", 110); REMOVE_TABLES($md5); die; } if (mysql_num_rows($results) == 0) { build_progress("{parsing_data} {no_data}", 7); echo $sql . "\n"; sleep(10); REMOVE_TABLES($md5); build_progress("{parsing_data} {failed}", 110); } build_progress("{parsing_data}", 40); while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) { $ligne["size"] = round($ligne["size"] / 1024); $MAIN["GRAPH2"][$ligne["familysite"]] = $ligne["size"]; } //---------------------------------------------------------------------------------------------------------- $sql = "SELECT SUM(size) as size,{$user} FROM {$md5_table} GROUP BY {$user} ORDER by size DESC LIMIT 0,15"; $results = $q->QUERY_SQL($sql); if (!$q->ok) { echo $q->mysql_error . "\n"; build_progress("{parsing_data} {failed}", 110); REMOVE_TABLES($md5); die; } if (mysql_num_rows($results) == 0) { build_progress("{parsing_data} {no_data}", 7); echo $sql . "\n"; sleep(10); REMOVE_TABLES($md5); build_progress("{parsing_data} {failed}", 110); } build_progress("{parsing_data}", 45); while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) { $ligne["size"] = round($ligne["size"] / 1024); $MAIN["GRAPH3"][$ligne[$user]] = $ligne["size"]; } //---------------------------------------------------------------------------------------------------------- $sql = "SELECT SUM(hits) as hits,category FROM {$md5_table} GROUP BY category ORDER by hits DESC LIMIT 0,15"; $results = $q->QUERY_SQL($sql); build_progress("{parsing_data}", 50); while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) { $category = $ligne["category"]; if ($category == null) { $category = "unknown"; } $MAIN["GRAPH4"][$category] = $ligne["hits"]; } //---------------------------------------------------------------------------------------------------------- $sql = "SELECT SUM(size) as size,category FROM {$md5_table} GROUP BY category ORDER by size DESC LIMIT 0,15"; $results = $q->QUERY_SQL($sql); build_progress("{parsing_data}", 55); while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) { $category = $ligne["category"]; $ligne["size"] = $ligne["size"] / 1024; if ($category == null) { $category = "unknown"; } $MAIN["GRAPH5"][$category] = $ligne["size"]; } //---------------------------------------------------------------------------------------------------------- $MAIN["csv"] = $GLOBALS["CSV1"]; echo "MD5:{$GLOBALS["zMD5"]}\n"; REMOVE_TABLES($GLOBALS["zMD5"]); $encoded_data = base64_encode(serialize($MAIN)); $datasize = strlen($encoded_data); echo "Saving " . strlen($encoded_data) . " bytes...\n"; $q->QUERY_SQL("UPDATE reports_cache SET `builded`=1,`values`='{$encoded_data}',`values_size`='{$datasize}' WHERE `zmd5`='{$GLOBALS["zMD5"]}'"); if (!$q->ok) { echo $q->mysql_error . "\n"; build_progress("MySQL {failed}", 110); return; } build_progress("{success}", 100); }