We use Visnetic Mailflow at work. With over 250 email addresses being processed by the system I was curious as to which are still used. There is a admin page that shows how many times a rule has been hit, but not in a given time-frame. I can do some basic SQL queries but none got me what I needed. I knew I had to query the 'MatchToAddreses' table which lists all the email addresses all the routing rules use, and then also query the 'InboundMessages' table for the prevoiously found email addresses, but only count emails that have arrived since a given date.
Luckily there are some clever SQL boys here and they came up with the following query:-
SELECT COUNT(DISTINCT im.InboundMessageID) AS [Count],
mta.emailaddress AS [Address]
FROM dbo.MatchToAddresses mta
LEFT JOIN dbo.InboundMessages im
ON mta.emailaddress = im.EmailPrimaryTo
AND im.EmailDatetime > 'Specify date'
GROUP BY mta.emailaddress
ORDER BY mta.emailaddress
I now have a list of how many times in the past 6 months an email address has been hit. From the 250 email addresses, 130 have been hit 10 times or less, and many have not been hit at all. I now have a fun job of pruning the addresses and subsiquent ruleset. Joy!