Re: [qmailtoaster] Log email Account creation date
On 03/02/12 21:33, Bharath Chari wrote: On Friday 02 March 2012 04:14 PM, Pak Ogah wrote: On 02/10/12 19:34, Bharath Chari wrote: 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. Halo Bharath, My colleague, confirm your steps is working as our expectation. now I am going to add this to wiki but my question is: - does these steps already in new vpopmail that currently in development? vpopmail-toaster-5.4.33-1.4.0.src.rpm - added flag --enable-many-domains and removed clear text password. This is the first package that needs to be installed. Hi Pak, The triggers I gave you are for use with the CURRENT version of vpopmail with --disable-many-domains option. I will rework one for the new version of vpopmail toaster which we will be releasing with 1.4.0. It will be easier to do it that version, since all domains will be in one table. I have not incorporated the trigger within the vpopmail source, because I would like to make it optional, and it's not part of the core functionality. Maybe I can create a shell script for those who want to audit the creation/deletion times/dates. I'll post the solution as soon as I write it. If you are planning to update the wiki with the current steps, please _ensure that you mention that it will work only for vpopmail-5.4.17 version of the toaster. Bharath Bharath Hello Bharath, I have created wiki for Account Creation and Deletion Date based on your steps. Please check it here: http://wiki.qmailtoaster.com/index.php/Account_Creation_and_Deletion_Date and edit it if you like. - 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
Re: [qmailtoaster] Log email Account creation date
On Monday 19 March 2012 11:59 AM, Pak Ogah wrote: On 03/02/12 21:33, Bharath Chari wrote: On Friday 02 March 2012 04:14 PM, Pak Ogah wrote: On 02/10/12 19:34, Bharath Chari wrote: 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. Halo Bharath, My colleague, confirm your steps is working as our expectation. now I am going to add this to wiki but my question is: - does these steps already in new vpopmail that currently in development? vpopmail-toaster-5.4.33-1.4.0.src.rpm - added flag --enable-many-domains and removed clear text password. This is the first package that needs to be installed. Hi Pak, The triggers I gave you are for use with the CURRENT version of vpopmail with --disable-many-domains option. I will rework one for the new version of vpopmail toaster which we will be releasing with 1.4.0. It will be easier to do it that version, since all domains will be in one table. I have not incorporated the trigger within the vpopmail source, because I would like to make it optional, and it's not part of the core functionality. Maybe I can create a shell script for those who want to audit the creation/deletion times/dates. I'll post the solution as soon as I write it. If you are planning to update the wiki with the current steps, please _ensure that you mention that it will work only for vpopmail-5.4.17 version of the toaster. Bharath Bharath Hello Bharath, I have created wiki for Account Creation and Deletion Date based on your steps. Please check it here: http://wiki.qmailtoaster.com/index.php/Account_Creation_and_Deletion_Date and edit it if you like. Thanks Pak, The wiki entry is fine. Strangely, I was just working on a script to do that for all domains when I got your mail :). I'll post that to the devel list for testing later today. 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
Re: [qmailtoaster] Log email Account creation date
On 02/10/12 19:34, Bharath Chari wrote: 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. Halo Bharath, My colleague, confirm your steps is working as our expectation. now I am going to add this to wiki but my question is: - does these steps already in new vpopmail that currently in development? vpopmail-toaster-5.4.33-1.4.0.src.rpm - added flag --enable-many-domains and removed clear text password. This is the first package that needs to be installed. so when users install new vpopmail they don't need to do these steps ? or they still need do it ? thanks before - 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
Re: [qmailtoaster] Log email Account creation date
On Friday 02 March 2012 04:14 PM, Pak Ogah wrote: On 02/10/12 19:34, Bharath Chari wrote: 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. Halo Bharath, My colleague, confirm your steps is working as our expectation. now I am going to add this to wiki but my question is: - does these steps already in new vpopmail that currently in development? vpopmail-toaster-5.4.33-1.4.0.src.rpm - added flag --enable-many-domains and removed clear text password. This is the first package that needs to be installed. Hi Pak, The triggers I gave you are for use with the CURRENT version of vpopmail with --disable-many-domains option. I will rework one for the new version of vpopmail toaster which we will be releasing with 1.4.0. It will be easier to do it that version, since all domains will be in one table. I have not incorporated the trigger within the vpopmail source, because I would like to make it optional, and it's not part of the core functionality. Maybe I can create a shell script for those who want to audit the creation/deletion times/dates. I'll post the solution as soon as I write it. If you are planning to update the wiki with the current steps, please _ensure that you mention that it will work only for vpopmail-5.4.17 version of the toaster. Bharath 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
Re: [qmailtoaster] Log email Account creation date
On 02/10/12 19:34, Bharath Chari wrote: 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 '-00-00 00:00:00', `deletion_date` datetime NOT NULL default '-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: mysqlinsert 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(),'-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 ; mysqlquit 7) Test and test again: Create a test user eg : x using qmailadmin Delete the user x Create the test user x again Delete the user x Your table userlog_example_com should contain two records both with x as the pw_name but with different uid numbers and creation/deletion dates. Hope this helps! Bharath Thank you very much Bharath, I really forgot about MySQL trigger feature I'll try this on development server first then I'll update the Wiki for it. - 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
Re: [qmailtoaster] Log email Account creation date
On Saturday 11 February 2012 03:45 PM, Pak Ogah wrote: Your table userlog_example_com should contain two records both with x as the pw_name but with different uid numbers and creation/deletion dates. Hope this helps! Bharath Thank you very much Bharath, I really forgot about MySQL trigger feature I'll try this on development server first then I'll update the Wiki for it. Yup, It'd would be nice to have validation from a few more installations before adding it to the Wiki. As I said earlier, I will take some of these suggestions over to the vpopmail list, but since they are not to do with core functionality, I don't expect it to be on the priority list. I will continue to work on a way to make this feature available to QMT users. Even a command line php script would do the job quite nicely. 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
Re: [qmailtoaster] Log email Account creation date
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 '-00-00 00:00:00', `deletion_date` datetime NOT NULL default '-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: mysqlinsert 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(),'-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 ; mysqlquit 7) Test and test again: Create a test user eg : x using qmailadmin Delete the user x Create the test user x again Delete the user x Your table userlog_example_com should contain two records both with x 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
Re: [qmailtoaster] Log email Account creation date
Hi Bharath, This is a wonderful tweak. I was also one for waiting the solution in the list. Thank you for the detailed steps. With Regards Nabin Limbu -Original Message- From: Bharath Chari qmailtoas...@arachnis.com To: qmailtoaster-list@qmailtoaster.com Date: Fri, 10 Feb 2012 10:41:22 +0530 Subject: [qmailtoaster] Log email Account creation date Here goes. It's a manual process for each domain, but it seems to work :) Note : A separate log table and trigger has to be created for each domain. This example is for a domain called example.com Please execute the following commands on the vpopmail database from the MySQL command prompt. Do not use phpmyadmin. 1) mysql -uroot -p vpopmail Enter your mysql root password here. 2) Create a log table for the domain example.com (I am going to use the table name log_example_com). Replace with your actual domain name CREATE TABLE `log_example_com` ( `user_id` varchar(255) character set latin1 NOT NULL, `creation_date` datetime NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.04 sec) 3) Create a trigger - the trigger name I have used is example_com_trigger and the corresponding table for the domain example.com is example_com mysql delimiter $$ mysql CREATE TRIGGER example_com_trigger - AFTER insert on example_com - FOR EACH ROW - BEGIN - insert into log_example_com values(new.pw_name,NOW()); - END$$ mysql delimiter ; (Resetting the delimiter is critical) 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. Pak : Since you are the resident wikifying expert, can you please add it to the wiki :) 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 - 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
Re: [qmailtoaster] Log email Account creation date
Here goes. It's a manual process for each domain, but it seems to work :) Note : A separate log table and trigger has to be created for each domain. This example is for a domain called example.com Please execute the following commands on the vpopmail database from the MySQL command prompt. Do not use phpmyadmin. 1) mysql -uroot -p vpopmail Enter your mysql root password here. 2) Create a log table for the domain example.com (I am going to use the table name log_example_com). Replace with your actual domain name CREATE TABLE `log_example_com` ( `user_id` varchar(255) character set latin1 NOT NULL, `creation_date` datetime NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.04 sec) 3) Create a trigger - the trigger name I have used is example_com_trigger and the corresponding table for the domain example.com is example_com mysql delimiter $$ mysql CREATE TRIGGER example_com_trigger - AFTER insert on example_com - FOR EACH ROW - BEGIN - insert into log_example_com values(new.pw_name,NOW()); - END$$ mysql delimiter ; (Resetting the delimiter is critical) 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! - 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
