email statistics per user

Hello Experts,

I need your help for tuning a PowerShell that works for a single user to pull out the number of emails sent a received by that user given a date range.

The problem is that I would like to run the same script below for a bunch of users that are contained on a csv. For example, given a CSV file[name, email address], I would like to execute the following script and export the results for each user that was indicated on the input csv, instead of running this script manually for each user.


[Int] $intSent = $intRec = 0
Get-TransportService | Get-MessageTrackingLog -ResultSize Unlimited -Start “12/01/2014” -End “04/30/2015” -Sender "JohnDoe@emaildomain.com" -EventID RECEIVE | ? {$_.Source -eq "STOREDRIVER"} | ForEach { $intSent++ }
Get-TransportService | Get-MessageTrackingLog -ResultSize Unlimited -Start “12/01/2014” -End “04/30/2015” -Recipients "JohnDoe@emaildomain.com" -EventID DELIVER | ForEach { $intRec++ }
Write-Host "E-mails sent:    ", $intSent
Write-Host "E-mails received:", $intRec

The script above will show for John Doe the number of emails sent and receive between December 1st 2014 until April 30th 2015

E-mails sent:  xxxx
E-mails received: xxx

Exchange version is 2013 SP1

Can someone please help me to finish the script?

More information

http://www.msexchange.org/kbase/ExchangeServerTips/ExchangeServer2010/Powershell/NumberofE-mailsSentandReceivedbyoneUser.html
Jerry SeinfieldAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Mauro CazabonnetSenior .NET Software EngineerCommented:
csv sample

email,
JohnDoe@emaildomain.com,
JohnDoe@emaildomain.com,
JohnDoe@emaildomain.com,
JohnDoe@emaildomain.com,
JohnDoe@emaildomain.com,
JohnDoe@emaildomain.com,
JohnDoe@emaildomain.com,
JohnDoe@emaildomain.com,
JohnDoe@emaildomain.com,
JohnDoe@emaildomain.com,
JohnDoe@emaildomain.com,
JohnDoe@emaildomain.com,
JohnDoe@emaildomain.com,
JohnDoe@emaildomain.com,

$path = "folder path\users.csv"
$csv = Import-csv -path $path
foreach($user in $csv)
{
 
  [Int] $intSent = $intRec = 0
  Get-TransportService | Get-MessageTrackingLog -ResultSize Unlimited -Start “12/01/2014” -End “04/30/2015” -Sender $user.mail -EventID RECEIVE | ? {$_.Source -eq "STOREDRIVER"} | ForEach { $intSent++ }
  Get-TransportService | Get-MessageTrackingLog -ResultSize Unlimited -Start “12/01/2014” -End “04/30/2015” -Recipients $user.email -EventID DELIVER | ForEach { $intRec++ }
  Write-Host "E-mails sent:    ", $intSent
  Write-Host "E-mails received:", $intRec
}
0
Mauro CazabonnetSenior .NET Software EngineerCommented:
On the Get-Transportservice line it should $user.email

[Int] $intSent = $intRec = 0
   Get-TransportService | Get-MessageTrackingLog -ResultSize Unlimited -Start “12/01/2014” -End “04/30/2015” -Sender $user.email -EventID RECEIVE | ? {$_.Source -eq "STOREDRIVER"} | ForEach { $intSent++ }
   Get-TransportService | Get-MessageTrackingLog -ResultSize Unlimited -Start “12/01/2014” -End “04/30/2015” -Recipients $user.email -EventID DELIVER | ForEach { $intRec++ }
   Write-Host "E-mails sent:    ", $intSent
   Write-Host "E-mails received:", $intRec
0
Mauro CazabonnetSenior .NET Software EngineerCommented:
Added user-email to output

$path = "c:\test\users.csv"
$csv = Import-csv -path $path
foreach($user in $csv)
{
 
  [Int] $intSent = $intRec = 0
  Get-TransportService | Get-MessageTrackingLog -ResultSize Unlimited -Start “12/01/2014” -End “04/30/2015” -Sender $user.email -EventID RECEIVE | ? {$_.Source -eq "STOREDRIVER"} | ForEach { $intSent++ }
  Get-TransportService | Get-MessageTrackingLog -ResultSize Unlimited -Start “12/01/2014” -End “04/30/2015” -Recipients $user.email -EventID DELIVER | ForEach { $intRec++ }
  Write-Host "User:", $user.email
  Write-Host "E-mails sent:    ", $intSent
  Write-Host "E-mails received:", $intRec
}
0
How the Cloud Can Help You as an MSSP

Today, every Managed Security Service Provider (MSSP) needs a platform to deliver effective and efficient security-as-a-service to their customers. Scale, elasticity and profitability are a few of the many features that a Cloud platform offers. Register today to learn more!

Jerry SeinfieldAuthor Commented:
Thanks

Any chance to export all these results onto a CSV file? right now with the write-host configuration I can only see these values on my computer's screen, but need to save and export these results to a csv file

if so,

can you please attach final code?
0
Mauro CazabonnetSenior .NET Software EngineerCommented:
Updated.. :)

$path = "c:\test\users.csv"
$filename = "c:\test\output.csv"
$null | Out-File $filename -Encoding ASCII

"user,sent,received" | Out-File $fileName -Encoding ASCII #csv header
$csv = Import-csv -path $path

