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

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

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

Hi,

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

Thanks,
Sunil