Unapproved comments notification with Notify module

The Drupal Notify module is a lightweight solution for notifications of new or revised content as well as comments. It is meant to be used by admins and webmasters monitoring new content on a website. The module can notify of comments that are automatically approved and posted, but there is no built-in mechanism for notification of unapproved comments.

One line of code change to the rescue:

• Install module (you must have a cron run scheduled for it to work)
• Adjust permissions
• Open notify.module in your favorite editor
• Replace line 481 of the code with: 

$cresult = db_query(db_rewrite_sql('SELECT c.nid, c.cid, c.subject, c.name FROM {comments} c INNER JOIN {node} n ON c.nid = n.nid WHERE c.timestamp > %d AND c.timestamp <= %d '. $reqntype . ' ORDER BY c.nid, c.timestamp', 'c'), $period, time());

• Post a test comment and watch your inbox

Be sure to set up the module properly:

• Configure global settings at admin/settings/notify
• Configure individual settings within the user profile at example.com/user - a new tab labeled Notification settings should be available
• Review all user settings at admin/user/user/notify - you can also force sending out emails from here when testing

Drupal 4 & 5 instructions are available within the community.