compare 2 rows of IP Subnets

Question

I need to be able to compare 2 rows of IP Subnets and tell if there is overlap.
For example:

In Row 1 I have a /24 and I need to check if this /24 is existing in Row 2 (either via the /24 or via the supernet /21 for instance)

so:

ROW 1: 192.168.2.0/24
ROW 2: 192.168.0.0/21

Result -> Row 1 exists in Row 2

I am not sure how to do this in Excel

Anybody any idea?

Why tagging PowerShell if you want to do this with Excel ? Try SuperUser instead of SO — CFou, Jan 10 '22 at 08:50

score 0 · Answer 1 · answered Jan 10 '22 at 09:04

If you want to do it in powershell, you may use this script:

Clear-Host
#Import csv file with IPs (Delimiter ";")
$rowlist = Import-Csv -Path "C:\rows_directory\rowlist.csv" -Delimiter ";"

$row1 = $rowlist | Select-Object -ExpandProperty "row1" 
$row2 = $rowlist | Select-Object -ExpandProperty "row2"

foreach($string in $row1) {
    if($string -in $row2) {
        Write-Output "ROW1: $string exist in ROW2"
    }
}

I filled file with:

And result was:

ROW1: 123 exist in ROW2

The SO wants to check that the ip address falls in the same subnet... — iRon, Jan 10 '22 at 09:24

iRon · Answer 2 · 2022-01-10T11:55:13.920

For this I would create a function to find the base address (as UInt32 type) of the IP address in the concerned Subnet:

Function Get-IPBase($Address) {
    $IP, $SubNet = $Address.Split('/', 2)
    $Bytes = ([IPAddress]$IP).GetAddressBytes()
    if ([BitConverter]::IsLittleEndian) { [Array]::Reverse($Bytes) }
    [BitConverter]::ToUInt32($bytes, 0) -BAnd -BNot ([UInt32][Math]::Pow(2, $SubNet) - 1)
}

Example of what the function returns:

Get-IPBase 192.168.2.0/24
3221225472
Get-IPBase 192.168.0.0/24
3221225472

Than, do a self-join, using this Join-Object script/Join-Object Module (see also: In Powershell, what's the best way to join two tables into one?):

Import-CSV .\My.csv |Join -On { Get-IPBase $_.Row1 } -Eq { Get-IPBase $_.Row2 }

Please add more details to your question (as what you tried yourself and a sample list. See also: How to Ask) if you like a more in dept explanation or have problems to implement this.

compare 2 rows of IP Subnets

2 Answers2