• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 108
  • Last Modified:

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
0
Jerry Seinfield
Asked:
Jerry Seinfield
  • 7
  • 4
1 Solution
 
Mauro CazabonnetSenior 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 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 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
Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

 
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 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 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 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
0
 
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 Software EngineerCommented:
Dont forget to click on accept as solution :)
0
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.

Join & Write a Comment

Featured Post

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.

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now