function GRAB_DATAS($ligne, $md5) { $GLOBALS["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(); $ou = $ligne["ou"]; $uid = $ligne["uid"]; $to = $params["TO"]; $from = $params["FROM"]; $interval = $params["INTERVAL"]; $user = $params["USER"]; $md5_table = "{$md5}report"; $search = $params["searchuser"]; echo "SMTP Flow: FROM {$from} to {$to} {$interval} organization:{$ou} user:{$user} {$search}\n"; $table = "smtpstats"; //zdate,mailfrom,domainfrom,mailto,domainto,subject,size,spamscore,spamreport,disclaimer,backuped,infected,filtered,whitelisted,compressed,stripped $sqlSource = null; $TimeGroup = "date_trunc('hour', zdate) as zdate"; $TimeGroupBy = "date_trunc('hour', zdate)"; $distance = $influx->DistanceHour($from, $to); echo "Distance: {$distance} hours\n"; $FilterDate = "(zdate >='" . date("Y-m-d H:i:s", $from) . "' and zdate <= '" . date("Y-m-d H:i:s", $to) . "')"; if ($search == "*") { $search = null; } if ($search != null) { $search = str_replace("*", ".*", $search); $SSEARCH = "WHERE ( (mailfrom ~* '{$search}') OR (mailto ~* '{$search}')"; } if ($ou != null) { $ldap = new clladp(); $domains = $ldap->hash_get_domains_ou($ou); while (list($domain, $MAIN) = each($domains)) { $domain = trim(strtolower($domain)); if ($domain == null) { continue; } echo "Domain: {$domain}\n"; $FDOMS[] = "domainfrom ='{$domain}'"; $FDOMS2[] = "domainto ='{$domain}'"; } $imploded1 = @implode(" OR ", $FDOMS); $imploded2 = @implode(" OR ", $FDOMS2); $sqlSource = "(select count(*) as hits,sum(size) as size,mailfrom,domainfrom,mailto,domainto,{$TimeGroup} FROM smtpstats WHERE {$FilterDate} AND (({$imploded1}) OR ({$imploded2})) GROUP BY mailfrom,domainfrom,mailto,domainto,{$TimeGroupBy} ORDER BY {$TimeGroupBy} ) as t"; $sqlSource = "select * FROM {$sqlSource} {$SSEARCH}"; } if ($sqlSource == null) { $sqlSource = "(select count(*) as hits,sum(size) as size,mailfrom,domainfrom,mailto,domainto,{$TimeGroup} FROM smtpstats WHERE {$FilterDate} GROUP BY mailfrom,domainfrom,mailto,domainto,{$TimeGroupBy} ORDER BY {$TimeGroupBy} ) as t"; $sqlSource = "select * FROM {$sqlSource} {$SSEARCH}"; } $sql = "CREATE TABLE IF NOT EXISTS \"{$md5}report\"\n\t(zdate timestamp,\n\tmailfrom VARCHAR(128),\n\tmailto VARCHAR(128),\n\tdomainfrom VARCHAR(128),\n\tdomainto VARCHAR(128),\t\n\tsize BIGINT,\n\thits BIGINT)"; echo "TEMP:\n{$sql}\n"; $q = new postgres_sql(); $q->QUERY_SQL($sql); if (!$q->ok) { echo "***************\n{$q->mysql_error}\n***************\n"; return false; } $q->QUERY_SQL("TRUNCATE TABLE \"{$md5}report\""); $q->QUERY_SQL("create index zdate{$md5}report on \"{$md5}report\"(zdate);"); $q->QUERY_SQL("create index mailfrom{$md5}report on \"{$md5}report\"(mailfrom,mailto,reason);"); $sql = "INSERT INTO \"{$md5}report\" (hits,size,mailfrom,domainfrom,mailto,domainto,zdate) {$sqlSource}"; echo "{$sql}\n"; build_progress("{step} {waiting_data}: BigData engine, {please_wait}", 6); $postgres = new postgres_sql(); $results = $postgres->QUERY_SQL($sql); if (!$postgres->ok) { echo "ERROR.....\n"; echo "***************\n{$postgres->mysql_error}\n***************\n"; $q->QUERY_SQL("DROP TABLE \"{$md5}report\""); return false; } $sql = "SELECT COUNT(*) AS tcount FROM \"{$md5}report\""; $ligne = pg_fetch_assoc($postgres->QUERY_SQL($sql)); $total = intval($ligne["tcount"]); echo "Members {$total} items inserted to PostGreSQL\n"; if ($total == 0) { $q->QUERY_SQL("DROP TABLE \"{$md5}report\""); return false; } return true; }
function GRAB_DATAS($ligne, $md5) { $GLOBALS["zMD5"] = $md5; $params = unserialize($ligne["params"]); while (list($num, $val) = each($params)) { echo "{$num}........: {$val}\n"; } $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(); $from = $params["FROM"]; $to = $params["TO"]; $interval = $params["INTERVAL"]; $USER_FIELD = strtolower($params["USER"]); $SEARCH = $params["SEARCH"]; $sql = "CREATE TABLE IF NOT EXISTS \"{$md5}report\"\n\t(zdate timestamp,\n\tmac macaddr,\n\tipaddr INET,\n\tuserid VARCHAR(64) NULL,\n\tcategory VARCHAR(64) NULL,\n\tfamilysite VARCHAR(128) NULL,\n\tsize BIGINT,\n\trqs BIGINT)"; $q = new postgres_sql(); $q->QUERY_SQL($sql); if (!$q->ok) { echo "********** FAILED **********\n"; echo $q->mysql_error . "\n"; build_progress("{step} {insert_data}: PostreSQL engine, {failed}", 110); return false; } $q->QUERY_SQL("create index zdate{$md5}report on \"{$md5}report\"(zdate);"); $q->QUERY_SQL("create index familysite{$md5}report on \"{$md5}report\"(familysite);"); $q->QUERY_SQL("create index user{$md5}report on \"{$md5}report\"(ipaddr,userid,familysite);"); $FIELDS["MAC"] = "mac"; $FIELDS["IPADDR"] = "ipaddr"; $FIELDS["USERID"] = "userid"; $TimeGroup = "zdate"; $distance = $influx->DistanceHour($from, $to); echo "Distance: {$distance} hours\n"; if ($distance > 4) { $TimeGroup = "date_trunc('hour', zdate) as zdate"; } $USER_FIELD = "category"; if ($SEARCH == "unknown") { $SEARCH = null; } $sqlA[] = "SELECT SUM(size) as size, SUM(rqs) as RQS,{$TimeGroup},FAMILYSITE, CATEGORY, MAC, IPADDR, USERID FROM access_log"; $sqlA[] = "WHERE {$USER_FIELD}='{$SEARCH}' and (zDate >'" . date("Y-m-d H:i:s", $from) . "'"; $sqlA[] = "and zDate < '" . date("Y-m-d H:i:s", $to) . "')"; $sqlA[] = "GROUP BY zdate, FAMILYSITE, CATEGORY, MAC, IPADDR, USERID"; if ($distance > 23) { $sqlA = array(); echo "Distance: {$distance} hours: Use the Month table\n"; $sqlA[] = "SELECT SUM(SIZE) as size, SUM(RQS) as rqs,zdate,familysite, category, mac, ipaddr, userid FROM access_month"; $sqlA[] = "WHERE {$USER_FIELD}='{$SEARCH}' and (zDate >='" . date("Y-m-d H:i:s", $from) . "'"; $sqlA[] = "and zDate <= '" . date("Y-m-d H:i:s", $to) . "')"; $sqlA[] = "GROUP BY zdate, FAMILYSITE, CATEGORY, MAC, IPADDR, USERID"; } if ($distance > 720) { $sqlA = array(); echo "Distance: {$distance} hours: Use the Year table\n"; $sqlA[] = "SELECT SUM(SIZE) as size, SUM(RQS) as rqs,zdate,familysite, category, mac, ipaddr, userid FROM access_year"; $sqlA[] = "WHERE {$USER_FIELD}='{$SEARCH}' and (zDate >='" . date("Y-m-d H:i:s", $from) . "'"; $sqlA[] = "and zDate <= '" . date("Y-m-d H:i:s", $to) . "')"; $sqlA[] = "GROUP BY zdate, FAMILYSITE, CATEGORY, MAC, IPADDR, USERID"; } $sql = @implode(" ", $sqlA); $sql = "INSERT INTO \"{$md5}report\" (size,rqs,zdate,familysite, category, mac, ipaddr, userid) {$sql}"; echo "{$sql}\n"; build_progress("{step} {waiting_data}: BigData engine, (websites) {please_wait}", 6); $q->QUERY_SQL($sql); if (!$q->ok) { echo "***************\n{$postgres->mysql_error}\n***************\n"; $q->QUERY_SQL("DROP TABLE \"{$md5}report\""); return false; } $sql = "SELECT COUNT(*) AS tcount FROM \"{$md5}report\""; $ligne = pg_fetch_assoc($q->QUERY_SQL($sql)); $total = intval($ligne["tcount"]); echo "Member {$total} items inserted to PostGreSQL\n"; if ($total == 0) { $q->QUERY_SQL("DROP TABLE \"{$md5}report\""); return false; } return true; }
function GRAB_DATAS($ligne, $md5) { $GLOBALS["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(); $from = $params["FROM"]; $to = $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; } $SSEARCH = array(); $distance = $influx->DistanceHour($from, $to); echo "Distance: {$distance} hours\n"; $TimeGroup = "date_trunc('hour', zdate) as zdate"; $SQLA[] = "SELECT SUM(xcount) as xcount,{$TimeGroup},src_ip,dst_ip,dst_port,proto,severity,signature FROM suricata_events"; $SQLA[] = "WHERE"; $SQLA[] = "(zdate >='" . date("Y-m-d H:i:s", $from) . "' and zdate <= '" . date("Y-m-d H:i:s", $to) . "')"; $SQLA[] = "GROUP BY zdate, src_ip,dst_ip,dst_port,proto,severity,signature"; build_progress("{step} {waiting_data}: BigData engine, (websites) {please_wait}", 6); $unix = new unix(); $hostname = $unix->hostname_g(); $sql = "CREATE TABLE IF NOT EXISTS \"{$md5}report\" (\n\t\tzDate timestamp,\n\t\tsrc_ip inet,\n\t\tdst_ip inet,\n\t\tdst_port smallint NOT NULL,\n\t\tproto varchar(10) NOT NULL,\n\t\tseverity smallint NOT NULL,\n\t\tsignature BIGINT,\n\t\txcount BIGINT )"; $q = new postgres_sql(); $q->QUERY_SQL($sql); if (!$q->ok) { echo "***************\n{$q->mysql_error}\n***************\n"; return false; } $q->QUERY_SQL("create index zdate{$md5}report on \"{$md5}report\"(zdate);"); $q->QUERY_SQL("create index src_ip{$md5}report on \"{$md5}report\"(src_ip);"); $q->QUERY_SQL("create index dst_ip{$md5}report on \"{$md5}report\"(dst_ip);"); $q->QUERY_SQL("TRUNCATE TABLE \"{$md5}report\""); $sql = @implode(" ", $SQLA); $sql = "INSERT INTO \"{$md5}report\" (xcount,zdate,src_ip,dst_ip,dst_port,proto,severity,signature) {$sql}"; echo "***************\n{$sql}\n*****************\n"; $q->QUERY_SQL($sql); if (!$q->ok) { echo "***************\nERROR {$q->mysql_error}\n***************\n"; $q->QUERY_SQL("DROP TABLE \"{$md5}report\""); return false; } $ligne = pg_fetch_assoc($q->QUERY_SQL("SELECT COUNT(*) as tcount FROM \"{$md5}report\"")); if (!$q->ok) { echo "***************\nERROR {$q->mysql_error}\n***************\n"; $q->QUERY_SQL("DROP TABLE \"{$md5}report\""); return false; } $c = $ligne["tcount"]; if ($c == 0) { echo "No data....\n"; $q->QUERY_SQL("DROP TABLE \"{$md5}report\""); return false; } echo "{$c} items inserted to PostgreSQL\n"; $MAIN_ARRAY = array(); return true; }
function GRAB_DATAS($ligne, $md5) { $GLOBALS["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(); $from = $params["FROM"]; $to = $params["TO"]; $interval = $params["INTERVAL"]; $user = strtolower($params["USER"]); $search = trim($params["SEARCH"]); if ($search == "*") { $search = null; } $md5_table = "{$md5}sites"; $SSEARCH = null; echo "FLOW: FROM {$from} to {$to} {$interval} user:{$user} search:{$search}\n"; if ($search != null) { $search = str_replace("*", ".*", $search); $SSEARCH = " (\"{$user}\" ~* '{$search}') AND "; } if ($user == "ipaddr") { $ip = new IP(); $operator = null; if (substr($search, 0, 1) == ">") { $operator = "<"; $search = substr($search, 1, strlen($search)); } if (substr($search, 0, 1) == "<") { $operator = ">"; $search = substr($search, 1, strlen($search)); } if (preg_match("#[0-9\\.]+\\/[0-9]+#", $search)) { $SSEARCH = " ( inet '{$search}' >> ipaddr) AND "; } if (preg_match("#^[0-9\\.]+\$#", $search)) { $SSEARCH = " ( inet '{$search}' {$operator}= ipaddr) AND "; } } $sql = "CREATE TABLE IF NOT EXISTS \"{$md5}report\" (zDate timestamp, familysite VARCHAR(128), \"user\" VARCHAR(128), size BIGINT)"; $q = new postgres_sql(); $q->QUERY_SQL($sql); echo $sql . "\n"; if (!$q->ok) { echo "***************\n{$q->mysql_error}\n***************\n"; return false; } $q->QUERY_SQL("create index zdate{$md5}report on \"{$md5}report\"(zdate);"); $q->QUERY_SQL("create index familysite{$md5}report on \"{$md5}report\"(familysite);"); $distance = $influx->DistanceHour($from, $to); echo "Distance: {$distance} hours\n"; if ($distance > 4) { $TimeGroup = "date_trunc('hour', zdate) as zdate"; } $sql = "SELECT SUM(SIZE) as size,familysite,{$TimeGroup},\"{$user}\"\n\t\tFROM access_log WHERE {$SSEARCH}zdate >'" . date("Y-m-d H:i:s", $from) . "' \n\t\tand zdate < '" . date("Y-m-d H:i:s", $to) . "' GROUP BY zdate,familysite,\"{$user}\""; if ($distance > 23) { echo "Distance: {$distance} hours: Use the Month table\n"; $sql = "SELECT SUM(SIZE) as size,familysite,zdate,\"{$user}\"\n\t\tFROM access_month WHERE {$SSEARCH}zdate >='" . date("Y-m-d H:i:s", $from) . "'\n\t\tand zdate <= '" . date("Y-m-d H:i:s", $to) . "' GROUP BY zdate,familysite,\"{$user}\""; } if ($distance > 720) { echo "Distance: {$distance} hours: Use the Year table\n"; $sql = "SELECT SUM(SIZE) as size,familysite,zdate,\"{$user}\"\n\t\tFROM access_year WHERE {$SSEARCH}zdate >='" . date("Y-m-d H:i:s", $from) . "'\n\t\tand zdate <= '" . date("Y-m-d H:i:s", $to) . "' GROUP BY zdate,familysite,\"{$user}\""; } $q->QUERY_SQL("TRUNCATE TABLE \"{$md5}report\""); build_progress("{step} {waiting_data}: BigData engine, (websites) {please_wait}", 6); $sql = "INSERT INTO \"{$md5}report\" (size,familysite,zdate,\"user\") {$sql}"; echo "***************\n{$sql}\n*****************\n"; $q->QUERY_SQL($sql); if (!$q->ok) { echo "***************\nERROR {$q->mysql_error}\n***************\n"; $q->QUERY_SQL("DROP TABLE \"{$md5}report\""); return false; } $ligne = pg_fetch_assoc($q->QUERY_SQL("SELECT COUNT(*) as tcount FROM \"{$md5}report\"")); if (!$q->ok) { echo "***************\nERROR {$q->mysql_error}\n***************\n"; $q->QUERY_SQL("DROP TABLE \"{$md5}report\""); return false; } $c = $ligne["tcount"]; if ($c == 0) { echo "\n\n\n!!! No data....!!!\n\n\n"; $q->QUERY_SQL("DROP TABLE \"{$md5}report\""); return false; } echo "{$c} items inserted to PostgreSQL\n"; $MAIN_ARRAY = array(); return true; }
function GRAB_DATAS($ligne, $md5) { $GLOBALS["zMD5"] = $md5; $params = unserialize($ligne["params"]); $params["TO"] = intval($params["TO"]); $params["FROM"] = abs(intval($params["FROM"])); while (list($A, $P) = each($params)) { echo "Params {$A}......: {$P}\n"; } echo "Date To......: {$params["TO"]}\n"; $influx = new influx(); $mintime = strtotime("2008-01-01 00:00:00"); $params["TO"] = intval($params["TO"]); $params["FROM"] = abs(intval($params["FROM"])); echo "Date From....: {$params["FROM"]} <> {$mintime}\n"; if ($params["FROM"] < $mintime) { echo "Date From....: {$params["FROM"]} < {$mintime} !!!\n"; $params["FROM"] = strtotime(date("Y-m-d 00:00:00")); } $params["TO"] = intval($params["TO"]); if ($params["TO"] < $mintime) { $params["TO"] = time(); } echo "Date From....: {$params["FROM"]}\n"; $from = InfluxQueryFromUTC($params["FROM"]); $to = InfluxQueryFromUTC($params["TO"]); $interval = $params["INTERVAL"]; $USER_FIELD = $params["USER"]; echo "LIMIT........: {$mintime}\n"; echo "Date From....: {$params["FROM"]}/{$from}/" . date("Y-m-d H:i:s", $from) . "\n"; echo "Date To......: {$params["TO"]}/{$to}/" . date("Y-m-d H:i:s", $to) . "\n"; echo "USER_FIELD...: {$USER_FIELD}\n"; $SSEARCH = null; $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); $searchuser_sql = str_replace("*", ".*", $searchuser_sql); if ($searchuser_sql != null) { $SSEARCH = " ({$USER_FIELD} ~* '{$searchuser_sql}') AND "; } } $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) { $searchuser = str_replace(".", "\\.", $searchuser); $searchuser = str_replace("*", ".*", $searchuser); $SSEARCH = " (client ~* '{$searchuser}') AND "; } $q = new mysql_squid_builder(); $TimeGroup = "date_trunc('hour', zdate)"; $distance = $influx->DistanceHour($from, $to); echo "Distance: {$distance} hours\n"; $sql = "CREATE TABLE IF NOT EXISTS \"{$md5}report\" (zDate timestamp,\n\twebsite VARCHAR(128),\n\tcategory VARCHAR(64),\n\trulename VARCHAR(128),\n\thostname VARCHAR(128),\n\tclient VARCHAR(128),\n\trqs BIGINT)"; $q = new postgres_sql(); $q->QUERY_SQL($sql); if (!$q->ok) { echo "***************\n{$q->mysql_error}\n***************\n"; return false; } $q->QUERY_SQL("create index zdate{$md5}report on \"{$md5}report\"(zdate);"); $q->QUERY_SQL("create index website{$md5}report on \"{$md5}report\"(website);"); $q->QUERY_SQL("create index hostname{$md5}report on \"{$md5}report\"(hostname);"); $q->QUERY_SQL("create index client{$md5}report on \"{$md5}report\"(client);"); $q->QUERY_SQL("TRUNCATE TABLE \"{$md5}report\""); $Z[] = "SELECT SUM(RQS) AS RQS,{$TimeGroup} as zdate,rulename,category,hostname,website,client FROM webfilter"; $Z[] = "WHERE {$SSEARCH}(zdate >='" . date("Y-m-d H:i:s", $from) . "'"; $Z[] = "and zdate <= '" . date("Y-m-d H:i:s", $to) . "')"; $Z[] = "GROUP BY {$TimeGroup},rulename,category,hostname,website,client"; $sql = @implode(" ", $Z); echo "{$sql}\n"; build_progress("{step} {waiting_data}: BigData engine, (websites) {please_wait}", 6); $sql = "INSERT INTO \"{$md5}report\" (rqs,zdate,rulename,category,hostname,website,client) {$sql}"; $postgres = new postgres_sql(); $results = $postgres->QUERY_SQL($sql); if (!$postgres->ok) { echo $postgres->mysql_error . "\n"; return false; } $ligne = pg_fetch_assoc($q->QUERY_SQL("SELECT COUNT(*) as tcount FROM \"{$md5}report\"")); if (!$q->ok) { echo "***************\nERROR {$q->mysql_error}\n***************\n"; $q->QUERY_SQL("DROP TABLE \"{$md5}report\""); return false; } $c = $ligne["tcount"]; if ($c == 0) { echo "No data....\n"; $q->QUERY_SQL("DROP TABLE \"{$md5}report\""); return false; } echo "{$c} items inserted to PostgreSQL\n"; $MAIN_ARRAY = array(); return true; }
function GRAB_DATAS($ligne, $md5) { $GLOBALS["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 = $params["TO"]; $from = $params["FROM"]; $interval = $params["INTERVAL"]; $user = $params["USER"]; $md5_table = "{$md5}report"; $search = $params["SEARCH"]; $USER_FIELD = strtolower($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 = " ({$USER_FIELD} ~* '{$search}') AND "; } $sql = "CREATE TABLE IF NOT EXISTS \"{$md5}report\"\n\t(zDate timestamp,\n\t{$USER_FIELD} VARCHAR(128),\n\tsize BIGINT)"; echo "TEMP:\n{$sql}\n"; $q = new postgres_sql(); $q->QUERY_SQL($sql); if (!$q->ok) { echo "***************\n{$q->mysql_error}\n***************\n"; return false; } $TimeGroup = "date_trunc('hour', zdate) as zdate"; $distance = $influx->DistanceHour($from, $to); echo "Distance: {$distance} hours\n"; $FilterDate = "(zdate >='" . date("Y-m-d H:i:s", $from) . "' and zdate <= '" . date("Y-m-d H:i:s", $to) . "')"; $sql = "SELECT SUM(size) as size,{$TimeGroup},{$USER_FIELD} FROM maillog\n\tWHERE {$SSEARCH} {$FilterDate}\n\tGROUP BY zdate, {$USER_FIELD}"; /* if($distance>23){ echo "Distance: {$distance} hours use the Month table\n"; $sql="SELECT SUM(SIZE) as size,zdate,$USER_FIELD FROM access_month WHERE $SSEARCH$FilterDate GROUP BY zdate, $USER_FIELD"; } if($distance>720){ echo "Distance: {$distance} hours use the Year table\n"; $sql="SELECT SUM(SIZE) as size,zdate,$USER_FIELD FROM access_year WHERE $SSEARCH$FilterDate GROUP BY zdate, $USER_FIELD"; } */ $q->QUERY_SQL("TRUNCATE TABLE \"{$md5}report\""); $q->QUERY_SQL("create index zdate{$md5}report on \"{$md5}report\"(zdate);"); $q->QUERY_SQL("create index {$USER_FIELD}{$md5}report on \"{$md5}report\"({$USER_FIELD});"); $sql = "INSERT INTO \"{$md5}report\" (size,zdate,{$USER_FIELD}) {$sql}"; echo "{$sql}\n"; build_progress("{step} {waiting_data}: BigData engine, (websites) {please_wait}", 6); $postgres = new postgres_sql(); $results = $postgres->QUERY_SQL($sql); if (!$postgres->ok) { echo "ERROR.....\n"; echo "***************\n{$postgres->mysql_error}\n***************\n"; $q->QUERY_SQL("DROP TABLE \"{$md5}report\""); return false; } $sql = "SELECT COUNT(*) AS tcount FROM \"{$md5}report\""; $ligne = pg_fetch_assoc($postgres->QUERY_SQL($sql)); $total = intval($ligne["tcount"]); echo "Members {$total} items inserted to PostGreSQL\n"; if ($total == 0) { $q->QUERY_SQL("DROP TABLE \"{$md5}report\""); return false; } return true; }
function GRAB_DATAS($ligne, $md5) { $GLOBALS["zMD5"] = $md5; $params = unserialize($ligne["params"]); while (list($num, $val) = each($params)) { echo "{$num}........: {$val}\n"; } $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(); $from = $params["FROM"]; $to = $params["TO"]; $interval = $params["INTERVAL"]; $USER_FIELD = strtolower($params["USER"]); $SEARCH = $params["SEARCH"]; $sql = "CREATE TABLE IF NOT EXISTS \"{$md5}report\"\n\t(zdate timestamp,\n\tfromdomain varchar(128),\n\trelay_s varchar(128),\n\trelay_r varchar(128),\n\ttodomain varchar(128),\n\tfrommail varchar(256),\n\ttomail varchar(256),\n\tsize bigint,\n\trqs bigint,\n\tsmtp_code int)"; $q = new postgres_sql(); $q->QUERY_SQL($sql); if (!$q->ok) { echo "********** FAILED **********\n"; echo $q->mysql_error . "\n"; build_progress("{step} {insert_data}: PostreSQL engine, {failed}", 110); return false; } $q->QUERY_SQL("create index zdate{$md5}report on \"{$md5}report\"(zdate);"); $q->QUERY_SQL("create index frommail{$md5}report on \"{$md5}report\"(frommail,fromdomain);"); $q->QUERY_SQL("create index tomail{$md5}report on \"{$md5}report\"(tomail,todomain);"); $TimeGroup = "zdate"; $distance = $influx->DistanceHour($from, $to); echo "Distance: {$distance} hours\n"; if ($distance > 4) { $TimeGroup = "date_trunc('hour', zdate) as zdate"; } $sqlA[] = "SELECT SUM(size) as size, COUNT(*) as RQS,{$TimeGroup},fromdomain, todomain, frommail, tomail, smtp_code FROM maillog"; $sqlA[] = "WHERE {$USER_FIELD}='{$SEARCH}' and (zDate >'" . date("Y-m-d H:i:s", $from) . "'"; $sqlA[] = "and zDate < '" . date("Y-m-d H:i:s", $to) . "')"; $sqlA[] = "GROUP BY zdate, fromdomain, todomain, frommail, tomail, smtp_code"; $sql = @implode(" ", $sqlA); $sql = "INSERT INTO \"{$md5}report\" (size,rqs,zdate,fromdomain, todomain, frommail, tomail, smtp_code) {$sql}"; echo "{$sql}\n"; build_progress("{step} {waiting_data}: BigData engine, {please_wait}", 6); $q->QUERY_SQL($sql); if (!$q->ok) { echo "***************\n{$postgres->mysql_error}\n***************\n"; $q->QUERY_SQL("DROP TABLE \"{$md5}report\""); return false; } $sql = "SELECT COUNT(*) AS tcount FROM \"{$md5}report\""; $ligne = pg_fetch_assoc($q->QUERY_SQL($sql)); $total = intval($ligne["tcount"]); echo "Member {$total} items inserted to PostGreSQL\n"; if ($total == 0) { $q->QUERY_SQL("DROP TABLE \"{$md5}report\""); return false; } return true; }
function GRAB_DATAS($ligne, $md5) { $GLOBALS["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(); $from = $params["FROM"]; $to = $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; } $SSEARCH = array(); $distance = $influx->DistanceHour($from, $to); echo "Distance: {$distance} hours\n"; $TimeGroup = "date_trunc('hour', zdate) as zdate"; if ($searchsites != null) { $searchsites_sql = str_replace("*", ".*", $searchsites); $SSEARCH[] = "FAMILYSITE ~* '{$searchsites_sql}'"; } if ($searchuser != null) { if (strtolower($USER_FIELD) == "ipaddr") { $ip = new IP(); $operator = null; if (substr($searchuser, 0, 1) == ">") { $operator = "<"; $searchuser = substr($searchuser, 1, strlen($searchuser)); } if (substr($searchuser, 0, 1) == "<") { $operator = ">"; $searchuser = substr($searchuser, 1, strlen($searchuser)); } if (preg_match("#[0-9\\.]+\\/[0-9]+#", $searchuser)) { $SSEARCH[] = " ( inet '{$searchuser}' >> {$USER_FIELD} ) "; } if (preg_match("#^[0-9\\.]+\$#", $searchuser)) { $SSEARCH[] = " ( inet '{$searchuser}' {$operator}= {$USER_FIELD} ) "; } } else { $searchuser_sql = str_replace("*", ".*", $searchuser); $SSEARCH[] = "{$USER_FIELD} ~* '{$searchuser_sql}'"; } } if (count($SSEARCH) > 0) { $SEARCHTEXT = @implode(" AND ", $SSEARCH) . " AND"; } $SQLA[] = "SELECT SUM(size) as size, SUM(rqs) as rqs,{$TimeGroup},familysite,userid,ipaddr,mac,proxyname FROM access_log"; $SQLA[] = "WHERE"; $SQLA[] = "{$SEARCHTEXT} (zdate >'" . date("Y-m-d H:i:s", $from) . "' and zdate < '" . date("Y-m-d H:i:s", $to) . "')"; if ($USER_FIELD == "USERID") { $SQLA[] = "AND USERID != 'none'"; } $SQLA[] = "GROUP BY zdate, familysite,userid,ipaddr,mac,proxyname"; if ($distance > 23) { echo "Using the * * Month table * *"; $SQLA = array(); $SQLA[] = "SELECT SUM(size) as size, SUM(rqs) as rqs,{$TimeGroup},familysite,userid,ipaddr,mac,proxyname FROM access_month"; $SQLA[] = "WHERE"; $SQLA[] = "{$SEARCHTEXT} (zdate >='" . date("Y-m-d H:i:s", $from) . "' and zdate <= '" . date("Y-m-d H:i:s", $to) . "')"; if ($USER_FIELD == "USERID") { $SQLA[] = "AND USERID != 'none'"; } $SQLA[] = "GROUP BY zdate, familysite,userid,ipaddr,mac,proxyname"; } if ($distance > 720) { echo "Using the * * Year table * *"; $SQLA = array(); $SQLA[] = "SELECT SUM(size) as size, SUM(rqs) as rqs,{$TimeGroup},familysite,userid,ipaddr,mac,proxyname FROM access_year"; $SQLA[] = "WHERE"; $SQLA[] = "{$SEARCHTEXT} (zdate >='" . date("Y-m-d H:i:s", $from) . "' and zdate <= '" . date("Y-m-d H:i:s", $to) . "')"; if ($USER_FIELD == "USERID") { $SQLA[] = "AND USERID != 'none'"; } $SQLA[] = "GROUP BY zdate, familysite,userid,ipaddr,mac,proxyname"; } build_progress("{step} {waiting_data}: BigData engine, (websites) {please_wait}", 6); $unix = new unix(); $hostname = $unix->hostname_g(); $sql = "CREATE TABLE IF NOT EXISTS \"{$md5}report\"\n\t\t(zDate timestamp,\n\t\tMAC macaddr,\n\t\tIPADDR INET,\n\t\tPROXYNAME VARCHAR(128) NOT NULL DEFAULT '{$hostname}',\n\t\tCATEGORY VARCHAR(64) NULL,\n\t\tFAMILYSITE VARCHAR(128) NULL,\t\t\t\t\n\t\tUSERID VARCHAR(64) NULL,\n\t\tSIZE BIGINT,\n\t\tRQS BIGINT)"; $q = new postgres_sql(); $q->QUERY_SQL($sql); if (!$q->ok) { echo "***************\n{$q->mysql_error}\n***************\n"; return false; } $q->QUERY_SQL("create index zdate{$md5}report on \"{$md5}report\"(zdate);"); $q->QUERY_SQL("create index familysite{$md5}report on \"{$md5}report\"(familysite);"); $q->QUERY_SQL("TRUNCATE TABLE \"{$md5}report\""); $sql = @implode(" ", $SQLA); $sql = "INSERT INTO \"{$md5}report\" (size,rqs,zdate,familysite,userid,ipaddr,mac,proxyname) {$sql}"; echo "***************\n{$sql}\n*****************\n"; $q->QUERY_SQL($sql); if (!$q->ok) { echo "***************\nERROR {$q->mysql_error}\n***************\n"; $q->QUERY_SQL("DROP TABLE \"{$md5}report\""); return false; } $ligne = pg_fetch_assoc($q->QUERY_SQL("SELECT COUNT(*) as tcount FROM \"{$md5}report\"")); if (!$q->ok) { echo "***************\nERROR {$q->mysql_error}\n***************\n"; $q->QUERY_SQL("DROP TABLE \"{$md5}report\""); return false; } $c = $ligne["tcount"]; if ($c == 0) { echo "No data....\n"; $q->QUERY_SQL("DROP TABLE \"{$md5}report\""); return false; } echo "{$c} items inserted to PostgreSQL\n"; $MAIN_ARRAY = array(); return true; }