locked
How to Export-CSV RRS feed

  • Question

  • This is absolutely doing my head in.
    My script is to capture mailbox rules forwards so I need to catch contacts and external email addresses - all fine, managed to cobble a script together and I can see the results in out-file but I cant seem to export-csv.
    I know this is something to do with my PSCustomObject but I cant seem to figure it out.

    $domains = Get-AcceptedDomain
    $orgmailbox=@()
    $orgmailbox=Get-Content C:\Temp\MBX.txt
    $rules=@()
    $Props=@()
    foreach ($mailbox in $orgmailbox)
    {
    $rules = Get-InboxRule -mailbox $mailbox -ErrorAction:SilentlyContinue | where{($_.forwardto -ne $null) -or ($_.redirectto -ne $null) -or ($_.ForwardAsAttachmentTo -ne $null) -or ($_.ForwardTo -match "EX:/") -or ($_.RedirectTo -match "EX:/") -or ($_.ForwardAsAttachmentTo -match "EX:/") -and $domains.DomainName -notcontains $domain}
    if ($rules -ne $null) {
    $Props += [ordered]@{
    User = $($rules.MailboxOwnerId)
    RuleName = $($rules.Name)
    ForwardTo = $($rules.Forwardto)
    RedirectTo = $($rules.RedirectTo)
    ForwardAsAttachmentTo = $($rules.ForwardAsAttachmentTo)
    }
    }
    }
    $Props | Out-File c:\temp\out.txt
    Invoke-Expression c:\temp\out.txt



    Thanks, M

    Maelito

    Thursday, June 6, 2019 5:53 PM

Answers

  • For a more formal programming style the  following is cleaner and easier to write, debug and understand.

    $select = @(
        'MailboxOwnerId', 
        'Name', 
        'Forwardto', 
        'RedirectTo',
        @{n='ForwardAsAttachmentTo';e={$_.ForwardAsAttachmentTo -join ';' }}
    )
    
    Get-Content C:\Temp\MBX.txt |
        ForEach-Object {
            Get-InboxRule -mailbox $_ -ErrorAction SilentlyContinue |
        } |
        Where-Object{
            $_.ForwardTo -match "EX://" -or
            $_.RedirectTo -match "EX://" -or
            $_.ForwardAsAttachmentTo -match "EX://" -and
            $_.DomainName -notin $domain
        } |
        Select-Object $select |
        Export-CSV c:\temp\out.csv -NoTypeInformation



    \_(ツ)_/



    Saturday, June 8, 2019 12:39 AM

