locked
How to Export-CSV RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    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

  • Question
    Sign in to vote
    1
    Sign in to vote

    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

  • Question
    Sign in to vote
    1
    Sign in to vote
    Change $props | out-file to $props | export-csv
    Thursday, June 6, 2019 5:57 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    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
  • Question
    Sign in to vote
    1
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    1
    Sign in to vote

    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
  • Question
    Sign in to vote
    1
    Sign in to vote

    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
  • Question
    Sign in to vote
    1
    Sign in to vote

    "Where" has to many unnecessary tests and the PSObject is unnecessary.

    \_(ツ)_/

    Friday, June 7, 2019 9:22 PM
  • Question
    Sign in to vote
    1
    Sign in to vote

    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
  • Question
    Sign in to vote
    1
    Sign in to vote

    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
  • Question
    Sign in to vote
    1
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote
    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

     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