====== Custom Reports for Untangle ======

===== Requirement =====
  * Remote Access to the internal PostgreSQL database
  * [[http://www.pgadmin.org/download/|pgAdmin]]

===== Database schema =====

I wanted to create custom reports for **Spam Blocker**.

They should have a admin's most concerned info:

^time_stamp ^sender ^recipient ^subject ^score ^is_spam ^action ^sender_ip ^msg_id^
|2010-01-27 13:04:53.867|mike@3open.org|foo@example.com|Hello, test msg|1.9|f|P|192.168.0.1|14190577|

I send a simple email to see what will be recorded in the database:
<xterm>
voyage:~# telnet 192.168.0.41 25
Trying 192.168.0.41...
Connected to 192.168.0.41.
Escape character is '^]'.
220 MS-654FB7ED3DEC.xp.example.com ESMTP MailEnable Service, Version: 4.17-- ready at 01/27/10 10:51:52
helo neo.minidns.net
250 Requested mail action okay, completed
mail from:root@neo.minidns.net
250 Requested mail action okay, completed
rcpt to:foo@xp.example.com
250 Requested mail action okay, completed
rcpt to:bar@xp.example.com
250 Requested mail action okay, completed
data
354 Start mail input; end with <CRLF>.<CRLF>
subject: test from neo 1

This is a simple test message.

.

250 Requested mail action okay, completed
</xterm>

Then, I use pgAdmin to browse related tables:
{{gallery>untangle:utm-msg-log.png?lightbox}}
{{gallery>untangle:utm-endp-log.png?lightbox}}

<del>So, the missing information is what values will be in the kind field and their meanings. I need it to figure out who is the sender or recipient.
</del>

File ''src/mail-casing/api/com/untangle/node/mail/papi/AddressKind.java'' tell us what the value in the **kind** field refer to:
<code>
public class AddressKind implements Serializable
{
    public static final AddressKind FROM = new AddressKind('F', "FROM");
    public static final AddressKind TO   = new AddressKind('T', "TO");
    public static final AddressKind CC   = new AddressKind('C', "CC");

    // These only apply to SMTP:
    public static final AddressKind ENVELOPE_FROM = new AddressKind('G', "ENVELOPE_FROM");
    public static final AddressKind ENVELOPE_TO   = new AddressKind('B', "ENVELOPE_TO");

    // These only apply to IMAP/POP3:
    public static final AddressKind USER   = new AddressKind('U', "USER");
</code>

The following sql query should get what we wanted:
<code>
SELECT   
  m.time_stamp,  
  a2.addr as sender,  
  a1.addr as recipient, 
  m.subject,
  sa.score,
  sa.is_spam,
  sa.action,
  ep.c_client_addr as sender_ip,
  a1.msg_id
FROM 
  events.n_mail_message_info_addr a1, 
  events.n_mail_message_info_addr a2,
  events.n_mail_message_info m,
  events.n_spam_evt_smtp sa,  /* use n_spam_evt for pop and imap event */
  events.pl_endp ep 

WHERE 
  a1.msg_id = a2.msg_id and
  a2.kind = 'G' and  /* use 'F' for pop and imap */
  a1.kind = 'B' and  /* use 'T' or 'C' for pop and imap */
  a1.msg_id = m.id and
  m.id = sa.msg_id and
  m.pl_endp_id = ep.event_id and
  m.time_stamp >= (date 'today') 
</code>

===== Admin Interface =====
The next task is to build a admin interface. It should allow filter on these criteria:
  * Start/End date
  * Sender Address
  * Recipient Address

I decided to use perl to build it. These packages are needed:
  * [[http://packages.debian.org/lenny/i386/libpq-dev/download|libpq-dev]]
  * [[http://packages.debian.org/lenny/i386/libc6-dev/download|libc6-dev]]
  * [[http://packages.debian.org/lenny/i386/linux-libc-dev/download|linux-libc-dev]]

Install the perl ''DBD::Pg'' module.

Enable ''cgi-bin'' script folder by creating file ''/etc/apache2/conf.d/cgi-bin.conf'':
<file>
 ScriptAlias /cgi-bin/ /usr/lib/cgi-bin/
</file> 

Put the following file in ''/usr/lib/cgi-bin/''

<file - email-report.pl>
#!/usr/bin/perl

# expect these arguments via HTTP POST:
# $sender: sender's email addr
# $rcpt: rcpt's email addr
# $time_frame: number of hours or days to look back from now


use CGI;
use DBI;

$w = CGI->new;

$rcpt = lc $w->param('rcpt');
$sender = lc $w->param('sender');


# setup rcpt clause
if ($rcpt ne 'any' && $rcpt ne '') {
	$sql_rcpt = "lower(a1.addr) like '$rcpt'";
} else {
	$sql_rcpt = "1 = 1"; 
}

# setup sender clause
if ($sender ne 'any' && $sender ne '') {
        $sql_sender = "lower(a2.addr) like '$sender'";
} else {
        $sql_sender = "1 = 1";
}


# TO-DO:
# setup time_frame clause
# default:
$sql_time_frame = "m.time_stamp >= (date 'today' - interval '1 day')";

$dbh = DBI->connect("dbi:Pg:dbname=uvm;host=localhost;", 'postgres', '', {AutoCommit => 0});

$sql = <<EOT;
SELECT   
  m.time_stamp,  
  a2.addr as sender,  
  a1.addr as rcpt, 
  m.subject,
  sa.score,
  sa.action,
  ep.c_client_addr as sender_ip
FROM 
  events.n_mail_message_info_addr a1, 
  events.n_mail_message_info_addr a2,
  events.n_mail_message_info m,
  events.n_spam_evt_smtp sa,
  events.pl_endp ep 
WHERE 
  a1.msg_id = a2.msg_id and
  a2.kind = 'G' and  
  a1.kind = 'B' and 
  a1.msg_id = m.id and
  m.id = sa.msg_id and
  m.pl_endp_id = ep.event_id and
  $sql_sender and
  $sql_rcpt and
  $sql_time_frame
ORDER BY
  m.time_stamp DESC
EOT

$q = $dbh->prepare($sql);
$q->execute;
$numrows = $q->rows;


print $w->header();
print $w->start_html('Custom Report');
print $w->h3("Rows: $numrows");
print "<pre>\n";
print "time_stamp | sender | rcpt | subject | score | action ", "\n";

# TO-DO: use a template to beautify the output, may be use a table
# loop print all rows:
while ($qrow = $q->fetchrow_hashref) {

	$time_stamp = $qrow->{time_stamp};
	$sender = $qrow->{sender};
	$rcpt = $qrow->{rcpt};
	$subject = $qrow->{subject};
	$score = $qrow->{score};
	$action = $qrow->{action};

	print $time_stamp, ' | ', $sender, ' | ', $rcpt, ' | ', $subject, ' | ', $score, ' | ', $action, "\n"; 

}

print "</pre>\n";
print $w->end_html;
</file>

Create a html file to hold the form. Submit form to:\\
''<nowiki>https://<untangle box ip>/cgi-bin/email-report.pl</nowiki>''

----