Create-BuildingMappingFromADSites

1.1.0-rc1

Create-BuildingMappingFromADSites.ps1

<#PSScriptInfo
 
.VERSION 1.1.0-rc1
.GUID 0cf0112f-96e2-4612-bea7-083ef943c249
.AUTHOR martrin@microsoft.com
.COMPANYNAME
.COPYRIGHT
.TAGS
.LICENSEURI
.PROJECTURI
.ICONURI
.EXTERNALMODULEDEPENDENCIES
.REQUIREDSCRIPTS
.EXTERNALSCRIPTDEPENDENCIES
.RELEASENOTES
 
#>

<#
.SYNOPSIS
Retrieves AD Site and Subnet information and creates a subnet mapping file for Call Quality Dashboard (CQD). Performs Check for overlap and duplicates.
 
.DESCRIPTION
The Create-BuildingMappingFromADSites script iterates through all AD Sites and Subnets. All valid IPv4 addresses are exported into a CSV-style format, all other formats like IPv6 as skipped as CQD doesn't support them today.
You can use the optional parameters to specify additional information for the mapping file, including City, Country, Region and all other data fields that CQD supports.
 
The script requires the computer to be domain joined so that it can leverage an AD context to retrieve data. Computers joined to Azure Active Directory aren't supported.
 
The script also performs check for overlap (aka supernetting) and duplicate subnet information. This check can be run without connection to AD if an input file is specified through -InputFileName parameter.
 
.EXAMPLE
Create-BuildingMappingFromADSites.ps1
Read AD site and subnet information and create building mapping file.
 
.EXAMPLE
Create-BuildingMappingFromADSites.ps1 -OutputFileName 'MyFile.csv' -BuildingOfficeType 'CompanyOwned'
Read AD site and subnet information, use OutputFileName MyFile.csv and assign BuildingOfficeType = CompanyOwned
 
.EXAMPLE
Create-BuildingMappingFromADSites.ps1 -ExpressRoute
Read AD site and subnet information, mark all sites to be connected via ExpressRoute
 
.Example
Create-BuildingMappingFromADSites.ps1 -InputFileName MyCQDFile.csv
Reads existing CQD building file and performs check for overlapping or duplicate subnets. Accepts both tab and comma delimited file (.tsv and .csv).
 
.Link
https://docs.microsoft.com/en-us/SkypeForBusiness/using-call-quality-in-your-organization/turning-on-and-using-call-quality-dashboard#upload-building-information
 
.NOTES
File Name: Create-BuildingMappingFromADSites.ps1
Author: Martin Rinas (martrin@microsoft.com)
 
You need to run this script from a Domain joined machine, AAD joined machines or machines in a workgroup aren't supported.
#>
[cmdletbinding()]            
param(
    [Parameter(Mandatory=$false)]
    [string]$OutputFileName='.\BuildingFile.csv',
    [Parameter(Mandatory=$false)]
    [string]$NetworkName='',    
    [Parameter(Mandatory=$false)]
    [string]$OwnershipType='',
    [Parameter(Mandatory=$false)]
    [string]$BuildingType='',
    [Parameter(Mandatory=$false)]
    [string]$BuildingOfficeType='',
    [Parameter(Mandatory=$false)]
    [string]$City='',
    [Parameter(Mandatory=$false)]
    [string]$ZipCode='',
    [Parameter(Mandatory=$false)]
    [string]$Country='',
    [Parameter(Mandatory=$false)]
    [string]$State='',
    [Parameter(Mandatory=$false)]
    [string]$Region='',
    [Parameter(Mandatory=$false)]
    [Switch]$ExpressRoute,
    [Parameter(Mandatory=$false)]
    [string]$InputFileName
)            

function DottedToBinary ($DottedIP) {
    $DottedIP.split(".") | ForEach-Object {$BinaryIP += $([convert]::ToString($_,2).padleft(8,"0"))}
    Return [string]$BinaryIP   
}

