aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
Diffstat
-rw-r--r--config/lightsquid/sqstat.css136
-rw-r--r--config/postfix/postfix.php1494
2 files changed, 815 insertions, 815 deletions
diff --git a/config/lightsquid/sqstat.css b/config/lightsquid/sqstat.css
index 4f5cc7d6..7575933e 100644
--- a/config/lightsquid/sqstat.css
+++ b/config/lightsquid/sqstat.css
@@ -1,68 +1,68 @@
-/* "connections" table */
-TABLE.result{
- border:1px solid #ccccdd;border-collapse:collapse;
-}
-TABLE.result TH{
- font-family: Verdana;font-size:14px;
-}
-TABLE.result TD{
- font-family: Verdana;font-size:11px;border:1px solid #c0c0c0;padding:2px;
-}
-TABLE.result TR.total TD{
- background-color:#DCDAD5;
-}
-
-TABLE.result TH{
- background-color:#ccccdd;
- white-space: nowrap; padding: 0px 2px;
-}
-
-TABLE.result tr.odd td {
- background-color: #eef;
-}
-TABLE.result tr.odd td#white {
- background-color: #fff;
-}
-TABLE.result td#highlight {
- background-color: #e9e9e9;
-}
-
-
-/* top header */
-DIV.header{
- border:3px solid #ccccdd;margin-bottom:10px;padding:3px;
- font-family: Verdana;font-size:12pt;
-}
-.copyleft,SELECT{
- font-family: Verdana;font-size:10px;
-}
-.copyleft A{
- text-decoration:none
-}
-.copyleft A:HOVER{
- text-decoration:underline
-}
-FORM{
- margin:0;padding:0;
-}
-
-#dhtmltooltip{
- position: absolute;
- /* width: 350px; */
- border: 2px solid black;
- padding: 2px;
- background-color: lightyellow;
- visibility: hidden;
- z-index: 100;
- font-family: Verdana; font-size: 10px;
-}
-
-
-#dhtmlpointer{
- position:absolute;
- left: -300px;
- z-index: 101;
- visibility: hidden;
-}
-
-
+/* "connections" table */
+TABLE.result{
+ border:1px solid #ccccdd;border-collapse:collapse;
+}
+TABLE.result TH{
+ font-family: Verdana;font-size:14px;
+}
+TABLE.result TD{
+ font-family: Verdana;font-size:11px;border:1px solid #c0c0c0;padding:2px;
+}
+TABLE.result TR.total TD{
+ background-color:#DCDAD5;
+}
+
+TABLE.result TH{
+ background-color:#ccccdd;
+ white-space: nowrap; padding: 0px 2px;
+}
+
+TABLE.result tr.odd td {
+ background-color: #eef;
+}
+TABLE.result tr.odd td#white {
+ background-color: #fff;
+}
+TABLE.result td#highlight {
+ background-color: #e9e9e9;
+}
+
+
+/* top header */
+DIV.header{
+ border:3px solid #ccccdd;margin-bottom:10px;padding:3px;
+ font-family: Verdana;font-size:12pt;
+}
+.copyleft,SELECT{
+ font-family: Verdana;font-size:10px;
+}
+.copyleft A{
+ text-decoration:none
+}
+.copyleft A:HOVER{
+ text-decoration:underline
+}
+FORM{
+ margin:0;padding:0;
+}
+
+#dhtmltooltip{
+ position: absolute;
+ /* width: 350px; */
+ border: 2px solid black;
+ padding: 2px;
+ background-color: lightyellow;
+ visibility: hidden;
+ z-index: 100;
+ font-family: Verdana; font-size: 10px;
+}
+
+
+#dhtmlpointer{
+ position:absolute;
+ left: -300px;
+ z-index: 101;
+ visibility: hidden;
+}
+
+
diff --git a/config/postfix/postfix.php b/config/postfix/postfix.php
index a6542e42..ff42918c 100644
--- a/config/postfix/postfix.php
+++ b/config/postfix/postfix.php
@@ -1,748 +1,748 @@
-<?php
-/*
- postfix.php
- part of pfSense (http://www.pfsense.com/)
- Copyright (C) 2011 Marcello Coutinho <marcellocoutinho@gmail.com>
- based on varnish_view_config.
- All rights reserved.
-
- Redistribution and use in source and binary forms, with or without
- modification, are permitted provided that the following conditions are met:
-
- 1. Redistributions of source code must retain the above copyright notice,
- this list of conditions and the following disclaimer.
-
- 2. Redistributions in binary form must reproduce the above copyright
- notice, this list of conditions and the following disclaimer in the
- documentation and/or other materials provided with the distribution.
-
- THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESS OR IMPLIED WARRANTIES,
- INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY
- AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE
- AUTHOR BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY,
- OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
- SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
- INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
- CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
- ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
- POSSIBILITY OF SUCH DAMAGE.
-*/
-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");
-require_once("/usr/local/pkg/postfix.inc");
-
-$uname=posix_uname();
-if ($uname['machine']=='amd64')
- ini_set('memory_limit', '250M');
-
-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="\(MailScanner\|postfix.cleanup\|postfix.smtp\|postfix.error\|postfix.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
- foreach ($postfix_arg['grep'] as $hour){
- 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);
- 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 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[$day].='insert or ignore into mail_from(sid,date,server,client) values ('.$values.');'."\n";
- ${$email[3]}=$email[3];
- }
- #Dec 2 22:21:18 pfsense MailScanner[60670]: Requeue: 8DC3BBDEAF.A29D3 to 5AD9ABDEB5
- else if (preg_match("/(\w+\s+\d+\s+[0-9,:]+) (\w+) MailScanner.*Requeue: (\w+)\W\w+ to (\w+)/",$line,$email)){
- $stm_queue[$day].= "update or ignore mail_from set sid='".$email[4]."' where sid='".$email[3]."';\n";
- }
- #Dec 5 14:06:10 srvchunk01 MailScanner[19589]: Message 775201F44B1.AED2C from 209.185.111.50 (marcellocoutinho@mailtest.com) to sede.mail.test.com is spam, SpamAssassin (not cached, escore=99.202, requerido 6, autolearn=spam, DKIM_SIGNED 0.10, DKIM_VALID -0.10, DKIM_VALID_AU -0.10, FREEMAIL_FROM 0.00, HTML_MESSAGE 0.00, RCVD_IN_DNSWL_LOW -0.70, WORM_TEST2 100.00)
- else if (preg_match("/(\w+\s+\d+\s+[0-9,:]+) (\w+) MailScanner\W\d+\W+\w+\s+(\w+).* is spam, (.*)/",$line,$email)){
- $stm_queue[$day].= "insert or ignore into mail_status (info) values ('spam');\n";
- print "\n#######################################\nSPAM:".$email[4].$email[3].$email[2]."\n#######################################\n";
- $stm_queue[$day].= "update or ignore mail_to set status=(select id from mail_status where info='spam'), status_info='".preg_replace("/(\<|\>|\s+|\'|\")/"," ",$email[4])."' where from_id in (select id from mail_from where sid='".$email[3]."' and server='".$email[2]."');\n";
- }
- #Nov 14 09:29:32 srvch011 postfix/error[58443]: 2B8EB1F5A5A: to=<hildae.sva@pi.email.com>, 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.test.com type=A: Host not found, try again)
- #Nov 3 21:45:32 srvch011 postfix/smtp[18041]: 4CE321F4887: to=<viinil@vitive.com.br>, 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=<alessandra.bueno@mg.test.com>, relay=mail.mg.test.com[172.25.3.5]:25, delay=39, delays=35/1.1/0.04/2.7, dsn=5.7.1, status=bounced (host mail.mg.test.com[172.25.3.5] said: 550 5.7.1 Unable to relay for alessandra.bueno@mg.test.com (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[$day].="update mail_from set msgid='".$email[4]."' where sid='".$email[3]."';\n";
- }
- #Nov 14 02:40:05 srvch011 postfix/qmgr[46834]: BC5931F4F13: from=<ceag@mx.crmcom.br>, 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[$day].= "update mail_from set fromm='".strtolower($email[4])."', size='".$email[5]."' 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=<apache@lxalpha.12b.com.br> to=<ritiele.faria@mail.test.com> proto=ESMTP helo=<lxalpha.12b.com.br>
- #Nov 8 09:31:50 srvch011 postfix/cleanup[11471]: 19C281F59C8: reject: header From: "Giuliana Flores - Parceiro do Grupo Virtual" <publicidade@parceiro-grupovirtual.com.br> from pm03-974.auinmeio.com.br[177.70.232.225]; from=<publicidade@parceiro-grupovirtual.com.br> to=<jorge.lustosa@mail.test.com> proto=ESMTP helo=<pm03-974.auinmeio.com.br>: 5.7.1 [SN007]
- #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=<ljmd6u8lrxke4@move2.igloojack.info> to=<edileva@aasdf..br> proto=SMTP helo=<move2.igloojack.info>: 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=<sergioalexandre6308@gmail.com> to=<sinza@tr.br> proto=ESMTP helo=<mail-yw0-f51.google.com>
- 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];
- $status['remote_hostname']=$email[6];
- $status['remote_ip']=$email[7];
- $status['from']=$email[8];
- $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"){
- if (${$status['sid']}=='hold'){
- $status['status']='hold';
- }
- else{
- $status['status']='incoming';
- $stm_queue[$day].= "insert or ignore into mail_status (info) values ('".$status['status']."');\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='".$status['status']."'),'".$status['status_info']."');\n";
- $stm_queue[$day].="update or ignore mail_to set status=(select id from mail_status where info='".$status['status']."'), 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['sid']}=$status['status'];
- $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 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=<maladireta@esadcos.com.br> to=<sexec.09vara@go.domain.test.com> proto=ESMTP helo=<capri0.wb.com.br>
- 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['date']=$email[1];
- $status['server']=$email[2];
- $status['status']=$email[3];
- $status['status_info']=$email[4];
- $status['from']=$email[5];
- $status['to']=$email[6];
- $status['helo']=$email[7];
- $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%1500 == 0){
- #save log in database
- 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,"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,$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
-
-}
-
-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);
-$stm = <<<EOF
- CREATE TABLE "mail_from"(
- "id" INTEGER PRIMARY KEY,
- "sid" VARCHAR(11) NOT NULL,
- "client" TEXT NOT NULL,
- "msgid" TEXT,
- "fromm" TEXT,
- "size" INTEGER,
- "subject" TEXT,
- "date" TEXT NOT NULL,
- "server" TEXT,
- "helo" TEXT
-);
- CREATE TABLE "mail_to"(
- "id" INTEGER PRIMARY KEY,
- "from_id" INTEGER NOT NULL,
- "too" TEXT,
- "status" INTEGER,
- "status_info" TEXT,
- "smtp" TEXT,
- "delay" TEXT,
- "relay" TEXT,
- "dsn" TEXT,
- "server" TEXT,
- "bounce" TEXT,
- FOREIGN KEY (status) REFERENCES mail_status(id),
- FOREIGN KEY (from_id) REFERENCES mail_from(id)
-);
-
-
-CREATE TABLE "mail_status"(
- "id" INTEGER PRIMARY KEY,
- "info" varchar(35) NOT NULL
-);
-
-CREATE TABLE "mail_noqueue"(
- "id" INTEGER PRIMARY KEY,
- "date" TEXT NOT NULL,
- "server" TEXT NOT NULL,
- "status" TEXT NOT NULL,
- "status_info" INTEGER NOT NULL,
- "fromm" TEXT NOT NULL,
- "too" TEXT NOT NULL,
- "helo" TEXT NOT NULL
-);
-
-CREATE TABLE "db_version"(
- "value" varchar(10),
- "info" TEXT
-);
-
-insert or ignore into db_version ('value') VALUES ('2.3.1');
-
-CREATE INDEX "noqueue_unique" on mail_noqueue (date ASC, fromm ASC, too ASC);
-CREATE INDEX "noqueue_helo" on mail_noqueue (helo ASC);
-CREATE INDEX "noqueue_too" on mail_noqueue (too ASC);
-CREATE INDEX "noqueue_fromm" on mail_noqueue (fromm ASC);
-CREATE INDEX "noqueue_info" on mail_noqueue (status_info ASC);
-CREATE INDEX "noqueue_status" on mail_noqueue (status ASC);
-CREATE INDEX "noqueue_server" on mail_noqueue (server ASC);
-CREATE INDEX "noqueue_date" on mail_noqueue (date ASC);
-
-CREATE UNIQUE INDEX "status_info" on mail_status (info ASC);
-
-CREATE UNIQUE INDEX "from_sid_server" on mail_from (sid ASC,server ASC);
-CREATE INDEX "from_client" on mail_from (client ASC);
-CREATE INDEX "from_helo" on mail_from (helo ASC);
-CREATE INDEX "from_server" on mail_from (server ASC);
-CREATE INDEX "from_subject" on mail_from (subject ASC);
-CREATE INDEX "from_msgid" on mail_from (msgid ASC);
-CREATE INDEX "from_fromm" on mail_from (fromm ASC);
-CREATE INDEX "from_date" on mail_from (date ASC);
-
-CREATE UNIQUE INDEX "mail_to_unique" on mail_to (from_id ASC, too ASC);
-CREATE INDEX "to_bounce" on mail_to (bounce ASC);
-CREATE INDEX "to_relay" on mail_to (relay ASC);
-CREATE INDEX "to_smtp" on mail_to (smtp ASC);
-CREATE INDEX "to_info" on mail_to (status_info ASC);
-CREATE INDEX "to_status" on mail_to (status ASC);
-CREATE INDEX "to_too" on mail_to (too ASC);
-
-EOF;
-#test file version
-print "checking". $postfix_dir.$postfix_db."\n";
-$dbhandle = sqlite_open($postfix_dir.$postfix_db, 0666, $error);
-if (!$dbhandle) die ($error);
-$ok = sqlite_exec($dbhandle,"select value from db_version", $error);
-sqlite_close($dbhandle);
-if (!$ok){
- print "delete previous table version\n";
- if (file_exists($postfix_dir.$postfix_db))
- unlink($postfix_dir.$postfix_db);
- $new_db=0;
-}
-if ($new_db==0){
- $dbhandle = sqlite_open($postfix_dir.$postfix_db, 0666, $error);
- $ok = sqlite_exec($dbhandle, $stm, $error);
- if (!$ok)
- print ("Cannot execute query. $error\n");
- $ok = sqlite_exec($dbhandle, $stm2, $error);
- if (!$ok)
- print ("Cannot execute query. $error\n");
- sqlite_close($dbhandle);
- }
-}
-
-$postfix_dir="/var/db/postfix/";
-$curr_time = time();
-#console script call
-if ($argv[1]!=""){
-switch ($argv[1]){
- case "01min":
- $postfix_arg=array( 'grep' => 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');
- break;
- case "01hour":
- $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 "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 "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');
- break;
-
- default:
- die ("invalid parameters\n");
-}
-# get remote log from remote server
-get_remote_log();
-# get local log from logfile
-grep_log();
-}
-
-#http client call
-if ($_REQUEST['files']!= ""){
- #do search
- 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']);
- $stm_fetch=array();
- $total_result=0;
- foreach ($files as $postfix_db)
- if (file_exists($postfix_dir.'/'.$postfix_db)){
- $dbhandle = sqlite_open($postfix_dir.'/'.$postfix_db, 0666, $error);
- if ($_REQUEST['from']!= ""){
- $next=($last_next==" and "?" and ":" where ");
- $last_next=" and ";
- 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 ";
- if (preg_match('/\*/',$_REQUEST['to']))
- $stm .=$next."too like '".preg_replace('/\*/','%',$_REQUEST['to'])."'";
- else
- $stm .=$next."too in('".$_REQUEST['to']."')";
- }
- if ($_REQUEST['sid']!= "" && $_REQUEST['queue']=="QUEUE"){
- $next=($last_next==" and "?" and ":" where ");
- $last_next=" and ";
- $stm .=$next."sid in('".$_REQUEST['sid']."')";
- }
- if ($_REQUEST['relay']!= "" && $_REQUEST['queue']=="QUEUE"){
- $next=($last_next==" and "?" and ":" where ");
- $last_next=" and ";
- if (preg_match('/\*/',$_REQUEST['subject']))
- $stm .=$next."relay like '".preg_replace('/\*/','%',$_REQUEST['relay'])."'";
- else
- $stm .=$next."relay = '".$_REQUEST['relay']."'";
- }
- if ($_REQUEST['subject']!= "" && $_REQUEST['queue']=="QUEUE"){
- $next=($last_next==" and "?" and ":" where ");
- $last_next=" and ";
- 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."mail_status.info = '".$_REQUEST['status']."'";
- }
- #print "<pre>".$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);
- if (is_array($row))
- $stm_fetch[]=$row;
- }
- }
- else{
- $stm_fetch = sqlite_fetch_all($result, SQLITE_ASSOC);
- }
- sqlite_close($dbhandle);
- }
- $fields= explode(",", $_REQUEST['fields']);
- if ($_REQUEST['sbutton']=='export'){
- print '<table class="tabcont" width="100%" border="0" cellpadding="8" cellspacing="0">';
- print '<tr><td colspan="'.count($fields).'" valign="top" class="listtopic">'.gettext("Search Results").'</td></tr>';
- print '<tr>';
- $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 '<td class="tabcont"><textarea id="varnishlogs" rows="50" cols="100%">';
- print "This export is in csv format, paste it without this line on any software that handles csv files.\n\n".$header."\n".$export;
- print "</textarea></td></tr></table>";
- }
- else{
- if ($_REQUEST['queue']=="NOQUEUE"){
- print '<table class="tabcont" width="100%" border="0" cellpadding="8" cellspacing="0">';
- print '<tr><td colspan="'.count($fields).'" valign="top" class="listtopic">'.gettext("Search Results").'</td></tr>';
- print '<tr>';
- if(in_array("date",$fields))
- print '<td class="listlr"><strong>date</strong></td>';
- if(in_array("server",$fields))
- print '<td class="listlr"><strong>server</strong></td>';
- if(in_array("from",$fields))
- print '<td class="listlr"><strong>From</strong></td>';
- if(in_array("to",$fields))
- print '<td class="listlr"><strong>to</strong></td>';
- if(in_array("helo",$fields))
- print '<td class="listlr"><strong>Helo</strong></td>';
- if(in_array("status",$fields))
- print '<td class="listlr"><strong>Status</strong></td>';
- if(in_array("status_info",$fields))
- print '<td class="listlr"><strong>Status Info</strong></td>';
- print '</tr>';
- foreach ($stm_fetch as $mail){
- print '<tr>';
- if(in_array("date",$fields))
- print '<td class="listlr">'.$mail['date'].'</td>';
- if(in_array("server",$fields))
- print '<td class="listlr">'.$mail['server'].'</td>';
- if(in_array("from",$fields))
- print '<td class="listlr">'.$mail['fromm'].'</td>';
- if(in_array("to",$fields))
- print '<td class="listlr">'.$mail['too'].'</td>';
- if(in_array("helo",$fields))
- print '<td class="listlr">'.$mail['helo'].'</td>';
- if(in_array("status",$fields))
- print '<td class="listlr">'.$mail['status'].'</td>';
- if(in_array("status_info",$fields))
- print '<td class="listlr">'.$mail['status_info'].'</td>';
- print '</tr>';
- $total_result++;
- }
- }
- else{
- print '<table class="tabcont" width="100%" border="0" cellpadding="8" cellspacing="0">';
- print '<tr><td colspan="'.count($fields).'" valign="top" class="listtopic">'.gettext("Search Results").'</td></tr>';
- print '<tr>';
- if(in_array("date",$fields))
- print '<td class="listlr" ><strong>Date</strong></td>';
- if(in_array("server",$fields))
- print '<td class="listlr" ><strong>Server</strong></td>';
- if(in_array("from",$fields))
- print '<td class="listlr" ><strong>From</strong></td>';
- if(in_array("to",$fields))
- print '<td class="listlr" ><strong>to</strong></td>';
- if(in_array("subject",$fields))
- print '<td class="listlr" ><strong>Subject</strong></td>';
- if(in_array("delay",$fields))
- print '<td class="listlr" ><strong>Delay</strong></td>';
- if(in_array("status",$fields))
- print '<td class="listlr" ><strong>Status</strong></td>';
- if(in_array("status_info",$fields))
- print '<td class="listlr" ><strong>Status Info</strong></td>';
- if(in_array("size",$fields))
- print '<td class="listlr" ><strong>Size</strong></td>';
- if(in_array("helo",$fields))
- print '<td class="listlr" ><strong>Helo</strong></td>';
- if(in_array("sid",$fields))
- print '<td class="listlr" ><strong>SID</strong></td>';
- if(in_array("msgid",$fields))
- print '<td class="listlr" ><strong>MSGID</strong></td>';
- if(in_array("bounce",$fields))
- print '<td class="listlr" ><strong>Bounce</strong></td>';
- if(in_array("relay",$fields))
- print '<td class="listlr" ><strong>Relay</strong></td>';
- print '</tr>';
- foreach ($stm_fetch as $mail){
- if(in_array("date",$fields))
- print '<td class="listlr">'.$mail['mail_from.date'].'</td>';
- if(in_array("server",$fields))
- print '<td class="listlr">'.$mail['mail_from.server'].'</td>';
- if(in_array("from",$fields))
- print '<td class="listlr">'.$mail['mail_from.fromm'].'</td>';
- if(in_array("to",$fields))
- print '<td class="listlr">'.$mail['mail_to.too'].'</td>';
- if(in_array("subject",$fields))
- print '<td class="listlr">'.$mail['mail_from.subject'].'</td>';
- if(in_array("delay",$fields))
- print '<td class="listlr">'.$mail['mail_to.delay'].'</td>';
- if(in_array("status",$fields))
- print '<td class="listlr">'.$mail['mail_status.info'].'</td>';
- if(in_array("status_info",$fields))
- print '<td class="listlr">'.$mail['mail_to.status_info'].'</td>';
- if(in_array("size",$fields))
- print '<td class="listlr">'.$mail['mail_from.size'].'</td>';
- if(in_array("helo",$fields))
- print '<td class="listlr">'.$mail['mail_from.helo'].'</td>';
- if(in_array("sid",$fields))
- print '<td class="listlr">'.$mail['mail_from.sid'].'</td>';
- if(in_array("msgid",$fields))
- print '<td class="listlr">'.$mail['mail_from.msgid'].'</td>';
- if(in_array("bounce",$fields))
- print '<td class="listlr">'.$mail['mail_to.bounce'].'</td>';
- if(in_array("relay",$fields))
- print '<td class="listlr">'.$mail['mail_to.relay'].'</td>';
- print '</tr>';
- $total_result++;
- }
- }
- print '<tr>';
- print '<td ><strong>Total:</strong></td>';
- print '<td ><strong>'.$total_result.'</strong></td>';
- print '</tr>';
- print '</table>';
- }
-}
+<?php
+/*
+ postfix.php
+ part of pfSense (http://www.pfsense.com/)
+ Copyright (C) 2011 Marcello Coutinho <marcellocoutinho@gmail.com>
+ based on varnish_view_config.
+ All rights reserved.
+
+ Redistribution and use in source and binary forms, with or without
+ modification, are permitted provided that the following conditions are met:
+
+ 1. Redistributions of source code must retain the above copyright notice,
+ this list of conditions and the following disclaimer.
+
+ 2. Redistributions in binary form must reproduce the above copyright
+ notice, this list of conditions and the following disclaimer in the
+ documentation and/or other materials provided with the distribution.
+
+ THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESS OR IMPLIED WARRANTIES,
+ INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY
+ AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE
+ AUTHOR BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY,
+ OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
+ SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
+ INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
+ CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
+ ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
+ POSSIBILITY OF SUCH DAMAGE.
+*/
+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");
+require_once("/usr/local/pkg/postfix.inc");
+
+$uname=posix_uname();
+if ($uname['machine']=='amd64')
+ ini_set('memory_limit', '250M');
+
+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="\(MailScanner\|postfix.cleanup\|postfix.smtp\|postfix.error\|postfix.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
+ foreach ($postfix_arg['grep'] as $hour){
+ 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);
+ 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 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[$day].='insert or ignore into mail_from(sid,date,server,client) values ('.$values.');'."\n";
+ ${$email[3]}=$email[3];
+ }
+ #Dec 2 22:21:18 pfsense MailScanner[60670]: Requeue: 8DC3BBDEAF.A29D3 to 5AD9ABDEB5
+ else if (preg_match("/(\w+\s+\d+\s+[0-9,:]+) (\w+) MailScanner.*Requeue: (\w+)\W\w+ to (\w+)/",$line,$email)){
+ $stm_queue[$day].= "update or ignore mail_from set sid='".$email[4]."' where sid='".$email[3]."';\n";
+ }
+ #Dec 5 14:06:10 srvchunk01 MailScanner[19589]: Message 775201F44B1.AED2C from 209.185.111.50 (marcellocoutinho@mailtest.com) to sede.mail.test.com is spam, SpamAssassin (not cached, escore=99.202, requerido 6, autolearn=spam, DKIM_SIGNED 0.10, DKIM_VALID -0.10, DKIM_VALID_AU -0.10, FREEMAIL_FROM 0.00, HTML_MESSAGE 0.00, RCVD_IN_DNSWL_LOW -0.70, WORM_TEST2 100.00)
+ else if (preg_match("/(\w+\s+\d+\s+[0-9,:]+) (\w+) MailScanner\W\d+\W+\w+\s+(\w+).* is spam, (.*)/",$line,$email)){
+ $stm_queue[$day].= "insert or ignore into mail_status (info) values ('spam');\n";
+ print "\n#######################################\nSPAM:".$email[4].$email[3].$email[2]."\n#######################################\n";
+ $stm_queue[$day].= "update or ignore mail_to set status=(select id from mail_status where info='spam'), status_info='".preg_replace("/(\<|\>|\s+|\'|\")/"," ",$email[4])."' where from_id in (select id from mail_from where sid='".$email[3]."' and server='".$email[2]."');\n";
+ }
+ #Nov 14 09:29:32 srvch011 postfix/error[58443]: 2B8EB1F5A5A: to=<hildae.sva@pi.email.com>, 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.test.com type=A: Host not found, try again)
+ #Nov 3 21:45:32 srvch011 postfix/smtp[18041]: 4CE321F4887: to=<viinil@vitive.com.br>, 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=<alessandra.bueno@mg.test.com>, relay=mail.mg.test.com[172.25.3.5]:25, delay=39, delays=35/1.1/0.04/2.7, dsn=5.7.1, status=bounced (host mail.mg.test.com[172.25.3.5] said: 550 5.7.1 Unable to relay for alessandra.bueno@mg.test.com (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[$day].="update mail_from set msgid='".$email[4]."' where sid='".$email[3]."';\n";
+ }
+ #Nov 14 02:40:05 srvch011 postfix/qmgr[46834]: BC5931F4F13: from=<ceag@mx.crmcom.br>, 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[$day].= "update mail_from set fromm='".strtolower($email[4])."', size='".$email[5]."' 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=<apache@lxalpha.12b.com.br> to=<ritiele.faria@mail.test.com> proto=ESMTP helo=<lxalpha.12b.com.br>
+ #Nov 8 09:31:50 srvch011 postfix/cleanup[11471]: 19C281F59C8: reject: header From: "Giuliana Flores - Parceiro do Grupo Virtual" <publicidade@parceiro-grupovirtual.com.br> from pm03-974.auinmeio.com.br[177.70.232.225]; from=<publicidade@parceiro-grupovirtual.com.br> to=<jorge.lustosa@mail.test.com> proto=ESMTP helo=<pm03-974.auinmeio.com.br>: 5.7.1 [SN007]
+ #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=<ljmd6u8lrxke4@move2.igloojack.info> to=<edileva@aasdf..br> proto=SMTP helo=<move2.igloojack.info>: 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=<sergioalexandre6308@gmail.com> to=<sinza@tr.br> proto=ESMTP helo=<mail-yw0-f51.google.com>
+ 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];
+ $status['remote_hostname']=$email[6];
+ $status['remote_ip']=$email[7];
+ $status['from']=$email[8];
+ $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"){
+ if (${$status['sid']}=='hold'){
+ $status['status']='hold';
+ }
+ else{
+ $status['status']='incoming';
+ $stm_queue[$day].= "insert or ignore into mail_status (info) values ('".$status['status']."');\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='".$status['status']."'),'".$status['status_info']."');\n";
+ $stm_queue[$day].="update or ignore mail_to set status=(select id from mail_status where info='".$status['status']."'), 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['sid']}=$status['status'];
+ $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 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=<maladireta@esadcos.com.br> to=<sexec.09vara@go.domain.test.com> proto=ESMTP helo=<capri0.wb.com.br>
+ 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['date']=$email[1];
+ $status['server']=$email[2];
+ $status['status']=$email[3];
+ $status['status_info']=$email[4];
+ $status['from']=$email[5];
+ $status['to']=$email[6];
+ $status['helo']=$email[7];
+ $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%1500 == 0){
+ #save log in database
+ 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,"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,$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
+
+}
+
+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);
+$stm = <<<EOF
+ CREATE TABLE "mail_from"(
+ "id" INTEGER PRIMARY KEY,
+ "sid" VARCHAR(11) NOT NULL,
+ "client" TEXT NOT NULL,
+ "msgid" TEXT,
+ "fromm" TEXT,
+ "size" INTEGER,
+ "subject" TEXT,
+ "date" TEXT NOT NULL,
+ "server" TEXT,
+ "helo" TEXT
+);
+ CREATE TABLE "mail_to"(
+ "id" INTEGER PRIMARY KEY,
+ "from_id" INTEGER NOT NULL,
+ "too" TEXT,
+ "status" INTEGER,
+ "status_info" TEXT,
+ "smtp" TEXT,
+ "delay" TEXT,
+ "relay" TEXT,
+ "dsn" TEXT,
+ "server" TEXT,
+ "bounce" TEXT,
+ FOREIGN KEY (status) REFERENCES mail_status(id),
+ FOREIGN KEY (from_id) REFERENCES mail_from(id)
+);
+
+
+CREATE TABLE "mail_status"(
+ "id" INTEGER PRIMARY KEY,
+ "info" varchar(35) NOT NULL
+);
+
+CREATE TABLE "mail_noqueue"(
+ "id" INTEGER PRIMARY KEY,
+ "date" TEXT NOT NULL,
+ "server" TEXT NOT NULL,
+ "status" TEXT NOT NULL,
+ "status_info" INTEGER NOT NULL,
+ "fromm" TEXT NOT NULL,
+ "too" TEXT NOT NULL,
+ "helo" TEXT NOT NULL
+);
+
+CREATE TABLE "db_version"(
+ "value" varchar(10),
+ "info" TEXT
+);
+
+insert or ignore into db_version ('value') VALUES ('2.3.1');
+
+CREATE INDEX "noqueue_unique" on mail_noqueue (date ASC, fromm ASC, too ASC);
+CREATE INDEX "noqueue_helo" on mail_noqueue (helo ASC);
+CREATE INDEX "noqueue_too" on mail_noqueue (too ASC);
+CREATE INDEX "noqueue_fromm" on mail_noqueue (fromm ASC);
+CREATE INDEX "noqueue_info" on mail_noqueue (status_info ASC);
+CREATE INDEX "noqueue_status" on mail_noqueue (status ASC);
+CREATE INDEX "noqueue_server" on mail_noqueue (server ASC);
+CREATE INDEX "noqueue_date" on mail_noqueue (date ASC);
+
+CREATE UNIQUE INDEX "status_info" on mail_status (info ASC);
+
+CREATE UNIQUE INDEX "from_sid_server" on mail_from (sid ASC,server ASC);
+CREATE INDEX "from_client" on mail_from (client ASC);
+CREATE INDEX "from_helo" on mail_from (helo ASC);
+CREATE INDEX "from_server" on mail_from (server ASC);
+CREATE INDEX "from_subject" on mail_from (subject ASC);
+CREATE INDEX "from_msgid" on mail_from (msgid ASC);
+CREATE INDEX "from_fromm" on mail_from (fromm ASC);
+CREATE INDEX "from_date" on mail_from (date ASC);
+
+CREATE UNIQUE INDEX "mail_to_unique" on mail_to (from_id ASC, too ASC);
+CREATE INDEX "to_bounce" on mail_to (bounce ASC);
+CREATE INDEX "to_relay" on mail_to (relay ASC);
+CREATE INDEX "to_smtp" on mail_to (smtp ASC);
+CREATE INDEX "to_info" on mail_to (status_info ASC);
+CREATE INDEX "to_status" on mail_to (status ASC);
+CREATE INDEX "to_too" on mail_to (too ASC);
+
+EOF;
+#test file version
+print "checking". $postfix_dir.$postfix_db."\n";
+$dbhandle = sqlite_open($postfix_dir.$postfix_db, 0666, $error);
+if (!$dbhandle) die ($error);
+$ok = sqlite_exec($dbhandle,"select value from db_version", $error);
+sqlite_close($dbhandle);
+if (!$ok){
+ print "delete previous table version\n";
+ if (file_exists($postfix_dir.$postfix_db))
+ unlink($postfix_dir.$postfix_db);
+ $new_db=0;
+}
+if ($new_db==0){
+ $dbhandle = sqlite_open($postfix_dir.$postfix_db, 0666, $error);
+ $ok = sqlite_exec($dbhandle, $stm, $error);
+ if (!$ok)
+ print ("Cannot execute query. $error\n");
+ $ok = sqlite_exec($dbhandle, $stm2, $error);
+ if (!$ok)
+ print ("Cannot execute query. $error\n");
+ sqlite_close($dbhandle);
+ }
+}
+
+$postfix_dir="/var/db/postfix/";
+$curr_time = time();
+#console script call
+if ($argv[1]!=""){
+switch ($argv[1]){
+ case "01min":
+ $postfix_arg=array( 'grep' => 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');
+ break;
+ case "01hour":
+ $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 "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 "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');
+ break;
+
+ default:
+ die ("invalid parameters\n");
+}
+# get remote log from remote server
+get_remote_log();
+# get local log from logfile
+grep_log();
+}
+
+#http client call
+if ($_REQUEST['files']!= ""){
+ #do search
+ 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']);
+ $stm_fetch=array();
+ $total_result=0;
+ foreach ($files as $postfix_db)
+ if (file_exists($postfix_dir.'/'.$postfix_db)){
+ $dbhandle = sqlite_open($postfix_dir.'/'.$postfix_db, 0666, $error);
+ if ($_REQUEST['from']!= ""){
+ $next=($last_next==" and "?" and ":" where ");
+ $last_next=" and ";
+ 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 ";
+ if (preg_match('/\*/',$_REQUEST['to']))
+ $stm .=$next."too like '".preg_replace('/\*/','%',$_REQUEST['to'])."'";
+ else
+ $stm .=$next."too in('".$_REQUEST['to']."')";
+ }
+ if ($_REQUEST['sid']!= "" && $_REQUEST['queue']=="QUEUE"){
+ $next=($last_next==" and "?" and ":" where ");
+ $last_next=" and ";
+ $stm .=$next."sid in('".$_REQUEST['sid']."')";
+ }
+ if ($_REQUEST['relay']!= "" && $_REQUEST['queue']=="QUEUE"){
+ $next=($last_next==" and "?" and ":" where ");
+ $last_next=" and ";
+ if (preg_match('/\*/',$_REQUEST['subject']))
+ $stm .=$next."relay like '".preg_replace('/\*/','%',$_REQUEST['relay'])."'";
+ else
+ $stm .=$next."relay = '".$_REQUEST['relay']."'";
+ }
+ if ($_REQUEST['subject']!= "" && $_REQUEST['queue']=="QUEUE"){
+ $next=($last_next==" and "?" and ":" where ");
+ $last_next=" and ";
+ 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."mail_status.info = '".$_REQUEST['status']."'";
+ }
+ #print "<pre>".$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);
+ if (is_array($row))
+ $stm_fetch[]=$row;
+ }
+ }
+ else{
+ $stm_fetch = sqlite_fetch_all($result, SQLITE_ASSOC);
+ }
+ sqlite_close($dbhandle);
+ }
+ $fields= explode(",", $_REQUEST['fields']);
+ if ($_REQUEST['sbutton']=='export'){
+ print '<table class="tabcont" width="100%" border="0" cellpadding="8" cellspacing="0">';
+ print '<tr><td colspan="'.count($fields).'" valign="top" class="listtopic">'.gettext("Search Results").'</td></tr>';
+ print '<tr>';
+ $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 '<td class="tabcont"><textarea id="varnishlogs" rows="50" cols="100%">';
+ print "This export is in csv format, paste it without this line on any software that handles csv files.\n\n".$header."\n".$export;
+ print "</textarea></td></tr></table>";
+ }
+ else{
+ if ($_REQUEST['queue']=="NOQUEUE"){
+ print '<table class="tabcont" width="100%" border="0" cellpadding="8" cellspacing="0">';
+ print '<tr><td colspan="'.count($fields).'" valign="top" class="listtopic">'.gettext("Search Results").'</td></tr>';
+ print '<tr>';
+ if(in_array("date",$fields))
+ print '<td class="listlr"><strong>date</strong></td>';
+ if(in_array("server",$fields))
+ print '<td class="listlr"><strong>server</strong></td>';
+ if(in_array("from",$fields))
+ print '<td class="listlr"><strong>From</strong></td>';
+ if(in_array("to",$fields))
+ print '<td class="listlr"><strong>to</strong></td>';
+ if(in_array("helo",$fields))
+ print '<td class="listlr"><strong>Helo</strong></td>';
+ if(in_array("status",$fields))
+ print '<td class="listlr"><strong>Status</strong></td>';
+ if(in_array("status_info",$fields))
+ print '<td class="listlr"><strong>Status Info</strong></td>';
+ print '</tr>';
+ foreach ($stm_fetch as $mail){
+ print '<tr>';
+ if(in_array("date",$fields))
+ print '<td class="listlr">'.$mail['date'].'</td>';
+ if(in_array("server",$fields))
+ print '<td class="listlr">'.$mail['server'].'</td>';
+ if(in_array("from",$fields))
+ print '<td class="listlr">'.$mail['fromm'].'</td>';
+ if(in_array("to",$fields))
+ print '<td class="listlr">'.$mail['too'].'</td>';
+ if(in_array("helo",$fields))
+ print '<td class="listlr">'.$mail['helo'].'</td>';
+ if(in_array("status",$fields))
+ print '<td class="listlr">'.$mail['status'].'</td>';
+ if(in_array("status_info",$fields))
+ print '<td class="listlr">'.$mail['status_info'].'</td>';
+ print '</tr>';
+ $total_result++;
+ }
+ }
+ else{
+ print '<table class="tabcont" width="100%" border="0" cellpadding="8" cellspacing="0">';
+ print '<tr><td colspan="'.count($fields).'" valign="top" class="listtopic">'.gettext("Search Results").'</td></tr>';
+ print '<tr>';
+ if(in_array("date",$fields))
+ print '<td class="listlr" ><strong>Date</strong></td>';
+ if(in_array("server",$fields))
+ print '<td class="listlr" ><strong>Server</strong></td>';
+ if(in_array("from",$fields))
+ print '<td class="listlr" ><strong>From</strong></td>';
+ if(in_array("to",$fields))
+ print '<td class="listlr" ><strong>to</strong></td>';
+ if(in_array("subject",$fields))
+ print '<td class="listlr" ><strong>Subject</strong></td>';
+ if(in_array("delay",$fields))
+ print '<td class="listlr" ><strong>Delay</strong></td>';
+ if(in_array("status",$fields))
+ print '<td class="listlr" ><strong>Status</strong></td>';
+ if(in_array("status_info",$fields))
+ print '<td class="listlr" ><strong>Status Info</strong></td>';
+ if(in_array("size",$fields))
+ print '<td class="listlr" ><strong>Size</strong></td>';
+ if(in_array("helo",$fields))
+ print '<td class="listlr" ><strong>Helo</strong></td>';
+ if(in_array("sid",$fields))
+ print '<td class="listlr" ><strong>SID</strong></td>';
+ if(in_array("msgid",$fields))
+ print '<td class="listlr" ><strong>MSGID</strong></td>';
+ if(in_array("bounce",$fields))
+ print '<td class="listlr" ><strong>Bounce</strong></td>';
+ if(in_array("relay",$fields))
+ print '<td class="listlr" ><strong>Relay</strong></td>';
+ print '</tr>';
+ foreach ($stm_fetch as $mail){
+ if(in_array("date",$fields))
+ print '<td class="listlr">'.$mail['mail_from.date'].'</td>';
+ if(in_array("server",$fields))
+ print '<td class="listlr">'.$mail['mail_from.server'].'</td>';
+ if(in_array("from",$fields))
+ print '<td class="listlr">'.$mail['mail_from.fromm'].'</td>';
+ if(in_array("to",$fields))
+ print '<td class="listlr">'.$mail['mail_to.too'].'</td>';
+ if(in_array("subject",$fields))
+ print '<td class="listlr">'.$mail['mail_from.subject'].'</td>';
+ if(in_array("delay",$fields))
+ print '<td class="listlr">'.$mail['mail_to.delay'].'</td>';
+ if(in_array("status",$fields))
+ print '<td class="listlr">'.$mail['mail_status.info'].'</td>';
+ if(in_array("status_info",$fields))
+ print '<td class="listlr">'.$mail['mail_to.status_info'].'</td>';
+ if(in_array("size",$fields))
+ print '<td class="listlr">'.$mail['mail_from.size'].'</td>';
+ if(in_array("helo",$fields))
+ print '<td class="listlr">'.$mail['mail_from.helo'].'</td>';
+ if(in_array("sid",$fields))
+ print '<td class="listlr">'.$mail['mail_from.sid'].'</td>';
+ if(in_array("msgid",$fields))
+ print '<td class="listlr">'.$mail['mail_from.msgid'].'</td>';
+ if(in_array("bounce",$fields))
+ print '<td class="listlr">'.$mail['mail_to.bounce'].'</td>';
+ if(in_array("relay",$fields))
+ print '<td class="listlr">'.$mail['mail_to.relay'].'</td>';
+ print '</tr>';
+ $total_result++;
+ }
+ }
+ print '<tr>';
+ print '<td ><strong>Total:</strong></td>';
+ print '<td ><strong>'.$total_result.'</strong></td>';
+ print '</tr>';
+ print '</table>';
+ }
+}
?> \ No newline at end of file
generated by cgit v1.2.3 (git 2.25.1) at 2024-11-23 20:50:41 +0000