Help with a PowerShell script for Exchange report

Hello Experts,

I need from your expertise to build a PowerShell script or cmdlet to perform the following:

Given a CSV file[csv input] that only contains one column [Exchange Alias Name or display name, or perhaps email address], I need to pull out following information and export to a new CSV file

could we get the total number of e-mails sent and total number of e-mails received by month for January 2015 – April 2015?

This report will be presented or displayed on a csv or xlsxl file, and will show the information from all users indicated as per csv input

Your help is appreciated
Jerry SeinfieldAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jerry SeinfieldAuthor Commented:
Any updates?
0
Marwan OsmanCommented:
Hi

what is the exchange server version? do you need (the total number of sent and received items per months) per user? or in all of the organization?
0
Will SzymkowskiSenior Solution ArchitectCommented:
I use a script called topuserstatistics.ps1. I cannot take credit for creating this but it works wonders and gets everything you are looking for...

$today = get-date 
$rundate = $($today).AddDays(-31).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" 
 
 
#Contact information 
#[string](0..33|%{[char][int](46+("686552495351636652556262185355647068516270555358646562655775 0645570").substring(($_*2),2))})-replace " "

Open in new window


Just modify line 2 .AddDays(-31) for numbers of days you want to go back.

Will.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jerry SeinfieldAuthor Commented:
Hi Mar 01

what is the exchange server version? do you need (the total number of sent and received items per months) per user?

Exchange server version is 2013 SP1, I need to total of number of sent and received items per month per user, that is correct

Thanks
0
Jerry SeinfieldAuthor Commented:
Hey Will, does your script export the information to a CSV file? if so, where is the path?
0
Will SzymkowskiSenior Solution ArchitectCommented:
Yes it does export it to CSV one for Distribution and one for Users. The CSV destination is the same directory where the .PS1 script is saved.

So if you saved this in c:\scripts\TopUserStatistics.ps1 then your CSV's will be created in the c:\scripts folder as well.

Will.
0
Jerry SeinfieldAuthor Commented:
Hi Mar01, can you please respond my last question?
0
Jerry SeinfieldAuthor Commented:
Thanks Will, appreciated that you can provide me with that script, but I am more interested in pulling out information about total number of sent items and total number of receiveing items for users in an exchange 2013 organization

Mar 1 0. waiting for your feedback
0
Will SzymkowskiSenior Solution ArchitectCommented:
Have you tried this script in Exchange 2013? It does provide total number of email send/received internal/external.

Will.
0
Marwan OsmanCommented:
0
Jerry SeinfieldAuthor Commented:
Thanks Mar 1o,

Does the script above work for Exchange 2013? if so, what is the correct syntax for Exchange 2013?
0
Marwan OsmanCommented:
change the line $ExchangeVersion = [Microsoft.Exchange.WebServices.Data.ExchangeVersion]::Exchange2010_SP2     to $ExchangeVersion = [Microsoft.Exchange.WebServices.Data.ExchangeVersion]::Exchange2013_SP1 and try it
0
Jerry SeinfieldAuthor Commented:
Mar, see the errors when I run the script from EMS at exchange server
ScriptError.JPG
0
Jerry SeinfieldAuthor Commented:
Will, although your script does not return any errors, the 2 csv files are empty[no values]

any ideas?
Scripterror2.JPG
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.