Comparing two multi-dimensional arrays in PowerShell

Question

I am trying to compare two multi-dimensional arrays in PowerShell. Each array has many thousands of elements – a small example follows. In one array I have:

$arrOne
Username                        LocalOffice
[email protected]             US-California
[email protected]            IT-Naples
[email protected]      TR-Istanbul
(etc...)

In the other array, I have:

$arrTwo
Username                        Location
[email protected]             US
[email protected]            US
[email protected]      TR
(etc...)

What I need to do is to compare the LocalOffice associated with each Username from $arrOne with the first two characters of Location using the matching Username in $arrTwo (if it exists). If LocalOffice and Location don’t match then take some actions. My code sample is as follows:

$arrOne | ForEach-Object {
    $strOneName = $_.Username
    If ($_.LocalOffice.Length -ge 2)
        {
        $strOneLocalOffice = $_.LocalOffice.substring(0,2)
        }
    Else
        {
        $strOneLocalOffice = "US"
        }
    $arrTwo | ForEach-Object {
        If ($_.Username -eq $strOneName -eq $True)
            {
            If ($_.Location -eq $strOneLocalOffice -ne $True)
                {
                ## Take action here if they don't match
                write-host $_.Username
                }
            }
    }
}

With the standard nested ForEach (above), it takes some time to process these arrays because each array is large (and this will be part of a script that runs every 30 minutes) and is time sensitive. To hopefully find my answer, I have a few questions about the above:

1)  Is there some other (quicker) method to get the desired results?

2)  Do I have to use ForEach and loop through arrTwo until I find the matching
Username from arrOne or is there some other quicker method to jump right to the
matching Username in arrTwo?

3)  Is there a way to quickly merge (join) these two arrays together so then I
can ForEach once through a single array and just compare individual objects
from the same element?

Thanks

UPDATE:

We use this script to help manage our local Active Directory and MSOL (Microsoft Online – Office 365) objects. We are using DirSync to keep sync our AD with Office 365. Although names are changed in the above examples for readability, these are the basic commands used to gather the array data:

[array]$arrOne = @(Get-ADObject -Filter {(objectClass -eq "User") -And (objectCategory -eq "Person")} -SearchBase “OU=Test,DC=domain,DC=com” -Properties UserPrincipalName,physicalDeliveryOfficeName) | Select-Object UserPrincipalName, physicalDeliveryOfficeName

[array]$arrTwo = @(Get-MsolUser -Synchronized -All) | Where-Object {$_.isLicensed -eq "True"} | Select-Object UserPrincipalName, UsageLocation

The arrays are different sizes (arrTwo is literally 10 times the size of arrOne). There is no guarantee that an object from arrOne will exist in arrTwo.

I’ve tried a few more things to solve this since my original posting (especially using BREAK to exit the second loop). After the original post, I realized I can get the best performance improvement if I can “break” out of the second ForEach-Object loop when a match is found. One thing that is slowing things down is that PowerShell keeps looping through arrTwo even after a match is found. I tried adding break after a match is found but I can’t get it to break out of the arrTwo loop and return to the next object in the arrOne collection. It keeps breaking (ending) the entire script.

    $arrTwo | ForEach-Object {
        If ($_.Username -eq $strOneName -eq $True)
            {
            If ($_.Location -eq $strOneLocalOffice -ne $True)
                {
                ## Take action here if they don't match
                write-host $_.Username
                }
            Break
            }
    }

I tried break, break/continue, break/label, using foreach instead of foreach-object, do/while and some others. No luck yet.

An additional question:

4)  Can break be used to exit a ForEach-Object loop and return it to the “parent”
ForEach-Object?

Thanks again

Answer 1

Thank you all for your help - they helped to lead me to finding a solution to solve my problem and getting Break/Continue to work properly. Now the performance is more comparable. I had to change the inner loop ($arrTwo) from ForEach-Object to ForEach. That changed the method by which the loop is initiated.

$arrOne | ForEach-Object {
    If ($_.LocalOffice.Length -ge 2)
        {
        $strOneOffice = $_.LocalOffice.substring(0,2)
        }
    Else
        {
        $strOneOffice = "US"
        }
    ForEach ($objTwo in $arrTwo) {
    If ($objTwo.Username -eq $_.Username)
        {
        If ($objTwo.UsageLocation -eq $strOneOffice -ne $True)
            {
            ## Take action here if they don't match
            write-host $_.Username "needs to be updated"
            Break
            }
        Else
            {
            ## Nothing to update here because they already match
            write-host $_.Username "does not need to be updated"
            Continue
            }
        }
    }
}

Answer 2

I suspect your data is coming from an Active Directory. A "real" programmer might have a better approach, but i think you could improve performance by first sorting your arrays, then checking if your value from arrOne is at all contained in arrTwo using -contains. Depending on the result you could check the actual values from arrTwo. Check this article, it also addresses the sorting performance issue. Also take a look at the compare-object commandlet to compare your arrays.

Answer 3

