3

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
john.doe@domain.com             US-California
need.help@domain.com            IT-Naples
another.example@domain.com      TR-Istanbul
(etc...)

In the other array, I have:

$arrTwo
Username                        Location
john.doe@domain.com             US
need.help@domain.com            US
another.example@domain.com      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

  • 1
    How are the arrays created? Is the username field unique? – EBGreen Apr 2 '14 at 19:27
  • Also, are the arrays the same size? – zdan Apr 3 '14 at 1:58
  • @EBGreen - See updated information above. – STGdb Apr 4 '14 at 13:17
  • @zdan - No sorry, they aren't the same size. $arrOne is much smaller than $arrTwo – STGdb Apr 4 '14 at 13:17
2

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
            }
        }
    }
}
1

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.

  • Yes, one of the arrays is coming from AD. The other is from Office 365 (MSOL). I realized that my best performance gain would be to stop looping through arrTwo when a match is found but I can't get it to return to arrOne properly after the match is found. I tried using compare but unfortunately it didn't help. Good idea though - thank you. – STGdb Apr 4 '14 at 13:21
0

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 $_";
        }
    }
}
  • Thank you for the code. Unfortunately the arrays aren't the same size. $arrOne is much smaller than $arrTwo (a lot smaller). I added some notes to the original post above. I think that I realize my best performance gain would be to break out of the 2nd loop when a match is found and return to the next object in the first loop. But I haven't been able to get that to work properly. I am still getting the workings of hashtables - work in progress. – STGdb Apr 4 '14 at 13:33
0

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. User@company.com would be retrieved as $Hash['User@company.com'] and if you wanted the LocalOffice property, it would be $Hash['User@company.com'].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
        }
}
  • (1) Super User doesn’t support direct messaging.   The correct thing to do would be to edit your answer (which you have done) and then click on “flag” (below the post, on the left, above the “deleted by” legend).   (2) I thought that your post was not an answer, and, in fact, I flagged it as “not an answer”.   The second sentence starts “I thought I would ask …”, which makes it look like you’re asking a follow-up question.   “I’m having trouble following your code …” makes it look like you’re asking for clarification. … (Cont’d) – Scott Jul 3 '18 at 16:31
  • (Cont’d) …  It may occasionally be appropriate, but it’s best to avoid asking questions in answers. (3) On second look, I guess it looks like you are providing an answer.  But the fact that your code is so long and so wide (the first line is over 250 characters long) makes it hard to read.  Can you insert line breaks? (4) The inconsistent indentation style also makes the code hard to read. (5) I’m sorry I flagged your answer.  But now you have some clearer direction on how to make an answer look like an answer. – Scott Jul 3 '18 at 16:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.