Microsoft.Exchange.Data.Storage.Management in Export-CSV column

This topic contains 6 replies, has 2 voices, and was last updated by  Neville D’Souza 2 days, 21 hours ago.

  • Author
    Posts
  • #87811

    Neville D’Souza
    Participant

    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.

  • #87836

    Jeremy Corbello
    Participant

    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
  • #88586

    Neville D’Souza
    Participant

    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
    
    • #88625

      Jeremy Corbello
      Participant

      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
  • #88628

    Neville D’Souza
    Participant

    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.

    • #88633

      Jeremy Corbello
      Participant

      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
  • #88636

    Neville D’Souza
    Participant

    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
    

You must be logged in to reply to this topic.