What you want to avoid is searching through the second array for each row of the first. The best way would depend on the nature of the 2 arrays (see the questions asked in comments). Assuming that the arrays are roughly the same size, what you would do is loop through both arrays at the same time (using an index) and build up some kind of table that stores the results. Then loop through that table to check your results (See below example). Now I've kept it to 2 loops for clarity, but you could also add a check in the first loop, whenever a hashtable's entry was complete and do the check then.

$ht = @{} # to store the results
#note this code could be simpler if the arrays are sorted  or the same length
for($i=0; $i -lt [Math]::Max($arrOne.Length,$arrTwo.Length); $i++){
    if($i -lt $arrOne.Length)
    {
        if($ht[$arrOne[$i].UserName])
        {
            #just modify the null value.
            $ht[$arrOne[$i].UserName].LocalOffice = $arrOne[$i].LocalOffice;
        }
        else
        {
            #create a new entry
            $ht[$arrOne[$i].UserName] = @{"LocalOffice"=$arrOne[$i].LocalOffice; "Location"=$null;}
        }
    }
    if($i -lt $arrTwo.Length)
    {
        if($ht[$arrTwo[$i].UserName])
        {
            $ht[$arrTwo[$i].UserName].Location = $arrTwo[$i].Location;
        }
        else
        {
            #create a new entry
            $ht[$arrTwo[$i].UserName] = @{"Location"=$arrTwo[$i].Location; "LocalOffice"=$null;}
        }
    }
 }

 # now loop through the resulting table
 $ht.Keys | foreach {
    if($ht[$_].LocalOffice -and $ht[$_].Location)
    {
        if($ht[$_].LocalOffice.Substring(0,2) -ne $ht[$_].Location)
        {
            "Problem for $_";
        }
    }
}

Answer 4

I want to know why @DavidPostill removed my answer. I do not see a way to message him directly, so sorry for this. I answered with code and some comments explaining it. It was a unique answer, i.e. not a duplicate and was likely more efficient than other methods posted. If it's because this post is 4 years old, update your help center to say not to comment on old posts and be helpful. I came across the post because of a search on google looking for something similar, ended up answering it myself. Answer is below again.

---

I know this is 4 years old, but I'm also sure it's likely still happening. I thought I would ask if it wouldn't be beneficial in this case to use Hash tables so you can directly query a specific result instead of iterating through everyone hoping something matches.

I admit, i'm having trouble following your code as your $arrOne and $arrTwo examples do not use the same properties as your final code suggests. I.e. Username vs UserPrincipalName, so bear with the code and update it accordingly.

The way I approach this is that I would gather the 2 arrays. One Array you know are the ones you may be updating (Office 365) and the other Array ($arrOne) you would use as your source/master data. So you'd build a hash table for each array, and use your SMALLER array, i.e. $ArrTwo as the one you're doing the ForEach on. Only those values are the ones we care about being matched or not.

When I create the 2 Hashtables, i'm using the UserPrincipalName as the key. I.e. [email protected] would be retrieved as $Hash['[email protected]'] and if you wanted the LocalOffice property, it would be $Hash['[email protected]'].LocalOffice So all we have to do is loop through all of the keys for the smaller of the 2 hashtables comparing values to the first hash table and updating accordingly.

[array]$arrOne = @(Get-ADObject -Filter {(objectClass -eq "User") -And (objectCategory -eq "Person")} -SearchBase “OU=Test,DC=domain,DC=com” -Properties UserPrincipalName,physicalDeliveryOfficeName) | Select-Object UserPrincipalName, physicalDeliveryOfficeName

[array]$arrTwo = @(Get-MsolUser -Synchronized -All) | Where-Object {$_.isLicensed -eq "True"} | Select-Object UserPrincipalName, UsageLocation

#Create Hash for AD
$hash1 = $null
$hash1 = @{}
foreach ($u in $arrOne)
{
$hash1.add($u.UserPrincipalName,$u)
}

#Create Hash for Office365
$Hash2 = $null
$Hash2 = @{}
foreach ($u2 in $arrTwo)
{
$Hash2.add($u2.UserPrincipalName,$u2)
}


#Itterate through Office365 Keys (UserPrincipalNames).
$Hash2.keys | ForEach {
#Quick check to see if the Hash1 value exists (Is there a UPN in Hash1 that matches Hash2)
If ($hash1[$_])
  {  
    If ($Hash1[$_].LocalOffice.Length -ge 2)
        {
        $strOneOffice = $Hash1[$_].LocalOffice.substring(0,2)
        }
    Else
        {
        $strOneOffice = "US"
        }
  }
  else
{
#Continue as the UPN does not exist in $Hash1, which should be rare.  This should skip to the next entry in Hash2 and start over.
  Continue
  }
if (!($hash2[$_].UsageLocation  -eq $strOneOffice ))
        {
        ## Take action here if they don't match
        write-host "$($_) needs to be updated; OldValue: $($hash2[$_].UsageLocation); NewValue: $strOneOffice"
        }
    Else
        {
        ## Nothing to update here because they already match
        write-host "$($_) does not need to be updated"
        Continue
        }
}