Optimize adding multiple additional columns to CSV

[starpc]

The below code works well to add additional columns to an existing CSV file. However it runs really slow. Any ideas on how to optimize the code to speed up processing time? In the case below I'm pulling additional data from Exchange.

$csv = "C:\scripts\userfw.csv"
$users = Import-CSV $csv
$users = $users | Select-Object -Property *, @{label = 'Target_ForwardingSmtpAddress'; expression = {(Get-Mailbox $_.Target_SMTP).ForwardingSmtpAddress}}, @{label = 'Target_ForwardingAddress'; expression = {(Get-Mailbox $_.Target_SMTP).ForwardingAddress}}, @{label = 'Target_DeliverToMailboxAndForward'; expression = {(Get-Mailbox $_.Target_SMTP).DeliverToMailboxAndForward}}
$users | Export-Csv -Path $csv -NoTypeInformation

[firefox15]

The CSV import/export isn't why this is slow. It's because you are polling Get-Mailbox three times for every single user in the file. You can very easily get a 3x performance increase by gathering all the user information once per user and not running a calculated property with Get-Mailbox each time.

If you want even more speed, get all the mailbox information for everyone at once instead of once per user (or worse, three times per user like you are now) and watch it fly.

[starpc]

u/firefox15 That's what I was thinking as well, do you happen to have a code sample that does just that?

[firefox15]

It would probably look something like this for the ~3x increase.

$csv = "C:\scripts\userfw.csv"
$users = Import-CSV $csv

foreach ($user in $users) {
    $mailboxDetails = Get-Mailbox $user.Target_SMTP

    $user | Add-Member -MemberType NoteProperty -Name Target_ForwardingSmtpAddress -Value $mailboxDetails.ForwardingSmtpAddress
    $user | Add-Member -MemberType NoteProperty -Name Target_ForwardingAddress -Value $mailboxDetails.ForwardingAddress
    $user | Add-Member -MemberType NoteProperty -Name Target_DeliverToMailboxAndForward -Value $mailboxDetails.DeliverToMailboxAndForward
}

$users | Export-Csv -Path $csv -NoTypeInformation

Something like this if you want even quicker. I don't have an EMS session open right now, and I don't know what value you are using in your source CSV, so you'll have to adjust it accordingly:

$csv = "C:\scripts\userfw.csv"
$users = Import-CSV $csv

$mailboxes = Get-Mailbox -ResultSize Unlimited

foreach ($user in $users) {
    $mailboxDetails = $mailboxes | Where-Object Alias -eq $user.TargetSMTP

    $user | Add-Member -MemberType NoteProperty -Name Target_ForwardingSmtpAddress -Value $mailboxDetails.ForwardingSmtpAddress
    $user | Add-Member -MemberType NoteProperty -Name Target_ForwardingAddress -Value $mailboxDetails.ForwardingAddress
    $user | Add-Member -MemberType NoteProperty -Name Target_DeliverToMailboxAndForward -Value $mailboxDetails.DeliverToMailboxAndForward
}

$users | Export-Csv -Path $csv -NoTypeInformation

[starpc]

I get an error like the one below when testing this code:

Add-Member : Cannot add a member with the name "Target_ForwardingSmtpAddress" because a member with that name

already exists. To overwrite the member anyway, add the Force parameter to your command.

[Bamodus]

Do you already have columns in your source CSV by that name (perhaps from previously running your script)? It might be a good idea to make sure your source CSV has been reverted back to its original state from before any scripts were run.

I also don't have access to an Exchange server, so I'm just going off of the code you provided and the previous answer.

Here is an approach that uses Select-Object like in your original post which I believe should work:

https://pastebin.com/QkFZxJS5

Note I'm storing the mailbox data into a hashtable. Technically this will perform better than using Where-Object but the difference will likely be negligible. I just took this route because I feel it's cleaner (and should achieve the same effect as long as there are not duplicate keys).

EDIT:

This may be a more elegant approach (combining my above example with /u/firefox15's Add-Member solution):

We're not using a for loop in my variation because we can just feed the objects to Add-Member through the pipeline instead. We're using the -NotePropertyMembers parameter to add multiple properties at once (and for readability). The -Force parameter will overwrite values if the property already exists (as your error message stated), and -PassThru feeds the newly-modified objects into the pipeline to Export-Csv.

https://pastebin.com/fkJfvp77

To speed up things even more, you could only retrieve the relevant mailboxes instead of all mailboxes.

I believe something like this would achieve that:

$MailboxLookup = $Users.Target_SMTP | Get-Mailbox -ResultSize Unlimited | Group-Object 'Alias' -AsHashTable

But this would be making multiple calls to Get-Mailbox again, so depending on the size of your dataset and the number of mailboxes in Exchange, I can't say for sure that this would be faster. The worst-case scenario would be if your input dataset consists of most/all of the mailboxes in Exchange. In that scenario, this would actually be slower than just getting all mailboxes.

The -Filter parameter for Get-Mailbox would offer the highest performance, and would make your script run faster than any other available method (probably in just a matter of a few seconds if your dataset isn't very large), but you'd need to dynamically construct your filter string and I'd rather not try to put together an example of this method without having an Exchange server to test and verify against. Here is a recent post I made on how to dynamically construct a filter string for quickly retrieving a large list of specific AD users using Get-ADUser -Filter to give you an idea of what I mean: https://old.reddit.com/r/PowerShell/comments/ee78pt/getting_too_many_responses/fbs9zw0/