Two Variables to PSObject Export-CSV issue

PowerShell Help
1

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.

2

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
3

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. :slight_smile:

 

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.

[pre]

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: (:slight_smile: [Invoke-Command], PSInvalidOperationException

  • FullyQualifiedErrorId : InvalidOperation,Microsoft.PowerShell.Commands.InvokeCommandCommand

 

[/pre]

4

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 :smiley: