Now that we've identified the domain parts of all the MX servers we're hitting (from Part One
), we can now move on to identify each domain which matches one of these groups of MX servers.
To start off, we'll choose the easiest, and the most common from my list, yahoo.com. The query below finds all MX server addresses (from dsnMta) containing the text .yahoo.com and strips the domain parts from all matching email addresses (rcpt). It then orders these with the most common domain at the top:
SELECT SUBSTRING_INDEX( `rcpt` , '@' , -1 ) AS `domain` , COUNT( * ) AS `cnt`
WHERE `dsnMta` LIKE '%.yahoo.com%'
GROUP BY `domain`
ORDER BY `cnt` DESC
The excerpt below is a sample of the query output when run against a sample of my own accounting data (note I have removed the volumes):
This output helps us identify a number of domains which all use yahoo.com as their mail exchanger. First, it helps us identify all the international TLDs which are being directed through the yahoo.com system and we can add those in to the domain macro:
domain-macro yahoo yahoo.com, yahoo.co.uk, yahoo.co.in, yahoo.fr, yahoo.ie, yahoo.it, yahoo.es, yahoo.com.hk, yahoo.com.ph, yahoo.in, yahoo.com.sg, yahoo.com.cn, yahoo.com.tw, yahoo.de, yahoo.com.vn, yahoo.pl, yahoo.gr, yahoo.co.id, yahoo.cn, yahoo.se, yahoo.com.my, yahoo.dk, yahoo.no, yahoo.co.th, yahoogroups.com
Now this leaves us with a number of domains which are not of the form @yahoo. These domains all use yahoo.com as their mail exchanger, but some are much larger than others. It's up to you to decide which of these domains you want to include in your yahoo domain-macro. Personally, I choose only to include those which are of any volume within our database. That means I can then include the following in my domain-macro:
I will not put the smaller domains in my yahoo macro as I don't want to make it entirely unreadable, and even if we have some email entering yahoo's system from another source the volume should be low so we shouldn't need to worry too much.
My completed yahoo domain macro now looks like this:
domain-macro yahoo yahoo.com, yahoo.co.uk, yahoo.co.in, yahoo.fr, yahoo.ie, yahoo.it, yahoo.es, yahoo.com.hk, yahoo.com.ph, yahoo.in, yahoo.com.sg, yahoo.com.cn, yahoo.com.tw, yahoo.de, yahoo.com.vn, yahoo.pl, yahoo.gr, yahoo.co.id, yahoo.cn, yahoo.se, yahoo.com.my, yahoo.dk, yahoo.no, yahoo.co.th, btinternet.com, btopenworld.com, talk21.com, xtra.co.nz, rogers.com, onelist.com
In the next part of this series we'll look at hotmail.com and gmail.com.
If you found this post helpful please leave a comment below: