Re: LastLogin update
> On Jun 23, 2019, at 4:43 AM, @lbutlr via dovecot wrote: > >> https://docs.iredmail.org/track.user.last.login.html > > This is cool, but I have a question: > >> For MySQL/MariaDB backends, we create the sql table in database vmail. > > Would this interfere with or confuse postfixadmin? I use that so that users > can update their own passwords and domain admins can add users and aliases. The document is for iRedMail, it supports storing mail accounts in SQL or OpenLDAP, that's why the document mentions the difference. You're free to use any database on your own mail server. Zhang Huangbin, founder of iRedMail project: https://www.iredmail.org/
Re: LastLogin update
> On 20 Jun 2019, at 11:36, Adrian Minta via dovecot > wrote: > > this seems to work very well: > > https://docs.iredmail.org/track.user.last.login.html This is cool, but I have a question: > For MySQL/MariaDB backends, we create the sql table in database vmail. Would this interfere with or confuse postfixadmin? I use that so that users can update their own passwords and domain admins can add users and aliases. > You could also add "rip = $rip" in "fields" for the ip address. -- Lead me not into temptation, I can find the way.
Re: last login user tracking
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
Re: last login user tracking
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?
Re: last login user tracking
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
