I've searched far and wide today to no avail. The basic issue is someone sent an email to a DL, and I want a list of people who received this email. Simple right? While Powershell is AWESOME.. some simple things turn into nightmares.. case in point. Here is my powershell command..

Get-ExchangeServer | where {$_.IsHubTransportServer -eq "true"} | sort-object Name | get-messagetrackinglog -ResultSize unlimited -sender john@contoso.com -EventID "deliver" -Start "05/14/2013 10:00:00 AM" -End "05/14/2013 12:00:00 PM" -messagesubject "I can't figure this out" | select-object timeStamp,eventid,ServerHostName,Sender,@{Name=Recipients;Expression={[string]::join(";", ($_.recipients))}} ,MessageSubject | Export-csv c:\sent1.csv -notypeinformation

This works, exactly as I expect but here is the BIG problem. #_.recipients is a string value, and this email was sent to A DL, with nested groups. So when I open the results, there are rows that have multiple recipient smtp addresses in the same row. So the output looks like this in Excel.  Normally this works fine, but I think the issue is that this was a DL, and in the output CSV I see rows that contain 1 recipient and others may contain 20 on the same row. Any suggestions on how I can fix this?

Timestamp         Sender Recipients           Recipients

5/14/2013 10:46 john@contoso.com       jim@consoto.com;jack@contso.com;bob@contoso.com

What I want is this..

Timestamp         Sender Recipients           Recipients

5/14/2013 10:46 john@contoso.com       jim@consoto.com;

5/14/2013 10:46 john@contoso.com       jack@contso.com;

5/14/2013 10:46 john@contoso.com       bob@contoso.com 

• Edited by x01e Tuesday, May 14, 2013 11:24 PM

Hi,

I don't know how to split the email addresses as there are fall under single parameter. 

I would suggest you to export to csv as you are doing and select the recipients column and split them based on semicolon(;). 

i.e:

http://just-excel.blogspot.com/2009/06/split-text-into-different-columns.html

Hello, yes I can do some manipulation within excel but that's not what I'm looking to do.  With a help a co-worker that is very good at scripting he was able to find a way to get me output like this below so that each name now has it's own row adding converto-csv --delimiter statement .. but this is still not what I'm looking for but close. 

Timestamp         Sender Recipients           Recipients

5/14/2013 10:46 john@contoso.com       jim@consoto.com;jack    @contso.com;    bob@contoso.com

Hi,

Here you go.. 

I got the help from Vinith Menon who is part of Bangalore Powershell User Group. Thanks a lot Vinith :-)

Save the below script as ps1 and execute it, make sure you are matching the servername, date, subject

$new=get-messagetrackinglog -server "Server Name" -sender Test@Mydomain.com -Start "5/12/2013 8:59:13 PM" -messagesubject "mytest" | select-object timeStamp,Sender,*subject,@{Name="Recipients";Expression={[string]::join(";", ($_.recipients))}}
foreach ($n in $new)
{
$values = $n | ForEach-Object {$_.recipients.split(";")}

$count = $n | ForEach-Object {$_.recipients.split(";")} | Measure-Object | select -ExpandProperty count

for ($i=0;$i-lt $count;$i++)
{

$array1 += $n | select timestamp,sender,*subject,@{Name=Recipients;Expression={$values[$i]}}

}

}

$array1

Thank you very much Imkottees and Vinith.  My in house scripter like wise was able to get me a solution.   His solution basically, added a delimiter then using replaced this with a break link to creat a line new so Excel adds a new row.

Get-ExchangeServer | where {$_.IsHubTransportServer -eq "true"} | sort-object Name | get-messagetrackinglog -ResultSize unlimited -sender john@contoso.com -EventID "receive" -Start "05/14/2013 10:00:00 AM" -End "05/14/2013 12:00:00 PM" -messagesubject "Subject of email" | select-object @{Name="Recipients";Expression={[string]::join('zzzzzz', ($_.recipients))}},TimeStamp,eventid,ServerHostName, MessageSubject,Sender | Export-csv c:\rawoutput.csv -notypeinformation -Delimiter ",";$a='"';get-content c:\rawoutput.csv|foreach{$_ -replace "zzzzzz","$a`r`n$a"}|out-file C:\results.csv -fo -en ascii

• Marked as answer by x01e Monday, May 20, 2013 1:22 PM

Hi Kottees,

in your script , if the output of the original command get-messagetracking looks like the below

Timestamp       : 4/23/2015 9:06:22 AM
EventId         : DUPLICATEDELIVER
Sender          : xx@yy.qa
MessageSubject  : xx '22-04-2015
Recipients      : h.xx@domain.com

in the output the recipients field is just one smtp address , at the end of the script , the recipients became "h" only and the rest of the email get removed ! , i guess cuz the recipients has no ";" at the end , how to solve this?