Store last access information for mail users
In one legacy system I have a daily cronjob that gathers all imap/pop3/smtp logins and updates a last_access date in the user table. I'm missing that in ISPconfig and would suggest adding something like that.
I've started hacking this together and with the patch below it's shown as a tooltip in the mail user listing. I'm open to any suggestions on how to properly implement this here.
The cron script, run just before log rotation (e.g. as /etc/cron.daily/ispconfig_log_parser):
#!/bin/bash
# Parse the mailserver's logs to update the last_access date for all mail accounts.
NOW=`date +%Y-%m-%d`
#NOW=$1
# Filter imap/pop/smtp logins
# prefix cat with 'ssh <slaveserver>' when mail is on a slave server (use command="cat /var/log/mail.log" in the authorized_keys file to restrict access).
cat /var/log/mail.log \
| perl -n -e "if (\$_ =~ s/(.*) dovecot: (imap|pop3)-login: Login: user=\<([\w\.@-]+)\>.*/\$3/ or \$_ =~ s/(.*) sasl_method=PLAIN, sasl_username=([\w\.@-]+)/\$2/) { print \$_}" \
| sort \
| uniq \
| perl -n -e "\$_ =~ s/([\w\.@-]+)/UPDATE mail_user SET last_access='$NOW' WHERE email='\$1';/; print \$_" \
| tee /tmp/mail_stat_access.sql \
| mysql dbispconfig
Adding a column:
ALTER TABLE `mail_user` ADD `last_access` DATE NULL after `disabledoveadm`;
The code:
diff --git a/interface/web/mail/list/mail_user.list.php b/interface/web/mail/list/mail_user.list.php
index 4513a516c..7f4253c82 100755
--- a/interface/web/mail/list/mail_user.list.php
+++ b/interface/web/mail/list/mail_user.list.php
@@ -124,5 +124,13 @@ $liste["item"][] = array( 'field' => "disablepop3",
'suffix' => "",
'width' => "",
'value' => array('n' => $app->lng('yes_txt'), 'y' => $app->lng('no_txt')));
+$liste["item"][] = array( 'field' => "last_access",
+ 'datatype' => "DATE",
+ 'formtype' => "TEXT",
+ 'op' => "=",
+ 'prefix' => "",
+ 'suffix' => "",
+ 'width' => "",
+ 'value' => "");
?>
diff --git a/interface/web/mail/templates/mail_user_list.htm b/interface/web/mail/templates/mail_user_list.htm
index 4e2fedd50..d73850923 100755
--- a/interface/web/mail/templates/mail_user_list.htm
+++ b/interface/web/mail/templates/mail_user_list.htm
@@ -59,7 +59,7 @@
<tbody>
<tmpl_loop name="records">
<tr>
- <td><a href="#" data-load-content="mail/mail_user_edit.php?id={tmpl_var name='id'}" data-toggle="tooltip" data-placement="bottom" title="{tmpl_var name='email'}">{tmpl_var name="email"}</a></td>
+ <td><a href="#" data-load-content="mail/mail_user_edit.php?id={tmpl_var name='id'}" data-toggle="tooltip" data-placement="bottom" title="Last access: {tmpl_var name='last_access'}">{tmpl_var name="email"}</a></td>
<tmpl_if name="enable_custom_login">
<td>
<a href="#" data-load-content="mail/mail_user_edit.php?id={tmpl_var name='id'}">{tmpl_var name="login"}</a>