[Dovecot] Possible to log IMAP connections to MySQL Table?

[Marcin Gryszkalis]

On Wednesday of December 24 2008, Corey Shaw wrote:
> I'll take a look at the post login scripting.  It sounds like it would be
> much better than a script that simply tails the log file for any IMAP
> connections.  Thanks for the help!

I used such script and it works fine (on not really busy site though):

I think you could use better way to find out if it's imap or pop3 (better than 
looking if $ENV{POP3_CLIENT_WORKAROUNDS} exists), but it works for me...

#!/usr/bin/perl
use DBI;
$db_user = 'dovecot';
$db_pass = 'DOVECOTPASSWORD';
$db_db = 'system';

$mode = exists $ENV{POP3_CLIENT_WORKAROUNDS} ? "pop3" : "imap";
$ip = $ENV{IP};

$u = $ENV{USER};
$virtual = $u =~ m/(.*)\@(.*)/ ? 1 : 0;
if ($virtual)
{
    $local = $1;
    $domain = $2;

    $dbh = DBI->connect("DBI:mysql:$db_db", $db_user, $db_pass) || 
die "Could not connect to database: $DBI::errstr";
    $dbh->do('UPDATE user set lastlogin=now(), lastlogin_ip=? where username=? 
and domain=?', undef, $ip, $local, $domain);
    $dbh->disconnect();
}

exec "/usr/local/libexec/dovecot/$mode";




-- 
Marcin Gryszkalis, PGP 0x9F183FA3 
jabber jid:mg at fork.pl, gg:2532994
http://the.fork.pl