Too many email address redirect to your gmail account? Watching your Spam folder getting bigger (and boring) everytime? It was for me.
So I decided to write some scripts (thanks to libgmailer) and now I can spend my time reading a newspaper or going out ;)
During the last years I've collected a lot of email addresses: one for each company I've worked for, one for msn, one for gmail, one for yahoo, and the list goes on. I finally decided to foward all my email address to a Gmail box. There's something about the conversation view that no other client has yet...Of course this move had the side effect of multiplying the spam emails I receive in the gmail box. The spam system does a good job, but I think some reports can be useful.
Several years ago I came up with the idea of using a domain name I have to create email address for each webpage I sign up and I don't trust much. So every email I create is something like thesiteurl@mydomain.com. I have all the domain emails redirected to my account. When I see some email address is sending too much spam I simply block the address and that's all.
Anyway it is still a pain in the a** to go through my spam folder every day to control this stuff. So I decided that a script should be doing it for me.
Basically the script connects to gmail, reads the spam folder, and saves certain data from the email to a database table which I later can review from a webpage with some cool effects. On the right you can see a screenshot of the reports. I've add some graphics to make it a little more interesting ;)
The script is written in php. It uses libgmailer (from the gmail-lite project). It reads the folder content for unread messages and when if found a conversation it read each email and saves the recepient, the sender and the subject for later analysis. Using adodb it inserts in a database table the data.
Let's see a bit of the collect.php code.
This scripts should be run from a cron job periodically (every hour should be fine). First, we have to create the table in the database where we are going to save the data the script will collect:
CREATE TABLE spam_occurance (
message_id VARCHAR(30) NOT NULL ,
ts INT(11) NOT NULL ,
recv_email VARCHAR(60) NOT NULL ,
subj VARCHAR(200) NOT NULL ,
from_email VARCHAR(60) NOT NULL ,
PRIMARY KEY (message_id),
INDEX (recv_email,ts),
INDEX (ts,recv_email)
);
Then we modify the config.inc.php and specify a database connection URI to access that table.
Now let's review some insteresting parts of the collect code:
Connect to gmail, apparently the library saves login cookies in a session var, so that it won't have to re-authenticate every time it runs... Anyway, I don't think that this works when running from the command line...
$gm = new GMailer();
$gm->setLoginInfo($myemail, $pwd, $tz);
Next, we get the spam box conversations and cycle in the unread conversations to get the real emails:
$gm->fetchBox(GM_STANDARD, "spam", 0);
$snapshot = $gm->getSnapshot(GM_STANDARD);
if ($snapshot) {
debug('Total # of conversations in Spam folder = ' . $snapshot->box_total.$nl.$nl);
foreach ($snapshot->box as $conv) {
// we will only inspect unread messages for better performance
if ($conv['is_read']==1) {
debug('Conversation "'.strip_tags($conv['subj']).'" (id: '.$conv['id'].')'.$nl);
// get the messages in the conversation
$q = "search=spam&view=cv&th=".$conv['id'];
$gm->fetch($q);
$snapshot2 = $gm->getSnapshot(GM_STANDARD | GM_LABEL| GM_QUERY| GM_CONVERSATION);
....
So now we have the real emails in the conversation in the $snapshot2 object. We only have to collect that email info, verify that it wasn't already analyzed/inserted (could happen when a new mail enters an already read conversation) and finally insert the data in the table:
foreach ($snapshot2->conv as $msg) {
debug(' From: '.$msg['sender_email'].' ID: '.$msg['id'].' was send to ');
foreach ($msg['recv_email'] as $recv_email){
$email = '';
eregi($regex, $recv_email, $email);
debug($email[0].' ');
$sql = 'select 1 from spam_occurance where message_id = ?';
$rs = $db->execute($sql,array($msg['id']));
if ($rs->EOF) {
$sql = 'insert into spam_occurance '.
'(message_id,ts,recv_email,subj,from_email) '.
' values (?,?,?,?,?)';
$db->execute($sql, array( $msg['id'],time(),$email[0],
strip_tags($conv['subj']),$msg['sender_email']));
debug('INSERTED');
} else {
debug('SKIPPED');
}
}
debug($nl);
}
Data is now in the database and ready to be seen by the report script.
The reports.php script is a little bit more complicated (with the ajax stuff) so just go ahead and download it to see it. Anyway the powerfull part was the use of libgmailer. It's a very good library that I could imagine using in a lot of ways!
You can get the source code here: 4TM Open Source Tools
