How to merge or append exported info of two different Exchange Powershell scripts?-CodePudding

I'm having a problem that I haven't been abble to resolve, I need to merge two scripts or append the second script exported info into the same delimited CSV file (created by the first script).

This is the first script:

Get-Mailbox -ResultSize Unlimited | Select-Object AddressBookPolicy, ProhibitSendQuota, SamAccountName, UserPrincipalName, WhenMailboxCreated, Alias, OrganizationalUnit, CustomAttribute1, DisplayName, PrimarySmtpAddress, RecipientType, RecipientTypeDetails, WindowsEmailAddress, WhenChanged, WhenCreated | export-csv -NoTypeInformation .\Mailboxes_filtered.csv -Delimiter ";" -Encoding unicode

And this the second one:

Get-Mailbox -ResultSize Unlimited | Get-MailboxStatistics | Select DisplayName, StorageLimitStatus, TotalItemSize | export-csv -NoTypeInformation .\Mailboxes_filtered.csv -Delimiter ";" -Encoding unicode

PS: I'm using Exchange 2010.

I managed to get some success using "AddContent -Path .\Mailboxes_filtered.csv", but the added info appeared under the delimited cells on the CSV file instead of showing up beside and organized in the same way, I guess it happened because in this case the -Delimited ";" parameter is not accepted...

Those two scripts work, I just need to merge or append the exported info into the same CSV file.

CodePudding user response：

You don't need to combine the two csv, you need to add the additional properties to each record. Since the record already has a DisplayName property, you would either need to overwrite it or change the property name to something else.

You can use calculated properties to tack on the additional properties. The flow will be take each mailbox one by one and look up the stats for that mailbox. Grab the properties you want out of the stats and add with calculated properties.

$mailboxlist = Get-Mailbox -ResultSize Unlimited | Select-Object AddressBookPolicy, ProhibitSendQuota, SamAccountName, UserPrincipalName, WhenMailboxCreated, Alias, OrganizationalUnit, CustomAttribute1, DisplayName, PrimarySmtpAddress, RecipientType, RecipientTypeDetails, WindowsEmailAddress, WhenChanged, WhenCreated 

$results = foreach($mailbox in $mailboxlist){
    $stats = Get-MailboxStatistics -Identity $mailbox.PrimarySmtpAddress | Select DisplayName, StorageLimitStatus, TotalItemSize

    $mailbox | Select-Object *, @{n='StorageLimitStatus';e={$stats.StorageLimitStatus}}, @{n='TotalItemSize';e={$stats.TotalItemSize}}
}

$results | Export-Csv -NoTypeInformation .\Mailboxes_filtered.csv -Delimiter ";" -Encoding unicode

CodePudding user response：

Looks like Doug already gave a good answer so I'll just leave mine as an alternative since I was already writing it anyway.

You could use the -PipelineVariable parameter, and create an object with all the info that you need so that it can be exported as a single line to your CSV.

Get-Mailbox -ResultSize Unlimited -PipelineVariable 'Mailbox' |
    Get-MailboxStatistics | 
    Select-Object DisplayName, StorageLimitStatus, TotalItemSize, @{l='AddressBookPolicy';e={$Mailbox.AddressBookPolicy}}, @{l='ProhibitSendQuota';e={$Mailbox.ProhibitSendQuota}}, @{l='SamAccountName';e={$Mailbox.SamAccountName}}, @{l='UserPrincipalName';e={$Mailbox.UserPrincipalName}}, @{l='WhenMailboxCreated';e={$Mailbox.WhenMailboxCreated}}, @{l='Alias';e={$Mailbox.Alias}}, @{l='OrganizationalUnit';e={$Mailbox.OrganizationalUnit}}, @{l='CustomAttribute1';e={$Mailbox.CustomAttribute1}}, @{l='DisplayName';e={$Mailbox.DisplayName}}, @{l='PrimarySmtpAddress';e={$Mailbox.PrimarySmtpAddress}}, @{l='RecipientType';e={$Mailbox.RecipientType}}, @{l='RecipientTypeDetails';e={$Mailbox.RecipientTypeDetails}}, @{l='WindowsEmailAddress';e={$Mailbox.WindowsEmailAddress}}, @{l='WhenChanged';e={$Mailbox.WhenChanged}}, @{l='WhenCreated';e={$Mailbox.WhenCreated}} | 
    export-csv -NoTypeInformation .\Mailboxes_filtered.csv -Delimiter ";" -Encoding unicode

This all happens in the pipeline so I don't know how memory efficient it is. Also the fact that we have to build far more calculated properties than the ones we're getting from Get-MailboxStatistics means that the Select-Object line gets extremely long. On relatively small sample size I did get better performance on this vs a ForEach loop, but that is likely to degrade as the number of results increases if you run out of memory and it has to use a page file.