Ebroch: Created page with "== 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 == # Use this tips with qmailtoaster's version >= vpopmail-5.4.17 with mysql backend (not LDAP). # Use only with MySQL version 5.0 or newer which has trigger feature. # This trigger only work with vpopma..."

2024-03-16T16:27:48Z

Created page with "== 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 == # Use this tips with qmailtoaster's version >= vpopmail-5.4.17 with mysql backend (not LDAP). # Use only with MySQL version 5.0 or newer which has trigger feature. # This trigger only work with vpopma..."

New page

== 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 ==
# Use this tips with qmailtoaster's version >= vpopmail-5.4.17 with mysql backend (not LDAP).
# Use only with MySQL version 5.0 or newer which has trigger feature.
# This trigger only work with vpopmail with --disable-many-domains (each domain has it's own table).
# 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.
# These steps are based on [http://www.mail-archive.com/qmailtoaster-list@qmailtoaster.com/msg33054.html email sent by Bharath Chari to QMailToaster's list].
# 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.

Account Creation and Deletion Date - Revision history