foreach($user in $csv)
{
  [Int] $intSent = $intRec = 0
  Get-TransportService | Get-MessageTrackingLog -ResultSize Unlimited -Start “12/01/2014” -End “04/30/2015” -Sender $user.email -EventID RECEIVE | ? {$_.Source -eq "STOREDRIVER"} | ForEach { $intSent++ }
  Get-TransportService | Get-MessageTrackingLog -ResultSize Unlimited -Start “12/01/2014” -End “04/30/2015” -Recipients $user.email -EventID DELIVER | ForEach { $intRec++ }
  $user.email + "," + $intSent + "," + $intRec | Out-File $filename -Append -Encoding ASCII
}
0
Jerry SeinfieldAuthor Commented:
The last script above creates the output.csv file and did not return any errors, however the number of sent and received emails returned is null or zeros

Any ideas?
0
Mauro CazabonnetSenior .NET Software EngineerCommented:
I suspect it may be the way were submiiting the user email variable for Get-TransportService
Try this

$path = "c:\test\users.csv"
$filename = "c:\test\output.csv"
$null | Out-File $filename -Encoding ASCII

"user,sent,received" | Out-File $fileName -Encoding ASCII #csv header
$csv = Import-csv -path $path

foreach($user in $csv)
{
  [Int] $intSent = $intRec = 0
  [string] $curruser = $user.email.trim()
  Get-TransportService | Get-MessageTrackingLog -ResultSize Unlimited -Start “12/01/2014” -End “04/30/2015” -Sender $curruser -EventID RECEIVE | ? {$_.Source -eq "STOREDRIVER"} | ForEach { $intSent++ }
  Get-TransportService | Get-MessageTrackingLog -ResultSize Unlimited -Start “12/01/2014” -End “04/30/2015” -Recipients $curruser -EventID DELIVER | ForEach { $intRec++ }
  $curruser + "," + $intSent + "," + $intRec | Out-File $filename -Append -Encoding ASCII
}
0
Jerry SeinfieldAuthor Commented:
Hi mcazabonnet

your script works, however I am getting into a weird situation

If I run the script with the original range date "12/01/2014  to 04/30/2015" the script works fine and return the number of emails sent and received per user, however because I need to change the range dates and pull out all emails per month, I am getting only zeros in the column of sent and received items

Can you please what is the reason of getting zeros when I run for example, "12/01/2014 to 12/31/214"?

Again the script does not fail, only returns zeros when I perform the search per month and change the original range date
0
Mauro CazabonnetSenior .NET Software EngineerCommented:
I variablized the start and end dates. I'm assuming there may be an issue when updating the date range manually. The updated code should rule this out

Here's an updated user.csv

email,sdate,edate
JohnDoe@emaildomain.com,12/01/2014,4/30/2015
JohnDoe@emaildomain.com,12/01/2014,4/30/2015
JohnDoe@emaildomain.com,12/01/2014,4/30/2015
JohnDoe@emaildomain.com,12/01/2014,4/30/2015
JohnDoe@emaildomain.com,12/01/2014,4/30/2015
JohnDoe@emaildomain.com,12/01/2014,4/30/2015
JohnDoe@emaildomain.com,12/01/2014,4/30/2015
JohnDoe@emaildomain.com,12/01/2014,4/30/2015
JohnDoe@emaildomain.com,12/01/2014,4/30/2015
JohnDoe@emaildomain.com,12/01/2014,4/30/2015
JohnDoe@emaildomain.com,12/01/2014,4/30/2015
JohnDoe@emaildomain.com,12/01/2014,4/30/2015
JohnDoe@emaildomain.com,12/01/2014,4/30/2015
JohnDoe@emaildomain.com,12/01/2014,4/30/2015

The updated code will parse the start and end dates for each user in the list

$path = "c:\test\users.csv"
$filename = "c:\test\output.csv"
$null | Out-File $filename -Encoding ASCII

"user,sdate,edate,sent,received" | Out-File $fileName -Encoding ASCII #csv header
$csv = Import-csv -path $path

foreach($user in $csv)
{
  [Int] $intSent = $intRec = 0
  [string] $curruser = $user.email.trim()
  [string] $sdate = $user.sdate.trim()
  [string] $edate = $user.edate.trim()
  Get-TransportService | Get-MessageTrackingLog -ResultSize Unlimited -Start $sdate -End $edate -Sender $curruser -EventID RECEIVE | ? {$_.Source -eq "STOREDRIVER"} | ForEach { $intSent++ }
  Get-TransportService | Get-MessageTrackingLog -ResultSize Unlimited -Start $sdate -End $edate -Recipients $curruser -EventID DELIVER | ForEach { $intRec++ }
  $curruser + "," + $sdate + "," + $edate + "," + $intSent + "," + $intRec | Out-File $filename -Append -Encoding ASCII
}

M
1

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
Jerry SeinfieldAuthor Commented:
Hi Mcazabonet,

I believe we are good with your last script, still returns only zeros for anything older than the month of April, but I suspect that either the message tracking logs at the exchange server were cleared up, or perhaps something else

Thank you so much for your support
0
Mauro CazabonnetSenior .NET Software EngineerCommented:
Dont forget to click on accept as solution :)
1
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.