Hi Guys ,
Lot of us have faced this issue where we are left with troubleshooting overlapping boundaries issue and do not how to go about finding the duplicate boundaries in case we have large number of IP ranges given in our SCCM environment . You can use the below mentioned Powershell script for finding duplicate boundaries between as many databases as possible .
The only thing required is you should have rights on the databases in order to run this script accurately .
Advantages of using this script :
- You can detect duplicate boundary among all standalone primary sites.
- You can find the output in terms of IP Ranges
When you run the following script against your database you can see the following output :
The script is as follows :
<#
– Please input the number of DBs to connect, and the detail of each DB.
– Each items should be separated by comma, even duplicated. The last item is always “”, but not counted. For example:
$numofDBs = 2
$SQLServer = “server1″,”server2″,””
$SQLDBName = “CM_PRS”,”CM_PRS”,””
$userid = “administrator”,”administrator”,””
$pwd = “Usesr@123″,”User@123″,””
– Run the script from a PowerShell console without any parameters.
#>
$numofDBs = 2
$SQLServer = “localhost”,””
$SQLDBName = “CM_PRS”,””
$userid = “administrator”,””
$pwd = “User@123″,””
$filename = New-Object String[] $numofDBs
<#
$SqlQuery = “Select value from dbo.BoundaryEx where BoundaryType = 3”
$connectionstring = New-Object String[] $numofDBs
$filename = New-Object String[] $numofDBs
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$DataSet = New-Object System.Data.DataSet
# for ($i=0; $i -lt $numofDBs; $i++) {
for ($i=0; $i -lt $numofDBs; $i++) {
$connectionstring[$i] = “Server=” + $SQLServer[$i] + “;Database=” + $SQLDBName[$i] + “;Integrated Security=True;User ID=” + $userid[$i] + “;Password=” + $pwd[$i] + “;”
$SqlConnection.ConnectionString = $connectionstring[$i]
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter.SelectCommand = $SqlCmd
$SqlAdapter.Fill($DataSet)
$filename[$i] = $SQLDBName[$i].trim() + “.txt”
$DataSet.Tables[0] | out-file $filename[$i]
$DataSet.Reset()
}
#>
$filename[0] = “IPRange0.txt”
$filename[1] = “IPRange1.txt”
#Array to record the number of IP Ranges in each DB
$numofIPRangesinDB = New-Object Int[] $numofDBs
#Total number of IP Ranges from all DBs
$totalNumofIPRanges = 0
#Array to hold all IP Ranges, in sequence of DB
$IPBoundaries = @()
#The regular expression to represent each IP range string, in the format of xxx.xxx.xxx.xxx-xxx.xxx.xxx.xxx
$regex1 = “(\d{1,3}\.){3}(\d{1,3})\-(\d{1,3}\.){3}(\d{1,3})” #IP Addresses Range pattern
for ($i=0; $i -lt $numofDBs; $i++) {
$numofIPRangesinDB[$i] = 0
}
for ($i=0; $i -lt $numofDBs; $i++) {
$str = “DB[” + $i + “]:”
Write-Host $str
$temp = 0
$alllines = Get-Content -Path $filename[$i]
foreach ($line in $alllines) {
if ($line -match $regex1) {
$str = “IP Address Range[” + $temp + “]: ” + $line
Write-Host $str
$IPBoundaries += $line.split(“-“)
$temp++
}
}
$numofIPRangesinDB[$i] = $temp
$totalNumofIPRanges += $numofIPRangesinDB[$i]
}
#The array to record the starting and ending position of the first and last IP range from each DB
$temp = 2 * $numofDBs
$PosofDB = New-Object Int[] $temp
$PosofDB[0] = 0
$PosofDB[1] = $numofIPRangesinDB[0]-1
for ($i=1; $i -lt $numofDBs; $i++) {
$PosofDB[2*$i] = $PosofDB[2*$i-1]+1
$PosofDB[2*$i+1] = $PosofDB[2*$i] + $numofIPRangesinDB[$i] – 1
}
#The regular expression to extract 4 octets from an IP address
$regex = “(?<octet1>\d{1,3})\.(?<octet2>\d{1,3})\.(?<octet3>\d{1,3})\.(?<octet4>\d{1,3})”
Function CompareIP ($IP1, $IP2)
{
$result = $IP1 -match $regex
$IP1octets = [Int]$matches.octet1,[Int]$matches.octet2,[Int]$matches.octet3,[Int]$matches.octet4
$result = $IP2 -match $regex
$IP2octets = [Int]$matches.octet1,[Int]$matches.octet2,[Int]$matches.octet3,[Int]$matches.octet4
If ($IP1octets[0] -gt $IP2octets[0]) {
return 1
} elseif ($IP1octets[0] -lt $IP2octets[0]) {
return -1
} elseif ($IP1octets[1] -gt $IP2octets[1]) {
return 1
} elseif ($IP1octets[1] -lt $IP2octets[1]) {
return -1
} elseif ($IP1octets[2] -gt $IP2octets[2]) {
return 1
} elseif ($IP1octets[2] -lt $IP2octets[2]) {
return -1
} elseif ($IP1octets[3] -gt $IP2octets[3]) {
return 1
} elseif ($IP1octets[3] -lt $IP2octets[3]) {
return -1
} else {
return 0
}
}
#2-dimensional array to record the overlap status between any 2 IP address ranges
$status = New-Object ‘Int[,]’ $totalNumofIPRanges, $totalNumofIPRanges
for ($i=0; $i -lt $totalNumofIPRanges; $i++) {
for ($j=0; $j -lt $totalNumofIPRanges; $j++) {
$status[$i,$j] = 0
}
}
# It’s the loop to get all overlaps. The loop status between any 2 IP Address ranges is recorded in the 2-dimensional array $status.
for ($i=0; $i -lt $numofDBs; $i++) {
# $str = “A: DB ” + $i
# Write-Host $str
$istart = $PosofDB[2*$i]
$iend = $PosofDB[2*$i+1]
# $str = “A: IP Range from ” + $istart + ” to ” + $iend
# Write-Host $str
for ($ii=$istart;$ii -le $iend; $ii++) {
# $str = “A: IP Range ” + $ii
# Write-Host $str
for ($j=0; $j -lt $numofDBs; $j++) {
# $str = “B: DB ” + $j
# Write-Host $str
if ($i -eq $j) {
# No need to compare IP ranges within the same DB. Skip!
# $str = “DB B = DB A. Skipped!”
# Write-Host $str
continue
}
$jstart = $PosofDB[2*$j]
$jend = $PosofDB[2*$j+1]
# $str = “B: IP Range from ” + $jstart + ” to ” + $jend
# Write-Host $str
for ($jj=$jstart; $jj -le $jend; $jj++) {
# $str = “B IP Range ” + $jj
# Write-Host $str
$a = CompareIP $IPBoundaries[2*$ii+1] $IPBoundaries[2*$jj]
$b = CompareIP $IPBoundaries[2*$ii] $IPBoundaries[2*$jj+1]
# $str = “Now compare: A: DB” + $i + ” IP Range” + $ii + ” with B: DB” + $j + ” IP Range” + $jj
# Write-Host $str
# Write-Host $a
# Write-Host $b
if (($a -eq -1) -or ($b -eq 1)) {
#no overlap, do nothing
} else {
# overlapped, mark it in $status
# $str = “Overlapped! A: DB” + $i + ” IP Range” + $ii + ” with B: DB” + $j + ” IP Range” + $jj
# Write-Host $str
$status[$ii,$jj] = 1
}
}
}
}
}
$str = “Now display the overlaps:”
Write-Host “”
Write-Host $str
for ($i=0; $i -lt $numofDBs; $i++) {
$istart = $PosofDB[2*$i]
$iend = $PosofDB[2*$i+1]
for ($ii=$istart;$ii -le $iend; $ii++) {
for ($j=0; $j -lt $numofDBs; $j++) {
if ($i -eq $j) {
continue
}
$jstart = $PosofDB[2*$j]
$jend = $PosofDB[2*$j+1]
for ($jj=$jstart; $jj -le $jend; $jj++) {
if ($status[$ii, $jj] -eq 1) {
$posi = $ii – $istart
$posj = $jj – $jstart
$str = “DB[” + $i + “] IP Address Range[” + $posi + “] ” + $IPBoundaries[2*$ii] + “-” + $IPBoundaries[2*$ii+1] + ” ********** ” + “DB[” + $j + “] IP Address Range[” + $posj + “] ” + $IPBoundaries[2*$jj] + “-” + $IPBoundaries[2*$jj+1]
Write-Host $str
}
}
}
}
}
More Information about the script :
The Number of DB – You can change accordingly .
The SQL Server host name : Please enter the host name of the SQL servers accordingly separated by a comma and double quotes .
Similarly for ID and password you need to put the ID and password to make sure you are able to connect to the SQL databases .
The number of instances of databases will have equal number of password and ID instances .
Hope this helps 😀
SukhdeepBlogs 
Leave a Reply