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.
On 21 Jun 2019, at 15:13, David Mehler via dovecot <dovecot at dovecot.org> wrote:> the problem is the last login value does not mean anything to me.It is standard unix ?seconds since the epoch? and can be converted into any format you want in any time zone at any time by using the date command. # date -r 1555555555 +%F-%T 2019-04-17-20:45:55 -- Mos Eisley spaceport. You will never find a more wretched hive of scum and villainy. We must be cautious.
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
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?