Create-BuildingMappingFromADSites

1.1.2-rc2

Create-BuildingMappingFromADSites.ps1

<#PSScriptInfo
 
.VERSION 1.1.2-rc2
.GUID 0cf0112f-96e2-4612-bea7-083ef943c249
.AUTHOR martrin@microsoft.com
.COMPANYNAME
.COPYRIGHT
.TAGS
.LICENSEURI
.PROJECTURI
.ICONURI
.EXTERNALMODULEDEPENDENCIES
.REQUIREDSCRIPTS
.EXTERNALSCRIPTDEPENDENCIES
.RELEASENOTES
Added support for using subnet and site description as building name.
 
#>

<#
.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 -BuildingNameSource SubnetDescription
Read AD site and subnet information and create building mapping file, uses site description as a name for the building.
 
.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 to read data from Active Directory.
#>
[cmdletbinding()]            
param(
    [Parameter(Mandatory=$false)]
    [string]$OutputFileName='.\BuildingFile.csv',
    [Parameter(Mandatory = $false)]
    [ValidateSet('SiteName','SubnetDescription','SiteDescription')]
    [string] $BuildingNameSource = 'SiteName',
    [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 
{
    <#
    .SYNOPSIS
    Converts a dotted IPv4 address into binary
    #> 
    param
    (
        # The dotted IPv4 address to convert, e.g. 192.168.1.1
        [Parameter(Mandatory=$true)]
        [string]$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 or if one is a subnet to another.
    #>
    [CmdletBinding()]
    param
    (
        # this functions expects two dottend IPAddresses (e.g. 192.168.1.1) and the subnet mask bits (e.g. 24)
        [Parameter(Mandatory=$true)]
        [string]$FirstIPAddressDotted,
        [Parameter(Mandatory=$true)]
        [int]$FirstSubnetMaskBits,
        [Parameter(Mandatory=$false)]
        [string]$SecondIPAddressDotted,
        [Parameter(Mandatory=$true)]
        [int]$SecondSubnetMaskBits       
    )
    
    $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'
        $CurrentForest = [System.DirectoryServices.ActiveDirectory.Forest]::GetCurrentForest()  
        $CurrentForestName = $CurrentForest.Name
        $Sites = $CurrentForest.Sites                   
        $ConfigurationPartitionDN = $CurrentForest.Schema.name.Substring(10)
        Write-Verbose "Connected to $CurrentForestName"
    }
    catch {
        # This is a fatal error. Cannot continue without connectivity to AD.
        Write-error -Message 'Could not connect to Active Directory.'
        Write-Warning $_.Exception.Message
        Write-Warning 'Please make sure that this computer is member of an Active Directory domain and you are logged on to the domain. Azure Active Directory joined computers are not supported at this time.'
        Write-Verbose ('error: {0}' -f $_.Exception)
        break
    }

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

    # 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
            }
            
            switch ($BuildingNameSource) {
                SiteName { $BuildingName = ([string]$Subnet.Site) }
                SubnetDescription 
                {
                    $ADSIPath = ('LDAP://CN={0},CN=Subnets,CN=Sites,{1}' -f ([string]$Subnet).replace('/','\/'), $ConfigurationPartitionDN )
                    Write-Verbose "Retrieving Site Description from Description of Subnet: $ADSIPath"
                    $objSubnet = [ADSI]$ADSIPath
                    [string]$BuildingName = $objSubnet.description

                    if([string]::IsNullOrEmpty($BuildingName))
                    {
                        Write-Verbose ('No description found for subnet {0}, using Sitename {1} instead' -f [string]$Subnet, [string]$subnet.Site)
                        $BuildingName = [string]$Subnet.Site
                    }
                }
                SiteDescription 
                {
                    $ADSIPath = ('LDAP://CN={0},CN=Sites,{1}' -f [string]$Subnet.Site, $ConfigurationPartitionDN )
                    Write-Verbose "Retrieving Site Description from Description of Site: $ADSIPath"
                    $objSites = [ADSI]$ADSIPath
                    [string]$BuildingName = $objSites.description
                    if([string]::IsNullOrEmpty($BuildingName))
                    {
                        Write-Verbose ('No description found for site {0}, using Sitename {1} instead' -f [string]$Subnet.site, [string]$subnet.Site)
                        $BuildingName = [string]$Subnet.Site
                    }
                }
            }

            # Fallback if Description is empty


            # 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 $BuildingName.replace(',','_')
            $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

# a few strings used that'll be used in the issues report
New-Variable -name strIdenticalSubnets -Value 'IdenticalSubnetsFound' -Option ReadOnly
New-Variable -Name strOverlapFound -Value 'OverlappingSubnetsFound' -Option ReadOnly

# 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.
$IssuesFileName = '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
    
    # Test if output file already exist, let's not override any existing file
    if(Test-Path($OutputFileName))
    {
        $ExistingFile = get-item $OutputFileName
        Write-Error ('File {0} already exists, please remove existing file.' -f $ExistingFile.FullName)
        break
    }
    Write-Host
    Write-Host "Reading subnet data from AD"
    Write-Host
    $SubnetMappingFile = Get-CQDSubnetsFromAD
    if($SubnetMappingFile.Length -lt 1)
    {
        # No subnets in AD found
        Write-Error ('No IPv4 Subnets in Forest {0} found.' -f [System.DirectoryServices.ActiveDirectory.Forest]::GetCurrentForest().Name)
        break
    }

}
else 
{
    # file found, lets import
    Write-Host 
    Write-Host "Reading input file $InputFileName"
    Write-Host

    # 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

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)

$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."
Write-Host

$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-debug "Comparing FirstSite: $FirstSiteIndex with SecondSite: $SecondSiteIndex"
            Write-debug ('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"
}

# Are there any issues?
if($results.Length -gt 0 ) 
{
    Write-Warning ('{0} Overlapping or duplicate subnets found. Please check output in {1} and resolve issues before uploading to CQD. You may use the -InputFile paramater to check the adjusted file.' -f $results.Length, $IssuesFileName)
    Write-Host
    $results | Export-csv -NoTypeInformation -Path $IssuesFileName
}
else 
{
    Write-Host "No overlapping or duplicate subnets found." -ForegroundColor Green
    Write-Host
}

# 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
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 for easier editing and to start bandwith planning.'
Write-Host 'Upload the file to CQD at https://cqd.lync.com to complete the process.'
Write-Host

#endregion