Account Creation and Deletion Date

Background

My Helpdesk was audited by external auditor regarding date of creation and deletion of email accounts. I can't find it the exact date by looking /home/vpopmail folders. So I ask the community and Bharath Chari kind enough to provide the tweaks.

Notes

1. Use this tips with qmailtoaster's version >= vpopmail-5.4.17 with mysql backend (not LDAP).
2. Use only with MySQL version 5.0 or newer which has trigger feature.
3. This trigger only work with vpopmail with --disable-many-domains (each domain has it's own table).
4. In the future, perhaps Bharath will make a script available via QTP to do this automatically, so you no longer need to follow the steps below.
5. These steps are based on email sent by Bharath Chari to QMailToaster's list.
6. MAKE BACKUP of vpopmail database before you do these steps.

Steps

Assumption: Domain to be monitored is example.com and the domain table is example_com.

1) Log in to mysql shell as root

mysql -uroot -p vpopmail

2) Alter the example_com table (replace example_com with your domain table)

ALTER TABLE `example_com` DROP PRIMARY KEY;
ALTER TABLE `example_com` ADD UNIQUE (`pw_name`);
ALTER TABLE `example_com` ADD `uid` INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST ;

3) Create log table - replace example_com with your domain table name

CREATE TABLE `userlog_example_com` (
 `uid` int(11) NOT NULL,
 `pw_name` varchar(255) NOT NULL,
 `creation_date` datetime NOT NULL default '0000-00-00 00:00:00',
 `deletion_date` datetime NOT NULL default '0000-00-00 00:00:00',
 PRIMARY KEY  (`uid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

4) Import existing records into the userlog_example_com table. While this won't help with creation date, it will allow you to track when an ID was deleted:

mysql>insert into userlog_example_com (uid,pw_name) select uid,pw_name from example_com;

5) Create the INSERT Trigger (again replace example_com as appropriate)

mysql> delimiter $$
mysql> create trigger example_com_insert_trigger
    -> AFTER insert on example_com
    -> FOR EACH ROW
    -> BEGIN

    -> insert into userlog_example_com values(new.uid,new.pw_name,NOW(),'0000-00-00 00:00:00');

    -> END$$
Query OK, 0 rows affected (0.02 sec)

mysql> delimiter ;

6) Create the DELETE Trigger (again replace example_com as appropriate)

mysql> delimiter $$
mysql> CREATE TRIGGER example_com_delete_trigger
   -> AFTER delete on example_com
   -> FOR EACH ROW
   -> BEGIN

   -> update userlog_example_com set deletion_date=NOW() where uid=old.uid;

   -> END$$
Query OK, 0 rows affected (0.02 sec)

mysql> delimiter ;

mysql>quit

7) Test and test again: Create a test user eg : xxxxx using qmailadmin Delete the user xxxxx Create the test user xxxxx again Delete the user xxxxx

Your table userlog_example_com should contain two records both with xxxxx as the pw_name but with different uid numbers and creation/deletion dates.