dear list,
I have create a bash script which can automatically delete inactive
account(s) (whose never accessing email via pop3/imap) for some amount
of time.
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 a lazy admin like me :)
note: this might be not suitable for ISP or where real-user can get long
time never logged to system
I hope it's useful,
if you have any comment, found a bug please let me know. I am not
BashScriptMaster
how to:
save the file as acountexpiration.sh on your mailserver and dont forget
to chmod it
edit the file for options
its up to you to run the script daily/weekly/monthly
#!/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 <[EMAIL PROTECTED]>
# === 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
---------------------------------------------------------------------
QmailToaster hosted by: VR Hosted <http://www.vr.org>
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
