CQDTools.psm1
|
class CQDBuildingFile { [string]$Network [string]$NetworkName [int]$NetworkRange [string]$BuildingName [string]$OwnershipType [string]$BuildingType [string]$BuildingOfficeType [string]$City [string]$ZipCode [string]$Country [string]$State [string]$Region [int]$InsideCorp [int]$ExpressRoute } function Write-VerboseLog{ <# .SYNOPSIS Writes messages to Verbose output and Verbose log file .DESCRIPTION This fuction will direct verbose output to the console if the -Verbose paramerter is specified. It will also write output to the Verboselog. #> param( [String]$Message ) $VerboseMessage = ('{0} Line:{1} {2}' -f (Get-Date), $MyInvocation.ScriptLineNumber, $Message) #OLD $VerboseMessage = "$(Get-Date):$($Invocation.MyCommand) $($Message)" Write-Verbose -Message $VerboseMessage Add-Content -Path $VerboseLogFileName -Value $VerboseMessage } function Write-ScriptError{ param( [Parameter(Mandatory=$true,HelpMessage='Provide message to include in the log')] [string] $Message, [Parameter(Mandatory=$false,HelpMessage='Error object to report')] [Object]$ErrorObject, [bool] $Terminating = $false ) Write-VerboseLog -Message $Message Write-VerboseLog -Message ('Error occurred: {0}' -f $ErrorObject.Exception.Message) Write-VerboseLog -Message ('STACKTRACE:{0}' -f $ErrorObject.ScriptStackTrace) if ($Terminating){Write-Error -Message $Message -ErrorAction Stop} } filter ArrayToHash { begin { $hash = @{} } process { $hash[$_] = 'exists' } end { return $hash } } 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-VerboseLog -Message $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-VerboseLog -Message $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-VerboseLog -Message 'Connecting to current AD forest' $CurrentForest = [System.DirectoryServices.ActiveDirectory.Forest]::GetCurrentForest() $CurrentForestName = $CurrentForest.Name $Sites = $CurrentForest.Sites $ConfigurationPartitionDN = $CurrentForest.Schema.name.Substring(10) Write-VerboseLog -Message "Connected to $CurrentForestName" } catch { # This is a fatal error. Cannot continue without connectivity to AD. Write-ScriptError -Message 'Could not connect to Active Directory. Please make sure that this computer is member of an Active Directory domain and you are logged on to the domain.' -ErrorObject $_ -Terminating $true } Write-VerboseLog -Message ('Processing {0} sites from {1}' -f $sites.count, $CurrentForestName) # Looping through all sites foreach ($Site in $Sites) { Write-VerboseLog -Message ('Processing site: {0}' -f $site.name) Write-VerboseLog -Message ('Found {0} subnets' -f $site.subnets.count) # Processing all subnets within the same site foreach ($subnet in $site.Subnets) { Write-VerboseLog -Message ('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-VerboseLog -Message ('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-VerboseLog -Message ('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-VerboseLog -Message "Retrieving Site Description from Description of Subnet: $ADSIPath" $objSubnet = [ADSI]$ADSIPath [string]$BuildingName = $objSubnet.description if([string]::IsNullOrEmpty($BuildingName)) { Write-VerboseLog -Message ('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-VerboseLog -Message "Retrieving Site Description from Description of Site: $ADSIPath" $objSites = [ADSI]$ADSIPath [string]$BuildingName = $objSites.description if([string]::IsNullOrEmpty($BuildingName)) { Write-VerboseLog -Message ('No description found for site {0}, using Sitename {1} instead' -f [string]$Subnet.site, [string]$subnet.Site) $BuildingName = [string]$Subnet.Site } } } # 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 } function Get-v4ScopeFromMsftDHCPServer { [CmdletBinding()] param ( # The dotted IPv4 address to convert, e.g. 192.168.1.1 [Parameter(Mandatory=$true, ValueFromPipeline=$true)] [string]$MSftDHCPServer ) Write-VerboseLog -Message ('Function Invocation: {0}' -f ($MyInvocation.BoundParameters | out-string)) Write-VerboseLog -Message "Processing $MSftDHCPServer" try{$Scopes = (get-DHCPServerv4Scope -computername $MSftDHCPServer -ErrorAction stop)} catch { Write-Warning "Couldn't retrieve data from $MSftDHCPServer" } $Scopes = $Scopes | Select-Object Name, ScopeId, SubnetMask, State, @{N='Server';E={$MSftDHCPServer}} return $Scopes } function Get-BuildingMappingFromMsftDHCP { <# .SYNOPSIS Creats a CQD compatible building file from Microsoft DHCP servers. .DESCRIPTION Creats a CQD compatible building file from Microsoft DHCP servers. Retrieves IPv4 DHCP scopes from Msft DHCP servers and turns them into CQD building information. The result is stored in an output file for validation and upload to CQD. The script can be run locally on a DHCP Server, or used to retrieve data from a specific server, or it can iterate through all authorized DHCP servers in AD. Requires Remote Server Administration Tools for DHCP to be installed on the local machine. This script requires the DHCPServer PowerShell module and read permissions to the DHCP service. The Scope name is used as the building name. The results file is already in a CQD compatible format. After manual validation for accuracy it can be uploaded into CQD without any further reformatting. .PARAMETER EnabledOnly Include only enabled (active) IPv4 DHCP scopes. .PARAMETER IterateThroughAllServers Find all authorized DHCP servers in Active Directory, retrieve IPv4 DHCP scope information from all servers found. Cannot be used together with the DHCPServer parameter. .PARAMETER DHCPServer Used to specify a specific DHCP server to retrieve IPv4 DHCP scopes from. Cannot be used together with the IterateThroughAllServers parameter. .PARAMETER OutputFileName Specifies the name of the outputfile containing the subnet mapping data. .EXAMPLE Get-BuildingMappingFromMsftDHCP Gets IPv4 DHCP scopes from a DHCP server, assumes that this script is run on the DHCP server itself. .EXAMPLE Get-BuildingMappingFromMsftDHCP -IterateThroughAllServers -EnabledOnly Gets IPv4 DHCP scopes from all authorized DHCP servers in AD. Includes enalbed IPv4 DHCP scopes only. .NOTES © 2018 Microsoft Corporation.  All rights reserved.  This document is provided "as-is." Information and views expressed in this document, including URL and other Internet Web site references, may change without notice. This document does not provide you with any legal rights to any intellectual property in any Microsoft product. Skype for Business and Microsoft Teams customers and partners may copy, use and share these materials for planning, deployment and operation of Skype for Business and Microsoft Teams. This sript requires permissions to read data from DHCP, i.e. DHCP Users or higher. #> [CmdletBinding()] param ( [Parameter(Mandatory=$false)] [string]$OutputFileName='.\BuildingFile.csv', [Parameter(Mandatory=$false)] [switch]$EnabledOnly, [Parameter(Mandatory=$false)] [switch]$AllADAuthorizedDHCPServers, [Parameter(Mandatory=$false)] [string]$DHCPServer='localhost', [Parameter(Mandatory=$false)] [string] $VerboseLogFileName = 'VerboseOutput.Log' ) Write-VerboseLog -Message ('Function Invocation: {0}' -f ($MyInvocation.BoundParameters | out-string)) Write-VerboseLog -Message ('Module Version: {0}' -f (get-module CQDTools | out-string )) # You should only have AllADAuthorizedDHCPServer or provided a list of servers, not both. if($AllADAuthorizedDHCPServers -and ($DHCPServer -ne 'localhost')) { Write-ScriptError -Message 'Invalid parameter combination. You cannot set both AllADAuthorizedDHCPServers and provide a DHCP Server list. Please select only one of the two parameters' -Terminating $true } if (!(Get-Module DhcpServer -ListAvailable)) { # module not found Write-ScriptError 'Cannot find required module DhcpServer, please install DHCP RSAT components and try again.' -Terminating $true } $DHCPScopes =@() # AllADAuthorizedDHCPServers switch is set, we should iterate through all servers if($AllADAuthorizedDHCPServers) { Write-Host Write-Host 'Getting DHCP Servers from AD' Write-Host Write-VerboseLog -Message 'Getting DHCP Servers from AD' $DHCPServer = '' $DHCPServer = (get-DHCPServerInDC).DnsName } # Do we have any DHCP servers to process? if($DHCPServer.Length -lt 1) { Write-ScriptError -Message 'No DHCP servers found, nothing to do here.' -Terminating $true } # Get Scopes from MSFT DHCP Servers server list Write-Host ('Retrieving IPv4 DHCP Scopes from {0} DHCP servers.' -f $DHCPServer.count) Write-VerboseLog -Message ('Serverlist: {0}' -f ($DHCPServer | Out-String) ) $DHCPScopes += $DHCPServer | Get-v4ScopeFromMsftDHCPServer Write-VerboseLog -Message ('Found {0} scopes' -f $DHCPScopes.count) if($DHCPScopes.count -lt 1) { Write-ScriptError -Message 'No DHCP scopes found, please check for previous errors. Possible reasons: Not running locally on the DHCP server and no server name or -AllADAuthorizedDHCPServers parameter provided, or no permissions to read, or no connectivity to DHCP servers.' -Terminating $true } # remove non-enabled DHCP Scopes if needed if($EnabledOnly) { $DHCPScopes = $DHCPScopes | Where-Object {$_.State -eq "Active"} Write-VerboseLog -Message ('Removed inactive scopes, new scope count: {0}' -f $DHCPScopes.count) } Write-VerboseLog -Message 'Formatting data for CQD' $SubnetMappingFile =@() $SubnetMappingFile = Convert-ToCQDBuildingFormat -InputObject $DHCPScopes -Network 'ScopeID' -NetworkRange 'SubnetMask' -BuildingName 'Name' Write-Host ('Writing file to path: {0}' -f $OutputFileName) Write-VerboseLog -Message ('Writing file to path: {0}' -f $OutputFileName) Save-CQDBuildingFile -CQDBuildingFile $SubnetMappingFile -ExportFilePath $OutputFileName # Check for duplicates and overlap Write-Verbose -Message ('Calling Get-BuildingMappingFromADSites to check for dupliactes in file {0}' -f $OutputFileName ) Get-BuildingMappingFromADSites -InputFileName $OutputFileName } function Convert-ToCQDBuildingFormat { [CmdletBinding()] param ( [Parameter(Mandatory=$true)] [array]$InputObject, [Parameter(Mandatory=$true)] [string]$Network, [Parameter(Mandatory=$false)] [string]$NetworkName, [Parameter(Mandatory=$true)] [string]$NetworkRange, [Parameter(Mandatory=$true)] [string]$BuildingName, [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)] [string]$InsideCorp=1, [Parameter(Mandatory=$false)] [string]$ExpressRoute=0 ) $CQDBuildingFile = @() foreach($subnet in $InputObject) { $CQDSubnetMapping = new-object CQDBuildingFile $CQDSubnetMapping.Network = $subnet.$Network $CQDSubnetMapping.NetworkName = $subnet.$NetworkName if(([string]$subnet.$NetworkRange).contains(".")) { #Network range is dotted, need to convert into bitmask $NetmaskBinary = DottedToBinary($subnet.$NetworkRange) $NetmaskBinary = $NetmaskBinary.trim("0") $NetmaskBits = $NetmaskBinary.Length $CQDSubnetMapping.NetworkRange = $NetmaskBits } else { #Network Range is a bitmask already, nothing to convert $CQDSubnetMapping.NetworkRange = $subnet.$NetworkRange } $CQDSubnetMapping.BuildingName = $subnet.$BuildingName $CQDSubnetMapping.OwnershipType = $subnet.$OwnershipType $CQDSubnetMapping.BuildingType = $subnet.$BuildingType $CQDSubnetMapping.BuildingOfficeType = $subnet.$BuildingOfficeType $CQDSubnetMapping.City = $subnet.$City $CQDSubnetMapping.ZipCode = $subnet.$ZipCode $CQDSubnetMapping.Country = $subnet.$Country $CQDSubnetMapping.State = $subnet.$State $CQDSubnetMapping.Region = $subnet.$Region if($InsideCorp -eq 1 -or $InsideCorp -eq 0) { $CQDSubnetMapping.InsideCorp = $InsideCorp } else { $CQDSubnetMapping.InsideCorp = $subnet.$InsideCorp } if($ExpressRoute -eq 1 -or $InsideCorp -eq 0) { $CQDSubnetMapping.ExpressRoute = $ExpressRoute } else { $CQDSubnetMapping.ExpressRoute = $subnet.$ExpressRoute } $CQDBuildingFile += $CQDSubnetMapping } return $CQDBuildingFile } function Get-BuildingMappingFromADSites { <# .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 Get-BuildingMappingFromADSites 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 Get-BuildingMappingFromADSites Read AD site and subnet information and create building mapping file. .EXAMPLE Get-BuildingMappingFromADSites -BuildingNameSource SubnetDescription Read AD site and subnet information and create building mapping file, uses site description as a name for the building. .EXAMPLE Get-BuildingMappingFromADSites -OutputFileName 'MyFile.csv' -BuildingOfficeType 'CompanyOwned' Read AD site and subnet information, use OutputFileName MyFile.csv and assign BuildingOfficeType = CompanyOwned .EXAMPLE Get-BuildingMappingFromADSites -ExpressRoute Read AD site and subnet information, mark all sites to be connected via ExpressRoute .Example Get-BuildingMappingFromADSites -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 © 2018 Microsoft Corporation.  All rights reserved.  This document is provided "as-is." Information and views expressed in this document, including URL and other Internet Web site references, may change without notice. This document does not provide you with any legal rights to any intellectual property in any Microsoft product. Skype for Business and Microsoft Teams customers and partners may copy, use and share these materials for planning, deployment and operation of Skype for Business and Microsoft Teams. 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, [Parameter(Mandatory=$false)] [string] $VerboseLogFileName = 'VerboseOutput.Log' ) Write-VerboseLog -Message ('Function Invocation: {0}' -f ($MyInvocation.BoundParameters | out-string)) Write-VerboseLog -Message ('Module Version: {0}' -f (get-module CQDTools | out-string )) #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-ScriptError -message ('File {0} already exists, please remove existing file.' -f $ExistingFile.FullName) -Terminating $true } 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-ScriptError ('No IPv4 Subnets in Forest {0} found.' -f [System.DirectoryServices.ActiveDirectory.Forest]::GetCurrentForest().Name) -Terminating $true } } else { # file found, lets import Write-VerboseLog -Message "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 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-VerboseLog -Message "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-VerboseLog -Message ('{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-VerboseLog "No overlapping or duplicate subnets found." Write-Host } # Export the file only if no input file was given. if([string]::IsNullOrEmpty($InputFileName)) { Write-VerboseLog -Message ('Writing {0} rows of CQD building file to: {1}' -f $SubnetMappingFile.count, $OutputFileName) Save-CQDBuildingFile -CQDBuildingFile $SubnetMappingFile -ExportFilePath $OutputFileName } Write-Host Write-Host 'Please validate the accuracy, adjust and complete as needed.' -ForegroundColor Green Write-Host 'Upload the file to CQD at https://cqd.lync.com to complete the process.' Write-Host #endregion } function Save-CQDBuildingFile { [CmdletBinding()] param ( [Parameter(Mandatory=$true)] [array]$CQDBuildingFile, [Parameter(Mandatory=$true)] [string]$ExportFilePath ) # Convert into CSV format $CQDBuildingFile = $CQDBuildingFile | convertto-csv -NoTypeInformation -Delimiter ',' # CQD doesn't expect quotes in the text, so we have to remove them $CQDBuildingFile = $CQDBuildingFile.Replace('"','') Write-Host ('Saving building mapping file: {0}' -f $ExportFilePath ) -ForegroundColor Green # CDQ also doesn't expect column headings, so we remove them and save the remaining content to disk $CQDBuildingFile[1..$CQDBuildingFile.Length] | Set-Content -Path $ExportFilePath -ErrorAction Stop } function Find-MissingBuildingsInCQD { <# .SYNOPSIS Helps you with CQD building mapping maintenance. Finds unmapped subnets in CQD that show the same public IP as seen for mapped subnets, reports found subnets. .DESCRIPTION Gets stream count for all subnets from CQD including public IP (second reflexive local IP) and second building. Checks all unmapped subnets (subnets with no building information) if the public IP is also seen for a mapped subnet. It is very likely that those unmapped subnets actually belong the the same building(s). Found subnets, streamcount and matching buildings are reported. The Results of this script can be used to enhance and further complete the CQD building mapping file. .PARAMETER ResultsFilePath Path and filename for the Results. Default: .\PotentialMissedSubnets.csv .PARAMETER Days Number of days for which data is returned from CQD. Default: 28 days. .PARAMETER DisplayResults If specified, all Results are shown after the script completes, in addition to saving all Results in the Results file. .EXAMPLE Find-MissingBuildingsInCQD Connects to CQD, reads data for the past 28 days (plus 2 day offset to account for CQD data delay), analyses Results and reports out any subnets that seem to be missing from the CQD building file. .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 © 2018 Microsoft Corporation.  All rights reserved.  This document is provided "as-is." Information and views expressed in this document, including URL and other Internet Web site references, may change without notice. This document does not provide you with any legal rights to any intellectual property in any Microsoft product. Skype for Business and Microsoft Teams customers and partners may copy, use and share these materials for planning, deployment and operation of Skype for Business and Microsoft Teams. Will prompt for credentials to read data from CQD. Provide a user with CQD access, a member of any of these O365 admin roles work: Report Reader, Skype Admin, Tenant Admin. #> [cmdletbinding()] param( [Parameter(Mandatory=$false)] [string]$ResultsFilePath='.\PotentialMissedSubnets.csv', [Parameter(Mandatory=$false)] [int16]$Days=28, [Parameter(Mandatory=$false)] [switch]$DisplayResults, [Parameter(Mandatory=$false)] [string] $VerboseLogFileName = 'VerboseOutput.Log' ) Write-VerboseLog -Message ('Function Invocation: {0}' -f ($MyInvocation.BoundParameters | out-string)) Write-VerboseLog -Message ('Module Version: {0}' -f (get-module CQDTools | out-string )) #region define variables and prepare for execution #check for CQDPowerShell module to read data from CQD if (!(Get-Module CQDPowershell -ListAvailable)) { # module not found Write-Warning 'Cannot find required module CQDPowerShell, trying to install in current user context...' try { Install-Module CQDPowerShell -ErrorAction Stop -Scope CurrentUser } catch { Write-Error 'Could not install PowerShell module CQDPowerShell. This module is required for this action. Please install module manually from PowerShell Gallery or run Install-Module CQDPowerShell.' break } } $Dimensions = @() $Dimensions += 'SecondTenantDataBuilding.Second Building Name' $Dimensions += 'AllStreams.Second Subnet' $Dimensions += 'AllStreams.Second Reflexive Local IP' New-Variable -Name Measures -Value 'Measures.Total Stream Count' New-Variable -Name GroupProperty -Value 'Second Building Name' New-Variable -Name OffsetDays -Value 2 New-Variable -Name StartDate -Value (Get-Date).AddDays(-$Offsetdays-$days) New-Variable -Name EndDate -Value (Get-Date).AddDays(-$Offsetdays) New-Variable -Name I $MappedSecondReflexiveIPs = @() $SecondReflexiveIPsArray = @() $SecondReflexiveIPsHash = @{} $CQDUnmanagedRows =@() $Results = @() # Cannot process Switch parameter during export, have to convert it into bool. if($DisplayResults) { [bool]$DisplayResults = $true } else { [bool]$DisplayResults = $false } #endregion #region main script Write-Host "Establishing connection to CQD. If prompted, please enter credentials with permissions to access CQD - a member of the Report Reader, Skype admin, or Tenant admin role." Write-Host "Reading Data from CQD might take a while, please be patient." # Getting data from CQD # clean-up data to remove rows with 2nd reflexive IP set to '0.0.0.' or with Second Subnet being empty, set to <null>, or containing ':' try{ $CQDData = get-CQDData -Dimensions $Dimensions -Measures $Measures -Transport 'UDP' -StartDate $StartDate -EndDate $EndDate -OutputType DataTable -ErrorAction Stop | Where-Object {` $_.'Second Subnet' -notlike '' ` -and $_.'Second Reflexive Local IP' -notlike '0.0.0.' ` -and $_.'Second Reflexive Local IP' -notlike '' ` -and $_.'Second Reflexive Local IP' -notlike '<null>' ` -and $_.'Second Reflexive Local IP' -notlike '*:*' ` } } catch { Write-ScriptError -Message 'Unable to connect to CQD' -ErrorObject $_ -Terminating $true } Write-VerboseLog -Message ('Retrieved {0} rows from CQD' -f $CQDData.Count) # Group by Second Building Name Write-VerboseLog -Message 'Group by Second Building Name' $MappedElements = $CQDData | Group-Object -Property $GroupProperty -AsHashTable Write-VerboseLog -Message ('Found buildings: {0} Buildings' -f $MappedElements.count) Write-VerboseLog -Message ('Found buildings: {0}' -f (($MappedElements.GetEnumerator() | Select-Object -Property 'Name' -ExpandProperty 'Name' | Sort-Object -Property 'Name') -join ',') ) # Remove element with no mapping, this leaves us with a hash table for containing all mapped subnets Write-VerboseLog -Message 'Find elements that have mappings' $MappedElements.Remove('') Write-VerboseLog -Message ('New building count: {0}' -f $MappedElements.count) Write-VerboseLog -Message ('Found buildings: {0}' -f (($MappedElements.GetEnumerator() | Select-Object -Property 'Name' -ExpandProperty 'Name' | Sort-Object -Property 'Name') -join ',') ) #Get unique list of second reflexive IPs for mapped subnets Write-VerboseLog -Message 'Get unique list of second reflexive IPs for mapped subnets' # There is a -unique parameter for the select-object cmdlet, however this takes *forever* if you have large amounts of data. Large being >10k records. So we're using a different route. But that is much faster. # loop through Hashtable for mapped sites and collect all second reflexive IPs. The hash table will contain a unique list of IPs. foreach ($site in $MappedElements.GetEnumerator()) { # built collection of all second reflexive IPs that don't contain a ':' - those are IPv6 addresses, we don't need them. $SecondReflexiveIPsArray += $site.Value.'second reflexive local ip' | Where-Object {$_ -notlike '*:*' } } # Turning the Array into a hash will get us the unique list of IPs $SecondReflexiveIPsHash = $SecondReflexiveIPsArray | ArrayToHash # now we have a hash list of all second reflexive local IPs, need to flatten this into a single list. foreach($IP in $SecondReflexiveIPsHash.GetEnumerator()) { $MappedSecondReflexiveIPs += $IP.Name } Write-VerboseLog -Message ('Found {0} unique reflexive IPs for mapped subnets.' -f $MappedSecondReflexiveIPs.count) # Find elements that are unmapped, remove all data sets where the Second Reflexive Local IP is not shared with mapped subnets. Write-VerboseLog -Message 'Find elements that are unmapped, remove all data sets where the Second Reflexive Local IP is not shared with mapped subnets. Depending on the amount of data, this might take quite a while.' if($CQDData.count -gt 1000) { Write-Host ('Preparing {0} records for compare, this might take a while' -f $CQDData.count) Write-VerboseLog -Message ('Preparing {0} records for compare, this might take a while' -f $CQDData.count) } $n=0 # a for loop is 5x faster than a foreach for this loop. for($i=0;$i -lt $CQDData.count;$i++) { # nesting if clauses to speed up, we only need to to the costly -in match for a small subset of records if($CQDData[$i].$GroupProperty -eq '') { $n++ if($n -eq '1000'){write-host '.' -nonewline;$n=0} if($CQDData[$i].'Second Reflexive Local IP' -in $MappedSecondReflexiveIPs) { $CQDUnmanagedRows += $CQDData[$i] } } } Write-Host $UnmappedElements = $CQDUnmanagedRows | Group-Object -Property 'Second Reflexive Local IP' -AsHashTable $MappedElementsCount = $MappedElements.Count $i=0 # loop through mappend elements and see if we find a match in the unmapped foreach($MappedElement in $MappedElements.GetEnumerator() | Sort-Object Name) { Write-Progress -Activity "Processing $MappedElementsCount Buildings for unmapped subnets." -Status ('Processing Building {0}' -f $MappedElement.Name) -PercentComplete (($i+1)/$MappedElementsCount*100) Write-VerboseLog -Message ('Processing Building {0}' -f $MappedElement.Name) # Get unique second reflexive local IPs $SecondReflexiveIPs = ($MappedElement.value | Select-Object 'Second Reflexive Local IP' -Unique).'Second Reflexive Local IP' Write-VerboseLog -Message ('Found {0} reflexive IPs: {1}' -f $SecondReflexiveIPs.count, ($SecondReflexiveIPs -join(',') )) # loop through second reflexive IPs and check if we find that in the unmapped elements foreach($SecondReflexiveIP in $SecondReflexiveIPs) { # Get unmapped elements that match the second reflexive IP $PotentialMissedSubnets = @() $PotentialMissedSubnets = $UnmappedElements.$SecondReflexiveIP Write-Debug ('Found {0} subnets for reflexive IP: {1}' -f $PotentialMissedSubnets.count, $SecondReflexiveIP) # loop through all of them and build our Results set foreach($PotentialMissedSubnet in $PotentialMissedSubnets) { $Row = New-Object System.Object $Row | Add-Member -MemberType NoteProperty -Name 'Unmapped Subnet' -Value $PotentialMissedSubnet.'Second Subnet' $Row | Add-Member -MemberType NoteProperty -Name 'Second Reflexive Local IP' -Value $PotentialMissedSubnet.'Second Reflexive Local IP' $Row | Add-Member -MemberType NoteProperty -Name 'Total Stream Count' -Value $PotentialMissedSubnet.'Total Stream Count' $Row | Add-Member -MemberType NoteProperty -Name 'Potentially missed in' -Value $MappedElement.Name $Results += $Row } } $i++ } # Done with the compare, did we find any unmapped elements? if ($Results.Length -eq 0) { # no Write-Host "Did not detect any unmapped subnets using the same public IP (second reflexive local IP) as any mapped subnet." -ForegroundColor Green Write-VerboseLog -Message 'Did not detect any unmapped subnets using the same public IP (second reflexive local IP) as any mapped subnet.' } else { # yes # apply some formatting, and merge multiple matches for a single unmapped subnet into a single row. $Results = $Results | Group-Object -Property 'Unmapped Subnet','Second Reflexive Local IP' $Results = $Results | Select-Object ` @{N='Unmapped Subnet';E={$_.Name.split(',')[0]}},` @{N='Second Reflexive Local IP';E={$_.Name.split(',')[1]}},` @{N='Sites sharing public IP';E={$_.Group.'Potentially missed in' -join ','}},` @{N='Streamcount';E={$_.Group.'Total Stream Count' | Select-Object -unique}} Write-Host ('Found {0} subnets that are potentially missing in the building mapping file' -f $Results.Length) -ForegroundColor Green Write-VerboseLog -Message ('Found {0} subnets that are potentially missing in the building mapping file' -f $Results.Length) Write-Host "Saving Results to $ResultsFilePath" Write-VerboseLog -Message "Saving Results to $ResultsFilePath" if($DisplayResults){$Results} $Results | Export-Csv -NoTypeInformation -Path $ResultsFilePath } #endregion } # SIG # Begin signature block # MIIkAgYJKoZIhvcNAQcCoIIj8zCCI+8CAQExDzANBglghkgBZQMEAgEFADB5Bgor # BgEEAYI3AgEEoGswaTA0BgorBgEEAYI3AgEeMCYCAwEAAAQQH8w7YFlLCE63JNLG # KX7zUQIBAAIBAAIBAAIBAAIBADAxMA0GCWCGSAFlAwQCAQUABCD8n2CqMm6lRMKj # GYo0Mh2z4f8wOJp2r1083nKxrzI2mqCCDYMwggYBMIID6aADAgECAhMzAAAAxOmJ # +HqBUOn/AAAAAADEMA0GCSqGSIb3DQEBCwUAMH4xCzAJBgNVBAYTAlVTMRMwEQYD # VQQIEwpXYXNoaW5ndG9uMRAwDgYDVQQHEwdSZWRtb25kMR4wHAYDVQQKExVNaWNy # b3NvZnQgQ29ycG9yYXRpb24xKDAmBgNVBAMTH01pY3Jvc29mdCBDb2RlIFNpZ25p # bmcgUENBIDIwMTEwHhcNMTcwODExMjAyMDI0WhcNMTgwODExMjAyMDI0WjB0MQsw # CQYDVQQGEwJVUzETMBEGA1UECBMKV2FzaGluZ3RvbjEQMA4GA1UEBxMHUmVkbW9u # ZDEeMBwGA1UEChMVTWljcm9zb2Z0IENvcnBvcmF0aW9uMR4wHAYDVQQDExVNaWNy # b3NvZnQgQ29ycG9yYXRpb24wggEiMA0GCSqGSIb3DQEBAQUAA4IBDwAwggEKAoIB # AQCIirgkwwePmoB5FfwmYPxyiCz69KOXiJZGt6PLX4kvOjMuHpF4+nypH4IBtXrL # GrwDykbrxZn3+wQd8oUK/yJuofJnPcUnGOUoH/UElEFj7OO6FYztE5o13jhwVG87 # 7K1FCTBJwb6PMJkMy3bJ93OVFnfRi7uUxwiFIO0eqDXxccLgdABLitLckevWeP6N # +q1giD29uR+uYpe/xYSxkK7WryvTVPs12s1xkuYe/+xxa8t/CHZ04BBRSNTxAMhI # TKMHNeVZDf18nMjmWuOF9daaDx+OpuSEF8HWyp8dAcf9SKcTkjOXIUgy+MIkogCy # vlPKg24pW4HvOG6A87vsEwvrAgMBAAGjggGAMIIBfDAfBgNVHSUEGDAWBgorBgEE # AYI3TAgBBggrBgEFBQcDAzAdBgNVHQ4EFgQUy9ZihM9gOer/Z8Jc0si7q7fDE5gw # UgYDVR0RBEswSaRHMEUxDTALBgNVBAsTBE1PUFIxNDAyBgNVBAUTKzIzMDAxMitj # ODA0YjVlYS00OWI0LTQyMzgtODM2Mi1kODUxZmEyMjU0ZmMwHwYDVR0jBBgwFoAU # SG5k5VAF04KqFzc3IrVtqMp1ApUwVAYDVR0fBE0wSzBJoEegRYZDaHR0cDovL3d3 # dy5taWNyb3NvZnQuY29tL3BraW9wcy9jcmwvTWljQ29kU2lnUENBMjAxMV8yMDEx # LTA3LTA4LmNybDBhBggrBgEFBQcBAQRVMFMwUQYIKwYBBQUHMAKGRWh0dHA6Ly93 # d3cubWljcm9zb2Z0LmNvbS9wa2lvcHMvY2VydHMvTWljQ29kU2lnUENBMjAxMV8y # MDExLTA3LTA4LmNydDAMBgNVHRMBAf8EAjAAMA0GCSqGSIb3DQEBCwUAA4ICAQAG # Fh/bV8JQyCNPolF41+34/c291cDx+RtW7VPIaUcF1cTL7OL8mVuVXxE4KMAFRRPg # mnmIvGar27vrAlUjtz0jeEFtrvjxAFqUmYoczAmV0JocRDCppRbHukdb9Ss0i5+P # WDfDThyvIsoQzdiCEKk18K4iyI8kpoGL3ycc5GYdiT4u/1cDTcFug6Ay67SzL1BW # XQaxFYzIHWO3cwzj1nomDyqWRacygz6WPldJdyOJ/rEQx4rlCBVRxStaMVs5apao # pIhrlihv8cSu6r1FF8xiToG1VBpHjpilbcBuJ8b4Jx/I7SCpC7HxzgualOJqnWmD # oTbXbSD+hdX/w7iXNgn+PRTBmBSpwIbM74LBq1UkQxi1SIV4htD50p0/GdkUieeN # n2gkiGg7qceATibnCCFMY/2ckxVNM7VWYE/XSrk4jv8u3bFfpENryXjPsbtrj4Ns # h3Kq6qX7n90a1jn8ZMltPgjlfIOxrbyjunvPllakeljLEkdi0iHv/DzEMQv3Lz5k # pTdvYFA/t0SQT6ALi75+WPbHZ4dh256YxMiMy29H4cAulO2x9rAwbexqSajplnbI # vQjE/jv1rnM3BrJWzxnUu/WUyocc8oBqAU+2G4Fzs9NbIj86WBjfiO5nxEmnL9wl # iz1e0Ow0RJEdvJEMdoI+78TYLaEEAo5I+e/dAs8DojCCB3owggVioAMCAQICCmEO # kNIAAAAAAAMwDQYJKoZIhvcNAQELBQAwgYgxCzAJBgNVBAYTAlVTMRMwEQYDVQQI # EwpXYXNoaW5ndG9uMRAwDgYDVQQHEwdSZWRtb25kMR4wHAYDVQQKExVNaWNyb3Nv # ZnQgQ29ycG9yYXRpb24xMjAwBgNVBAMTKU1pY3Jvc29mdCBSb290IENlcnRpZmlj # YXRlIEF1dGhvcml0eSAyMDExMB4XDTExMDcwODIwNTkwOVoXDTI2MDcwODIxMDkw # OVowfjELMAkGA1UEBhMCVVMxEzARBgNVBAgTCldhc2hpbmd0b24xEDAOBgNVBAcT # B1JlZG1vbmQxHjAcBgNVBAoTFU1pY3Jvc29mdCBDb3Jwb3JhdGlvbjEoMCYGA1UE # AxMfTWljcm9zb2Z0IENvZGUgU2lnbmluZyBQQ0EgMjAxMTCCAiIwDQYJKoZIhvcN # AQEBBQADggIPADCCAgoCggIBAKvw+nIQHC6t2G6qghBNNLrytlghn0IbKmvpWlCq # uAY4GgRJun/DDB7dN2vGEtgL8DjCmQawyDnVARQxQtOJDXlkh36UYCRsr55JnOlo # XtLfm1OyCizDr9mpK656Ca/XllnKYBoF6WZ26DJSJhIv56sIUM+zRLdd2MQuA3Wr # aPPLbfM6XKEW9Ea64DhkrG5kNXimoGMPLdNAk/jj3gcN1Vx5pUkp5w2+oBN3vpQ9 # 7/vjK1oQH01WKKJ6cuASOrdJXtjt7UORg9l7snuGG9k+sYxd6IlPhBryoS9Z5JA7 # La4zWMW3Pv4y07MDPbGyr5I4ftKdgCz1TlaRITUlwzluZH9TupwPrRkjhMv0ugOG # jfdf8NBSv4yUh7zAIXQlXxgotswnKDglmDlKNs98sZKuHCOnqWbsYR9q4ShJnV+I # 4iVd0yFLPlLEtVc/JAPw0XpbL9Uj43BdD1FGd7P4AOG8rAKCX9vAFbO9G9RVS+c5 # oQ/pI0m8GLhEfEXkwcNyeuBy5yTfv0aZxe/CHFfbg43sTUkwp6uO3+xbn6/83bBm # 4sGXgXvt1u1L50kppxMopqd9Z4DmimJ4X7IvhNdXnFy/dygo8e1twyiPLI9AN0/B # 4YVEicQJTMXUpUMvdJX3bvh4IFgsE11glZo+TzOE2rCIF96eTvSWsLxGoGyY0uDW # iIwLAgMBAAGjggHtMIIB6TAQBgkrBgEEAYI3FQEEAwIBADAdBgNVHQ4EFgQUSG5k # 5VAF04KqFzc3IrVtqMp1ApUwGQYJKwYBBAGCNxQCBAweCgBTAHUAYgBDAEEwCwYD # VR0PBAQDAgGGMA8GA1UdEwEB/wQFMAMBAf8wHwYDVR0jBBgwFoAUci06AjGQQ7kU # BU7h6qfHMdEjiTQwWgYDVR0fBFMwUTBPoE2gS4ZJaHR0cDovL2NybC5taWNyb3Nv # ZnQuY29tL3BraS9jcmwvcHJvZHVjdHMvTWljUm9vQ2VyQXV0MjAxMV8yMDExXzAz # XzIyLmNybDBeBggrBgEFBQcBAQRSMFAwTgYIKwYBBQUHMAKGQmh0dHA6Ly93d3cu # bWljcm9zb2Z0LmNvbS9wa2kvY2VydHMvTWljUm9vQ2VyQXV0MjAxMV8yMDExXzAz # XzIyLmNydDCBnwYDVR0gBIGXMIGUMIGRBgkrBgEEAYI3LgMwgYMwPwYIKwYBBQUH # AgEWM2h0dHA6Ly93d3cubWljcm9zb2Z0LmNvbS9wa2lvcHMvZG9jcy9wcmltYXJ5 # Y3BzLmh0bTBABggrBgEFBQcCAjA0HjIgHQBMAGUAZwBhAGwAXwBwAG8AbABpAGMA # eQBfAHMAdABhAHQAZQBtAGUAbgB0AC4gHTANBgkqhkiG9w0BAQsFAAOCAgEAZ/KG # pZjgVHkaLtPYdGcimwuWEeFjkplCln3SeQyQwWVfLiw++MNy0W2D/r4/6ArKO79H # qaPzadtjvyI1pZddZYSQfYtGUFXYDJJ80hpLHPM8QotS0LD9a+M+By4pm+Y9G6XU # tR13lDni6WTJRD14eiPzE32mkHSDjfTLJgJGKsKKELukqQUMm+1o+mgulaAqPypr # WEljHwlpblqYluSD9MCP80Yr3vw70L01724lruWvJ+3Q3fMOr5kol5hNDj0L8giJ # 1h/DMhji8MUtzluetEk5CsYKwsatruWy2dsViFFFWDgycScaf7H0J/jeLDogaZiy # WYlobm+nt3TDQAUGpgEqKD6CPxNNZgvAs0314Y9/HG8VfUWnduVAKmWjw11SYobD # HWM2l4bf2vP48hahmifhzaWX0O5dY0HjWwechz4GdwbRBrF1HxS+YWG18NzGGwS+ # 30HHDiju3mUv7Jf2oVyW2ADWoUa9WfOXpQlLSBCZgB/QACnFsZulP0V3HjXG0qKi # n3p6IvpIlR+r+0cjgPWe+L9rt0uX4ut1eBrs6jeZeRhL/9azI2h15q/6/IvrC4Dq # aTuv/DDtBEyO3991bWORPdGdVk5Pv4BXIqF4ETIheu9BCrE/+6jMpF3BoYibV3FW # TkhFwELJm3ZbCoBIa/15n8G9bW1qyVJzEw16UM0xghXVMIIV0QIBATCBlTB+MQsw # CQYDVQQGEwJVUzETMBEGA1UECBMKV2FzaGluZ3RvbjEQMA4GA1UEBxMHUmVkbW9u # ZDEeMBwGA1UEChMVTWljcm9zb2Z0IENvcnBvcmF0aW9uMSgwJgYDVQQDEx9NaWNy # b3NvZnQgQ29kZSBTaWduaW5nIFBDQSAyMDExAhMzAAAAxOmJ+HqBUOn/AAAAAADE # MA0GCWCGSAFlAwQCAQUAoIHIMBkGCSqGSIb3DQEJAzEMBgorBgEEAYI3AgEEMBwG # CisGAQQBgjcCAQsxDjAMBgorBgEEAYI3AgEVMC8GCSqGSIb3DQEJBDEiBCB/nuSw # KOqdiMfgIxS/KXzqmJJTk3JQOAiShr5SjM31vDBcBgorBgEEAYI3AgEMMU4wTKAS # gBAAQwBRAEQAVABvAG8AbABzoTaANGh0dHBzOi8vd3d3LnBvd2Vyc2hlbGxnYWxs # ZXJ5LmNvbS9wYWNrYWdlcy9DUURUb29scyAwDQYJKoZIhvcNAQEBBQAEggEAUVHN # QvegOUfu8eF+kn9WWnK0zbfGze+236aaNKZtTtV2kSikLLhX8Xbwt3Iq5E37pLbU # wgGU4kW8NGT+vNDPGzdGFuB00/zy5Q30TXC4x00GHTA5vGFVBYYKKsZjOjtMIt2i # dTyAvFA5tCvvedQl0GgFYRtE9cRfR51tNUiwkXZJTdxdBqPn0wYib5JVisD/2nlo # cksSNOmSHgDKtOjPKnHVvRbvAobJe0m0Q5mzHYwiAfrp4MUXxaM07tm3OBlO4P4K # QCoN08n6GUwXM9pa2KcLbu6UHk+ICsjjdgMREwvHt5/KeOtX7hdJKbHzGMicJeV+ # bMs8iiRLJe8r1xqAoqGCE0UwghNBBgorBgEEAYI3AwMBMYITMTCCEy0GCSqGSIb3 # DQEHAqCCEx4wghMaAgEDMQ8wDQYJYIZIAWUDBAIBBQAwggE6BgsqhkiG9w0BCRAB # BKCCASkEggElMIIBIQIBAQYKKwYBBAGEWQoDATAxMA0GCWCGSAFlAwQCAQUABCBC # B/RMtFsnCxzs+GuWv1zuak4ny6S/a5Y1U8NKzIYcXQIGWylci2rbGBIyMDE4MDYy # NjE2MDA1Mi40OFowBwIBAYACAfSggbekgbQwgbExCzAJBgNVBAYTAlVTMRMwEQYD # VQQIEwpXYXNoaW5ndG9uMRAwDgYDVQQHEwdSZWRtb25kMR4wHAYDVQQKExVNaWNy # b3NvZnQgQ29ycG9yYXRpb24xDDAKBgNVBAsTA0FPQzEmMCQGA1UECxMdVGhhbGVz # IFRTUyBFU046NzBERC00QjVCLTQ1NjgxJTAjBgNVBAMTHE1pY3Jvc29mdCBUaW1l # LVN0YW1wIFNlcnZpY2Wggg7LMIIGcTCCBFmgAwIBAgIKYQmBKgAAAAAAAjANBgkq # hkiG9w0BAQsFADCBiDELMAkGA1UEBhMCVVMxEzARBgNVBAgTCldhc2hpbmd0b24x # EDAOBgNVBAcTB1JlZG1vbmQxHjAcBgNVBAoTFU1pY3Jvc29mdCBDb3Jwb3JhdGlv # bjEyMDAGA1UEAxMpTWljcm9zb2Z0IFJvb3QgQ2VydGlmaWNhdGUgQXV0aG9yaXR5 # IDIwMTAwHhcNMTAwNzAxMjEzNjU1WhcNMjUwNzAxMjE0NjU1WjB8MQswCQYDVQQG # EwJVUzETMBEGA1UECBMKV2FzaGluZ3RvbjEQMA4GA1UEBxMHUmVkbW9uZDEeMBwG # A1UEChMVTWljcm9zb2Z0IENvcnBvcmF0aW9uMSYwJAYDVQQDEx1NaWNyb3NvZnQg # VGltZS1TdGFtcCBQQ0EgMjAxMDCCASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoC # ggEBAKkdDbx3EYo6IOz8E5f1+n9plGt0VBDVpQoAgoX77XxoSyxfxcPlYcJ2tz5m # K1vwFVMnBDEfQRsalR3OCROOfGEwWbEwRA/xYIiEVEMM1024OAizQt2TrNZzMFcm # gqNFDdDq9UeBzb8kYDJYYEbyWEeGMoQedGFnkV+BVLHPk0ySwcSmXdFhE24oxhr5 # hoC732H8RsEnHSRnEnIaIYqvS2SJUGKxXf13Hz3wV3WsvYpCTUBR0Q+cBj5nf/Vm # wAOWRH7v0Ev9buWayrGo8noqCjHw2k4GkbaICDXoeByw6ZnNPOcvRLqn9NxkvaQB # wSAJk3jN/LzAyURdXhacAQVPIk0CAwEAAaOCAeYwggHiMBAGCSsGAQQBgjcVAQQD # AgEAMB0GA1UdDgQWBBTVYzpcijGQ80N7fEYbxTNoWoVtVTAZBgkrBgEEAYI3FAIE # DB4KAFMAdQBiAEMAQTALBgNVHQ8EBAMCAYYwDwYDVR0TAQH/BAUwAwEB/zAfBgNV # HSMEGDAWgBTV9lbLj+iiXGJo0T2UkFvXzpoYxDBWBgNVHR8ETzBNMEugSaBHhkVo # dHRwOi8vY3JsLm1pY3Jvc29mdC5jb20vcGtpL2NybC9wcm9kdWN0cy9NaWNSb29D # ZXJBdXRfMjAxMC0wNi0yMy5jcmwwWgYIKwYBBQUHAQEETjBMMEoGCCsGAQUFBzAC # hj5odHRwOi8vd3d3Lm1pY3Jvc29mdC5jb20vcGtpL2NlcnRzL01pY1Jvb0NlckF1 # dF8yMDEwLTA2LTIzLmNydDCBoAYDVR0gAQH/BIGVMIGSMIGPBgkrBgEEAYI3LgMw # gYEwPQYIKwYBBQUHAgEWMWh0dHA6Ly93d3cubWljcm9zb2Z0LmNvbS9QS0kvZG9j # cy9DUFMvZGVmYXVsdC5odG0wQAYIKwYBBQUHAgIwNB4yIB0ATABlAGcAYQBsAF8A # UABvAGwAaQBjAHkAXwBTAHQAYQB0AGUAbQBlAG4AdAAuIB0wDQYJKoZIhvcNAQEL # BQADggIBAAfmiFEN4sbgmD+BcQM9naOhIW+z66bM9TG+zwXiqf76V20ZMLPCxWbJ # at/15/B4vceoniXj+bzta1RXCCtRgkQS+7lTjMz0YBKKdsxAQEGb3FwX/1z5Xhc1 # mCRWS3TvQhDIr79/xn/yN31aPxzymXlKkVIArzgPF/UveYFl2am1a+THzvbKegBv # SzBEJCI8z+0DpZaPWSm8tv0E4XCfMkon/VWvL/625Y4zu2JfmttXQOnxzplmkIz/ # amJ/3cVKC5Em4jnsGUpxY517IW3DnKOiPPp/fZZqkHimbdLhnPkd/DjYlPTGpQqW # hqS9nhquBEKDuLWAmyI4ILUl5WTs9/S/fmNZJQ96LjlXdqJxqgaKD4kWumGnEcua # 2A5HmoDF0M2n0O99g/DhO3EJ3110mCIIYdqwUB5vvfHhAN/nMQekkzr3ZUd46Pio # SKv33nJ+YWtvd6mBy6cJrDm77MbL2IK0cs0d9LiFAR6A+xuJKlQ5slvayA1VmXqH # czsI5pgt6o3gMy4SKfXAL1QnIffIrE7aKLixqduWsqdCosnPGUFN4Ib5KpqjEWYw # 07t0MkvfY3v1mYovG8chr1m1rtxEPJdQcdeh0sVV42neV8HR3jDA/czmTfsNv11P # 6Z0eGTgvvM9YBS7vDaBQNdrvCScc1bN+NR4Iuto229Nfj950iEkSMIIE2DCCA8Cg # AwIBAgITMwAAALf4IhR9AyL++gAAAAAAtzANBgkqhkiG9w0BAQsFADB8MQswCQYD # VQQGEwJVUzETMBEGA1UECBMKV2FzaGluZ3RvbjEQMA4GA1UEBxMHUmVkbW9uZDEe # MBwGA1UEChMVTWljcm9zb2Z0IENvcnBvcmF0aW9uMSYwJAYDVQQDEx1NaWNyb3Nv # ZnQgVGltZS1TdGFtcCBQQ0EgMjAxMDAeFw0xNzEwMDIyMzAwNTJaFw0xOTAxMDIy # MzAwNTJaMIGxMQswCQYDVQQGEwJVUzETMBEGA1UECBMKV2FzaGluZ3RvbjEQMA4G # A1UEBxMHUmVkbW9uZDEeMBwGA1UEChMVTWljcm9zb2Z0IENvcnBvcmF0aW9uMQww # CgYDVQQLEwNBT0MxJjAkBgNVBAsTHVRoYWxlcyBUU1MgRVNOOjcwREQtNEI1Qi00 # NTY4MSUwIwYDVQQDExxNaWNyb3NvZnQgVGltZS1TdGFtcCBTZXJ2aWNlMIIBIjAN # BgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAtIV2Zy5+zQJdUAsSfGQTNy72V6Da # EzWh4oMtVkjQ3K/Iyj7Fa62+ZK2RJcDPnYtRuN3n4uZNzthEjxtfBPp63WjCa5zq # H/nwsF0S4heF3Uzl0CNoM7cPRMFZWJ3X9Hc3SWeO+9cbZiSYwTQNhpABO5iUD1wL # fklCx4fHyB68D98VcE/C8uDTcCVqs5Z9dVNyZtTUrFDOvGXQqocQbkR9BOKzL5nA # 8MY52p84pO86u9aENniLuBxfwqb/4Xu0RtkSmdrgcJKJvGARHYrlAaBLm5FyUgc0 # S9EGUWy1mmA7PX2DM1VefZIzWJEvN334zjprO+2bIw8QJ2o6XL7m2tieOwIDAQAB # o4IBGzCCARcwHQYDVR0OBBYEFG1ir4N7RIZ1cXfSTVuP7kQuYDVpMB8GA1UdIwQY # MBaAFNVjOlyKMZDzQ3t8RhvFM2hahW1VMFYGA1UdHwRPME0wS6BJoEeGRWh0dHA6 # Ly9jcmwubWljcm9zb2Z0LmNvbS9wa2kvY3JsL3Byb2R1Y3RzL01pY1RpbVN0YVBD # QV8yMDEwLTA3LTAxLmNybDBaBggrBgEFBQcBAQROMEwwSgYIKwYBBQUHMAKGPmh0 # dHA6Ly93d3cubWljcm9zb2Z0LmNvbS9wa2kvY2VydHMvTWljVGltU3RhUENBXzIw # MTAtMDctMDEuY3J0MAwGA1UdEwEB/wQCMAAwEwYDVR0lBAwwCgYIKwYBBQUHAwgw # DQYJKoZIhvcNAQELBQADggEBAGmkqdLrsaKfiuZ7JHu0J+7eeqI2Vog1VRyH2bzb # +wEZTMRAnnTPjv84cEcI4Q/43zx8MFrIIO9OdodSWig8Zweq8SFiZDb1N/lKG7KY # 2kg2mRXUVKU01txA6c1oRrP/kiGpoIPlFGQviVWBeZjAHe2+mHTMVAPmAHfNtzTR # 3sKVJRG96JOkJWn0/SWXjqrvU08wjnC/qdlnjmdPe3XqsfW3jHeOofSlMqYH9vD6 # Y3UjnIFmUZr4llbum+L+OOeOtDTiAxkA8AYaGDrtVzE/ysY50T968uU6vvrOESNE # HoMKcSieo6WBkP12jYY4ZHIdsKy1gvOHiIzqq1OCldjh+GmhggN2MIICXgIBATCB # 4aGBt6SBtDCBsTELMAkGA1UEBhMCVVMxEzARBgNVBAgTCldhc2hpbmd0b24xEDAO # BgNVBAcTB1JlZG1vbmQxHjAcBgNVBAoTFU1pY3Jvc29mdCBDb3Jwb3JhdGlvbjEM # MAoGA1UECxMDQU9DMSYwJAYDVQQLEx1UaGFsZXMgVFNTIEVTTjo3MERELTRCNUIt # NDU2ODElMCMGA1UEAxMcTWljcm9zb2Z0IFRpbWUtU3RhbXAgU2VydmljZaIlCgEB # MAkGBSsOAwIaBQADFQDV49D+WTbkwmRZSQfp1yKMx0XqDaCBwTCBvqSBuzCBuDEL # MAkGA1UEBhMCVVMxEzARBgNVBAgTCldhc2hpbmd0b24xEDAOBgNVBAcTB1JlZG1v # bmQxHjAcBgNVBAoTFU1pY3Jvc29mdCBDb3Jwb3JhdGlvbjEMMAoGA1UECxMDQU9D # MScwJQYDVQQLEx5uQ2lwaGVyIE5UUyBFU046MjY2NS00QzNGLUM1REUxKzApBgNV # BAMTIk1pY3Jvc29mdCBUaW1lIFNvdXJjZSBNYXN0ZXIgQ2xvY2swDQYJKoZIhvcN # AQEFBQACBQDe3JwdMCIYDzIwMTgwNjI2MTEwMzU3WhgPMjAxODA2MjcxMTAzNTda # MHcwPQYKKwYBBAGEWQoEATEvMC0wCgIFAN7cnB0CAQAwCgIBAAICAfwCAf8wBwIB # AAICF1wwCgIFAN7d7Z0CAQAwNgYKKwYBBAGEWQoEAjEoMCYwDAYKKwYBBAGEWQoD # AaAKMAgCAQACAxbjYKEKMAgCAQACAx6EgDANBgkqhkiG9w0BAQUFAAOCAQEAQUaw # MZv5RHlj+j21QNSeFvwRwOwVl44Erq0yzQXSUq6yKXTssn3UQHj+RwebWS3mf6qg # d7s5zviqL1z7gN3/ZhIE7oEDJnDqeEHATmwSX2YuyFEn3iL2W/17XqTK6DGFfHYo # 8RVF73E47ion7UMr+NNKc9ODL1V2TzVGragwjaZXcryAgeiXWojePdxSh9nMfk/r # GHLuH8IGw2TY0hu+Yt+QUnDzS1yxYz+/B4QvnfHzDiiEmLecBgdCPg3TaH6BNXyo # vg+Fuf0WiATvvlCDWdFcrv513sjSKdFBiCln87FSzt5RBsTno1Q10+Rf9xyOGxTi # mXsC1gb57/qSNtg4qjGCAvUwggLxAgEBMIGTMHwxCzAJBgNVBAYTAlVTMRMwEQYD # VQQIEwpXYXNoaW5ndG9uMRAwDgYDVQQHEwdSZWRtb25kMR4wHAYDVQQKExVNaWNy # b3NvZnQgQ29ycG9yYXRpb24xJjAkBgNVBAMTHU1pY3Jvc29mdCBUaW1lLVN0YW1w # IFBDQSAyMDEwAhMzAAAAt/giFH0DIv76AAAAAAC3MA0GCWCGSAFlAwQCAQUAoIIB # MjAaBgkqhkiG9w0BCQMxDQYLKoZIhvcNAQkQAQQwLwYJKoZIhvcNAQkEMSIEIEsv # lxXiusAdVU1Jl4xLhgDT31o25VFaJpSWkemA26rHMIHiBgsqhkiG9w0BCRACDDGB # 0jCBzzCBzDCBsQQU1ePQ/lk25MJkWUkH6dcijMdF6g0wgZgwgYCkfjB8MQswCQYD # VQQGEwJVUzETMBEGA1UECBMKV2FzaGluZ3RvbjEQMA4GA1UEBxMHUmVkbW9uZDEe # MBwGA1UEChMVTWljcm9zb2Z0IENvcnBvcmF0aW9uMSYwJAYDVQQDEx1NaWNyb3Nv # ZnQgVGltZS1TdGFtcCBQQ0EgMjAxMAITMwAAALf4IhR9AyL++gAAAAAAtzAWBBRA # 4wd/AQ+IP+VPsY7GliCUAVLaijANBgkqhkiG9w0BAQsFAASCAQCEkp55P19cKLZ+ # YTnAk2CMgIU0qaCui9rcl3yuwe+vt+IG/1Jzsy9E8lr/m4e5BAMs7RH6iYhMOjvr # rJN9NBBAzWbdmWAwyPV1LOXqYBcGhRVxDsdn6Wbr3wU8Xw1rZCLuJSAnMNHOPXNt # ki5h6ntxUWBozZ+MLrlVmIaVIYmSDLjoslYsc5h3D1XcsDytzUFWbIi4rO7ASn00 # 79OU/8eKz41v+5YaHE9fNh5o3y3NTJ0oG3OscTb5JDp3HiPqCDIwcNiog5TTXW+b # 4MWANxh8+1vwK2fsOGbUiFS6axMGuRsE+luYQNYQsSiUglANr1fFJg2BJx1fKmwU # jd0uj/uG # SIG # End signature block |