Custom Reports for Untangle

• Remote Access to the internal PostgreSQL database
• pgAdmin

I wanted to create custom reports for Spam Blocker.

They should have a admin's most concerned info:

I send a simple email to see what will be recorded in the database:

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

Then, I use pgAdmin to browse related tables:

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.

File src/mail-casing/api/com/untangle/node/mail/papi/AddressKind.java tell us what the value in the kind field refer to:

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");

The following sql query should get what we wanted:

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') 

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:

• libpq-dev
• libc6-dev
• linux-libc-dev

Install the perl DBD::Pg module.

Enable cgi-bin script folder by creating file /etc/apache2/conf.d/cgi-bin.conf:

 ScriptAlias /cgi-bin/ /usr/lib/cgi-bin/

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

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;

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