Exchange PowerShell Unable to export list to CSV ?

Ask your Windows PowerShell-related questions, including questions on cmdlet development!
Forum rules
Do not post any licensing information in this forum.

Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
User avatar
ITEngineer
Posts: 90
Joined: Wed Oct 12, 2011 10:52 am

Re: Exchange PowerShell Unable to export list to CSV ?

Post by ITEngineer » Wed Jun 06, 2018 6:59 pm

jvierra wrote:
Wed Jun 06, 2018 6:48 pm
And what does this show you?

Code: Select all

foreach ($email in (Import-Csv $inputFile)) {

    Write-host "Email address: $($email.email)"
	$resultFile = "C:\$($email.email).CSV"
	
	$TransportServers = Get-TransportService
	$TransportServers | % {
        Get-Messagetrackinglog -Server $_.Name @trackparams -Recipients $email.email |
        ? { $_.Sender -notmatch $rxDomains } |
        Group-Object -NoElement Sender |
        Sort-Object Count -Descending |
        Select Name, Count
	}
}
Mr. Vierra,

Thanks for the quick reply.

here's the result in my PowerGUI console:

Code: Select all

Email address: MyEmail@domain.com

Email address: Manager1Email@domain.com
Name                                                 Count
----                                                 -----
no-reply@Oracle.com                                   9
noreply@email.teams.microsoft.com                        6
alert@vmware.com                                        4
alerts-noreply@mail.windowsazure.com                     4
Email address: Manager2Email@domain2.com
no-reply@Oracle.com                                  15
transfer@citi.com                                       13
alerts-noreply@mail.windowsazure.com                    10
ai-noreply@applicationinsights.io                        7
alert@vmware.com                                        6
Note, the content of the input trackingemail.csv file:

Code: Select all

Email
MyEmail@domain.com
Manager1Email@domain.com
Manager2Email@domain2.com
/* IT Engineer */

User avatar
jvierra
Posts: 12774
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: Exchange PowerShell Unable to export list to CSV ?

Post by jvierra » Wed Jun 06, 2018 7:21 pm

Now tell me what this produces.

Code: Select all

foreach ($email in (Import-Csv $inputFile)) {

    Write-host "Email address: $($email.email)"
	$resultFile = "C:\$($email.email).CSV"
	
	$TransportServers = Get-TransportService
	$TransportServers | % {
        Get-Messagetrackinglog -Server $_.Name @trackparams -Recipients $email.email |
        ? { $_.Sender -notmatch $rxDomains } |
        Group-Object -NoElement Sender |
        Sort-Object Count -Descending |
        Select Name, Count |        
        Export-CSV $resultFile -NoTypeInformation
	}
}

User avatar
ITEngineer
Posts: 90
Joined: Wed Oct 12, 2011 10:52 am

Re: Exchange PowerShell Unable to export list to CSV ?

Post by ITEngineer » Wed Jun 06, 2018 7:34 pm

jvierra wrote:
Wed Jun 06, 2018 7:21 pm
Now tell me what this produces.

Code: Select all

foreach ($email in (Import-Csv $inputFile)) {

    Write-host "Email address: $($email.email)"
	$resultFile = "C:\$($email.email).CSV"
	
	$TransportServers = Get-TransportService
	$TransportServers | % {
        Get-Messagetrackinglog -Server $_.Name @trackparams -Recipients $email.email |
        ? { $_.Sender -notmatch $rxDomains } |
        Group-Object -NoElement Sender |
        Sort-Object Count -Descending |
        Select Name, Count |        
        Export-CSV $resultFile -NoTypeInformation
	}
}
Console shows:
Email address: MyEmail@domain.com
Email address: Manager1Email@domain.com
Email address: Manager2Email@domain2.com
The three files created with 0 bytes (empty content).
/* IT Engineer */

User avatar
jvierra
Posts: 12774
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: Exchange PowerShell Unable to export list to CSV ?

Post by jvierra » Wed Jun 06, 2018 7:42 pm

That doesn't make any sense. If the output is correct then the CSV will reflect the output. You have some kind of mistake in the code and you are likely not using the exact code I posted.

This would be a better way to do this:

Code: Select all

$inputFile = 'C:\trackingemail.csv'
$trackparams = @{
    Start      = (Get-Date).AddDays(-1)
    end        = Get-Date
    ResultSize = 'Unlimited'
    EventId    = 'DELIVER'
}

$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri http://PRODMAIL01-VM/PowerShell/ -Authentication Kerberos
Import-PSSession $Session -AllowClobber

$rxDomains = '@' + ((Get-AcceptedDomain).Name -join '|@')

Write-host "Time window between $($trackparams.start.ToString('dd/MM/yyyy HH:MM')) until $($trackparams.end.ToString('dd/MM/yyyy HH:MM')):"
Import-Csv $inputFile |
    ForEach-Object{
        $email = $_.email
        Write-host "Email address: $email" -Fore Green	
	    Get-TransportService |
            ForEach-Object {Get-Messagetrackinglog -Server $_.Name @trackparams -Recipients $email} |
                Where-Object { $_.Sender -notmatch $rxDomains } |
                Group-Object -NoElement Sender |
                Sort-Object Count -Descending |
                Select Name, Count |
                Export-Csv "C:\$email.CSV" -NoTypeInformation
    }

User avatar
ITEngineer
Posts: 90
Joined: Wed Oct 12, 2011 10:52 am

Re: Exchange PowerShell Unable to export list to CSV ?

Post by ITEngineer » Wed Jun 06, 2018 7:55 pm

jvierra wrote:
Wed Jun 06, 2018 7:42 pm
That doesn't make any sense. If the output is correct then the CSV will reflect the output. You have some kind of mistake in the code and you are likely not using the exact code I posted.

This would be a better way to do this:

Code: Select all

$inputFile = 'C:\trackingemail.csv'
$trackparams = @{
    Start      = (Get-Date).AddDays(-1)
    end        = Get-Date
    ResultSize = 'Unlimited'
    EventId    = 'DELIVER'
}

$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri http://PRODMAIL01-VM/PowerShell/ -Authentication Kerberos
Import-PSSession $Session -AllowClobber

$rxDomains = '@' + ((Get-AcceptedDomain).Name -join '|@')

Write-host "Time window between $($trackparams.start.ToString('dd/MM/yyyy HH:MM')) until $($trackparams.end.ToString('dd/MM/yyyy HH:MM')):"
Import-Csv $inputFile |
    ForEach-Object{
        $email = $_.email
        Write-host "Email address: $email" -Fore Green	
	    Get-TransportService |
            ForEach-Object {Get-Messagetrackinglog -Server $_.Name @trackparams -Recipients $email} |
                Where-Object { $_.Sender -notmatch $rxDomains } |
                Group-Object -NoElement Sender |
                Sort-Object Count -Descending |
                Select Name, Count |
                Export-Csv "C:\$email.CSV" -NoTypeInformation
    }
Mr. Vierra,

You da man !
:D

It works beautifully, not sure why it didn't work correctly on my script, but your code works as expected.

Many thanks for the assistance in this matter.
/* IT Engineer */

User avatar
jvierra
Posts: 12774
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: Exchange PowerShell Unable to export list to CSV ?

Post by jvierra » Wed Jun 06, 2018 8:00 pm

I suspect copy/paste issues as the previous code should have worked.

Glad you are set.

Locked