All replies

  • Change $props | out-file to $props | export-csv
    Thursday, June 6, 2019 5:57 PM
  • Nops... that is the problem as I get garbage on CSV. There is a way to covert to csv readable format... just can figure it out.

    Maelito

    Thursday, June 6, 2019 6:21 PM
  • Does this work?

    $domains = Get-AcceptedDomain
    
    $Props=[ordered]@{
        User=''
        RuleName=''
        ForwardTo=''
        RedirectTo=''
        ForwardAsAttachmentTo=''
    }
    
    Get-Content C:\Temp\MBX.txt |
        foreach {
            $mailbox = $_
            $rules = Get-InboxRule -mailbox $mailbox -ErrorAction:SilentlyContinue | 
                where{  ($_.forwardto -ne $null) -or
                        ($_.redirectto -ne $null) -or 
                        ($_.ForwardAsAttachmentTo -ne $null) -or 
                        ($_.ForwardTo -match "EX:/") -or 
                        ($_.RedirectTo -match "EX:/") -or 
                        ($_.ForwardAsAttachmentTo -match "EX:/") -and 
                        $domains.DomainName -notcontains $domain}
    
            if ($rules -ne $null) {
                $Props.User = $rules.MailboxOwnerId
                $Props.RuleName = $rules.Name
                $Props.ForwardTo = $rules.Forwardto
                $Props.RedirectTo = $rules.RedirectTo
                $Props.ForwardAsAttachmentTo = $rules.ForwardAsAttachmentTo
                [PSCustomObject]$Props
            }
        } | Export-CSV c:\temp\out.csv -NoTypeInformation



    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)


    Thursday, June 6, 2019 6:37 PM
  • Almost...

    When checking I get this on the report:

    User	RuleName	ForwardTo	RedirectTo	ForwardAsAttachmentTo
    System.Object[]	System.Object[]	System.Object[]	System.Object[]	System.Object[]
    MickeyMouse	sent only to me	"MickeyTeam" [EX:/o=ExchangeLabs/ou=Exchange Administrative Group (xxxxxxxxxxx)/cn=Recipients/cn=xxxxxxxxxxxxxxxxxxxxxxxx-MickeyTeam]		
    

    I think the "System.Object" is coming up because the user as loads of mbx rules....

    Not sure how to workaround this one.

    M



    Maelito

    Friday, June 7, 2019 2:33 AM
  • Rules is a collection and must be enumerated.

    Get-Content C:\Temp\MBX.txt | ForEach-Object { Get-InboxRule -mailbox $_ -ErrorAction SilentlyContinue | Where-Object{ $_.ForwardTo -match "EX://" -or $_.RedirectTo -match "EX://" -or $_.ForwardAsAttachmentTo -match "EX://" -and $domains.DomainName -ne $domain }

    } |
    Select MailboxOwnerId,Name,Forwardto,RedirectTo | Export-CSV c:\temp\out.csv -NoTypeInformation




    \_(ツ)_/






    • Edited by jrv Friday, June 7, 2019 2:46 AM
    • Proposed as answer by jrv Friday, June 7, 2019 9:21 PM
    Friday, June 7, 2019 2:41 AM
  • How about this? It checks each rule individually and 'stringifies' the properties that are multi-valued:

     $domains = Get-AcceptedDomain
    
    $Props=[ordered]@{
        User=''
        RuleName=''
        ForwardTo=''
        RedirectTo=''
        ForwardAsAttachmentTo=''
    }
    
    Get-Content C:\Temp\MBX.txt |
        foreach {
            Get-InboxRule -mailbox $_ -ErrorAction:SilentlyContinue | 
                foreach{
                    if ( ($_.forwardto -ne $null) -or
                         ($_.redirectto -ne $null) -or 
                         ($_.ForwardAsAttachmentTo -ne $null) -or 
                         ($_.ForwardTo -match "EX:/") -or 
                         ($_.RedirectTo -match "EX:/") -or 
                         ($_.ForwardAsAttachmentTo -match "EX:/") -and 
                         $domains.DomainName -notcontains $domain
                       ){
                            $Props.User = $_.MailboxOwnerId
                            $Props.RuleName = $_.Name
                            $Props.ForwardTo = [String]::join(";",$_.Forwardto)
                            $Props.RedirectTo = [String]::join(";",$_.RedirectTo)
                            $Props.ForwardAsAttachmentTo = [String]::join(";",$_.ForwardAsAttachmentTo)
                            [PSCustomObject]$Props
                        }
            }
        } | Export-CSV c:\temp\out.csv -NoTypeInformation


    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)

    Friday, June 7, 2019 6:54 PM
  • "Where" has to many unnecessary tests and the PSObject is unnecessary.


    \_(ツ)_/

    Friday, June 7, 2019 9:22 PM
  • I "kind of" agree, but I was preserving the OP's structure from his original post.

    But the "Where" is gone, replaced by "If", and the OP was only looking for rules that directed message to other recipients, so while the two conditions that check for "Ex:/" explicitly (forward and redirect) are unnecessary, the other's are still needed.

    I thought about just using a "Select" instead of the PSCustomObject, but I thought the explicit assignment was clearer, and after having to "stringifify" the multi-valued properties, the need for the "@{n='blah';e={stuff}}" looked more cluttered.


    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)

    Friday, June 7, 2019 11:50 PM
  • The checks for null are unnecessary.  Checking for an explicit value also excludes nulls.

               Where-Object{
                    $_.ForwardTo -match "EX://" -or
                    $_.RedirectTo -match "EX://" -or
                    $_.ForwardAsAttachmentTo -match "EX://" -and
                    $domains.DomainName -ne $domain
     

    Pre building the object supplies little added convenience and also "clutters" the code.

    The use of "select" over an object is optional However it is more efficient and maintains the integrity of the pipeline.


    \_(ツ)_/

    Saturday, June 8, 2019 12:33 AM
  • For a more formal programming style the  following is cleaner and easier to write, debug and understand.

    $select = @(
        'MailboxOwnerId', 
        'Name', 
        'Forwardto', 
        'RedirectTo',
        @{n='ForwardAsAttachmentTo';e={$_.ForwardAsAttachmentTo -join ';' }}
    )
    
    Get-Content C:\Temp\MBX.txt |
        ForEach-Object {
            Get-InboxRule -mailbox $_ -ErrorAction SilentlyContinue |
        } |
        Where-Object{
            $_.ForwardTo -match "EX://" -or
            $_.RedirectTo -match "EX://" -or
            $_.ForwardAsAttachmentTo -match "EX://" -and
            $_.DomainName -notin $domain
        } |
        Select-Object $select |
        Export-CSV c:\temp\out.csv -NoTypeInformation



    \_(ツ)_/



    Saturday, June 8, 2019 12:39 AM
  • Sorry, got an error:

    Select-Object : A positional parameter cannot be found that accepts argument 'Name'.
    At line:19 char:5
    +     Select-Object @select |
    +     ~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : InvalidArgument: (:) [Select-Object], ParameterBindingException
        + FullyQualifiedErrorId : PositionalParameterNotFound,Microsoft.PowerShell.Commands.SelectObjectCommand


    Maelito

    Monday, June 10, 2019 9:50 AM
  • That may be the result of a mailbox having no inbox rules. Try my suggested code and see if it works. You can clean up the code later if you have to reuse it.

    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)

    Monday, June 10, 2019 2:37 PM
  • Great JRV, as usual! :)

    This works fine, only thing is that the records are not comma delimited - I have tried using Rich's code to try to get this done but I still  get it in this format:

    "blue@green.com" [EX:/o=ExchangeLabs/ou=Exchange Administrative Group (FYDIBOHF23SPDLT)/cn=Recipients/cn=9......-Blue] "PeterSampras" [EX:/o=ExchangeLabs/ou=Exchange Administrative Group (FYDIBOHF23SPDLT)/cn=Recipients/cn=9.......-PeterSampras] "Roger Federer" [EX:/o=ExchangeLabs/ou=Exchange Administrative Group (FYDIBOHF23SPDLT)/cn=Recipients/cn=9......-Roger Ferered}

    I need to be able to see the name in " " so I can compare it to a Get-Contacts out put.

    the issue here is that Exchange does not differentiate between a contact or a user when it comes to rules (on the get-inbox rules output) 

    or even better is there is a why to just output the " " text without the [EX... bits...

    Thanks, M


    Maelito


    • Edited by Maelito Monday, June 10, 2019 4:17 PM more text
    Monday, June 10, 2019 4:01 PM
  •  Sorry. It works for me. Just to check if you solved your problem。

    Best regards,

    Lee


    Just do it.

    Friday, July 5, 2019 2:17 AM