Automatic account expirations
From Qmailtoaster
Contents |
Information
From: http://www.mail-archive.com/qmailtoaster-list@qmailtoaster.com/msg16395.html
Below is a script to delete in-active account autimaticaally deleted from system. When I say in-active, it look from table vpopmail.lastauth.
It's very suitable for enterprise/business mailserver which has many employees and maybe some of them are contract employees with medium turnover, and lacks of IT resource. And may not for ISP / Hosting company which has paying customer whose never login.
Important Note
This script has 2 bugs/holes in it and I dont know how to solve it automatically:
1. If you create a new account and the user never use it, the data won't be available at table vpopmail.lastauth, so the account won't be deleted.
Manual fix: After you create new account, login to webmail to test/verify the new account working and to insert data automatically to
table vpopmail.lastauth.
2. If the user resigned, but he still remember credentials (url/server,user,pass) s/he may still using the account which causing
the account active and won't be deleted.
Manual fix: Inform your HR/Account Dept to notify you any resigned user, so you can deleted it manually or change the password.
How to use
- Copy the script into a file
- Chmod +x accountexpiration.sh
- Added into crontab (daily/weekly/monthly)
The Scripts
#!/bin/bash
# Automatic Account Expiration # deleting user email after some period of time inactivity # (whose never check email via pop3 / imap3) # ver 0.1 # Nov 28, 2007 # PakOgah <pakogah@pala.bo-tak.info>
# === Variables need to be edited === # Exclude some users from deleting (postmaster will be a good one) # Otherwise all users will be checked # separate with space #EXCLUDED_USERS="" EXCLUDED_USERS="postmaster spam notspam pakogah" # List of domains you want to include / exclude from search # separate with space # empty meant all domain are included #DOMAIN_LISTS="" DOMAIN_LISTS="pala.bo-tak.info" # Are above Domain(s) included on search ? DOMAIN_INCLUDED="Y" #DOMAIN_INCLUDED="N" # If records found delete record ? # WARNING: NO Prompt is asked #EXECUTE_DELETE="Y" EXECUTE_DELETE="N" # Delete account after x days of inactivity DAYS="180" # Email you the report ? If yes, then enter your email address # leave blank for not sending the report EMAIL_REPORT="[EMAIL PROTECTED]" #EMAIL_REPORT="" # vpopmail connection # Mysql host MYSQL_HOST="localhost" # Mysql username MYSQL_USERNAME="vpopmail" # Mysql password MYSQL_PASSWORD="SsEeCcRrEeTt" # Mysql database MYSQL_DATABASE="vpopmail" # Verbose ? (output emailaddress to stdout for checking) VERBOSE="Y" #VERBOSE="N" # === Stop editing === # === script parameter === SYSDATE=`date +%Y%m%d` HUMANDATE=`date '+%b %d, %Y'` QMAILDEL="/home/vpopmail/bin/vdeluser" MYSQLCLIENT="/usr/bin/mysql" GREP="/bin/grep" GAWK="/bin/gawk" RM="/bin/rm" MAIL="/bin/mail" TMPFILE="/tmp/$SYSDATE.log" SUBJECT="Account(s) expired due $DAYS days of inactivity per $HUMANDATE"
# construct SQL
SQL_QUERY="select concat_ws('@',user,domain) as email from lastauth where timestamp < UNIX_TIMESTAMP(SUBDATE(CURDATE(), INTERVAL $DAYS DAY))"
if [ ! "$EXCLUDED_USERS" = "" ]
then
count=0
for i in $EXCLUDED_USERS
do
count=$((count + 1))
done
SQL_QUERY="$SQL_QUERY and"
i=0
for user in $EXCLUDED_USERS
do
SQL_QUERY="$SQL_QUERY user<>'$user'"
i=$((i+1))
if [ "$i" -lt "$count" ]
then
SQL_QUERY="$SQL_QUERY and"
fi
done
fi
if [ ! "$DOMAIN_LISTS" = "" ]; then
count=0
for i in $DOMAIN_LISTS
do
count=$((count + 1))
done
SQL_QUERY="$SQL_QUERY and"
if [ "$DOMAIN_INCLUDED" = "Y" ]; then
SIGN="="
if [ "$count" -eq "1" ]; then
SQL_QUERY="$SQL_QUERY domain$SIGN'$DOMAIN_LISTS'"
else
i=0
SQL_QUERY="$SQL_QUERY ("
for domain in $DOMAIN_LISTS
do
SQL_QUERY="$SQL_QUERY domain$SIGN'$domain'"
i=$((i+1))
if [ "$i" -lt "$count" ]; then
SQL_QUERY="$SQL_QUERY or"
fi
done
SQL_QUERY="$SQL_QUERY )"
fi
else
SIGN="<>"
if [ "$count" -eq "1" ]; then
SQL_QUERY="$SQL_QUERY domain$SIGN'$DOMAIN_LISTS'"
else
i=0
SQL_QUERY="$SQL_QUERY ("
for domain in $DOMAIN_LISTS
do
SQL_QUERY="$SQL_QUERY domain$SIGN'$domain'"
i=$((i+1))
if [ "$i" -lt "$count" ]; then
SQL_QUERY="$SQL_QUERY and"
fi
done
SQL_QUERY="$SQL_QUERY )"
fi
fi
fi
SQL_QUERY="$SQL_QUERY;"
#echo $SQL_QUERY
# get the output
$MYSQLCLIENT -u $MYSQL_USERNAME -p$MYSQL_PASSWORD -h $MYSQL_HOST
$MYSQL_DATABASE -E -e "$SQL_QUERY" | $GREP email | \
$GAWK -F' ' '{print $2}' > $TMPFILE
# execute it
while read email
do
if [ "$VERBOSE" = "Y" ]; then
echo "$email expired"
fi
if [ "$EXECUTE_DELETE" = "Y" ]; then
$QMAILDEL $email
fi
done < $TMPFILE
# report it
if [ ! "$EMAIL_REPORT" = "" ]; then
$MAIL -s "$SUBJECT" $EMAIL_REPORT < $TMPFILE
fi
# clean up
$RM $TMPFILE