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?
