none
Yearly Email Traffic Reports? RRS feed

  • Question

  • The CFO of our company recently sent me this request: 

    "Please run or create a report that shows email volume by employee inbound and outbound.  Exclude email caught by the spam filter or flagged as email junk."

    I found a powershell script that did this, and I could run it for the previous day. But he sent it back saying he wants it for the past year. "I definitely do not want to see this for a single day.  I do not want it to check on people.  But I do want to understand email trends over longer time periods.  Could you please send me one report for 1/1/13 to 12/31/13.  And one report for 1/1/14 to 2/28/14. "

    I can't find anything that will do that. I either need to find something that can run that kind of report, or give him a valid explanation of why I can't. Everything I've found so far says that logs only go back 30 days, so it can't be done. Is that the truth?

     

    Monday, March 17, 2014 2:19 PM

All replies

  • If you could paste the powershell you found into a comment on this post I'll see if it can be edited to extend the time it is ran for.

    Beware however that running it for a year may take a long time.

    Monday, March 17, 2014 2:55 PM
  • Thanks!

    $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}" -f $($total_bytessent[$address]/1mb)) 
    Received Total = $(0 + $msgrec[$address]) 
    Received MB Total = $("{0:F2}" -f $($bytesrec[$address]/1mb)) 
    Sent Internal = $(0 + $total_msgsent_exch[$address]) 
    Sent Internal MB = $("{0:F2}" -f $($total_bytessent_exch[$address]/1mb)) 
    Sent External = $(0 + $total_msgsent_smtpext[$address]) 
    Sent External MB = $("{0:F2}" -f $($total_bytessent_smtpext[$address]/1mb)) 
    Received Internal = $(0 + $msgrec_exch[$address]) 
    Received Internal MB = $("{0:F2}" -f $($bytesrec_exch[$address]/1mb)) 
    Received External = $(0 + $msgrec_smtpext[$address]) 
    Received External MB = $("{0:F2}" -f $($bytesrec_smtpext[$address]/1mb)) 
    Sent Unique Total = $(0 + $unique_msgsent[$address]) 
    Sent Unique MB Total = $("{0:F2}" -f $($unique_bytessent[$address]/1mb)) 
    Sent Internal Unique  = $(0 + $unique_msgsent_exch[$address])  
    Sent Internal Unique MB = $("{0:F2}" -f $($unique_bytessent_exch[$address]/1mb)) 
    Sent External  Unique = $(0 + $unique_msgsent_smtpext[$address]) 
    Sent External Unique MB = $("{0:F2}" -f $($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" 

    Monday, March 17, 2014 3:01 PM
  • Ok try the simple step of changing the -1 in line 2 (the $rundate line) to -365 as below:

    $rundate = $($today.adddays(-365)).toshortdatestring() 

    What that line is essentially doing is figuring out today's date then subtracting a day, which gives it the start date to run from, so the change I just suggested should subtract a year and give you a star date of a year ago.

    EDIT: As it's declared at the top you shouldnt need to change any more of the code.
    • Edited by TomCee44 Monday, March 17, 2014 3:17 PM
    Monday, March 17, 2014 3:15 PM
  • Thanks Tom. I'll give it a try right now.
    Monday, March 17, 2014 3:17 PM
  • The report was blank (no data). The only error in red on the EMS was talking about this:

    The term 'else' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelli
    ng of the name, or if a path was included, verify that the path is correct and try again.
    At line:1 char:5
    + else <<<<  {
        + CategoryInfo          : ObjectNotFound: (else:String) [], CommandNotFoundException
        + FullyQualifiedErrorId : CommandNotFoundException

    Not sure if this is related to the change made or not... Or the reason there was no data.

    Monday, March 17, 2014 3:29 PM
  • Have you tried running the original code again without the change? If the error still occurs it means the code is broken somewhere.

    There are 10 types of people in the world: those who understand binary, and those who don't.

    Monday, March 17, 2014 3:36 PM
  • Yeah, it runs, but now it only shows data for 18 people (out of 103). I'll try to locate the ps script again and try the change. (Thanks for your help and patience!) 
    Monday, March 17, 2014 3:45 PM