last login user tracking

[John Fawcett]

On 22/06/2019 16:59, Lefteris Tsintjelis via dovecot wrote:
> On 22/6/2019 16:07, John Fawcett via dovecot wrote:
>> 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.
> Does it work with IPv4 as well as IPv6? What is the optimal way to
> declare the rip field as?

yes IPv4 and IPv6. The value is in a string format. You can define a
VARCHAR(45) to hold it since no IPv4 or IPv6 address will be longer than
that.

John