[Dovecot] mailbox not acceced

[mouss]

Osvaldo Alvarez Pozo wrote:
> i changed permissions, but  no results
> So i added a field to the table mailbox like this:
> alter table mailbox add last_login datetime NOT NULL default
> '0000-00-00 00:00:00';
>   

That's not a strictly valid date. better use a real date ('1970-01-01 
00:00:00' for instance). software that accesses databases in a portable 
manner may break if you use non portable dates.

> for the moment I run two cron jobs
> 1 egrep "dovecot: (imap|pop3)-login" /var/log/mail.log >/var/log/maillog
>
>   

since you're using perl, no need for the egrep part. see below.

> 2 /root/accounts
> this scripts has de following content:
>
> #!/usr/bin/perl
> use DBI;
>    $dbpath = "dbi:mysql:database=postfix;host=localhost";
>    $dbh = DBI->connect($dbpath, "user","passwd")
>      or die "Can't open database: $DBI::errstr";
>    open (FICHIER ,"/var/log/maillog");
>    while  (<FICHIER>) {
>    ($value0,$value1,undef)=split(/</);
>    ($user,undef)=split(/>/,$value1);
>
>
> $requete = "update mailbox set last_login =now() where  username='$user'";
> $sth = $dbh->prepare($requete);
>
> $sth->execute();
> $sth -> finish;
>    }
> close FICHIER;
> $dbh -> disconnect
>   

#!/usr/bin/perl
use DBI;
use strict;
my $logfile = "/var/log/mail.log"; # or $ARGV[0]...

# syslog doesn't include a year... we could use current time or stat the 
logfile or ...
my $current_year='2008';
my %monthnum =
    qw( Jan 1 Feb 2 Mar 3 Apr 4 May 5 Jun 6 Jul 7 Aug 8 Sep 9 Oct 10 Nov 
11 Dec 12 );

my %lastaccess = ();
open(LOGFILE, "$logfile") or die "Cannot open $logfile: $!\n";
while (<LOGFILE>)
    chomp;
    if (! /dovecot: (imap|pop3)-login/) { next; }
    my ($monthname, $day, $time, $line) = split('\s+', $_, 4);
    # XXX no space in usernames...
    if ($line !~ /user=<(\S+)>/) { next;}
   
    $lastaccess{$1} = $current_year . "-" . $monthnum{$monthname} . 
"-$day $time";
}
close(LOGFILE);

#foreach (keys %lastaccess) { print "$_: $lastaccess{$_}\n"; }

now you have the timestamp of last access for each user. and you can 
update your table.

to avoid, a lot of UPDATE queries, create a temporrary table in the 
script and use INSERT with multiple values in a single query. then 
UPDATE using the temporary table.

> This solution is far from being efficient.The precition is 24 hours
> wich is ok for know. I would really love doing this from dovecot. I am
> disapointed for not being able to make it work from dovecot.
> I use debian Etch & dovecot was compiled by hand, is that important?
>