Compare IPAddress column from csv1 and csv2 and if matches get the uptime column from csv2

Question

i have 2 csv's with below details

csv1:

LNX_HOST_NAME   IPAddress     HOST_ID  SERVER_TYPE
-------------   ---------     -------  -----------
head01.com      10.16.0.11     ABI      WB         
head02.com      10.16.0.12     ABI      WB         
head03.com      10.16.0.14     ABI      WB

csv2:

Lastboot               IPAddress      SystemUpTime   OSType
--------               ---------      ------------   ------
2/29/2020 3:28:00 AM   10.16.0.10   2698626.75     Unix  
2/29/2020 3:29:00 AM   10.16.0.11   2698560.75     Unix  
2/29/2020 3:34:00 AM   10.16.0.12   2698200.5      Unix

Have to compare the IPAddress column from both the csv and return the common records like below

HOST_NAME       IPAddress     HOST_ID  SERVER_TYPE   SystemUpTime
-------------   ---------     -------  -----------   -------------
head01.com      10.16.0.11     ABI      WB           2698560.75  
head02.com      10.16.0.12     ABI      WB           2698200.5

Need help to get the logic for this.i wrote the below code which is giving me the unique records, but not able to get the UpTime column

$Reference  = Import-Csv -Path "D:\Script\csv2.csv"  | Select-Object -Skip 1
$Difference = Import-Csv -Path "D:\Script\csv1.csv"  | Select-Object -Skip 1


$keys = $Reference | ForEach-Object -MemberName IPAddress | Select-Object -Unique
$DiffVal = $Difference | Where-Object -FilterScript {$keys -Contains $_.IPAddress}

score 3 · Accepted Answer · answered Apr 01 '20 at 11:49

In this case, I would use Compare-Object to get the items in the csv files that have similar IPAddress properties and output new objects with all properties you need:

$csv1 = Import-Csv -Path 'D:\csv1.csv'
$csv2 = Import-Csv -Path 'D:\csv2.csv'

$result = Compare-Object -ReferenceObject $csv1 -DifferenceObject $csv2 -Property IPAddress -IncludeEqual -PassThru | 
    Where-Object { $_.SideIndicator -eq '==' } | 
    ForEach-Object {
        $ip = $_.IPAddress
        [PsCustomObject]@{
            'HOST_NAME'    = $_.LNX_HOST_NAME
            'IPAddress'    = $ip
            'HOST_ID'      = $_.HOST_ID
            'SERVER_TYPE'  = $_.SERVER_TYPE
            'SystemUpTime' = ($csv2 | Where-Object {$_.IPAddress -eq $ip}).SystemUpTime
        }
    }

# output on screen
$result | Format-Table -AutoSize

# output to new CSV file
$result | Export-Csv -Path 'D:\Result.csv' -NoTypeInformation

Result on screen:

HOST_NAME  IPAddress  HOST_ID SERVER_TYPE SystemUpTime
---------  ---------  ------- ----------- ------------
head01.com 10.16.0.11 ABI     WB          2698560.75
head02.com 10.16.0.12 ABI     WB          2698200.5

Thanks @Theo. It's working fine. but again there is a problem is my csv1 is having around 850+ records and csv2 is having 2600+ records. i checked with less data and the output came within no time but when i am trying with the entire data it is taking too much time. i ran it and from last 20 min there is no response. why it is happening — Nandy, Apr 01 '20 at 12:55

score 1 · Answer 2 · answered Apr 01 '20 at 12:01

My go-to approach for this is:

Load the second data set into a hashtable, using the common value as a key
Use the hashtable to grab the new column values with Select-Object:

# Create hashtable containing the desired values from csv2
$SystemUptime = @{}
Import-Csv D:\csv2.csv |ForEach-Object {
    $SystemUptime[$_.IPAddress] = $_.SystemUptime
}

# Filter set based on ip addresses found in the second data set
# Use a calculated property to reference the values in $SystemUptime, then export
Import-Csv D:\csv1.csv |Where-Object {
  $SystemUptime.ContainsKey($_.IPAddress)
} |Select-Object *,@{Name='SystemUptime'; Expression = {$SystemUptime[$_.IPAddress]}} |Export-Csv -Path 'D:\Result.csv' -NoTypeInformation

score 0 · Answer 3 · answered Apr 01 '20 at 18:48

In case performance matters and you do not want to reinvent the wheel (and don't want to deal with pitfalls and issues as "what if there are duplicate keys in either list?").
The Join-Object cmdlet (see also In Powershell, what's the best way to join two tables into one?) is based on the approach @Mathias R. Jessen mentioned and takes less then a second for joining the two csv files with the given sizes:

1..2600 | ForEach-Object {
    [pscustomobject]@{HOST_NAME = 'head{0:00}.com' -f ($_ % 100); IPAddress = 10, 16, [math]::floor($_ / 256), ($_ % 256) -Join '.'; HOST_ID = 'ABI'; SERVER_TYPE = 'WB'}
} | Export-Csv .\csv1.csv

1..850 | ForEach-Object {
    [pscustomobject]@{lastboot = Get-Date -f s; IPAddress = 10, 16, [math]::floor(3 * $_ / 256), (3 * $_ % 256) -Join '.'; SystemUpTime = Get-Random 999999; OSType = 'Unix'}
} | Export-Csv .\csv2.csv


Measure-Command {
    Import-Csv .\csv1.csv | Join (Import-Csv .\csv2.csv) -On IPAddress -Property HOST_NAME, IPAddress, HOST_ID, SERVER_TYPE, SystemUpTime | Export-Csv .\Result.Csv
}

Note1: to respect the PowerShell pipeline you should try to avoid assigning the csv files to a variable (or using brackets (...))
Note2: Putting the larger file at the left side of the Join-Object command is usually a little faster then the other way around.

Compare IPAddress column from csv1 and csv2 and if matches get the uptime column from csv2

3 Answers3

Linked