Today I’ve been poking at Microsoft Exchange tracking logs, asking them the very simple question of ‘How many emails have I sent?’.
Unfortunately, Exchange tracking logs are not used to simple questions, and are likely to return a complicated and / or misleading answer.
But the confusion it seems, all comes down to definitions. Once you understand these definitions, things start to make a bit more sense.
What is an Email?
If you send an email to one person, you’ve sent one email. But if you’ve sent that same email to 500 people, have you sent one email, or 500? I will take a guess, and say that a large majority of you will want to see 500 in your reports.
Microsoft Exchange 2007 tracking logs contain an excellent field called ‘Message ID’. If you send an email to someone, that message is uniquely identified by a Message ID that persists though Exchange’s various functions for the lifetime of the message.
At first glance, it seems that counting Message IDs will give us what we want. But if you send the same email to 500 recipients, all those emails get the same unique message ID. So counting message IDs will show us that only one email has been sent. No good.
Then next obvious step is to count the number of recipients that received the email.
What is a Recipient?
The definition of recipient can also get clouded when you start talking about distribution lists. If you send an email to one real person, then that is one recipient. If you send the same email to five real people then that is five recipients. If you send an email to an internal distribution list, the number of recipients is the number of people that are members of that distribution list.
If you send an email to an external distribution list (such as SalesDL@othercompany.com) this will only be recorded as only one recipient, as your Exchange box has no way of knowing how many real people are members of that DL at the other company.
How do I count Recipients?
Again, Exchange Tracking logs contain another excellent field called ‘Recipient Count’. But don’t get carried away as this too can be misleading.
Without going into specifics, Exchange has a bunch of internal functions to deal with an entire message transmission. The tracking logs files contain another excellent field called Internal Message ID that identifies each of these processes per-message.
Unfortunately, each Internal Message ID contains its own value for ‘Recipient Count’. So when you sum the Recipient Count field for a single message, the final result may be much larger than the actual number of real recipients.
To illustrate, WebSpy Vantage imports Recipient Count into a Summary of the same name. Here is a screenshot of the Recipient Count Summary for one message
The Recipient Count Summary for a Single Message
As you can see, there are multiple rows of individual Recipient Counts. The first row, is actually correct. This email was actually sent to 961 people. But there are additional entries where Exchange performed an internal operation with a subset of those messages. Therefore, summing the Recipient Count field for a message is also no good.
Counting recipients “properly”
The best way to count recipients is to use WebSpy Vantage to import your logs, then drilldown into a message to the Recipients summary and look at the total number of recipients at the bottom. Alternatively, add a Count Distinct aggregate for the Recipients summary to any report template.
Here’s a screenshot of the Recipients summary:
The Recipients Summary showing Total 'Real' Recipients
And here’s a screenshot showing how to add the aggregate to a report template:
Adding the Number of Recipients to a report template
Counting Total Number of Emails
The above screenshot will give you a count of all the recipients you have ever sent email to. However, what you really want is a count of recipients per message. You can do this by concatenating the Recipient with the Message ID, and counting the total number of rows. To do this, edit the Number of recipients aggregate column above and enter [Recipient] + [MessageID] in the ‘Custom’ edit box.
Customizing an aggregate column to concatenate Recipient and MessageID
Exchange 2007 Report Templates
You can download a WebSpy Vantage Templates file here that includes three reports (Email Overview, User Email Activity, and Email Trends) that uses columns such as Number of Emails, Number of Unique Messages and Number of Recipients.
Tip: You can convert any email template that has the schema ‘All Mail Schemas’ into an Exchange 2007 template in order to report and filter using all the fields available in Exchange 2007.
To do this:
- Right click an ‘All Mail Schema’ email template and select Duplicate.
- Select Microsoft Exchange 2007 from the schema drop down and click OK.
- When you edit the nodes in your new template, you will have access to all the fields that Exchange 2007 records.
Cheers!
Leave A Comment