Description

*UPDATE* There was a typo in the originally posted script that caused Recevied stats to not accumulate.  Should be fixed now.

Analyzes Exchange 2007 or 2010 Message Tracking Logs and produces a .csv file of mail stats per user, and keeps distribution list usage.  As posted, it will find all the HT servers in the organizaions, retrieve the logs from the previous day, and generate stats for each user, by primary smtp address, for

Total Messages and Bytes Sent

Unique Messages and Bytes Sent

Total Messages and Bytes Received

for both Internal and External emails.

Notes:  A "unique" email is a user composing and sending an email, regardless of the number of recipients.   The Total Messages and Bytes Sent  are from the total number of emails delivered that were sent by that user.

"Internal" email is email sent and received between mailbox servers.  External email is any email sent to or received from an SMTP connector.  System generated messages (mailbox quota messages, delivery and read receipts, etc) are not counted.

The Object Table is basically a double-quoted here-string, in a script block.  You can make changes to the column headings or formatting by changing the entries in the table according to the rules for double quoted here-strings, or re-arrange the lines in the table if you want the csv columns in a different order.

The script produces a new user mail stats .csv per run.  The .csv of DL usage is re-used and updated on each run.  The Used column is the number of times and Expand event was recorded for that DL within the lifetime of the .csv file.  The Since column contains the date of first time an Expand event for that DL was recorded, and LastUsed contains the date the last Expand event was recorded.

All the accumulations are done using hash tables, and there are no blocking cmdlets (measure-object, group-object, etc.) in the pipeline, so it's relatively resource-friendly.  In testing in my environment, it processes log entries at a rate of 800-1200 per second, and takes less than 4 minutes to complete processing on an average day's 200,000 log entries.  Memory utilization stayed at around 250MB for the Exchange 2010 EMS session is was running in during processing.

 

 

 

 

Script

PowerShell
Edit|Remove
#mjolinor 
#02/24/2011 
 
#requires -version 2.0 
 
$today = get-date 
$rundate = $($today.adddays(-1)).toshortdatestring() 
 
