From 1536d3590d52e56a67f3443df9aa3dfe378c3c18 Mon Sep 17 00:00:00 2001 From: marcelloc Date: Thu, 1 Dec 2011 13:36:59 -0200 Subject: postfix - package version 2.3 --- config/postfix/postfix.php | 560 ++++++++++++++++++++++++++++++++++----------- 1 file changed, 432 insertions(+), 128 deletions(-) (limited to 'config/postfix/postfix.php') diff --git a/config/postfix/postfix.php b/config/postfix/postfix.php index d50fd326..0f397cc9 100644 --- a/config/postfix/postfix.php +++ b/config/postfix/postfix.php @@ -31,13 +31,118 @@ require_once("/etc/inc/util.inc"); require_once("/etc/inc/functions.inc"); require_once("/etc/inc/pkg-utils.inc"); require_once("/etc/inc/globals.inc"); -function grep_log($from="",$to="",$subject=""){ - global $postfix_dir,$postfix_db,$postfix_arg; - create_db(); +require_once("/usr/local/pkg/postfix.inc"); + +function get_remote_log(){ + global $config,$g,$postfix_dir; + $curr_time = time(); + $log_time=date('YmdHis',$curr_time); + #get protocol + if($config['system']['webgui']['protocol'] != "") + $synchronizetoip = $config['system']['webgui']['protocol']. "://"; + #get port + $port = $config['system']['webgui']['port']; + #if port is empty lets rely on the protocol selection + if($port == "") + $port =($config['system']['webgui']['protocol'] == "http"?"80":"443"); + $synchronizetoip .= $sync_to_ip; + if (is_array($config['installedpackages']['postfixsync'])) + foreach($config['installedpackages']['postfixsync']['config'][0]['row'] as $sh){ + $sync_to_ip = $sh['ipaddress']; + $sync_type = $sh['sync_type']; + $password = $sh['password']; + $file= '/var/db/postfix/'.$server.'.sql'; + #get remote data + if ($sync_type=='fetch'){ + $url= $synchronizetoip . $sync_to_ip; + print "$sync_to_ip $url, $port\n"; + $method = 'pfsense.exec_php'; + $execcmd = "require_once('/usr/local/www/postfix.php');\n"; + $execcmd .= '$toreturn=get_sql('.$log_time.');'; + /* assemble xmlrpc payload */ + $params = array(XML_RPC_encode($password), + XML_RPC_encode($execcmd)); + log_error("postfix get sql data from {$sync_to_ip}."); + $msg = new XML_RPC_Message($method, $params); + $cli = new XML_RPC_Client('/xmlrpc.php', $url, $port); + $cli->setCredentials('admin', $password); + #$cli->setDebug(1); + $resp = $cli->send($msg, "250"); + $a=$resp->value(); + $errors=0; + #var_dump($sql); + foreach($a as $b) + foreach ($b as $c) + foreach ($c as $d) + foreach ($d as $e){ + $update=unserialize($e['string']); + print $update['day']."\n"; + if ($update['day'] != ""){ + create_db($update['day'].".db"); + if ($debug=true) + print $update['day'] ." writing from remote system to db..."; + $dbhandle = sqlite_open($postfix_dir.'/'.$update['day'].".db", 0666, $error); + #file_put_contents("/tmp/".$key.'-'.$update['day'].".sql",gzuncompress(base64_decode($update['sql'])), LOCK_EX); + $ok = sqlite_exec($dbhandle, gzuncompress(base64_decode($update['sql'])), $error); + if (!$ok){ + $errors++; + die ("Cannot execute query. $error\n".$update['sql']."\n"); + } + else{ + if ($debug=true) + print "ok\n"; + } + sqlite_close($dbhandle); + } + } + if ($errors ==0){ + $method = 'pfsense.exec_php'; + $execcmd = "require_once('/usr/local/www/postfix.php');\n"; + $execcmd .= 'flush_sql('.$log_time.');'; + /* assemble xmlrpc payload */ + $params = array(XML_RPC_encode($password), + XML_RPC_encode($execcmd)); + log_error("postfix flush sql buffer file from {$sync_to_ip}."); + $msg = new XML_RPC_Message($method, $params); + $cli = new XML_RPC_Client('/xmlrpc.php', $url, $port); + $cli->setCredentials('admin', $password); + #$cli->setDebug(1); + $resp = $cli->send($msg, "250"); + } + } + } +} +function get_sql($log_time){ + global $config,$xmlrpc_g; + $server=$_SERVER['REMOTE_ADDR']; + + if (is_array($config['installedpackages']['postfixsync'])) + foreach($config['installedpackages']['postfixsync']['config'][0]['row'] as $sh){ + $sync_to_ip = $sh['ipaddress']; + $sync_type = $sh['sync_type']; + $password = $sh['password']; + $file= '/var/db/postfix/'.$server.'.sql'; + if ($sync_to_ip==$server && $sync_type=='share' && file_exists($file)){ + rename($file,$file.".$log_time"); + return (file($file.".$log_time")); + } + } + return ""; +} + +function flush_sql($log_time){ + if (preg_match("/\d+\.\d+\.\d+\.\d+/",$_SERVER['REMOTE_ADDR'])) + unlink_if_exists('/var/db/postfix/'.$_SERVER['REMOTE_ADDR'].".sql.$log_time"); +} + +function grep_log(){ + global $postfix_dir,$postfix_arg,$config,$g; $total_lines=0; + $days=array(); $grep="postfix.\(cleanup\|smtp\|error\|qmgr\)"; $curr_time = time(); + $log_time=strtotime($postfix_arg['time'],$curr_time); $m=date('M',strtotime($postfix_arg['time'],$curr_time)); $j=substr(" ".date('j',strtotime($postfix_arg['time'],$curr_time)),-3); # file grep loop @@ -45,39 +150,68 @@ function grep_log($from="",$to="",$subject=""){ print "/usr/bin/grep '^".$m.$j." ".$hour.".*".$grep."' /var/log/maillog\n"; $lists=array(); exec("/usr/bin/grep " . escapeshellarg('^'.$m.$j." ".$hour.".*".$grep)." /var/log/maillog", $lists); - $stm_noqueue="BEGIN;\n"; - $stm_queue="BEGIN;\n"; foreach ($lists as $line){ + #check where is first mail record + if (preg_match("/ delay=(\d+)/",$line,$delay)){ + $day=date("Y-m-d",strtotime("-".$delay[1]." second",$log_time)); + if (! in_array($day,$days)){ + $days[]=$day; + create_db($day.".db"); + print "Found logs to $day.db\n"; + $stm_queue[$day]="BEGIN;\n"; + $stm_noqueue[$day]="BEGIN;\n"; + } + } + else{ + $day=date("Y-m-d",strtotime($postfix_arg['time'],$curr_time)); + if (! in_array($day,$days)){ + $days[]=$day; + create_db($day.".db"); + print "Found logs to $day.db\n"; + $stm_queue[$day]="BEGIN;\n"; + $stm_noqueue[$day]="BEGIN;\n"; + } + } $status=array(); $total_lines++; - #Nov 8 09:31:50 srvchunk01 postfix/smtpd[43585]: 19C281F59C8: client=pm03-974.auinmeio.com.br[177.70.232.225] + #Nov 8 09:31:50 srvch011 postfix/smtpd[43585]: 19C281F59C8: client=pm03-974.auinmem.br[177.70.0.3] if(preg_match("/(\w+\s+\d+\s+[0-9,:]+) (\w+) postfix.smtpd\W\d+\W+(\w+): client=(.*)/",$line,$email)){ $values="'".$email[3]."','".$email[1]."','".$email[2]."','".$email[4]."'"; if(${$email[3]}!=$email[3]) - $stm_queue.='insert into mail_queue(sid,date,server,client) values('.$values.');'."\n"; + $stm_queue[$day].='insert or ignore into mail_from(sid,date,server,client) values ('.$values.');'."\n"; ${$email[3]}=$email[3]; } - #Nov 13 00:09:07 srvchunk01 postfix/smtp[51436]: 9145C1F67F7: to=, relay=srvmail1-ma.ma.mail.test.com[172.23.3.6]:25, delay=2.4, delays=2.2/0/0.13/0.11, dsn=5.7.1, status=bounced (host srvmail1-ma.ma.mail.test.com[172.23.3.6] said: 550 5.7.1 Unable to relay for lidia.santos@ma.mail.test.com (in reply to RCPT TO command)) - #Nov 3 21:45:32 srvchunk01 postfix/smtp[18041]: 4CE321F4887: to=, relay=smtpe1.emv3.com[81.92.120.9]:25, delay=1.9, delays=0.06/0.01/0.68/1.2, dsn=2.0.0, status=sent (250 2.0.0 Ok: queued as 2C33E2382C8) - else if(preg_match("/(\w+\s+\d+\s+[0-9,:]+) (\w+) postfix.smtp\W\d+\W+(\w+): to=\<(.*)\>, relay=(.*), delay=([0-9.]+).*dsn=([0-9.]+), status=(\w+) (.*)/",$line,$email)){ - $stm_queue.= "update mail_queue set too='".$email[4]."', relay='".$email[5]."', dsn='".$email[7]."', status='".$email[8]."', status_info='".preg_replace("/(\s+|\'|\")/"," ",$email[9])."', delay='".$email[6]."' where sid='".$email[3]."';\n"; - #print "update mail_queue set too='".$email[4]."', relay='".$email[5]."', dsn='".$email[9]."',status='".$email[8]."', status_info='".$email[9]."', delay='".$email[6]."' where sid='".$email[3]."';\n"; + #Nov 14 09:29:32 srvch011 postfix/error[58443]: 2B8EB1F5A5A: to=, relay=none, delay=0.66, delays=0.63/0/0/0.02, dsn=4.4.3, status=deferred (delivery temporarily suspended: Host or domain name not found. Name service error for name=mail.pi.trf1.gov.br type=A: Host not found, try again) + #Nov 3 21:45:32 srvch011 postfix/smtp[18041]: 4CE321F4887: to=, relay=smtpe1.eom[81.00.20.9]:25, delay=1.9, delays=0.06/0.01/0.68/1.2, dsn=2.0.0, status=sent (250 2.0.0 Ok: queued as 2C33E2382C8) + #Nov 16 00:00:14 srvch011 postfix/smtp[7363]: 7AEB91F797D: to=, relay=mail.mg.trf1.gov.br[172.25.3.5]:25, delay=39, delays=35/1.1/0.04/2.7, dsn=5.7.1, status=bounced (host mail.mg.trf1.gov.br[172.25.3.5] said: 550 5.7.1 Unable to relay for alessandra.bueno@mg.trf1.gov.br (in reply to RCPT TO command)) + else if(preg_match("/(\w+\s+\d+\s+[0-9,:]+) (\w+) postfix.\w+\W\d+\W+(\w+): to=\<(.*)\>, relay=(.*), delay=([0-9,.]+), .* dsn=([0-9,.]+), status=(\w+) (.*)/",$line,$email)){ + $stm_queue[$day].= "insert or ignore into mail_status (info) values ('".$email[8]."');\n"; + $stm_queue[$day].= "insert or ignore into mail_to (from_id,too,status,status_info,relay,delay,dsn) values ((select id from mail_from where sid='".$email[3]."' and server='".$email[2]."'),'".strtolower($email[4])."',(select id from mail_status where info='".$email[8]."'),'".preg_replace("/(\<|\>|\s+|\'|\")/"," ",$email[9])."','".$email[5]."','".$email[6]."','".$email[7]."');\n"; + $stm_queue[$day].= "update or ignore mail_to set status=(select id from mail_status where info='".$email[8]."'), status_info='".preg_replace("/(\<|\>|\s+|\'|\")/"," ",$email[9])."', dsn='".$email[7]."', delay='".$email[6]."', relay='".$email[5]."', too='".strtolower($email[4])."' where from_id in (select id from mail_from where sid='".$email[3]."' and server='".$email[2]."');\n"; } #Nov 13 01:48:44 srvch011 postfix/cleanup[16914]: D995B1F570B: message-id=<61.40.11745.10E3FBE4@ofertas6> else if(preg_match("/(\w+\s+\d+\s+[0-9,:]+) (\w+) postfix.cleanup\W\d+\W+(\w+): message-id=\<(.*)\>/",$line,$email)){ - $stm_queue.="update mail_queue set msgid='".$email[4]."' where sid='".$email[3]."';"; + $stm_queue[$day].="update mail_from set msgid='".$email[4]."' where sid='".$email[3]."';\n"; } - #Nov 14 02:40:05 srvchunk01 postfix/qmgr[46834]: BC5931F4F13: from=, size=32727, nrcpt=1 (queue active) + #Nov 14 02:40:05 srvch011 postfix/qmgr[46834]: BC5931F4F13: from=, size=32727, nrcpt=1 (queue active) else if(preg_match("/(\w+\s+\d+\s+[0-9,:]+) (\w+) postfix.qmgr\W\d+\W+(\w+): from=\<(.*)\>\W+size=(\d+)/",$line,$email)){ - $stm_queue.= "update mail_queue set fromm='".$email[4]."', size='".$email[5]."' where sid='".$email[3]."';\n"; + $stm_queue[$day].= "update mail_from set fromm='".strtolower($email[4])."', size='".$email[5]."' where sid='".$email[3]."';\n"; } - #Nov 13 00:09:07 srvchunk01 postfix/bounce[56376]: 9145C1F67F7: sender non-delivery notification: D5BD31F6865 - else if(preg_match("/(\w+\s+\d+\s+[0-9,:]+) (\w+) postfix.bounce\W\d+\W+(\w+): sender non-delivery notification: (\w+)/",$line,$email)){ - $stm_queue.= "update mail_queue set bounce='".$email[4]."' where sid='".$email[3]."';\n"; + #Nov 13 00:09:07 srvch011 postfix/bounce[56376]: 9145C1F67F7: sender non-delivery notification: D5BD31F6865 + #else if(preg_match("/(\w+\s+\d+\s+[0-9,:]+) (\w+) postfix.bounce\W\d+\W+(\w+): sender non-delivery notification: (\w+)/",$line,$email)){ + # $stm_queue[$day].= "update mail_queue set bounce='".$email[4]."' where sid='".$email[3]."';\n"; + #} + #Nov 14 01:41:44 srvch011 postfix/smtpd[15259]: warning: 1EF3F1F573A: queue file size limit exceeded + else if(preg_match("/(\w+\s+\d+\s+[0-9,:]+) (\w+) postfix.smtpd\W\d+\W+warning: (\w+): queue file size limit exceeded/",$line,$email)){ + $stm_queue[$day].= "insert or ignore into mail_status (info) values ('".$email[8]."');\n"; + $stm_queue[$day].= "update mail_to set status=(select id from mail_status where info='reject'), status_info='queue file size limit exceeded' where from_id in (select id from mail_from where sid='".$email[3]."' and server='".$email[2]."');\n"; } + #Nov 9 02:14:57 srvch011 postfix/cleanup[6856]: 617A51F5AC5: warning: header Subject: Mapeamento de Processos from lxalpha.12b.com.br[66.109.29.225]; from= to= proto=ESMTP helo= #Nov 8 09:31:50 srvch011 postfix/cleanup[11471]: 19C281F59C8: reject: header From: "Giuliana Flores - Parceiro do Grupo Virtual" from pm03-974.auinmeio.com.br[177.70.232.225]; from= to= proto=ESMTP helo=: 5.7.1 [SN007] - else if(preg_match("/(\w+\s+\d+\s+[0-9,:]+) (\w+) postfix.cleanup\W\d+\W+(\w+): (\w+): header (.*) from ([a-z,A-Z,0-9,.,-]+)\W([0-9,.]+)\W+from=\<(.*)\> to=\<(.*)\>.*helo=\W([a-z,A-Z,0-9,.,-]+)\W(.*|)/",$line,$email)){ + #Nov 13 00:03:24 srvch011 postfix/cleanup[4192]: 8A5B31F52D2: reject: body http://platform.roastcrack.info/mj0ie6p-48qtiyq from move2.igloojack.info[173.239.63.16]; from= to= proto=SMTP helo=: 5.7.1 [BD040] + #Nov 14 01:41:35 srvch011 postfix/cleanup[58446]: 1EF3F1F573A: warning: header Subject: =?windows-1252?Q?IMOVEL_Voc=EA_=E9_um_Cliente_especial_da_=93CENTURY21=22?=??=?windows-1252?Q?Veja_o_que_tenho_para_voc=EA?= from mail-yw0-f51.google.com[209.85.213.51]; from= to= proto=ESMTP helo= + else if(preg_match("/(\w+\s+\d+\s+[0-9,:]+) (\w+) postfix.cleanup\W\d+\W+(\w+): (\w+): (.*) from ([a-z,A-Z,0-9,.,-]+)\W([0-9,.]+)\W+from=\<(.*)\> to=\<(.*)\>.*helo=\W([a-z,A-Z,0-9,.,-]+)(.*)/",$line,$email)){ $status['date']=$email[1]; $status['server']=$email[2]; $status['sid']=$email[3]; @@ -87,21 +221,26 @@ function grep_log($from="",$to="",$subject=""){ $status['to']=$email[9]; $status['helo']=$email[10]; $status['status']=$email[4]; - + $stm_queue[$day].= "insert or ignore into mail_status (info) values ('".$email[4]."');\n"; if ($email[4] =="warning"){ - $status['status_info']=preg_replace("/(\s+|\'|\")/"," ",$email[11]); - $status['subject']=preg_replace("/Subject: /","",$email[5]); - $status['subject']=preg_replace("/(\s+|\'|\")/"," ",$status['subject']); - $stm_queue.="update mail_queue set subject='".$status['subject']."', status='".$status['status']."', status_info='".$status['info']."', fromm='".$status['from']."',too='".$status['size']."',helo='".$status['helo']."' where sid='".$status['sid']."';\n"; + $stm_queue[$day].= "insert or ignore into mail_status (info) values ('incoming');\n"; + #print "$line\n"; + $status['status_info']=preg_replace("/(\<|\>|\s+|\'|\")/"," ",$email[11]); + $status['subject']=preg_replace("/header Subject: /","",$email[5]); + $status['subject']=preg_replace("/(\<|\>|\s+|\'|\")/"," ",$status['subject']); + $stm_queue[$day].="update mail_from set subject='".$status['subject']."', fromm='".strtolower($status['from'])."',helo='".$status['helo']."' where sid='".$status['sid']."';\n"; + $stm_queue[$day].="insert or ignore into mail_to (from_id,too,status,status_info) VALUES ((select id from mail_from where sid='".$email[3]."' and server='".$email[2]."'),'".strtolower($status['to'])."',(select id from mail_status where info='incoming'),'".$status['status_info']."');\n"; + $stm_queue[$day].="update or ignore mail_to set status=(select id from mail_status where info='incoming'), status_info='".$status['status_info']."', too='".strtolower($status['to'])."' where from_id in (select id from mail_from where sid='".$status['sid']."' and server='".$email[2]."');\n"; } else{ - $status['status_info']=$email[5].$email[11]; - $stm_queue.="update mail_queue set status='".$status['status']."', status_info='".$status['status_info']."', fromm='".$status['from']."',too='".$status['to']."',helo='".$status['helo']."' where sid='".$status['sid']."';\n"; - } + $stm_queue[$day].="update mail_from set fromm='".strtolower($status['from'])."',helo='".$status['helo']."' where sid='".$status['sid']."';\n"; + $status['status_info']=preg_replace("/(\<|\>|\s+|\'|\")/"," ",$email[5].$email[11]); + $stm_queue[$day].="insert or ignore into mail_to (from_id,too,status,status_info) VALUES ((select id from mail_from where sid='".$email[3]."' and server='".$email[2]."'),'".strtolower($status['to'])."',(select id from mail_status where info='".$email[4]."'),'".$status['status_info']."');\n"; + $stm_queue[$day].="update or ignore mail_to set status=(select id from mail_status where info='".$email[4]."'), status_info='".$status['status_info']."', too='".strtolower($status['to'])."' where from_id in (select id from mail_from where sid='".$status['sid']."' and server='".$email[2]."');\n"; + } } - #Nov 9 02:14:34 srvchunk01 postfix/smtpd[38129]: NOQUEUE: reject: RCPT from unknown[201.36.98.7]: 450 4.7.1 Client host rejected: cannot find your hostname, [201.36.98.7]; from= to= proto=ESMTP helo= + #Nov 9 02:14:34 srvch011 postfix/smtpd[38129]: NOQUEUE: reject: RCPT from unknown[201.36.0.7]: 450 4.7.1 Client host rejected: cannot find your hostname, [201.36.98.7]; from= to= proto=ESMTP helo= else if(preg_match("/(\w+\s+\d+\s+[0-9,:]+) (\w+) postfix.smtpd\W\d+\W+NOQUEUE:\s+(\w+): (.*); from=\<(.*)\> to=\<(.*)\>.*helo=\<(.*)\>/",$line,$email)){ - $status['sid']='NOQUEUE'; $status['date']=$email[1]; $status['server']=$email[2]; $status['status']=$email[3]; @@ -109,124 +248,219 @@ function grep_log($from="",$to="",$subject=""){ $status['from']=$email[5]; $status['to']=$email[6]; $status['helo']=$email[7]; - $values="'".$status['date']."','".$status['status']."','".$status['status_info']."','".$status['from']."','".$status['to']."','".$status['helo']."'"; - $stm_noqueue.='insert into mail_noqueue(date,status,status_info,fromm,too,helo) values('.$values.');'."\n"; + $values="'".$status['date']."','".$status['status']."','".$status['status_info']."','".strtolower($status['from'])."','".strtolower($status['to'])."','".$status['helo']."','".$status['server']."'"; + $stm_noqueue[$day].='insert or ignore into mail_noqueue(date,status,status_info,fromm,too,helo,server) values ('.$values.');'."\n"; } - if ($total_lines%1000 == 0){ + if ($total_lines%1500 == 0){ #save log in database - write_db($stm_noqueue."COMMIT;","noqueue"); - write_db($stm_queue."COMMIT;","queue"); - $stm_noqueue="BEGIN;\n"; - $stm_queue="BEGIN;\n"; - } - if ($total_lines%1000 == 0) + write_db($stm_noqueue,"noqueue",$days); + write_db($stm_queue,"from",$days); + foreach ($days as $d){ + $stm_noqueue[$d]="BEGIN;\n"; + $stm_queue[$d]="BEGIN;\n"; + } + } + if ($total_lines%1500 == 0) print "$line\n"; } #save log in database - write_db($stm_noqueue."COMMIT;","noqueue"); - write_db($stm_queue."COMMIT;","queue"); - $stm_noqueue="BEGIN;\n"; - $stm_queue="BEGIN;\n"; + write_db($stm_noqueue,"noqueue",$days); + write_db($stm_queue,"from",$days); + foreach ($days as $d){ + $stm_noqueue[$d]="BEGIN;\n"; + $stm_queue[$d]="BEGIN;\n"; + } } + + $config=parse_xml_config("{$g['conf_path']}/config.xml", $g['xml_rootobj']); + print count($config['installedpackages']); + #start db replication if configured + if ($config['installedpackages']['postfixsync']['config'][0]['rsync']) + foreach ($config['installedpackages']['postfixsync']['config'] as $rs ) + foreach($rs['row'] as $sh){ + $sync_to_ip = $sh['ipaddress']; + $sync_type = $sh['sync_type']; + $password = $sh['password']; + print "checking replication to $sync_to_ip..."; + if ($password && $sync_to_ip && preg_match("/(both|database)/",$sync_type)) + postfix_do_xmlrpc_sync($sync_to_ip, $password,$sync_type); + print "ok\n"; + } + } -function write_db($stm,$table){ - global $postfix_dir,$postfix_db; - print date("H:i:s") . " writing db..."; - $dbhandle = sqlite_open($postfix_dir.'/'.$postfix_db, 0666, $error); - $ok = sqlite_exec($dbhandle, $stm, $error); - if (!$ok) - die ("Cannot execute query. $error\n$stm\n"); +function write_db($stm,$table,$days){ + global $postfix_dir,$config,$g; + conf_mount_rw(); + $do_sync=array(); + print "writing to database..."; + foreach ($days as $day) + if (strlen($stm[$day]) > 10){ + if ($config['installedpackages']['postfixsync']['config'][0]) + foreach ($config['installedpackages']['postfixsync']['config'] as $rs ) + foreach($rs['row'] as $sh){ + $sync_to_ip = $sh['ipaddress']; + $sync_type = $sh['sync_type']; + $password = $sh['password']; + $sql_file='/var/db/postfix/'.$sync_to_ip.'.sql'; + ${$sync_to_ip}=""; + if (file_exists($sql_file)) + ${$sync_to_ip}=file_get_contents($sql_file); + if ($sync_to_ip && $sync_type=="share"){ + ${$sync_to_ip}.=serialize(array('day'=> $day,'sql'=> base64_encode(gzcompress($stm[$day]."COMMIT;",9))))."\n"; + if (! in_array($sync_to_ip,$do_sync)) + $do_sync[]=$sync_to_ip; + } + } + #write local db file + create_db($day.".db"); + if ($debug=true) + print " writing to local db $day..."; + $dbhandle = sqlite_open($postfix_dir.$day.".db", 0666, $error); + if (!$dbhandle) die ($error); + #file_put_contents("/tmp/".$key.'-'.$update['day'].".sql",gzuncompress(base64_decode($update['sql'])), LOCK_EX); + $ok = sqlite_exec($dbhandle, $stm[$day]."COMMIT;", $error); + if (!$ok){ + if ($debug=true) + print ("Cannot execute query. $error\n".$stm[$day]."COMMIT;\n"); + } + else{ + if ($debug=true) + print "ok\n"; + } + sqlite_close($dbhandle); + } + #write update sql files + if (count ($do_sync) > 0 ){ + + foreach($do_sync as $ip) + file_put_contents('/var/db/postfix/'.$ip.'.sql',${$ip},LOCK_EX); + conf_mount_ro(); + } + #write local file - print "ok "; - $result = sqlite_query($dbhandle, "select count(*) ".$table." from mail_".$table); - $row = sqlite_fetch_array($result, SQLITE_ASSOC); - print $table .":". $row[$table]."\n"; - sqlite_close($dbhandle); -echo "
"; } -function create_db(){ - global $postfix_dir,$postfix_db,$postfix_arg; - if ($postfix_arg['time']== "-01 day"){ - unlink_if_exists($postfix_dir.'/'.$postfix_db); - unlink_if_exists($postfix_dir.'/'.$postfix_db."-journal"); - } +function create_db($postfix_db){ + global $postfix_dir,$postfix_arg; if (! is_dir($postfix_dir)) mkdir($postfix_dir,0775); - $new_db=(file_exists($postfix_dir.'/'.$postfix_db)?1:0); - $dbhandle = sqlite_open($postfix_dir.'/'.$postfix_db, 0666, $error); - if (!$dbhandle) die ($error); + $new_db=(file_exists($postfix_dir.$postfix_db)?1:0); $stm = << array(date("H:i",strtotime('-1 min',$curr_time))), + 'time' => '-1 min'); + break; case "10min": $postfix_arg=array( 'grep' => array(substr(date("H:i",strtotime('-10 min',$curr_time)),0,-1)), 'time' => '-10 min'); @@ -235,17 +469,22 @@ switch ($argv[1]){ $postfix_arg=array( 'grep' => array(date("H:",strtotime('-01 hour',$curr_time))), 'time' => '-01 hour'); break; + case "04hour": + $postfix_arg=array( 'grep' => array(date("H:",strtotime('-04 hour',$curr_time)),date("H:",strtotime('-03 hour',$curr_time)), + date("H:",strtotime('-02 hour',$curr_time)),date("H:",strtotime('-01 hour',$curr_time))), + 'time' => '-04 hour'); + break; case "24hours": $postfix_arg=array( 'grep' => array('00:','01:','02:','03:','04:','05:','06:','07:','08:','09:','10:','11:', '12:','13:','14:','15:','16:','17:','18:','19:','20:','21:','22:','23:'), 'time' => '-01 day'); break; - case "24hours2": + case "02days": $postfix_arg=array( 'grep' => array('00:','01:','02:','03:','04:','05:','06:','07:','08:','09:','10:','11:', '12:','13:','14:','15:','16:','17:','18:','19:','20:','21:','22:','23:'), 'time' => '-02 day'); break; - case "24hours3": + case "03days": $postfix_arg=array( 'grep' => array('00:','01:','02:','03:','04:','05:','06:','07:','08:','09:','10:','11:', '12:','13:','14:','15:','16:','17:','18:','19:','20:','21:','22:','23:'), 'time' => '-03 day'); @@ -254,14 +493,23 @@ switch ($argv[1]){ default: die ("invalid parameters\n"); } -$postfix_db=date("Y-m-d",strtotime($postfix_arg['time'],$curr_time)).".db"; +# get remote log from remote server +get_remote_log(); +# get local log from logfile grep_log(); } #http client call if ($_REQUEST['files']!= ""){ #do search - $queue=($_REQUEST['queue']=="QUEUE"?"mail_queue":"mail_noqueue"); + if($_REQUEST['queue']=="QUEUE"){ + $stm="select * from mail_from, mail_to ,mail_status where mail_from.id=mail_to.from_id and mail_to.status=mail_status.id "; + $last_next=" and "; + } + else{ + $stm="select * from mail_noqueue"; + $last_next=" where "; + } $limit_prefix=(preg_match("/\d+/",$_REQUEST['limit'])?"limit ":""); $limit=(preg_match("/\d+/",$_REQUEST['limit'])?$_REQUEST['limit']:""); $files= explode(",", $_REQUEST['files']); @@ -269,40 +517,70 @@ if ($_REQUEST['files']!= ""){ $total_result=0; foreach ($files as $postfix_db) if (file_exists($postfix_dir.'/'.$postfix_db)){ - $last_next=""; $dbhandle = sqlite_open($postfix_dir.'/'.$postfix_db, 0666, $error); - $stm='select * from '.$queue; if ($_REQUEST['from']!= ""){ $next=($last_next==" and "?" and ":" where "); $last_next=" and "; - $stm .=$next."fromm in('".$_REQUEST['from']."')"; + if (preg_match('/\*/',$_REQUEST['from'])) + $stm .=$next."fromm like '".preg_replace('/\*/','%',$_REQUEST['from'])."'"; + else + $stm .=$next."fromm in('".$_REQUEST['from']."')"; } if ($_REQUEST['to']!= ""){ $next=($last_next==" and "?" and ":" where "); $last_next=" and "; - $stm .=$next."too in('".$_REQUEST['to']."')"; + if (preg_match('/\*/',$_REQUEST['to'])) + $stm .=$next."too like '".preg_replace('/\*/','%',$_REQUEST['to'])."'"; + else + $stm .=$next."too in('".$_REQUEST['to']."')"; } - if ($_REQUEST['sid']!= ""){ + if ($_REQUEST['sid']!= "" && $_REQUEST['queue']=="QUEUE"){ $next=($last_next==" and "?" and ":" where "); $last_next=" and "; $stm .=$next."sid in('".$_REQUEST['sid']."')"; } - if ($_REQUEST['subject']!= ""){ + if ($_REQUEST['relay']!= "" && $_REQUEST['queue']=="QUEUE"){ $next=($last_next==" and "?" and ":" where "); $last_next=" and "; - $stm .=$next."subject like '%".$_REQUEST['subject']."%'"; + if (preg_match('/\*/',$_REQUEST['subject'])) + $stm .=$next."relay like '".preg_replace('/\*/','%',$_REQUEST['relay'])."'"; + else + $stm .=$next."relay = '".$_REQUEST['relay']."'"; } - if ($_REQUEST['msgid']!= ""){ + if ($_REQUEST['subject']!= "" && $_REQUEST['queue']=="QUEUE"){ $next=($last_next==" and "?" and ":" where "); $last_next=" and "; - $stm .=$next."msgid = '".$_REQUEST['msgid']."'"; + if (preg_match('/\*/',$_REQUEST['subject'])) + $stm .=$next."subject like '".preg_replace('/\*/','%',$_REQUEST['subject'])."'"; + else + $stm .=$next."subject = '".$_REQUEST['subject']."'"; } + if ($_REQUEST['msgid']!= "" && $_REQUEST['queue']=="QUEUE"){ + $next=($last_next==" and "?" and ":" where "); + $last_next=" and "; + if (preg_match('/\*/',$_REQUEST['msgid'])) + $stm .=$next."msgid like '".preg_replace('/\*/','%',$_REQUEST['msgid'])."'"; + else + $stm .=$next."msgid = '".$_REQUEST['msgid']."'"; + } + if ($_REQUEST['server']!= "" ){ + $next=($last_next==" and "?" and ":" where "); + $last_next=" and "; + if( $_REQUEST['queue']=="QUEUE") + $stm .=$next."mail_from.server = '".$_REQUEST['server']."'"; + else + $stm .=$next."server = '".$_REQUEST['server']."'"; + } + if ($_REQUEST['status']!= ""){ $next=($last_next==" and "?" and ":" where "); $last_next=" and "; - $stm .=$next."status = '".$_REQUEST['status']."'"; + $stm .=$next."mail_status.info = '".$_REQUEST['status']."'"; } - $result = sqlite_query($dbhandle, $stm." order by date desc $limit_prefix $limit "); + #print "
".$stm;
+				#$stm = "select * from mail_to,mail_status where mail_to.status=mail_status.id";
+				$result = sqlite_query($dbhandle, $stm." order by date desc $limit_prefix $limit ");
+				#$result = sqlite_query($dbhandle, $stm."  $limit_prefix $limit ");	
 			if (preg_match("/\d+/",$_REQUEST['limit'])){
 				for ($i = 1; $i <= $limit; $i++) {
 					$row = sqlite_fetch_array($result, SQLITE_ASSOC);
@@ -316,12 +594,32 @@ if ($_REQUEST['files']!= ""){
 			sqlite_close($dbhandle);
 	}
 	$fields= explode(",", $_REQUEST['fields']);
-	if ($queue=="mail_noqueue"){
+	if ($_REQUEST['sbutton']=='export'){
+		print '';
+		print '';
+		print '';
+		$header="";
+		foreach ($stm_fetch as $mail){
+			foreach ($mail as $key => $data){
+				if (!preg_match("/$key/",$header))
+					$header .= $key.",";
+				$export.=preg_replace('/,/',"",$mail[$key]).",";
+				}
+			$export.= "\n";
+		}
+		print '
'.gettext("Search Results").'
";
+		}
+	else{
+	if ($_REQUEST['queue']=="NOQUEUE"){
 		print '';
 		print '';
 		print '';
 		if(in_array("date",$fields))
 			print '';
+		if(in_array("server",$fields))
+			print '';
 		if(in_array("from",$fields))
 			print '';
 		if(in_array("to",$fields))
@@ -337,6 +635,8 @@ if ($_REQUEST['files']!= ""){
 			print '';
 		if(in_array("date",$fields))
 			print '';
+		if(in_array("server",$fields))
+			print '';
 		if(in_array("from",$fields))
 			print '';
 		if(in_array("to",$fields))
@@ -357,6 +657,8 @@ if ($_REQUEST['files']!= ""){
 		print '';
 		if(in_array("date",$fields))
 			print '';
+		if(in_array("server",$fields))
+			print '';
 		if(in_array("from",$fields))
 			print '';
 		if(in_array("to",$fields))
@@ -383,33 +685,34 @@ if ($_REQUEST['files']!= ""){
 			print '';
 		print '';
 		foreach ($stm_fetch as $mail){
-			print '';
 			if(in_array("date",$fields))
-				print '';
+				print '';
+			if(in_array("server",$fields))
+				print '';
 			if(in_array("from",$fields))
-				print '';
+				print '';
 			if(in_array("to",$fields))
-				print '';
+				print '';
 			if(in_array("subject",$fields))
-				print '';
+				print '';
 			if(in_array("delay",$fields))
-				print '';
+				print '';
 			if(in_array("status",$fields))
-				print '';
+				print '';
 			if(in_array("status_info",$fields))
-				print '';
+				print '';
 			if(in_array("size",$fields))
-				print '';
+				print '';
 			if(in_array("helo",$fields))
-				print '';
+				print '';
 			if(in_array("sid",$fields))
-				print '';
+				print '';
 			if(in_array("msgid",$fields))
-				print '';
+				print '';
 			if(in_array("bounce",$fields))
-				print '';
+				print '';
 			if(in_array("relay",$fields))
-				print '';
+				print '';
 			print '';
 			$total_result++;
 		}
@@ -418,6 +721,7 @@ if ($_REQUEST['files']!= ""){
 	print '';
 	print '';
 	print '';	
-	print '
'.gettext("Search Results").'
dateserverFrom
'.$mail['date'].''.$mail['server'].''.$mail['fromm'].'
DateServerFromRelay
'.$mail['date'].''.$mail['mail_from.date'].''.$mail['mail_from.server'].''.$mail['fromm'].''.$mail['mail_from.fromm'].''.$mail['too'].''.$mail['mail_to.too'].''.$mail['subject'].''.$mail['mail_from.subject'].''.$mail['delay'].''.$mail['mail_to.delay'].''.$mail['status'].''.$mail['mail_status.info'].''.$mail['status_info'].''.$mail['mail_to.status_info'].''.$mail['size'].''.$mail['mail_from.size'].''.$mail['helo'].''.$mail['mail_from.helo'].''.$mail['sid'].''.$mail['mail_from.sid'].''.$mail['msgid'].''.$mail['mail_from.msgid'].''.$mail['bounce'].''.$mail['mail_to.bounce'].''.$mail['relay'].''.$mail['mail_to.relay'].'
Total:'.$total_result.'
';					
+	print '';
+	}				
 }
 ?>
\ No newline at end of file
-- 
cgit v1.2.3