function Compare-IPSubnets {
    <#
    .SYNOPSIS
    Compares two IPv4 subnets and tells if the subnets are identical, if one is a subnet to another, or different.
    #>
    [CmdletBinding()]
    param
    (
        # Parameter help description
        [Parameter(Mandatory=$true)]
        [string]$FirstIPAddressDotted,
        [Parameter(Mandatory=$true)]
        [int]$FirstSubnetMaskBits,
        [Parameter(Mandatory=$false)]
        [string]$SecondIPAddressDotted,
        [Parameter(Mandatory=$true)]
        [int]$SecondSubnetMaskBits       
    )
    
    $strIdenticalSubnets = 'IdenticalSubnetsFound'
    $strOverlapFound = 'OverlappingSubnetsFound'

    $Result = New-Object System.Object
 
    $Result | Add-Member -MemberType NoteProperty -Name CompareStatus -TypeName string -value $null        
    $Result | Add-Member -MemberType NoteProperty -Name FirstIPAddressDotted -TypeName string  -value $FirstIPAddressDotted
    $Result | Add-Member -MemberType NoteProperty -Name SecondIPAddressDotted -TypeName string  -value $SecondIPAddressDotted
    $Result | Add-Member -MemberType NoteProperty -Name CompareDetails -TypeName string -value $null
    $Result | Add-Member -MemberType NoteProperty -Name IsSubnetSupernet -TypeName Bool -value $null
    $Result | Add-Member -MemberType NoteProperty -Name Match -TypeName bool  -value $false
       
    # check for identical subnets, can shorten the process in this case
    if(($FirstIPAddressDotted -eq $SecondIPAddressDotted) -and ($FirstSubnetMaskBits -eq $SecondSubnetMaskBits) )
    {
        $message = "Subnet and Mask are identical: "
        $message += "$FirstIPAddressDotted/$FirstSubnetMaskBits and $SecondIPAddressDotted/$SecondSubnetMaskBits"
        Write-Debug $message
        $Result.IsSubnetSupernet = $false
        $Result.CompareDetails = $message
        $result.CompareStatus = $strIdenticalSubnets
        $result.match = $true
        
        return $Result

    }

    # Identify smaller subnet and larger supernet
    if($SecondSubnetMaskBits -gt $FirstSubnetMaskBits)
    {
        $SupernetDotted = $FirstIPAddressDotted
        $SupernetMaskBits = $FirstSubnetMaskBits
        
        $SubnetDotted = $SecondIPAddressDotted
        $SubnetMaskBits = $SecondSubnetMaskBits
        
    }
    else {

        $SupernetDotted = $SecondIPAddressDotted
        $SupernetMaskBits = $SecondSubnetMaskBits
        
        $SubnetMaskBits = $FirstSubnetMaskBits
        $SubnetDotted = $FirstIPAddressDotted
    }

    # Convert dotted network to binary and identify the actual network ID
    $SubnetBinary = DottedToBinary($SubnetDotted)
    $SubnetBinaryNetworkdID = $SubnetBinary.Substring(0,$SubnetMaskBits).PadRight(32,"0")

    $SupernetBinary = DottedToBinary($SupernetDotted)
    $SupernetBinaryNetworkID = $supernetBinary.Substring(0,$SupernetMaskBits).PadRight(32,"0")

    # compare if supernet network ID is the same as the subnet network ID if we apply the supernet mask to the subnet ID
    if($SubnetBinaryNetworkdID.Substring(0,$SupernetMaskBits) -eq $SupernetBinaryNetworkID.substring(0,$SupernetMaskBits))
    {
        if($SupernetMaskBits -eq $SubnetMaskBits)
        {
            $message = "Subnet and Mask are identical: $SubnetDotted/$SubnetMaskBits is same Subnet as $SupernetDotted/$SupernetMaskBits"
            Write-Verbose $message
            
            $Result.IsSubnetSupernet = $false
            $Result.CompareDetails = $message
            $result.CompareStatus = $strIdenticalSubnets
            $result.match = $true
            
        }
        else 
        {
            $message = "Overlapping Subnet/Supernet found: $SubnetDotted/$SubnetMaskBits is a subnet to $SupernetDotted/$SupernetMaskBits"
            Write-Verbose $message
            
            $Result.IsSubnetSupernet = $true
            $Result.CompareDetails = $message
            $result.CompareStatus = $strOverlapFound
            $result.match = $true
            
        }
        return $Result
    }
    # no overlap found, returning empty result set
    return $Result
}


