In PowerShell, what's the best way to join two tables into one?

user_71116 0
Viewed 46419

I'm fairly new to PowerShell, and am wondering if someone knows of any better way to accomplish the following example problem.

I have an array of mappings from IP address to host-name. This represents a list of active DHCP leases:

PS H:\> $leases

IP                    Name
--                    ----
192.168.1.1           Apple
192.168.1.2           Pear
192.168.1.3           Banana
192.168.1.99          FishyPC

I have another array of mappings from MAC address to IP address. This represents a list of IP reservations:

PS H:\> $reservations

IP                    MAC
--                    ---
192.168.1.1           001D606839C2
192.168.1.2           00E018782BE1
192.168.1.3           0022192AF09C
192.168.1.4           0013D4352A0D

For convenience, I was able to produce a third array of mappings from MAC address to IP address and host name using the following code. The idea is that $reservations should get a third field, "Name", which is populated whenever there's a matching "IP" field:

$reservations = $reservations | foreach {
    $res = $_
    $match = $leases | where {$_.IP -eq $res.IP} | select -unique
    if ($match -ne $NULL) {
        "" | select @{n="IP";e={$res.IP}}, @{n="MAC";e={$res.MAC}}, @{n="Name";e={$match.Name}}
    }
}

The desired output is something like this:

PS H:\> $ideal

IP                    MAC                 Name
--                    ---                 ----
192.168.1.1           001D606839C2        Apple
192.168.1.2           00E018782BE1        Pear
192.168.1.3           0022192AF09C        Banana
192.168.1.4           0013D4352A0D

Is there any better way of doing this?

dhcp powershell scripting
5 Answers

This can also be done using my module Join-Object

Install-Module 'Join-Object'

Join-Object -Left $leases -Right $reservations -LeftJoinProperty 'IP' -RightJoinProperty 'IP'

Regarding performance, I tested against a sample data of 100k lines:

  1. Hashtable example posted by @js2010 run in 8 seconds.
  2. Join-Object by me run in 14 seconds.
  3. LeftJoin by @iRon run in 1 minute and 50 seconds
user_78869650

Here's a simple example using a hashtable. With big arrays, this turns out to be faster.

$leases =
'IP,Name
192.168.1.1,Apple
192.168.1.2,Pear
192.168.1.3,Banana
192.168.1.99,FishyPC' | convertfrom-csv

$reservations =
'IP,MAC
192.168.1.1,001D606839C2
192.168.1.2,00E018782BE1
192.168.1.3,0022192AF09C
192.168.1.4,0013D4352A0D' | convertfrom-csv

$hashRes=@{}
foreach ($resRecord in $reservations) {
  $hashRes[$resRecord.IP] = $resRecord
}

$leases | foreach {
  $other = $hashRes[$_.IP]

  [pscustomobject]@{IP=$_.IP
                   MAC=$other.MAC
                  Name=$_.name}
}

IP           MAC          Name
--           ---          ----
192.168.1.1  001D606839C2 Apple
192.168.1.2  00E018782BE1 Pear
192.168.1.3  0022192AF09C Banana
192.168.1.99              FishyPC
user_66549420

Easiest way I've found to Merge two Powershell Objects is using ConvertTo-Json and ConvertFrom-Json

One liner based on the OPs Senario:

$leases | foreach {(ConvertTo-Json $_) -replace ("}$", (ConvertTo-Json ($reservations | where IP -eq $_.IP | select * -ExcludeProperty IP)) -Replace "^{", ",")} 
| ConvertFrom-Json

Results in:

IP          Name  Mac
--          ----  ---
192.168.1.1 Apple 001D606839C2
192.168.1.2 Pear  00E018782BE1

For another example lets make a couple objects:

$object1 = [PSCustomObject]@{"A" = "1"; "B" = "2"}
$object2 = [PSCustomObject]@{"C" = "3"; "D" = "4"}

Merge them together using Json by replacing the opening and closing brackets:

(ConvertTo-Json $object1) -replace ("}$", $((ConvertTo-Json $object2) -Replace "^{", ",")) | ConvertFrom-Json

Output:

A B C D
- - - -
1 2 3 4

Another example using a group of objects:

$mergedObjects = [PSCustomObject]@{"Object1" = $Object1; "Object2" = $Object2}

Object1     Object2
-------     -------
@{A=1; B=2} @{C=3; D=4}

Can just do the same again within a foreach:

$mergedObjects | foreach {(ConvertTo-Json $_.Object1) -replace ("}$", $((ConvertTo-Json $_.Object2) -Replace "^{", ",")) | ConvertFrom-Json}

Output:

A B C D
- - - -
1 2 3 4
user_196444370

You can use script block like this

$leases | select IP, NAME, @{N='MAC';E={$tmp=$_.IP;($reservations| ? IP -eq $tmp).MAC}}
user_120408610
Related