Solved

Powershell

Posted on 2013-05-17
18
436 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
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 68

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
 
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 68

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 68

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 68

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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 68

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 68

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 68

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

This article shows how a content item can be identified directly or through translation of a navigation type. It then shows how this information can be used to create a menu for further navigation.
This article will help you understand what HashTables are and how to use them in PowerShell.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the fileā€¦
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

707 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now