Ebroch: Created page with "== 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 cu..."

2024-03-16T16:28:48Z

Created page with "== 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 cu..."

New page

== 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.<br/>
'''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.<br/>
'''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

Automatic account expirations - Revision history