$outfile_date = ([datetime]$rundate).tostring("yyyy_MM_dd"$outfile = "email_stats_" + $outfile_date + ".csv" 
 
$dl_stat_file = "DL_stats.csv" 
 
$accepted_domains = Get-AcceptedDomain |% {$_.domainname.domain} 
[regex]$dom_rgx = "`(?i)(?:" + (($accepted_domains |% {"@" + [regex]::escape($_)}) -join "|"+ ")$" 
 
$mbx_servers = Get-ExchangeServer |? {$_.serverrole -match "Mailbox"}|% {$_.fqdn} 
[regex]$mbx_rgx = "`(?i)(?:" + (($mbx_servers |% {"@" + [regex]::escape($_)}) -join "|"+ ")\>$" 
 
$msgid_rgx = "^\<.+@.+\..+\>$" 
 
$hts = get-exchangeserver |? {$_.serverrole -match "hubtransport"|% {$_.name} 
 
$exch_addrs = @{} 
 
$msgrec = @{} 
$bytesrec = @{} 
 
$msgrec_exch = @{} 
$bytesrec_exch = @{} 
 
$msgrec_smtpext = @{} 
$bytesrec_smtpext = @{} 
 
$total_msgsent = @{} 
$total_bytessent = @{} 
$unique_msgsent = @{} 
$unique_bytessent = @{} 
 
$total_msgsent_exch = @{} 
$total_bytessent_exch = @{} 
$unique_msgsent_exch = @{} 
$unique_bytessent_exch = @{} 
 
$total_msgsent_smtpext = @{} 
$total_bytessent_smtpext = @{} 
$unique_msgsent_smtpext=@{} 
$unique_bytessent_smtpext = @{} 
 
$dl = @{} 
 
 
$obj_table = { 
@" 
Date = $rundate 
User = $($address.split("@")[0]) 
Domain = $($address.split("@")[1]) 
Sent Total = $(0 + $total_msgsent[$address]) 
Sent MB Total = $("{0:F2}" -$($total_bytessent[$address]/1mb)) 
Received Total = $(0 + $msgrec[$address]) 
Received MB Total = $("{0:F2}" -$($bytesrec[$address]/1mb)) 
Sent Internal = $(0 + $total_msgsent_exch[$address]) 
Sent Internal MB = $("{0:F2}" -$($total_bytessent_exch[$address]/1mb)) 
Sent External = $(0 + $total_msgsent_smtpext[$address]) 
Sent External MB = $("{0:F2}" -$($total_bytessent_smtpext[$address]/1mb)) 
Received Internal = $(0 + $msgrec_exch[$address]) 
Received Internal MB = $("{0:F2}" -$($bytesrec_exch[$address]/1mb)) 
Received External = $(0 + $msgrec_smtpext[$address]) 
Received External MB = $("{0:F2}" -$($bytesrec_smtpext[$address]/1mb)) 
Sent Unique Total = $(0 + $unique_msgsent[$address]) 
Sent Unique MB Total = $("{0:F2}" -$($unique_bytessent[$address]/1mb)) 
Sent Internal Unique  = $(0 + $unique_msgsent_exch[$address])  
Sent Internal Unique MB = $("{0:F2}" -$($unique_bytessent_exch[$address]/1mb)) 
Sent External  Unique = $(0 + $unique_msgsent_smtpext[$address]) 
Sent External Unique MB = $("{0:F2}" -$($unique_bytessent_smtpext[$address]/1mb)) 
"@ 
} 
 
$props = $obj_table.ToString().Split("`n")|% {if ($_ -match "(.+)="){$matches[1].trim()}} 
 
$stat_recs = @() 
 
function time_pipeline { 
param ($increment  = 1000) 
begin{$i=0;$timer = [diagnostics.stopwatch]::startnew()} 
process { 
    $i++ 
    if (!($i % $increment)){Write-host “`rProcessed $i in $($timer.elapsed.totalseconds) seconds” -nonewline} 
    $_ 
    } 
end { 
    write-host “`rProcessed $i log records in $($timer.elapsed.totalseconds) seconds” 
    Write-Host "   Average rate: $([int]($i/$timer.elapsed.totalseconds)) log recs/sec." 
    } 
} 
 
foreach ($ht in $hts){ 
 
    Write-Host "`nStarted processing $ht" 
 
    get-messagetrackinglog -Server $ht -Start "$rundate" -End "$rundate 11:59:59 PM" -resultsize unlimited | 
    time_pipeline |%{ 
     
     
    if ($_.eventid -eq "DELIVER" -and $_.source -eq "STOREDRIVER"){ 
     
        if ($_.messageid -match $mbx_rgx -and $_.sender -match $dom_rgx) { 
             
            $total_msgsent[$_.sender] +$_.recipientcount 
            $total_bytessent[$_.sender] += ($_.recipientcount * $_.totalbytes) 
            $total_msgsent_exch[$_.sender] +$_.recipientcount 
            $total_bytessent_exch[$_.sender] += ($_.totalbytes * $_.recipientcount) 
         
            foreach ($rcpt in $_.recipients){ 
            $exch_addrs[$rcpt++ 
            $msgrec[$rcpt++ 
            $bytesrec[$rcpt+$_.totalbytes 
            $msgrec_exch[$rcpt++ 
            $bytesrec_exch[$rcpt+$_.totalbytes 
            } 
             
        } 
         
        else { 
            if ($_messageid -match $messageid_rgx){ 
                    foreach ($rcpt in $_.recipients){ 
                        $msgrec[$rcpt++ 
                        $bytesrec[$rcpt+$_.totalbytes 
                        $msgrec_smtpext[$rcpt++ 
                        $bytesrec_smtpext[$rcpt+$_.totalbytes 
                    } 
                } 
         
            } 
                 
    } 
     
     
    if ($_.eventid -eq "RECEIVE" -and $_.source -eq "STOREDRIVER"){ 
        $exch_addrs[$_.sender] ++ 
        $unique_msgsent[$_.sender] ++ 
        $unique_bytessent[$_.sender] +$_.totalbytes 
         
            if ($_.recipients -match $dom_rgx){ 
                $unique_msgsent_exch[$_.sender] ++ 
                $unique_bytessent_exch[$_.sender] +$_.totalbytes 
                } 
 
            if ($_.recipients -notmatch $dom_rgx){ 
                $ext_count = ($_.recipients -notmatch $dom_rgx).count 
                $unique_msgsent_smtpext[$_.sender] ++ 
                $unique_bytessent_smtpext[$_.sender] +$_.totalbytes 
                $total_msgsent[$_.sender] +$ext_count 
                $total_bytessent[$_.sender] += ($ext_count * $_.totalbytes) 
                $total_msgsent_smtpext[$_.sender] +$ext_count 
                 $total_bytessent_smtpext[$_.sender] += ($ext_count * $_.totalbytes) 
                } 
                                
             
        } 
         
    if ($_.eventid -eq "expand"){ 
        $dl[$_.relatedrecipientaddress] ++ 
        } 
    }      
     
} 
 
foreach ($address in $exch_addrs.keys){ 
 
$stat_rec = (new-object psobject -property (ConvertFrom-StringData (&$obj_table))) 
$stat_recs +$stat_rec | select $props 
} 
 
$stat_recs | export-csv $outfile -notype  
 
if (Test-Path $dl_stat_file){ 
    $DL_stats = Import-Csv $dl_stat_file 
    $dl_list = $dl_stats |% {$_.address} 
    } 
     
else { 
    $dl_list = @() 
    $DL_stats = @() 
    } 
 
 
$DL_stats |% { 
    if ($dl[$_.address]){ 
        if ([datetime]$_.lastused -le [datetime]$rundate){  
            $_.used = [int]$_.used + [int]$dl[$_.address] 
            $_.lastused = $rundate 
            } 
        } 
} 
     
$dl.keys |% { 
    if ($dl_list -notcontains $_){ 
        $new_rec = "" | select Address,Used,Since,LastUsed 
        $new_rec.address = $_ 
        $new_rec.used = $dl[$_] 
        $new_rec.Since = $rundate 
        $new_rec.lastused = $rundate 
        $dl_stats += @($new_rec) 
    } 
} 
 
$dl_stats | Export-Csv $dl_stat_file -NoTypeInformation -force 
 
 
Write-Host "`nRun time was $(((get-date) - $today).totalseconds) seconds." 
Write-Host "Email stats file is $outfile" 
Write-Host "DL usage stats file is $dl_stat_file" 
 
 
#Contact information 
#[string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "