PowerShell to parse Exchange message tracking not working ?

[ITEngineer]

Hi All,

I need some help in fixing the below PowerShell script for Exchange Server message tracking log.

Basically, the script is searching for all NDR (email with the subject "*Undeliverable: *") that is delivered within the specific time frame, in the case above is 1st March 2018 until today 27th June 2018 that was sent from the external sender, hence the email must not be coming from (Get-AcceptedDomain).Name

Content of the $env:USERPROFILE\Desktop\trackingemail.csv file is:

Code: Select all

email
*@domain1.com
*@domain2.com

This is the script:

Code: Select all

$start = "1/3/2018 1:00:00 AM"
$end = "27/06/2018 1:00:00 PM"

Write-host "Search the Exchange transaction logs between $start until $end :"

foreach ($email in (Import-Csv "$env:USERPROFILE\Desktop\trackingemail.csv")) {
	$trackparams = @{
		start	    = $start
		end		    = $end
		resultsize  = 'Unlimited'
		Recipients   = $email.email
		MessageSubject = "*Undeliverable: *"
	}
	
	$Messages = (Get-TransportService) | Get-Messagetrackinglog @trackparams
	
	ForEach ($Domain in (Get-AcceptedDomain).Name) {
		$Messages = $Messages | where { $_.Sender -notlike "*$Domain" }
	}
	
	Write-host "Email address: $($email.email) :"
	
	$Messages |
	Select-Object @{ Name = "Recipients"; Expression = { $_.Recipients -join ';' } }, Sender, ClientIp, ClientHostname, Timestamp, EventID, Source, ServerHostname, ServerIp, MessageSubject, TotalBytes, ConnectorId |
} | Export-Csv C:\RESULT\Result.csv -NoTypeInformation

and this is the error message I received when executing the above script.

Code: Select all

Cannot process argument transformation on parameter 'End'. Cannot convert value "27/06/2018 11:00:00 AM" to type "System.DateTime". 
Error: "String was not recognized as a valid DateTime."

The input object cannot be bound to any parameters for the command either because the command does not take pipeline input or the input and its properties do not match any of the parameters that take pipeline input.
    + CategoryInfo          : InvalidArgument: (PRODEX02-VM:PSObject) [Get-MessageTrackingLog], ParameterBindingException
    + FullyQualifiedErrorId : InputObjectNotBound,Get-MessageTrackingLog
    + PSComputerName        : PRODEX01-VM
	
The input object cannot be bound to any parameters for the command either because the command does not take pipeline input or the input and its properties do not match any of the parameters that take pipeline input.
    + CategoryInfo          : InvalidArgument: (PRODEX03-VM:PSObject) [Get-MessageTrackingLog], ParameterBindingException
    + FullyQualifiedErrorId : InputObjectNotBound,Get-MessageTrackingLog
    + PSComputerName        : PRODEX01-VM
	
The input object cannot be bound to any parameters for the command either because the command does not take pipeline input or the input and its properties do not match any of the parameters that take pipeline input.
    + CategoryInfo          : InvalidArgument: (PRODEX04-VM:PSObject) [Get-MessageTrackingLog], ParameterBindingException
    + FullyQualifiedErrorId : InputObjectNotBound,Get-MessageTrackingLog
    + PSComputerName        : PRODEX01-VM
	
The input object cannot be bound to any parameters for the command either because the command does not take pipeline input or the input and its properties do not match any of the parameters that take pipeline input.
    + CategoryInfo          : InvalidArgument: (PRODEX05-VM:PSObject) [Get-MessageTrackingLog], ParameterBindingException
    + FullyQualifiedErrorId : InputObjectNotBound,Get-MessageTrackingLog
    + PSComputerName        : PRODEX01-VM
	
...

The input object cannot be bound to any parameters for the command either because the command does not take pipeline input or the input and its properties do not match any of the parameters that take pipeline input.
    + CategoryInfo          : InvalidArgument: (PRODEX15-VM:PSObject) [Get-MessageTrackingLog], ParameterBindingException
    + FullyQualifiedErrorId : InputObjectNotBound,Get-MessageTrackingLog
    + PSComputerName        : PRODEX01-VM

Any kind of help and suggestion would be greatly appreciated.

[jvierra]

You are passing a string to a parameter that requires a datetime object. That is exactly what the error message says.

Cannot process argument transformation on parameter 'End'. Cannot convert value "27/06/2018 11:00:00 AM" to type "System.DateTime".
Error: "String was not recognized as a valid DateTime."

[jvierra]

Like this:

Code: Select all

$trackparams = @{
	start	    = [datetime]'1/3/2018 1:00:00 AM'
	end		    = [datetime]'27/06/2018 1:00:00 PM'
	resultsize  = 'Unlimited'
	Recipients   = $null
	MessageSubject = "*Undeliverable: *"
}

Write-host "Search the Exchange transaction logs between $start until $end :"

Import-Csv "$env:USERPROFILE\Desktop\trackingemail.csv" |
    ForEach-Object{
    	$email = $_
        $trackparams.Recipients = $email.email
    	$Messages = (Get-TransportService) | Get-Messagetrackinglog @trackparams
    	ForEach ($Domain in (Get-AcceptedDomain).Name) {
    		$Messages | where { $_.Sender -notlike "*$Domain" }
    	}
    	Write-host "Email address: $($email.email) :"
    } | 
    Select-Object @{ Name = "Recipients"; Expression = { $_.Recipients -join ';' } }, 
                  Sender, ClientIp, ClientHostname, Timestamp, EventID, 
                  Source, ServerHostname, ServerIp, MessageSubject, TotalBytes, ConnectorId |
    Export-Csv C:\RESULT\Result.csv -NoTypeInformation

[ITEngineer]

Thanks for the assistance in this matter, somehow it is still displaying the same issue:

Code: Select all

Cannot convert value "1/03/2018 3:00:00 PM" to type "System.DateTime". Error: "String was not recognized as a valid DateTime."
At C:\Users\Admin\AppData\Local\Temp\12567d90-4897-4db9-856c-8ae0385a7fde.ps1:5 char:1
+ $trackparams = @{
+ ~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidArgument: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvalidCastParseTargetInvocationWithFormatProvider

is it due to my local date format DD/MM/YYYY ?

[jvierra]

You have the month and the day swapped. The standard date format is "MM/dd/yyyy" or, using the international version, "yyyy/MM/dd".

To create a date using the "New" method we can do this:

[datetime]::New(int year,int month,int day,int hour,int minute,int second)

[jvierra]

The following will convert in any us or European system.
[datetime]'1/3/2018 1:00:00 AM'

The following will only convert in European systems:
[datetime]'27/06/2018 1:00:00 PM'

In US-en systems "27" is not a month.

[ITEngineer]

I've swapped the DD/MM/YYYY with MM/DD/YYYY,
However the error message is now just like below repeated 15x as the number of my Exchange servers:

The input object cannot be bound to any parameters for the command either because the command does not take pipeline input or the input and its properties do not match any of the parameters that take pipeline input

[jvierra]

Copy and paste this:

[datetime]'1/3/2018 1:00:00 AM'

[ITEngineer]

Copy and paste this:

[datetime]'1/3/2018 1:00:00 AM'

The result is:

Wednesday, 3 January 2018 1:00:00 AM

[jvierra]

So it works correctly. What is the issue then?

If you paste this it won't work because the month is I the wrong place.
[datetime]'27/06/2018 1:00:00 PM'

This will work:
[datetime]'6/27/2018 1:00:00 PM'