Exchange MessageTracking (Help!)

PowerShell Help
1

Hello PowerShell companions. I’ve just signed up to this wonderful site after watching the PowerShell introduction by Jeffrey Snover and Jason Helmick on Microsoft Virtual Academy, who highly recommended it! I’m glad to be here!

I have written a little PowerShell script (can I call them ‘scripts’? Or should I say CMDLets?) to retrieve some simple Exchange messagetracking results. Here’s my script, which uses a csv file which only contains two columns. Column A contains a sending email address and Column B contains a recipient email address. My PowerShell script retrieves all emails sent from the sending email address from Column A to the recipient email address in Column B.

$UserList = IMPORT-CSV c:\data.csv

This foreach will loop all the rows in the above userlist

FOREACH ($User in $UserList)

{get-transportservice | get-messagetrackinglog -start “01/01/16” -sender $User.sender -recipients $User.recipient -eventid “submit” | select-object eventid, sender, timestamp, @{name=“recipients”;expression={$.recipients}},@{name=“RecipientStatus”;expression={$.recipientstatus}},messagesubject | export-csv c:\Output.csv -append }

The above works perfectly. The only issue is that in the output.csv, I would like more data, such as the TotalItemSize. When I modify the above script to include:

{get-transportservice | get-messagetrackinglog -start “01/01/16” -sender $User.sender -recipients $User.recipient -eventid “submit” | select-object eventid, sender, timestamp, @{name=“recipients”;expression={$.recipients}},@{name=“RecipientStatus”;expression={$.recipientstatus}},messagesubject, TOTALITEMSIZE | export-csv c:\Output.csv -append }

Unfortunately, the TotalItemSize output is always empty.

If anyone on this site is kind enough to let me know where I am going wrong, I would very appreciative.

Thanking you in advance,

TQ

2

TQ, it’s possible that the cmdlet ‘get-messagetrackinglog’ does not return a property called ‘TOTALITEMSIZE’. (MSDN not telling us the return object :frowning: )

Can you run these lines of code and post the result to show us what kind of object is returned by ‘get-messagetrackinglog’?

$Sample = IMPORT-CSV c:\data.csv | select -First 1 | get-transportservice | 
    get-messagetrackinglog -start "01/01/16" -sender $_.sender -recipients $_.recipient -eventid "submit" 
$Sample | Get-Member
$Sample.GetType()
3

Hi

If you are looking for the size of the email try the below

Replace TOTALITEMSIZE with TotalBytes (Tested working in Exchange 2013 CU15)

{get-transportservice | get-messagetrackinglog -start “01/01/16” -sender $User.sender -recipients $User.recipient -eventid “submit” | select-object eventid, sender, timestamp, @{name=“recipients”;expression={$.recipients}},@{name=“RecipientStatus”;expression={$.recipientstatus}},messagesubject, TotalByes | export-csv c:\Output.csv -append }

4

Hello Prabkahar and Sam,

Thank you SO much for your lovely contributions. They are most appreciated. I have changed my script as per Prabkahar’s recommendations and replaced TOTALITEMSIZE with TotalBytes. However, the output is still empty.

Our MessageTracking logs definitely write data to the TotalBytes field. I have proved this by simply running:

Get-TransportService | Get-MessageTrackingLog -sender me@company.com | Select-Object Sender, MessageSubject, TotalBytes

The ‘TotalBytes’ column always contains data when I run the above. However, when I run:

$UserList = IMPORT-CSV c:\input.csv

FOREACH ($User in $UserList)

{get-transportservice | get-messagetrackinglog -sender $User.sender -recipients $User.recipient -eventid “submit” | select-object eventid, sender, timestamp, @{name=“recipients”;expression={$.recipients}},@{name=“RecipientStatus”;expression={$.recipientstatus}},messagesubject, TotalBytes | export-csv c:\Output.csv -append }

The TotalBytes column is always empty.

Any assistance on how to solve this mystery would be very greatly appreciated!

Kind regards,

Tan

5

Also - to answer Sam’s question:

Running

$Sample = IMPORT-CSV c:\data.csv | select -First 1 | get-transportservice |
get-messagetrackinglog -start “01/01/16” -sender $.sender -recipients $.recipient -eventid “submit”
$Sample | Get-Member
$Sample.GetType()

Gives:

TypeName: Microsoft.Exchange.Management.TransportLogSearchTasks.MessageTrackingEvent

Name MemberType Definition

Equals Method bool Equals(System.Object obj)
GetHashCode Method int GetHashCode()
GetType Method type GetType()
ToString Method string ToString()
PSComputerName NoteProperty System.String PSComputerName=ukdc124310.uk.deloitte.com
PSShowComputerName NoteProperty System.Boolean PSShowComputerName=False
RunspaceId NoteProperty System.Guid RunspaceId=780d2c97-c709-4b48-bf3b-616d2642d5f3
ClientHostname Property string ClientHostname {get;}
ClientIp Property string ClientIp {get;}
ConnectorId Property string ConnectorId {get;}
Directionality Property string Directionality {get;}
EventData Property System.Collections.Generic.KeyValuePair[string,System.Object] EventData {get;}
EventId Property string EventId {get;}
InternalMessageId Property string InternalMessageId {get;}
MessageId Property string MessageId {get;}
MessageInfo Property string MessageInfo {get;}
MessageLatency Property System.Nullable[Microsoft.Exchange.Data.EnhancedTimeSpan] MessageLatency {get;}
MessageLatencyType Property Microsoft.Exchange.Management.TransportLogSearchTasks.MessageLatencyType Messag…
MessageSubject Property string MessageSubject {get;}
OriginalClientIp Property string OriginalClientIp {get;}
RecipientCount Property System.Nullable[int] RecipientCount {get;}
Recipients Property string Recipients {get;}
RecipientStatus Property string RecipientStatus {get;}
Reference Property string Reference {get;}
RelatedRecipientAddress Property string RelatedRecipientAddress {get;}
ReturnPath Property string ReturnPath {get;}
Sender Property string Sender {get;}
ServerHostname Property string ServerHostname {get;}
ServerIp Property string ServerIp {get;}
Source Property string Source {get;}
SourceContext Property string SourceContext {get;}
TenantId Property string TenantId {get;}
Timestamp Property datetime Timestamp {get;}
TotalBytes Property System.Nullable[int] TotalBytes {get;}

and

IsPublic IsSerial Name BaseType

True True Object System.Array

6

Hi TQ

I tried the below in our environment and i could see the totalbytes column filled with values

$UserList = Import-Csv -Path C:\temp\input.csv

FOREACH ($User in $UserList)
{
$User.sender
$User.recipient
get-transportservice | get-messagetrackinglog -sender $User.sender -recipients $User.recipient | select-object eventid, sender, timestamp, @{name=“recipients”;expression={$.recipients}},@{name=“RecipientStatus”;expression={$.recipientstatus}},messagesubject, TotalBytes | export-csv output.csv -append}

I do not see the event “SUBMIT” in the environment, mostly i could see deliver, send, receive, HAREDIRECT, AGENTINFO and so on.
Lets search for all the events and probably filter the excel sheet later on

7

Hi Prabhakar EG,

Thank you SO very much!

I have managed to get my messagetracking script working thanks to your help! The mistake I was making was trying to obtain data for the TotalBytes column whilst also specifying ‘-eventID submit’. When obtaining MessageTracking results in which the EventID is specified to ‘submit’, the TotalBytes column was always empty. I removed the ‘-eventID’ section altogether and noticed that the TotalBytes column was populated when the ‘-EventID’ section was ‘receive’.

Thanks again for getting me safely to a great conclusion!

Kind regards,

TQ