Introduction

Recently I was asked to provide an overview of the amount of mails that flow in and out a specific mailbox during a specific timeframe. The purpose was to create some kind of ‘profile’ (baseline) out of this mailbox. 
Previously, this would typically have been information that you could get from the Exchange Profile Analyzer (EPA). But the Profile Analyzer uses WebDAV to ‘tap into’ Exchange which isn’t supported in Exchange 2010 anymore making it unusable in this case.

Thus, I had to revert back to either a 3rd party tool or the message tracking logs. On top of that, the Management Console has no option to easily export information into e.g. a CSV-file…

Based on the requirements, I decided to write a script that would fetch me some information from the message tracking logs:

- Date/Time of the mails that have been sent/received
- Recipient/Sender
- Subject

In this particular case, Message Tracking Logs were kept for 7 days. Even though it was not required, I decided to create daily statistics. Last but not least, I wanted these reports to be sent out automatically so that no further ‘maintenance’ was required.

The solution

Note: I’ve masked some of the output in the screenshots because the scripts were ran in a live environment.

The EMS allows to query the message tracking logs using the Get-MessageTrackingLog cmdlet:

Get-TransportServer

In the customer’s environment, there are multiple Hub Transport servers. To make sure that I include all the servers, I either add the –server parameter and run the command for each Hub Transport server, or I do the following:

Get-TransportServer | Get-MessageTrackingLog

(The output is exactly the same, but now includes the logs from all Hub Transport servers)

Next, I wanted to filter the events that are shown. As stated above, I only need the Date/Time, Recipient or Sender and the Subject:

Get-TransportServer | Get-MessageTrackingLog | ft Sender,Recipients,TimeStamp,MessageSubject

Note: the message shown above are the system messages which count as received messages as well! (if you want to filter them out you’ll have to add a where-clause to the cmdlet):

Get-TransportServer | Get-MessageTrackingLog | Where-Object{$_.MessageSubject -ne "Your mailbox is almost full."} | ft Sender,Recipients,TimeStamp,MessageSubject

So far so good

Running the cmdlets above, will provide us with the entire message tracking logs from all the different hub transport servers. In order to get the stats for a specific mailbox, during a specific timeframe, we need to filter:

• the date (we want a daily report with the sent / received messages from the day before)
• the sender/recipient

To fetch results from the message tracking logs during a specific timeframe, we need to use the –start and –end parameters:

Get-TransportServer | Get-MessageTrackingLog –Start "MM/dd/yyyy hh:mm:ss" –End "MM/dd/yyyy hh:mm:ss" | ft Sender,Recipients,TimeStamp,MessageSubject

Fetching results for a specific mailbox (email) means that this mailbox can either be sender or recipient, resulting in the following cmdlets:

Get-TransportServer | Get-MessageTrackingLog –Start "MM/dd/yyyy hh:mm:ss" –End "MM/dd/yyyy hh:mm:ss" –Sender email@domain.tld | ft Sender,Recipients,TimeStamp,MessageSubject

Get-TransportServer | Get-MessageTrackingLog –Start "MM/dd/yyyy hh:mm:ss" –End "MM/dd/yyyy hh:mm:ss" –Recipients email@domain.tld | ft Sender,Recipients,TimeStamp,MessageSubject

However, because of the way messages are logged in the message tracking logs, this might cause to return a message multiple times. For instance, if a mail is sent to both internal and external recipients, a second entry in the message tracking logs can be found for the external recipients only. This is by design the way the Exchange store handles messages. If we want to avoid that, we need to specify the EventID’s and Sources as well:

Get-TransportServer | Get-MessageTrackingLog -Recipients email@domain.tld -EventId DELIVER -Start "mm/dd/yyyy 0:00:00" -End "mm/dd/yyyy 23:59:59" | where{$_.Source -eq "STOREDRIVER"}

Get-TransportServer | Get-MessageTrackingLog –Sender email@domain.tld -Start "mm/dd/yyyy 0:00:00" -End "mm/dd/yyy 23:59:59" -EventID RECEIVE

Bringing it all together

If we bring all the parts from above together, we’ll finally end up with the following code below. Note the way we handle the date & time to make sure that it’s in the correct format. This is because the script will be run using a scheduled task and I want it to automatically “calculate” the date of the day before, based on the systems’ time and date at the moment the script is ran:

#defining the variables
$servers = Get-TransportServer
$email = "email@domail.tld"
$total_received = @()
$total_sent = @()

$getdate = '{0:MM/dd/yyyy}' –f (get-date).addDays(-1)
$filedate= '{0:ddMMyyyy}' –f (get-date).addDays(-1)

foreach($server in $servers){

#fetching the received messages
$in = Get-MessageTrackingLog -Server $server -Recipients $email -EventId DELIVER -Start "$getdate 0:00:00" -End "$getdate 23:59:59" | where{$_.Source -eq "STOREDRIVER"}

   foreach ($i in $in){
      $received = New-Object PSobject
      $received | Add-Member -type NoteProperty -name Sender -value $i.sender
      $received | Add-Member -type NoteProperty -name Subject -value $i.messagesubject
      $received | Add-Member -type NoteProperty -name Time -value $i.timestamp
      $total_received += $received
   }

#fetching the sent messages
$out = Get-MessageTrackingLog -server $server –sender $email -Start "$getdate 0:00:00" -End "$getdate 23:59:59" -EventID RECEIVE

   #add a extra loop to detect whether the fetched results (recipients) are an array or not. If it’s an array, join to a string (needed because of output format)
   foreach($o in $out){
         if($o.recipients -is [system.array]){
        $recip = [string]::join(";",$o.recipients)
     }
     $sent = New-Object PSObject
     $sent | Add-Member -type NoteProperty -name Recipients -value $recip
     $sent | Add-Member -type NoteProperty -name Subject -value $o.messagesubject
     $sent | Add-Member -type NoteProperty -name Time -value $o.timestamp
     $total_sent += $sent

   }

}

#results are exported to a csv-file on the local hdd. This way we have a file that we can re-use later.
$total_received | Export-CSV c:\mailstats\received_$filedate.csv -notypeinformation
$total_sent | Export-CSV c:\mailstats\sent_$filedate.csv –notypeinformation

#the code below is used to mail the files we’ve created before
$smtpServer = "servername.domain.tld"
$msg = new-object Net.Mail.MailMessage
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$msg.From = "mailstats@domail.tld"
$msg.To.Add("recipient@domain.com")
$msg.Subject = "FYI: Exchange Mailbox Statistics for $email"
$msg.Body = "Daily Mail Stats for $email"

$att_sent = New-Object Net.Mail.Attachment("c:\mailstats\sent_$filedate.csv")
$att_received = New-Object Net.Mail.Attachment("c:\mailstats\received_$filedate.csv")
$msg.Attachments.Add($att_sent)
$msg.Attachments.Add($att_received)

$smtp.Send($msg)

Conclusion

Although there is no GUI which will allow you to save data from the message tracking logs, fetching data through the EMS is pretty straightforward and not that hard at all. You can – if needed – customize the script to include multiple mailboxes and file-handling (e.g.: exporting to html).

Cheers!

Michael