compare 2 rows of IP Subnets-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

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.