Exchange PowerShell Unable to export list to CSV ?

Ask your 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.
This topic is 5 years and 9 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked
User avatar
ITEngineer
Posts: 216
Last visit: Thu Mar 23, 2023 5:45 pm
Has voted: 4 times

Re: Exchange PowerShell Unable to export list to CSV ?

Post by ITEngineer »

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 */
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Exchange PowerShell Unable to export list to CSV ?

Post by jvierra »

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: 216
Last visit: Thu Mar 23, 2023 5:45 pm
Has voted: 4 times

Re: Exchange PowerShell Unable to export list to CSV ?

Post by ITEngineer »

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 */
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Exchange PowerShell Unable to export list to CSV ?

Post by jvierra »

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: 216
Last visit: Thu Mar 23, 2023 5:45 pm
Has voted: 4 times

Re: Exchange PowerShell Unable to export list to CSV ?

Post by ITEngineer »

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 */
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Exchange PowerShell Unable to export list to CSV ?

Post by jvierra »

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

Glad you are set.
This topic is 5 years and 9 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked