last login user tracking

[John Fawcett]

On 21/06/2019 23:13, David Mehler via dovecot wrote:
> Hello,
>
> I'm trying to get last login user tracking going, using Dovecot and
> mysql. I'm using:
>
> https://docs.iredmail.org/track.user.last.login.html
>
> and it is working, I am seeing an entry and it is updated, the problem
> is the last login value does not mean anything to me. I was wondering
> if it's possible to have the value be of a last login date and time,
> and also a remote ip would be helpful so I can see from where users
> are logging in from.
>
> Thanks.
> Dave.

You can easily view the value in a readable format with the
FROM_UNIXTIME mysql funciton

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_from-unixtime

To get remote ip

define the rip field in your table and add it to the primary key

add %r to the last_login_key

last_login_key = last-login/%u/%d/%r

add $rip to the pattern and fields section in your
/etc/dovecot/dovecot-last-login.conf

map {
    pattern = shared/last-login/$user/$domain/$rip
    table = last_login
    value_field = last_login
    value_type = uint

    fields {
        username = $user
        domain = $domain
       rip = $rip
    }
}

By adding the rip field to the primary key of the table, you can end up
with multiple rows per username each with different ips, but if you
don't do that, the rip field is never updated after the first insert.
John