QmailtoasterMain Page | About | Help | FAQ | Special pages | Log in

Printable version | Disclaimers | Privacy policy

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

  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

Retrieved from "http://wiki.qmailtoaster.com/index.php/Automatic_account_expirations"

This page has been accessed 6,832 times. This page was last modified on 13 April 2011, at 03:17. Content is available under GNU Free Documentation License 1.2.


Find

Browse
Main page
Community portal
Current events
Recent changes
Random page
Help
Edit
View source
Editing help
This page
Discuss this page
New section
Printable version
Context
Page history
What links here
Related changes
My pages
Log in / create account
Special pages
New pages
File list
Statistics
More...