On Friday 10 February 2012 11:35 AM, patr...@aofh.us wrote:
4) Exit the mysql shell. On creating a new ID in the domain example.com,
you should get the ID and creation date/time in the table log_example_com.
Just tried it, works perfectly!
Good to know!
Now for a more complete version which logs creation and deletion date.
It's not as simple as writing a trigger for delete, because a single
user id can be created/deleted any number of times and we wouldn't know
which record to update. Since this is kind of an auditing table, we
would like to track each instance of creation/deletion.
So, there are a few modifications to be made to the domain table in the
vpopmail database.
Note: I have just tried it on a production server and it worked without
any problem for me, but please make a backup of the table first or try
it on a dummy domain.
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.
Hope this helps!
Bharath
---------------------------------------------------------------------------------
Qmailtoaster is sponsored by Vickers Consulting Group
(www.vickersconsulting.com)
Vickers Consulting Group offers Qmailtoaster support and installations.
If you need professional help with your setup, contact them today!
---------------------------------------------------------------------------------
Please visit qmailtoaster.com for the latest news, updates, and packages.
To unsubscribe, e-mail: qmailtoaster-list-unsubscr...@qmailtoaster.com
For additional commands, e-mail: qmailtoaster-list-h...@qmailtoaster.com