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

Exchange PowerShell Unable to export list to CSV ?

Post by ITEngineer »

Hi,

I have modified this script to take multiple inputs from trackingemail.csv to count the statistics for the external email that was delivered in the past 1 day.

Code: Select all


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

$start = (Get-Date).AddDays(-1)
$end   = (Get-Date)
$inputFile = "C:\trackingemail.csv"

Write-host "Time window between $($start.ToString("dd/MM/yyyy HH:MM")) until $($end.ToString("dd/MM/yyyy HH:MM")):"

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

$trackparams = @{
    start      = $start
    end        = $end
    resultsize = 'Unlimited'
    EventId    = 'DELIVER'
}

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
	}
}
The content of trackingemail.csv file consist of two email address:

Code: Select all

email
MyEmail@domain.com
MyManager@domain.com
However, the result is blank 0 KB .CSV file:

Code: Select all

MyEmail@domain.com.CSV
MyManager@domain.com.CSV
Any help would be greatly appreciated to fix the issue above.

Thanks,
/* 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 »

Run as follows to be sure you are getting any results.

Code: Select all

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

        Write-host "Email address: $($email.email)"
	Get-TransportService |
	      ForEach-Object {
                  Get-Messagetrackinglog -Server $_.Name @trackparams -Recipients $email.email
	     }
}
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 5:15 pm Run as follows to be sure you are getting any results.

Code: Select all

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

        Write-host "Email address: $($email.email)"
	Get-TransportService |
	      ForEach-Object {
                  Get-Messagetrackinglog -Server $_.Name @trackparams -Recipients $email.email
	     }
}
Yes, Mr. Vierra,

I got lots of console result.
/* 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 »

Then add one line at a time until you have found the issue.

Time to learn how to debug a script … eh?
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 5:47 pm Then add one line at a time until you have found the issue.

Time to learn how to debug a script … eh?
Yes, I ahve removed the line
Export-CSV $resultFile -NoTypeInformation

from the original script and the result is displayed on the console. But how to export it to the respective .CSV file ?
hence I need some help here :shock:
/* 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 run this to see what you get.

Code: Select all

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

    Write-host "Email address: $($email.email)"	
	$TransportServers = Get-TransportService
	$TransportServers | % {
        Get-Messagetrackinglog -Server $_.Name @trackparams -Recipients $email.email |
              ? { $_.Sender -notmatch $rxDomains } 
}
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:03 pm Now run this to see what you get.

Code: Select all

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

    Write-host "Email address: $($email.email)"	
	$TransportServers = Get-TransportService
	$TransportServers | % {
        Get-Messagetrackinglog -Server $_.Name @trackparams -Recipients $email.email |
              ? { $_.Sender -notmatch $rxDomains } 
}
yes, I can see the result:
EventId Source Sender Recipients MessageSubject
/* 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 »

Is that what you want? Where is the data. Look like a bunch of headers to me.
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:31 pm Is that what you want? Where is the data. Look like a bunch of headers to me.
I only want these as the content of the each respective .CSV file:

Code: Select all

Name                                                 Count
----                                                 -----
no-reply@email.com                                   123
no-reply@email2.com                                   93
Alert@email2.com                                     67
jvierra wrote: Wed Jun 06, 2018 6:03 pm Now run this to see what you get.
It works to display the result in the console up until the below code:

Code: Select all

foreach ($email in (Import-Csv $inputFile)) {
	    Write-host "Email address: $($email.email)"	
		
		$resultFile = "C:\LOGS\$($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
		}
}
When I add the the next line of code:

Code: Select all

Select Name, Count
The result is started to behave strangely:

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@Azure.com                                        4
alerts-noreply@mail.windowsazure.com                     4
Email address: Manager2Email@domain2.com
no-reply@Oracle.com                                  15
alerts-noreply@mail.windowsazure.com                    10
transfer@citi.com                                        9
ai-noreply@applicationinsights.io                        7
There are 3 lines of content in the input .CSV file:
/* 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 »

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