Em 12/11/2021 14:47, Lefteris Tsintjelis escreveu:> Hi, > > I am currently using postfix/dovecot with postfix admin and I track > the last login date already by using this: > > https://doc.dovecot.org/configuration_manual/lastlogin_plugin/ > > Besides last login date, I would like to also implement IPv4 and IPv6 > last login tracking also and if possible, IP based login checking. Is > post-login scripting the best most efficient way to go? > > Regards, > > LefterisHi, This is my lastlogin config to track remote ip: dovecot.conf: dict { ? lastlogin = mysql:/etc/dovecot/dovecot-last-login.conf ? .... } dovecot-last-login.conf: connect = host=127.0.0.1 port=3306 dbname=vmail user=vmailadmin password=XXXXXXXXXXXXXXX map { ??? pattern = shared/last-login/$user/$domain/$rip/$service ??? table = last_login ??? value_field = last_login ??? value_type = uint ??? fields { ??????? username = $user ??????? domain = $domain ??????? rip = $rip ??????? proto = $service ??? } } Mysql table last_login: +------------+--------------+------+-----+---------+-------+ | Field????? | Type???????? | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+-------+ | username?? | varchar(255) | NO?? | PRI |???????? |?????? | | domain???? | varchar(255) | NO?? | MUL |???????? |?????? | | last_login | int(11)????? | YES? | MUL | NULL??? |?????? | | rip??????? | varchar(16)? | NO?? | MUL |???????? |?????? | | proto????? | varchar(10)? | NO?? |???? | NULL??? |?????? | +------------+--------------+------+-----+---------+-------+ Hope this help! ---------------------------------- _ Eng? Julio Cesar Covolato 0v0 <julio at psi.com.br> /(_)\ F: 55-11-99175-9260 ^ ^ PSI INTERNET ----------------------------------
And a litle shell script to query data from db:
#!/bin/sh
#########################################################################################
# ex.: uso: dovecotLastLogin.sh -d 10
#?????? dovecotLastLogin.sh -u julio
#########################################################################################
# For Mysql postfixadmin database
#########################################################################################
MYSQL="mysql --login-path=vmail -Dvmail -t -e"
??????? case $1 in
??????? -d)
??????? DAYSAGO=`date --date="$2 days ago" +%s`
??????? $MYSQL "select
username,last_login.domain,FROM_UNIXTIME(last_login) AS
last_login,rip,proto,active FROM last_login INNER JOIN mailbox USING
(username) WHERE last_login < '$DAYSAGO' ORDER BY
mailbox.active,last_login;"
??????? ;;
??????? -u)
??????? $MYSQL "select
username,last_login.domain,FROM_UNIXTIME(last_login) AS
last_login,rip,proto,active FROM last_login INNER JOIN mailbox USING
(username) WHERE username LIKE '%$2%' ORDER BY
mailbox.active,last_login;"
??????? ;;
??????? *)
??????? echo
??????? echo " USO: dovecotLastLogin.sh [-d -u] [days user]"
??????? echo
??????? echo " -d --> All users whith no login in N days ago, ex.:
dovecotLastLogin.sh -d 90"
??????? echo " -u --> Last login from user, ex.: dovecotLastLogin.sh -u
user at domain.com"
??????? echo " List last login from ALL users from one domain:
dovecotLastLogin.sh -u domain.com"
??????? echo " List lat login from ALL users: dovecotLastLogin.sh -u
%"
??????? exit 1
??????? ;;
??????? esac
# End
----------------------------------
_ Eng? Julio Cesar Covolato
0v0 <julio at psi.com.br>
/(_)\ F: 55-11-99175-9260
^ ^ PSI INTERNET
----------------------------------
Em 12/11/2021 15:33, julio covolato escreveu:>
> Em 12/11/2021 14:47, Lefteris Tsintjelis escreveu:
>> Hi,
>>
>> I am currently using postfix/dovecot with postfix admin and I track
>> the last login date already by using this:
>>
>> https://doc.dovecot.org/configuration_manual/lastlogin_plugin/
>>
>> Besides last login date, I would like to also implement IPv4 and IPv6
>> last login tracking also and if possible, IP based login checking. Is
>> post-login scripting the best most efficient way to go?
>>
>> Regards,
>>
>> Lefteris
>
> Hi,
>
> This is my lastlogin config to track remote ip:
>
> dovecot.conf:
>
> dict {
>
> ? lastlogin = mysql:/etc/dovecot/dovecot-last-login.conf
> ? ....
> }
>
> dovecot-last-login.conf:
>
> connect = host=127.0.0.1 port=3306 dbname=vmail user=vmailadmin
> password=XXXXXXXXXXXXXXX
>
> map {
> ??? pattern = shared/last-login/$user/$domain/$rip/$service
> ??? table = last_login
> ??? value_field = last_login
> ??? value_type = uint
>
> ??? fields {
> ??????? username = $user
> ??????? domain = $domain
> ??????? rip = $rip
> ??????? proto = $service
> ??? }
> }
>
> Mysql table last_login:
>
> +------------+--------------+------+-----+---------+-------+
> | Field????? | Type???????? | Null | Key | Default | Extra |
> +------------+--------------+------+-----+---------+-------+
> | username?? | varchar(255) | NO?? | PRI |???????? |?????? |
> | domain???? | varchar(255) | NO?? | MUL |???????? |?????? |
> | last_login | int(11)????? | YES? | MUL | NULL??? |?????? |
> | rip??????? | varchar(16)? | NO?? | MUL |???????? |?????? |
> | proto????? | varchar(10)? | NO?? |???? | NULL??? |?????? |
> +------------+--------------+------+-----+---------+-------+
>
> Hope this help!
>
> ----------------------------------
> ??? _??? Eng? Julio Cesar Covolato
> ?? 0v0?? <julio at psi.com.br>
> ? /(_)\? F: 55-11-99175-9260
> ?? ^ ^?? PSI INTERNET
> ----------------------------------
>
Exactly what I was looking for and the script as well! Thank you very much Julio On 12/11/2021 20:33, julio covolato wrote:> > Em 12/11/2021 14:47, Lefteris Tsintjelis escreveu: >> Hi, >> >> I am currently using postfix/dovecot with postfix admin and I track >> the last login date already by using this: >> >> https://doc.dovecot.org/configuration_manual/lastlogin_plugin/ >> >> Besides last login date, I would like to also implement IPv4 and IPv6 >> last login tracking also and if possible, IP based login checking. Is >> post-login scripting the best most efficient way to go? >> >> Regards, >> >> Lefteris > > Hi, > > This is my lastlogin config to track remote ip: > > dovecot.conf: > > dict { > > ? lastlogin = mysql:/etc/dovecot/dovecot-last-login.conf > ? .... > } > > dovecot-last-login.conf: > > connect = host=127.0.0.1 port=3306 dbname=vmail user=vmailadmin > password=XXXXXXXXXXXXXXX > > map { > ??? pattern = shared/last-login/$user/$domain/$rip/$service > ??? table = last_login > ??? value_field = last_login > ??? value_type = uint > > ??? fields { > ??????? username = $user > ??????? domain = $domain > ??????? rip = $rip > ??????? proto = $service > ??? } > } > > Mysql table last_login: > > +------------+--------------+------+-----+---------+-------+ > | Field????? | Type???????? | Null | Key | Default | Extra | > +------------+--------------+------+-----+---------+-------+ > | username?? | varchar(255) | NO?? | PRI |???????? |?????? | > | domain???? | varchar(255) | NO?? | MUL |???????? |?????? | > | last_login | int(11)????? | YES? | MUL | NULL??? |?????? | > | rip??????? | varchar(16)? | NO?? | MUL |???????? |?????? | > | proto????? | varchar(10)? | NO?? |???? | NULL??? |?????? | > +------------+--------------+------+-----+---------+-------+ > > Hope this help! > > ---------------------------------- > ??? _??? Eng? Julio Cesar Covolato > ?? 0v0?? <julio at psi.com.br> > ? /(_)\? F: 55-11-99175-9260 > ?? ^ ^?? PSI INTERNET > ---------------------------------- >