1

Topic: Track user last login time

- iRedMail version (check /etc/iredmail-release)
==> 0.9.9
- Deployed with iRedMail Easy or the downloadable installer?
==>downloadable installer?
- Linux/BSD distribution name and version
==>  Debian Linux 9
- Store mail accounts in which backend (LDAP/MySQL/PGSQL)
==> MySQL
- Web server (Apache or Nginx)
==> Nginx
- Manage mail accounts with iRedAdmin-Pro?
==> Yes.
- [IMPORTANT] Related original log or error message is required if you're experiencing an issue.
====

Hi,

I have enable User Last login feature and it is successfully implemented along with date, time and protocal.

Also i want IP address too show in this feature.

Please provide us how can we implement the same?

Thanks,
Sunil

----

Spider Email Archiver: On-Premises, lightweight email archiving software developed by iRedMail team. Supports Amazon S3 compatible storage and custom branding.

2

Re: Track user last login time

Should be possible. FYI:
https://doc.dovecot.org/configuration_m … in_plugin/
https://doc.dovecot.org/configuration_m … variables/

3

Re: Track user last login time

Hi,

Thanks for update. I have  already enable this and getting output in database, but need same output in iredmail admin panel too just like last login columns shows in admin panel.

Please let me is this possible?

Thanks,
Sunil

4

Re: Track user last login time

RajeshM wrote:

I have  already enable this and getting output in database, but need same output in iredmail admin panel too just like last login columns shows in admin panel.

Would you mind sharing the detailed configuration? so that i can reproduce it and integrate it in iRedMail first, then iRedAdmin-Pro.

btw, are you sure the IP address get updated for each login? I failed to achieve this, because Dovecot updates only the last login TIME and ignore the last login IP address. I doubt you get this fixed. smile

5

Re: Track user last login time

ZhangHuangbin wrote:
RajeshM wrote:

I have  already enable this and getting output in database, but need same output in iredmail admin panel too just like last login columns shows in admin panel.

Would you mind sharing the detailed configuration? so that i can reproduce it and integrate it in iRedMail first, then iRedAdmin-Pro.

btw, are you sure the IP address get updated for each login? I failed to achieve this, because Dovecot updates only the last login TIME and ignore the last login IP address. I doubt you get this fixed. smile

Hi,

Following are the details

Dovecot config as below
################################################
protocol imap {
    # Append plugin name `last_login` here
    mail_plugins = last_login
}

protocol pop3 {
    # Append plugin name `last_login` here
    mail_plugins = last_login
}

dict {
    # Add this line. For FreeBSD, please replace the path by
    # /usr/local/etc/dovecot/dovecot-last-login.conf
    lastlogin = mysql:/etc/dovecot/dovecot-last-login.conf
}

plugin {
    # Add 2 lines
    last_login_dict = proxy::lastlogin
    last_login_key = last-login/%s/%u/%d/%r/%l
}


##################################################

SQL statement:

################################################

CREATE TABLE IF NOT EXISTS `last_login` (
    `username` VARCHAR(255) NOT NULL DEFAULT '',
    `domain` VARCHAR(255) NOT NULL DEFAULT '',
    `ip` varchar(40) NOT NULL DEFAULT '',
    `imap` INT(11) DEFAULT NULL,
    `pop3` INT(11) DEFAULT NULL,
    `lda` INT(11) DEFAULT NULL,
    PRIMARY KEY (`username`),
    INDEX (`domain`),
    INDEX (`ip`),
    INDEX (`imap`),
    INDEX (`pop3`),
    INDEX (`lda`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

################################################


/etc/dovecot/dovecot-last-login.conf file content

#################################################

connect = host=127.0.0.1 port=3306 dbname=vmail user=vmailadmin password=my_secret_password

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

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

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

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

# If you want to track the time of last email delivered to mailbox via LDA,
# please enable `last_login` plugin in the `protocol lda {}` block in dovecot.conf.
#map {
#    pattern = shared/last-login/lda/$user/$domain/$ip
#    table = last_login
#    value_field = lda
#    value_type = uint
#
#    fields {
#        username = $user
#        domain = $domain
#                ip = $ip
#    }
#}

# If you want to track the time of last email delivered to mailbox via LMTP,
# please enable `last_login` plugin in the `protocol lmtp {}` block in dovecot.conf.
# We treat lmtp as lda, store the time in sql column `lda`.
#map {
#    pattern = shared/last-login/lmtp/$user/$domain/$ip
#    table = last_login
#    value_field = lda
#    value_type = uint
#
#    fields {
#        username = $user
#        domain = $domain
#                ip = $ip
#    }
#}

#################################################

db screenshot attach for reference

Thanks,
Sunil

Post's attachments

Untitled-1.png
Untitled-1.png 14.25 kb, 1 downloads since 2020-08-19 

You don't have the permssions to download the attachments of this post.

6

Re: Track user last login time

Again, are you sure the IP address get updated for each login?

7

Re: Track user last login time

ZhangHuangbin wrote:

Again, are you sure the IP address get updated for each login?

Hi,

You are right IP address not get updated for each login. Sorry i have not posted modified sql statement yesterday.

That is why i have modified sql statement as below

############################################## 

CREATE TABLE IF NOT EXISTS `last_login` (
    `username` VARCHAR(255) NOT NULL DEFAULT '',
    `domain` VARCHAR(255) NOT NULL DEFAULT '',
    `ip` VARCHAR(40) NOT NULL DEFAULT '',
    `imap` INT(11) DEFAULT NULL,
    `pop3` INT(11) DEFAULT NULL,
    `lda` INT(11) DEFAULT NULL,
    `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`username`,`ip`),
    INDEX (`domain`),
    INDEX (`timestamp`),
    INDEX (`imap`),
    INDEX (`pop3`),
    INDEX (`lda`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

##############################################

in above statement i have define two primary key one for username and other for ip, so if user loged in with different ip address or isp, immediately new row get inserted in db and with the help of current timestamp i can easily trace out last login.

Thanks
Sunil

8

Re: Track user last login time

RajeshM wrote:

    PRIMARY KEY (`username`,`ip`),

I don't think this is a good idea.

If user logins from multiple IP addresses on different devices, you will get multiple records. How are you going to clean up this sql table? and how do you define which records should be removed?

9

Re: Track user last login time

ZhangHuangbin wrote:
RajeshM wrote:

    PRIMARY KEY (`username`,`ip`),

I don't think this is a good idea.

If user logins from multiple IP addresses on different devices, you will get multiple records. How are you going to clean up this sql table? and how do you define which records should be removed?

Hi,

can truncate the table and keep last 3 logins in sql.

Thanks,
Sunil

10

Re: Track user last login time

- What's the SQL commands to keep last 3 logins?
- I'm afraid that i have no plan to implement this imperfect solution. sad