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