Author Posts

December 8, 2017 at 12:06 pm

Hi All,

I wrote a small script to get me a report that have forwarding rules in their outlook. The output is fine on screen but when I try to output it to excel I get these 1",,"Microsoft.Exchange.Data.Storage.Management.ADRecipientOrAddress[]" in RedirectTo and ForwardTo columns. The code is as follows

 
$Report = @()

$Report += foreach ($i in (Get-Mailbox -ResultSize unlimited)) { Get-InboxRule -Mailbox $i.DistinguishedName | 
				Where {($_.RedirectTo) -or ($_.ForwardTo)} | 
				Select Name,Description,MailboxOwnerID,RedirectTo,ForwardTo }
				
$Report | Export-CSV C:\Scripts\InboxRuleReport.csv -Append

Can anyone help me get the output in excel properly please? Any suggestion to get the mailbox from email address in one column is welcome.

December 8, 2017 at 2:15 pm

The RedirectTo and ForwardTo objects are arrays, so when you try to convert it to a string it'll just give you the type – not very useful when you're trying to get the data within the array.

This is when you'd use the '-join' operator. It takes the elements of the array and 'joins' them into a single string, separating them with the delimiter of your choice. In the below code, I've use commas to delimit the values, but you can use ';', '/', etc.

$Report = @()

$Report += foreach ($i in (Get-Mailbox -ResultSize 200)) { Get-InboxRule -Mailbox $i.DistinguishedName | 
				Where {($_.RedirectTo) -or ($_.ForwardTo)} | 
				Select Name,Description,MailboxOwnerID,@{Name="RedirectTo";Expression={$_.RedirectTo -join ','}},@{Name="ForwardTo";Expression={$_.ForwardTo -join ','}} }

				
$Report | Export-CSV C:\Scripts\InboxRuleReport.csv -Append

December 11, 2017 at 6:37 am

Thanks Jeremy. It works well. The only thing that remains is to get email address instead of MailboxOwnerID. Any ideas anyone?
I have added ForwardAsAttachmentTo to cover all kinds of forwards.

$Report = @()

$Report += foreach ($i in (Get-Mailbox -ResultSize 200)) { Get-InboxRule -Mailbox $i.DistinguishedName | 
				Where {($_.RedirectTo) -or ($_.ForwardTo)} | 
				Select Name,Description,MailboxOwnerID,@{Name="RedirectTo";Expression={$_.RedirectTo -join ','}},@{Name="ForwardTo";Expression={$_.ForwardTo -join ','}},@{Name="ForwardAsAttachmentTo";Expression={$_.ForwardAsAttachmentTo -join ','}} }
				
$Report | Export-CSV C:\Scripts\InboxRuleReport.csv -Append

December 11, 2017 at 11:25 am

Just swap out "MailboxOwnerID" for "PrimarySmtpAddress".

$Report = @()

$Report += foreach ($i in (Get-Mailbox -ResultSize 200)) { Get-InboxRule -Mailbox $i.DistinguishedName | 
				Where {($_.RedirectTo) -or ($_.ForwardTo)} | 
				Select Name,Description,PrimarySmtpAddress,@{Name="RedirectTo";Expression={$_.RedirectTo -join ','}},@{Name="ForwardTo";Expression={$_.ForwardTo -join ','}},@{Name="ForwardAsAttachmentTo";Expression={$_.ForwardAsAttachmentTo -join ','}} }
				
$Report | Export-CSV C:\Scripts\InboxRuleReport.csv -Append

December 11, 2017 at 11:52 am

Thanks Jeremy for your response but PrimarySmtpAddress comes blank on screen and csv. I had tried with UserPrincipleName and SamAccountName if I could at least get the username but that too comes blank.

December 11, 2017 at 12:20 pm

Oops, that's what I get for not having coffee first.

The "PrimarySmtpAddress" field is pulled from your Get-Mailbox request. So when we call that data below, we have to specify where we're pulling "PrimarySmtpAddress" from – otherwise it pulls it from the current object, which in our case is Get-InboxRule.

Below I'm changing Select PrimarySmtpAddress, which attempts to pull that data from Get-InboxRule, to Select @{Name="PrimarySmtpAddress";Expression={$i.PrimarySmtpAddress.Address}

$Report = @()

$Report += foreach ($i in (Get-Mailbox -ResultSize 200)) { Get-InboxRule -Mailbox $i.DistinguishedName | 
				Where {($_.RedirectTo) -or ($_.ForwardTo)} | 
				Select-Object Name,Description,@{Name="PrimarySmtpAddress";Expression={$i.PrimarySmtpAddress.Address},@{Name="RedirectTo";Expression={$_.RedirectTo -join ','}},@{Name="ForwardTo";Expression={$_.ForwardTo -join ','}},@{Name="ForwardAsAttachmentTo";Expression={$_.ForwardAsAttachmentTo -join ','}} }
				
$Report | Export-CSV C:\Scripts\InboxRuleReport.csv -Append

December 11, 2017 at 12:51 pm

Thanks Jeremy for the help and explanation. This is perfect now. I did a few modifications to change column names of the CSV, changed -Resultsize to Unlimited and added ($_.ForwardAsAttachmentTo) to Where logic.

$Report = @()

$Report += foreach ($i in (Get-Mailbox -ResultSize unlimited)) { Get-InboxRule -Mailbox $i.DistinguishedName | 
				Where {($_.RedirectTo) -or ($_.ForwardTo) -or ($_.ForwardAsAttachmentTo) } | 
				Select @{Name="Rule Name";Expression={$_.Name}},`
				       @{Name="Rule Description";Expression={$_.Description}},`
				       @{Name="PrimarySmtpAddress";Expression={$i.PrimarySmtpAddress.Address}},`
				       @{Name="RedirectTo";Expression={$_.RedirectTo -join ','}},`
				       @{Name="ForwardTo";Expression={$_.ForwardTo -join ','}},`
				       @{Name="ForwardAsAttachmentTo";Expression={$_.ForwardAsAttachmentTo -join ','}} }
				
$Report | Export-CSV C:\Scripts\InboxRuleReport.csv -Append