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

Question

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.

Doug Maurer · Accepted Answer · 2022-11-08T14:30:55.310

0

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.UserPrincipalName | 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

edited Nov 08 '22 at 14:30

answered Nov 02 '22 at 20:27

Doug Maurer

8,090
3
12
13

unfortunately it didn't work...I got this error: Cannot process argument transformation on parameter 'Identity'. Cannot convert the "user@domain.local" value of type "Microsoft.Exchange.Data.SmtpAddress" to type "Microsoft.Exchange.Configuration.Tasks.GeneralMailboxOrMailUse rIdParameter". + CategoryInfo : InvalidData: (:) [Get-MailboxStatistics], ParameterBindin...mationException + FullyQualifiedErrorId : ParameterArgumentTransformationError,Get-MailboxStatistics – Mike_SBG Nov 07 '22 at 16:48
Why is the domain.local the primary smtp address? You may try changing that to `$mailbox.UserPrincipalName` – Doug Maurer Nov 07 '22 at 17:33
@doug-maurer....well....THATS IT!!! It's working just fine now!! Thank you so much Doug, I spent days and days around this issue, I lost the count of how many forums, articles and posts I read...and finally I can generate the reports I need! Thanks m8!! – Mike_SBG Nov 08 '22 at 13:07
@Mike_SBG Glad to help. Please consider accepting my answer as the solution. You can always change this if a better answer comes along. – Doug Maurer Nov 08 '22 at 14:31

score 0 · Answer 2 · answered Nov 02 '22 at 20:47

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.

I also tried your script...it didn't work either...I got this error: A positional parameter cannot be found that accepts argument 'Mailbox'. + CategoryInfo : InvalidArgument: (:) [Get-Mailbox], ParameterBindingException + FullyQualifiedErrorId : PositionalParameterNotFound,Get-Mailbox — Mike_SBG, Nov 07 '22 at 17:02
There were no positional parameters in my script. I don't have `Get-Mailbox`, so it may not support `-PipelineVariable`. — TheMadTechnician, Nov 07 '22 at 22:12
you're probably right, it's Exchange 2010 running on a Windows Server 2008 R2 VM, so it's technology that already it's a "little bit old", lol, but gladly Doug sorted it out and finally the script is working. But thanks so much for your help too, by analysing your script I learned a little bit more than I knew before, and that's always a plus for me! Thanks m8! — Mike_SBG, Nov 08 '22 at 13:31

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

2 Answers2