#SCCM Expert #Private Cloud Consultant#SQL Expert#Corporate Trainer #Automation Consultant

Monitoring Duplicate Boundaries in SCCM Hierarchy

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 :

GetOverlappedIPRanges

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 😀

 

Advertisements

Tagged as: , , ,

Categorised in: Powershell, SCCM

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Translate

%d bloggers like this: