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