Solved

Powershell

Posted on 2013-05-17
18
463 Views
Last Modified: 2013-05-22
I am looking for a powershell command that will tell me the total number of sent\received items per day per mailbox from exchange 2010. Right now I can use these to get results on an individual user but I am wondering how I would add multiple users to this?
Could I point it to a .txt file with all the user names in it?

get-messagetrackinglog -sender user@domain.com -start (get-date).adddays(-1) |select messageid -unique | measure

get-messagetrackinglog -recipient user@domain.com -start (get-date).adddays(-1) |select messageid -unique | measure

Thanks!
0
Comment
Question by:Winsoup
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
18 Comments
 
LVL 52

Expert Comment

by:Manpreet SIngh Khatra
ID: 39176309
Try this .... the Text file should have the Alias of the users
Adams
Chrisb
Arianm

Get-Content "Location of text file" | Get-Messagetrackinglog -Sender user@domain.com -start (get-date).adddays(-1) |select messageid -unique | measure

- Rancy
0
 
LVL 16

Expert Comment

by:Rajitha Chimmani
ID: 39176370
Small correction to the above commands and the above command will give the total number of emails again.

Have the email address of the users in the csv file with the title "emailaddress"

$FinalArray = @()
foreach($user in import-csv "csvfilelocation"){
$emails = Get-Messagetrackinglog -Sender $user.emailaddress -start (get-date).adddays(-1) |select messageid -unique | measure-Object
$Obj = New-Object PSObject -Property @{
UserEmail = $user.emailaddress
EmailCount = $emails.count}
$FinalArray += $Obj
}
}
$FinalArray | Export-csv -path "resultscsvfilelocation" -NoTypeInformation
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 39177341
Instead of collecting data in vars and dumping those at the very end, it is much better to use the streaming features of PowerShell, in particular if there is a lot of data. That allows for better memory handling, and usually speeds up things.
Further, for a single property to read from a file we do not need to use a CSV format, a simple text file is enough.
get-content "Users.txt" | foreach {
  $cnt = (Get-Messagetrackinglog -Sender $_ -start (get-date).adddays(-1) |select messageid -unique | measure-Object).Count
  New-Object PSObject @{
    UserMail = $_
    EmailCount = $cnt
  }
} | export-csv -NoType "Result.csv"

Open in new window

0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 3

Author Comment

by:Winsoup
ID: 39181282
Qlemo, I am getting this message when your script.

Unexpected token 'New-Object' in expression or statement.
At line:1 char:195
+ get-content C:\xxxxx\xxxxxx_xxxxx\desktop\users.txt | foreach {$cn = (Get-Messagetrackinglog -Sender
ate).adddays(-1) |select messageid -unique | measure-Object).Count New-Object <<<<  PSObject @{UserMail
= $cnt}} | export-csv -NoType Result.csv
    + CategoryInfo          : ParserError: (New-Object:String) [], ParentContainsErrorRecordException
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 39181536
You need to retain the line breaks as shown. The error message shows all or parts of the code are in a single line, which won't work.
0
 
LVL 3

Author Comment

by:Winsoup
ID: 39181692
I tried that and it's not doing anything, no errors or anything, just sits there like it's still running the command. I only have a few users in the text document right now for testing so I wouldn't think it would take that long.
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 39181772
Getting the tracking log can be time consuming. You will see results only at the very end, so restrict your test to a single user.
0
 
LVL 3

Author Comment

by:Winsoup
ID: 39182145
I changed it to just one user but it's doing the same thing. Here is exactly what I am using so you can see if I am doing something incorrectly.

get-content C:\Users.txt | foreach {
  $cnt = (Get-Messagetrackinglog -Sender $_ -start (get-date).adddays(-1) |select messageid -unique | measure-Object).Count
  New-Object PSObject @{
    UserMail = $_
    EmailCount = $cnt
  }
} | export-csv -NoType C:\Results.csv
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 39182183
That's correct. Let's try some debugging code:
get-content C:\Users.txt | foreach {
  Get-Messagetrackinglog -Sender $_ -start (get-date).adddays(-1)
  Get-Messagetrackinglog -Sender $_ -start (get-date).adddays(-1) |select messageid -unique
  (Get-Messagetrackinglog -Sender $_ -start (get-date).adddays(-1) |select messageid -unique | measure-Object).Count
}

Open in new window

This should allow to see whether there is anything executed, and the impact of the most important operations.
0
 
LVL 3

Author Comment

by:Winsoup
ID: 39182213
That said it can't find C:\users.txt
It is in that location.
0
 
LVL 3

Author Comment

by:Winsoup
ID: 39182251
I just deleted the users.txt and created a new one and ran it again and it worked.
It just has a category for Count and has a number, is there a way to tell how many are sent and how many are received?
I'm guessing this is just one or the other?

I actually just added a couple more users to the users.txt and tried it and it just shows the same number under "count" for all of them so I am not sure what that number is.
And is there a way to tell what users mailbox it is in the .csv?
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 39182766
I don't know why the counts should be the same for different users. The count should be shown for sent items only, and the username should be in the CSV already. To have the received count, we have to extend the code:
get-content "Users.txt" | foreach {
  $cntsent = (Get-Messagetrackinglog -Sender $_ -start (get-date).adddays(-1) |select messageid -unique | measure-Object).Count
  $cntrcvd = (Get-Messagetrackinglog -Recipient $_ -start (get-date).adddays(-1) |select messageid -unique | measure-Object).Count
  New-Object PSObject @{
    UserMail = $_
    EmailsSent = $cntsent
    EmailsRcvd = $cntrcvd
  }
} | export-csv -NoType "Result.csv"

Open in new window

If you do not see a username in the "UserMail" column of Result.csv, that is the issue for getting the same counts for different user - there is a format/content error in users.txt, which should just contain one email address per line, and nothing else.
0
 
LVL 3

Author Comment

by:Winsoup
ID: 39184677
I've attached the results that I am getting...


My text file looks like this:

user@domain.com
user@domain.com
user@domain.com
Results.xlsx
0
 
LVL 70

Accepted Solution

by:
Qlemo earned 500 total points
ID: 39185913
I was able to test the script myself now against MSX 2007, and now seeing what happens. The culprit is line 5 - a typo, I left out -Property - doh! That leads to trying to dump a hash table into a CSV, and that is never a good idea.
get-content "Users.txt" | foreach {
  $cntsent = (Get-Messagetrackinglog -Sender $_ -start (get-date).adddays(-1) |select messageid -unique | measure-Object).Count
  $cntrcvd = (Get-Messagetrackinglog -Recipient $_ -start (get-date).adddays(-1) |select messageid -unique | measure-Object).Count
  New-Object PSObject -Property @{
    UserMail = $_
    EmailsSent = $cntsent
    EmailsRcvd = $cntrcvd
  }
} | select UserMail, EmailsRcvd, EmailsSent | export-csv -NoType "Result.csv"

Open in new window

I have also added a select statement to get the columns into a specific order (hash tables do not have a specific one).
0
 
LVL 3

Author Comment

by:Winsoup
ID: 39185941
That worked great!!
Thank you very much.
0
 
LVL 3

Author Comment

by:Winsoup
ID: 39188442
Would it be possible to create a column that would have the date in it?  
It would just need it to be there once since it's pulling all these counts from the day before.
So the report run on 5/22/2013 would have a date of 5/21/2013.  

They are getting emailed automatically and would be nice to know what days they are for.
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 39189167
Sure. We can either name the result file:
$dt = (get-date).adddays(-1)
get-content "Users.txt" | foreach {
  $cntsent = (Get-Messagetrackinglog -Sender    $_ -start $dt |select messageid -unique | measure-Object).Count
  $cntrcvd = (Get-Messagetrackinglog -Recipient $_ -start $dt |select messageid -unique | measure-Object).Count
  New-Object PSObject -Property @{
    UserMail = $_
    EmailsSent = $cntsent
    EmailsRcvd = $cntrcvd
  }
} | select UserMail, EmailsRcvd, EmailsSent | export-csv -NoType "Result."+(Get-Date($dt) -Format yyyyMMdd)+".csv"

Open in new window

or store the date in each row:
$dt = (get-date).adddays(-1)
get-content "Users.txt" | foreach {
  $cntsent = (Get-Messagetrackinglog -Sender    $_ -start $dt |select messageid -unique | measure-Object).Count
  $cntrcvd = (Get-Messagetrackinglog -Recipient $_ -start $dt |select messageid -unique | measure-Object).Count
  New-Object PSObject -Property @{
    UserMail   = $_
    EmailsSent = $cntsent
    EmailsRcvd = $cntrcvd
    Date       = $dt.Date
  }
} | select Date, UserMail, EmailsRcvd, EmailsSent | export-csv -NoType "Result.csv"

Open in new window

0
 
LVL 3

Author Comment

by:Winsoup
ID: 39189300
You rock, thank you!
0

Featured Post

Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows 10 came with  a lot of built in applications, Some organisations leave them there, some will control them using GPO's. This Article is useful for those who do not want to have any applications in their image (example:me).
My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question