Two Variables to PSObject Export-CSV issue

Welcome Forums General PowerShell Q&A Two Variables to PSObject Export-CSV issue

This topic contains 3 replies, has 2 voices, and was last updated by

 
Participant
1 month ago.

  • Author
    Posts
  • #112219

    Participant
    Points: 0
    Rank: Member

    Hi Peepls,

    I've been trying to fix this for a number of days, I can get the output to display from the console, just not exported. If the PSObject returns one object the export-CSV works.

    Essentially I am creating two variables containing different mailbox settings. From here I am then creating a new PSObject with the required information. I am just unable to export it to CSV.

    $SharedMBX = Get-Mailbox -ResultSize unlimited -RecipientTypeDetails SharedMailbox | where {($_.Name -notlike "Room -*") -and ($_.Name -notlike "*Calendar - *") -and ($_.Name -notlike "Address Book - *") -and ($_.Name -notlike "365*") -and ($_.LitigationHoldEnabled -eq $FALSE)}
    $CAS = $SharedMBX | Get-CASMailbox -resultsize unlimited | Select Name, OWAEnabled, OWAMailboxPolicy, POPEnabled, ImapEnabled, ActiveSyncEnabled
    
    
    $report= New-Object PsObject 
    
    #Get-Mailbox Settings
    
    $report | Add-Member -MemberType NoteProperty -Name DisplayName -Value $SharedMBX.Name
    $report | Add-Member -MemberType NoteProperty -Name PrimarySMTPAddress -Value $SharedMBX.PrimarySMTPAddress
    $report | Add-Member -MemberType NoteProperty -Name LitigationHoldEnabled -Value $SharedMBX.LitigationHoldEnabled
    $report | Add-Member -MemberType NoteProperty -Name AddressBookPolicy -Value $SharedMBX.AddressBookPolicy
    $report | Add-Member -MemberType NoteProperty -Name RetentionPolicy -Value $SharedMBX.RetentionPolicy
    $report | Add-Member -MemberType NoteProperty -Name IssueWarningQuota -Value $SharedMBX.IssueWarningQuota
    $report | Add-Member -MemberType NoteProperty -Name ProhibitSendQuota -Value $SharedMBX.ProhibitSendQuota
    $report | Add-Member -MemberType NoteProperty -Name ProhibitSendReceiveQuota -Value $SharedMBX.ProhibitSendReceiveQuota
    $report | Add-Member -MemberType NoteProperty -Name RetainDeletedItemsFor -Value $SharedMBX.RetainDeletedItemsFor
    $report | Add-Member -MemberType NoteProperty -Name MessageCopyForSentAsEnabled -Value $SharedMBX.MessageCopyForSentAsEnabled
    $report | Add-Member -MemberType NoteProperty -Name MessageCopyForSendonBehalfOfEnabled -Value $SharedMBX.MessageCopyForSendOnBehalfEnabled
    
    #CAS Mailbox Settings
    
    $report | Add-Member -MemberType NoteProperty -Name OWAEnabled -Value $CAS.OWAEnabled
    $report | Add-Member -MemberType NoteProperty -Name OWAMailboxPolicy -Value $CAS.OWAMailboxPolicy
    $report | Add-Member -MemberType NoteProperty -Name PopEnabled -Value $CAS.PopEnabled
    $report | Add-Member -MemberType NoteProperty -Name ImapEnabled -Value $CAS.ImapEnabled
    $report | Add-Member -MemberType NoteProperty -Name ActiveSyncEnabled -Value $CAS.ActiveSyncEnabled
    
    $report
    

    This returns the following in the console:

    DisplayName                         : {IT Training, ITD Project Office}
    PrimarySMTPAddress                  : {ITTraining@domain.com, ITDProjectOffice@domain.com}
    LitigationHoldEnabled               : {False, False}
    AddressBookPolicy                   : {ABP, ABP}
    RetentionPolicy                     : {Delete Deleted Items After 30 Days, Delete Deleted Items After 30 Days}
    IssueWarningQuota                   : {9.9 GB (10,630,044,672 bytes), 9.9 GB (10,630,044,672 bytes)}
    ProhibitSendQuota                   : {9.95 GB (10,683,731,968 bytes), 9.95 GB (10,683,731,968 bytes)}
    ProhibitSendReceiveQuota            : {10 GB (10,737,418,240 bytes), 10 GB (10,737,418,240 bytes)}
    RetainDeletedItemsFor               : {30.00:00:00, 30.00:00:00}
    MessageCopyForSentAsEnabled         : {True, True}
    MessageCopyForSendonBehalfOfEnabled : {True, True}
    OWAEnabled                          : {True, True}
    OWAMailboxPolicy                    : {OwaMailboxPolicy-BlockAttachments, OwaMailboxPolicy-BlockAttachments}
    PopEnabled                          : {True, False}
    ImapEnabled                         : {False, True}
    ActiveSyncEnabled                   : {True, True}
    
    

    Is it even possible to export the data this way? or am I going about this the wrong way?

    Any advice welcome.

  • #112226

    Participant
    Points: 19
    Rank: Member

    So... what you have is basically something that should be two (or more!) separate PSObjects. There is also a much cleaner and faster way to build those objects...

    So your main variable ($SharedMBX) has multiple values to begin with, and $CAS derives from it. What you perhaps should do is simply loop over the objects stored in $SharedMBX one at a time, pulling each individual CAS, and building a separate object for each. This will do something a little more like what you would expect, and will export to things like CSV cleanly — CSV exports expect a single object per row of the CSV, all with the same set of properties.

     

    $SharedMailBoxes = Get-Mailbox -ResultSize 'Unlimited' -RecipientTypeDetails 'SharedMailbox' | 
        Where-Object {
            $_.Name -notmatch '^(Room -|Address Book -|365)|Calendar -' -and
            $_.LitigationHoldEnabled -eq $FALSE
        }
    
    $Report = foreach ($Mailbox in $SharedMailBoxes) {
        $CAS = $Mailbox | Get-CASMailbox -ResultSize 'Unlimited' | 
            Select-Object -Property 'Name', 'OWAEnabled', 'OWAMailboxPolicy', 'POPEnabled', 'ImapEnabled', 'ActiveSyncEnabled'
        
        [PSCustomObject]@{
            DisplayName                         = $Mailbox.Name
            PrimarySMTPAddress                  = $Mailbox.PrimarySMTPAddress
            LitigationHoldEnabled               = $Mailbox.LitigationHoldEnabled
            AddressBookPolicy                   = $Mailbox.AddressBookPolicy
            RetentionPolicy                     = $Mailbox.RetentionPolicy
            IssueWarningQuota                   = $Mailbox.IssueWarningQuota
            ProhibitSendQuota                   = $Mailbox.ProhibitSendQuota
            ProhibitSendReceiveQuota            = $Mailbox.ProhibitSendReceiveQuota
            RetainDeletedItemsFor               = $Mailbox.RetainDeletedItemsFor
            MessageCopyForSentAsEnabled         = $Mailbox.MessageCopyForSentAsEnabled
            MessageCopyForSendonBehalfOfEnabled = $Mailbox.MessageCopyForSendOnBehalfEnabled
    
            OWAEnabled                          = $CAS.OWAEnabled
            OWAMailboxPolicy                    = $CAS.OWAMailboxPolicy
            PopEnabled                          = $CAS.PopEnabled
            ImapEnabled                         = $CAS.ImapEnabled
            ActiveSyncEnabled                   = $CAS.ActiveSyncEnabled
        }
    }
    
    $Report | Export-Csv -Path .\Results.csv -NoTypeInformation

    Note that I've replaced your nest of -notlike clauses with a single regex -notmatch clause that checks against the same strings; regex isn't familiar to everyone, but it does absolutely make for some tidier code in the right places!

    And once you get that, you can pipeline the whole thing for a slight improvement to operating efficiency (no overhead from storing things in arrays, etc.)

    Get-Mailbox -ResultSize 'Unlimited' -RecipientTypeDetails 'SharedMailbox' | 
        Where-Object {
            $_.Name -notmatch '^(Room -|Address Book -|365)|Calendar -' -and
            $_.LitigationHoldEnabled -eq $FALSE
        } | ForEach-Object {
            $CAS = $_ | Get-CASMailbox -ResultSize 'Unlimited' | 
                Select-Object -Property 'Name', 'OWAEnabled', 'OWAMailboxPolicy', 'POPEnabled', 'ImapEnabled', 'ActiveSyncEnabled'
            
            [PSCustomObject]@{
                DisplayName                         = $_.Name
                PrimarySMTPAddress                  = $_.PrimarySMTPAddress
                LitigationHoldEnabled               = $_.LitigationHoldEnabled
                AddressBookPolicy                   = $_.AddressBookPolicy
                RetentionPolicy                     = $_.RetentionPolicy
                IssueWarningQuota                   = $_.IssueWarningQuota
                ProhibitSendQuota                   = $_.ProhibitSendQuota
                ProhibitSendReceiveQuota            = $_.ProhibitSendReceiveQuota
                RetainDeletedItemsFor               = $_.RetainDeletedItemsFor
                MessageCopyForSentAsEnabled         = $_.MessageCopyForSentAsEnabled
                MessageCopyForSendonBehalfOfEnabled = $_.MessageCopyForSendOnBehalfEnabled
    
                OWAEnabled                          = $CAS.OWAEnabled
                OWAMailboxPolicy                    = $CAS.OWAMailboxPolicy
                PopEnabled                          = $CAS.PopEnabled
                ImapEnabled                         = $CAS.ImapEnabled
                ActiveSyncEnabled                   = $CAS.ActiveSyncEnabled
            }
        } | Export-Csv -Path '.\Results.csv' -NoTypeInformation
  • #112229

    Participant
    Points: 0
    Rank: Member

    Wow,
    I can't thank you enough for fixing this. I've just tried the middle example and it works, plus it is quick! It's elegant and it's efficient. ūüôā

     

    I attempted the second example, please note I am running it against Exchange Online, it errored with the text below,  to confirm it wasn't the session, I tested the middle example and it worked.

    
    The pipeline was not run because a pipeline is already running. Pipelines cannot be run concurrently.
    
    + CategoryInfo          : OperationStopped: (Microsoft.Power...tHelperRunspace:ExecutionCmdletHelperRunspace) [], PSInvalidOperationException
    
    + FullyQualifiedErrorId : RemotePipelineExecutionFailed
    
     
    
    Get-CASMailbox : The session Session17, 6528403e-0c8d-40d9-9dd7-9ef6ba81692d, outlook.office365.com is not available to run commands.  The session availability is Busy.
    
    At line:6 char:9
    
    +         $CAS = $_ | Get-CASMailbox -ResultSize 'Unlimited' |
    
    +         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    
    + CategoryInfo          : InvalidOperation: ([PSSession]Session17:PSSession) [Invoke-Command], InvalidRunspaceStateException
    
    + FullyQualifiedErrorId : InvokeCommandCommandInvalidSessionAvailability,Microsoft.PowerShell.Commands.InvokeCommandCommand
    
     
    
    Get-CASMailbox : No valid sessions were specified.  Ensure you provide valid sessions that are in the Opened state and are available to run commands.
    
    At line:6 char:9
    
    +         $CAS = $_ | Get-CASMailbox -ResultSize 'Unlimited' |
    
    +         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Command], PSInvalidOperationException
    
    + FullyQualifiedErrorId : InvalidOperation,Microsoft.PowerShell.Commands.InvokeCommandCommand
    
     
    
    
  • #112232

    Participant
    Points: 19
    Rank: Member

    Hmm, good to know! Yeah, sometimes pipelines look OK and work fine nested, but sometimes... it just refuses. I've not messed with it a great deal just yet, but that might be in the cards ūüėÄ

You must be logged in to reply to this topic.