function Get-CQDSubnetsFromAD
{
    $SubnetMappingFile = @()
    
    # get all Sites from currenct AD Forest
    try {
        Write-Verbose 'connecting to current AD forest'
        $CurrentForestName = [System.DirectoryServices.ActiveDirectory.Forest]::GetCurrentForest().Name
        $Sites = [System.DirectoryServices.ActiveDirectory.Forest]::GetCurrentForest().Sites                   
    }
    catch {
        # This is a fatal error. Cannot continue without connectivity to AD.
        Write-Host 'Could not connect to Active Directory.' -ForegroundColor red
        Write-Host $_.Exception.Message -ForegroundColor Yellow
        Write-Verbose ('error: {0}' -f $_.Exception)
        break
    }

    Write-Host ('Processing {0} sites from {1}' -f $sites.count, $CurrentForestName) -ForegroundColor Yellow



    # Looping through all sites
    foreach ($Site in $Sites) {            

        write-verbose ('processing site: {0}' -f $site.name)
        write-verbose ('found {0} subnets' -f $site.subnets.count)
        
        # Processing all subnets within the same site
        foreach ($subnet in $site.Subnets)
        {
            write-verbose ('processing subnet: {0}' -f $subnet.name)
            
            #split Subnet into Address and mask
            $SplitSubnet = $subnet.name.split("/")

            if ($SplitSubnet.count -ne 2)
            {
                # testing if split was successufl
                write-verbose ('Cannot split {0} into Subnet ID and mask, skipping..."' -f $Subnet.name)
                continue
            }
            
            # is this a valid IPv4 address? CQD doesn't support IPv6
            if(!($SplitSubnet[0] -match $IPv4Regex))
            {
                # not a IPv4 address, continue with next subnet for same site
                write-verbose ('skipping subnet, not a valid IPv4 address: {0}' -f $SplitSubnet)
                continue
            }
            
            # create and populate our object
            $Row = New-Object System.Object
            
            # replacing any comma character ',' in the sting with an underscore '_' so that the comma doesn't mess up with the CSV export
            # AD doesn't allow any special characters in the site name, no special handling required there.
            $Row | Add-Member -MemberType NoteProperty -Name Network -Value ([string]$SplitSubnet[0])
            $Row | Add-Member -MemberType NoteProperty -Name NetworkName -Value  $NetworkName.replace(',','_')
            $Row | Add-Member -MemberType NoteProperty -Name NetworkRange -Value ([int]$SplitSubnet[1])
            $Row | Add-Member -MemberType NoteProperty -Name BuildingName -Value ([string]$Subnet.Site)
            $Row | Add-Member -MemberType NoteProperty -Name OwnershipType -Value $OwnershipType.replace(',','_')
            $Row | Add-Member -MemberType NoteProperty -Name BuildingType -Value $BuildingType.replace(',','_')
            $Row | Add-Member -MemberType NoteProperty -Name BuildingOfficeType -Value $BuildingOfficeType.replace(',','_')
            $Row | Add-Member -MemberType NoteProperty -Name City -Value $City.replace(',','_')
            $Row | Add-Member -MemberType NoteProperty -Name ZipCode -Value $ZipCode.replace(',','_')
            $Row | Add-Member -MemberType NoteProperty -Name Country -Value $Country.replace(',','_')
            $Row | Add-Member -MemberType NoteProperty -Name State -Value $State.replace(',','_')
            $Row | Add-Member -MemberType NoteProperty -Name Region -Value $Region.replace(',','_')
            $Row | Add-Member -MemberType NoteProperty -Name InsideCorp -Value '1'
            $Row | Add-Member -MemberType NoteProperty -Name ExpressRoute -Value ([int]$ExpressRoute)

            # Add results to mapping file
            $SubnetMappingFile += $Row
        }
    }
    return $SubnetMappingFile
}

#region define variables and prepare for execution

# RegEx to detect a single IPv4 Octet
New-Variable -Name Octet -Value  '(?:0?0?[0-9]|0?[1-9][0-9]|1[0-9]{2}|2[0-5][0-5]|2[0-4][0-9])' -Option ReadOnly
# and we need four of them for a valid IPv4 address, combine them into a RegEx
[regex] $IPv4Regex = "^(?:$Octet\.){3}$Octet$"

# Cannot process Switch parameter during export, have to convert it into bool.
if($ExpressRoute)
{
    [bool]$ExpressRoute = $true
}
else 
{
    [bool]$ExpressRoute = $false
        
}



# This file will collect overlap and duplicats, if there are any.
$IssuesFile = 'Issues.csv'

