last-login Plugin

[John Fawcett]

On 16/11/2020 11:55, Andrea Gabellini wrote:
> Hello,
>
> i'm using last_login plugin and store data into a MySql DB. I'm using
> version 2.3.10.1 and the config is at the end of the email.
>
> I would like to store also the remote IP and the session name. The
> config is working but other informations are written only the first
> time. Any update is ignored but last_login time.
>
> Debugging MySQL, I notice that dovecot is using this query:
>
> INSERT INTO last_login (last_login,userid,rip,protocol,session) VALUES
> (1605350938,'xxxx',x.x.x.x','imap','jLFs5A609cdSN4Wh') ON DUPLICATE KEY
> UPDATE last_login=1605350938
>
> How can I modify the configuration?
>
>
>
> conf.d/95-last_login.conf:
> dict {
>     lastlogin = mysql:/etc/dovecot/dovecot-last-login.conf.ext
> }
>
> plugin {
>     last_login_dict = proxy::lastlogin
>     last_login_key = last-login/%u/%r/%s/%{session}
> }
>
> protocol imap {
>     mail_plugins = $mail_plugins last_login
> }
> protocol pop3 {
>     mail_plugins = $mail_plugins last_login
> }
>
> dovecot-last-login.conf.ext:
> connect = host=/var/lib/mysql/mysql.sock dbname=dovecot user=xxx
> password=xxx
>
> map {
>     pattern = shared/last-login/$userid/$rip/$service/$session
>     table = last_login
>     value_field = last_login
>     value_type = uint
>
>     fields {
>         userid = $userid
>         rip = $rip
>         protocol = $service
>         session = $session
>     }
> }
>
>
One solution is to use a primary key containing all the columns except
last_login. The side effect is that you can get more than one row per
userid, but if you order them by last_login you can get the last one.

John