Automatic account expirations

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

1. Copy the script into a file
2. Chmod +x accountexpiration.sh
3. 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