# Define colum header if we need to read from a CQD input file as this file doesn't contain any headers
$CQDHeader = "Network", "NetworkName" , "NetworkRange" , "BuildingName", "OwnershipType", "BuildingType", "BuildingOfficeType", "City", "ZipCode", "Country", "State", "Region", "InsideCorp", "ExpressRoute"
New-Variable -Name CQDDelimiter -Value `t

# Create the empty array as a container for the mapping file
$SubnetMappingFile = @()

#endregion

#region main script

# check if an input file is given, don't need to fetch data from AD in this case
if([string]::IsNullOrEmpty($InputFileName))
{
    # no input file given, fetch data from AD
    Write-Host "Reading subnet data from AD"
    # Test if output file already exist, let's not override any existing file
    if(Test-Path($OutputFileName))
    {
        $ExistingFile = get-item $OutputFileName
        Write-Host ('File {0} already exists, please remove existing file.' -f $ExistingFile.FullName)   -ForegroundColor Red
        break
    }
    $SubnetMappingFile = Get-CQDSubnetsFromAD

}
else 
{
    # file found, lets import
    Write-Host "Reading input file $InputFileName"
    # Check if file is comma or tab separated
    $FirstLine = get-content -Path $InputFileName
    if(!$FirstLine.Contains($CQDDelimiter))
    {
        # doesn't contain tab, assuming comma as separator
        $CQDDelimiter = ','
    }

    $SubnetMappingFile = Import-Csv -Path $InputFileName -Delimiter $CQDDelimiter -Header $CQDHeader -ErrorAction Stop
}

# retrieved all subnets
# Insert check for overlapping subnets here

Write-Host ('found {0} IPv4 subnets' -f $SubnetMappingFile.Length)

# loop through all records, compare subnet[i] with all subnet[n>i] and store results
# report on results (if resultset not empty, call out issues found)
# break into discrete functions, one for pulling data from AD, another to read from existing CSV / TSV file for CQD input validation


$FirstSiteIndex=0
$SiteCount = $SubnetMappingFile.Count

write-host "checking $SiteCount subnets for overlap and duplicates. This may take a while for a large amount of subnets."

$results = @()
foreach ($FirstSite in $SubnetMappingFile)
{
    Write-Progress -Activity "Checking $SiteCount Subnets for overlap and duplicates" -Status ('Processing network {0}' -f $Firstsite.Network) -PercentComplete (($FirstSiteIndex+1)/$SiteCount*100)
    write-verbose "processing FirstSite: $FirstSiteIndex of $SiteCount"
    
    if ($FirstSiteIndex -lt $SiteCount-1)
    {
        foreach ($SecondSiteIndex in ($FirstSiteIndex+1)..($SiteCount-1))
        {
            $SecondSite = $SubnetMappingFile[$SecondSiteIndex]
            
            write-verbose "comparing FirstSite: $FirstSiteIndex with SecondSite: $SecondSiteIndex"
            Write-verbose ('first site: {0} second site: {1}' -f $FirstSite.Network, $SecondSite.Network)
            
            $result = New-Object System.Object
            $result = Compare-IPSubnets -FirstIPAddressDotted $FirstSite.Network -FirstSubnetMaskBits $FirstSite.NetworkRange -SecondIPAddressDotted $SecondSite.Network -SecondSubnetMaskBits $SecondSite.NetworkRange
            if($result.match)
            {
                # found match, needs to be fixed
                $results += $result
            }
        }
    }
    
    $FirstSiteIndex++
    write-debug "next first site"
}

if($results.Length -gt 0 ) 
{
    write-host "Overlapping or duplicate subnets found. Please check output in $IssuesFile and resolve issues before uploading to CQD. You may use the -InputFile paramater to check the adjusted file." -ForegroundColor Yellow
    $results | Export-csv -NoTypeInformation -Path $IssuesFile
}
else 
{
    Write-Host "No overlapping or duplicate subnets found." -ForegroundColor Green
}

# Export the file only if no input file was given.
if([string]::IsNullOrEmpty($InputFileName))
{
    # Convert into CSV format
    $SubnetMappingFile = $SubnetMappingFile | convertto-csv -NoTypeInformation -Delimiter ','

    # CQD doesn'T expect quotes in the text, so we have to remove them
    $SubnetMappingFile = $SubnetMappingFile.Replace('"','')

    Write-Host ('Saving building mapping file: {0}' -f $OutputFileName ) -ForegroundColor Green

    # CDQ also doesn't expect column headings, so we remove them and save the remaining content to disk
    $SubnetMappingFile[1..$SubnetMappingFile.Length] | Set-Content -Path $OutputFileName -ErrorAction Stop
}

Write-Host 'Please validate the accuracy, adjust and complete as needed.' -ForegroundColor Green
Write-Host 'You may use the graphical interface of the Network Planner at https://aka.ms/MyAdvisor to manipulate the file as needed'
Write-Host 'Upload the file to CQD at https://cqd.lync.com to complete the process.'

#endregion