Find Un-Migrated Phone Numbers in Cisco Call Manager to Port to Skype for Business / Teams

June 17, 2019 Brad Wyatt Comments 0 Comment

Table of Contents

Currently I am doing a Skype for Business migration for a large enterprise. The current phone system is Cisco Call Manager and I am migrating everything to Skype for Business / Microsoft Teams in Office 365. I am at a point where I have migrated a majority of my local users but I want to make sure I grab all available phone numbers I can. These phone numbers could be left from old users, systems, etc.

ImportExcel PowerShell Module

To make this work easier, I am going to be using the ImportExcel PowerShell module. I could use Export-CSV but then I will end up with several CSV data files to work with, and it can get confusing quick. Installing the module is easy as it is available on the PSGallery.

Gathering the Phone Data

Export Phone Numbers in Skype for Business Portal

By logging into Skype for Business Online via PowerShell I can grab all migrated / available numbers in a single line:

Get-CsOnlineTelephoneNumber | Select-Object FriendlyName,Id,ActivationState,CityCode,Location,O365Region,UserId | Export-Excel C:\Export\Master.xlsx -WorksheetName SkypeNumbers -AutoSize

By using the -WorkSheetName parameter I can specify the data to be dumped to a specific worksheet in a workbook. I will be using this workbook throughout the process which I have named “Master.xlsx” located at C:\Export.

Export Phone Numbers from CUCM

Export Local Users with Phones

My local users have phones with assigned numbers. In CUCM I can export my existing phones / numbers by going to:

Bulk Administration
Phones
Export Phone
All Details

Once the job has ran (which you can monitor in Job Scheduler) I can download the files in Bulk Administration > Upload/Download files. The file is a .txt file but I can save it as a .csv file by using Windows Notepad.

I save this list as exportphones.csv. I then went into it and copied its data and pasted it into my Master.xslx workbook under a new worksheet named, “LocalUsers”.

Export Remote Users with Phones

My remote users do not have phones, so they will not be in the above list. Instead they are set up as CTI Route Points which designates a virtual device that can receive multiple, simultaneous calls for application-controlled redirection. By using a CTI Route Point I can set up forwarding from their work number to their cell phone.

I can export a list of my CTI Route Points in CUCM by going to:

Bulk Administration
Import/Export
Export

I first want to give my exported file a valid name

The ONLY thing I want to run is CTI Route Point, so I check that and then select the “Run Immediately” radio button and then click Submit.

You may get a warning because some dependencies are missing, we can ignore this because we will not use this export as a configuration import at any time.

You can monitor the status of your job in the Job Scheduler. Once it is complete you can download the results by going to Bulk Administration > Upload/Download Files

Once you locate your file, select it and the press “Download Selected”

This file is a TAR file which will open in Notepad and then I just save it as a CSV file.

I then copied the CSV file data and created the worksheet, “RemoteUsers” in my master workbook.

WorkBook Formatting

Once you have your LocalUsers and RemoteUsers worksheets completed, I would format the “Forward All Destination 1” row to Number with 0 decimal places. This should have your external forwarding value and if you include the typical “9” and “1” before the area code it will not format properly.

Data Parsing and Manipulation

The next few sections will walk you through each step of the process, from how I figure out what the Direct Inward Dialing number is, checking if the number has been migrated or not, to figuring out if my user is local or remote. If you are familiar enough with PowerShell and can understand the final script, please feel free to jump down to the bottom where I post the source script. However, I highly recommend reading through the guide.

String Manipulation and Concatenation

So now my goal now is to parse the LocalUsers and RemoteUsers worksheets, combine the external mask (which will be a value like 618203XXXX) value and the extension (which will be a value like 6438) to get the full Direct Inward Dialing (DID) number (618-203-6438). This number will be the number that I will need to port if not ported already.

By using the -split method and splitting the string at the first “X”, selecting the first object, and then concatenating of both strings (external phone mask without the trailing XXXX and the extension), I can create the entire DID number that I need to migrate and store it in a variable.

$Phone = (($var.'EXTERNAL PHONE NUMBER MASK 1') -Split ("X") | select-object -first 1) + $var.'DIRECTORY NUMBER 1'

I can also figure out if my already migrated users are set up properly by querying the “Forward All Destination 1” value and making sure it is not NULL. Once users are migrated from Cisco Call Manager to Skype for Business / Teams, forwarding should be set up so Cisco Call Manager knows to route the calls from users not migrated yet back out and to Office 365.

ExternalForwarding = $var.'Forward All Destination 1'

False Positive Prevention

In my Cisco dumps I noticed that I have quite a few entries with no EXTERNAL PHONE NUMBER MASK 1 value. This value has the first half of our DID that we need to migrate, if this value is not present then there is no DID to migrate. To prevent getting a bunch of garbage data, I will only parse data that has an EXTERNAL PHONE NUMBER MASK 1 value.

If (($var.'EXTERNAL PHONE NUMBER MASK 1').length -gt 0)

Splitting the Results

My two data ‘buckets’ that I will be iterating through are my local users and my remote users worksheets, but I want to split my results into two more items of “Migrated” and “Not Migrated” users / DID’s. To do this I will check the $Phone variable (where I created the full DID by adding the External Mask and the Extension) and doing a lookup against the Skype Numbers worksheet.

If (($SkypePhoneNumbers).ID -match $Phone)

System.Collections.Generic.List

As I parse through the data I will be storing the results in a generic list which will then become my new excel worksheets at the end. I create properties and then supply the value based on the original data. Below we can see the ExternalForwarding property will have a value found in the ‘Forward All Destination 1’ header.

			$Body_LocalMigrated = @(
				[pscustomobject]@{
					Description = $var.DESCRIPTION
					ExternalForwarding = $var.'Forward All Destination 1'
					ExternalMask = $var.'EXTERNAL PHONE NUMBER MASK 1'
					Extension = $var.'DIRECTORY NUMBER 1'
					PhoneNumber = $Phone
				}
			)
			$TableLocalMigrated.Add($Body_LocalMigrated)

At the end, we will export our entire list object into a new worksheet. The one above will contain the items for my local users who have already been migrated to Office 365.

Script

Below is the entire script put together. In the end I will have 4 more worksheets, LocalUsers_AlreadyMigrated, LocalUsers_NotMigrated, RemoteUsers_AlreadyMigrated, and RemoteUsers_NotMigrated.

$TableLocalMigrated = New-Object 'System.Collections.Generic.List[System.Object]'
$TableLocalToMigrate = New-Object 'System.Collections.Generic.List[System.Object]'
$TableRemoteMigrated = New-Object 'System.Collections.Generic.List[System.Object]'
$TableRemoteToMigrate = New-Object 'System.Collections.Generic.List[System.Object]'

$VerbosePreference = "Continue"
$Workbook = Import-Excel -Path "C:\Export\Master.xlsx"
$SkypePhoneNumbers = Import-Excel "C:\Export\Master.xlsx" -WorkSheetName SkypeNumbers

$vars = Import-Excel -Path "C:\Export\Master.xlsx" -WorkSheetName LocalUsers
Foreach ($Var in $Vars)
{
	#Only parse numbers that have an external number and not just internal extensions, cannot migrated numbers that are just local extensions
	If (($var.'EXTERNAL PHONE NUMBER MASK 1').length -gt 0)
	{
		$Phone = (($var.'EXTERNAL PHONE NUMBER MASK 1') -Split ("X") | select-object -first 1) + $var.'DIRECTORY NUMBER 1'
		
		If (($SkypePhoneNumbers).ID -match $Phone)
		{
			Write-Verbose "$Phone is present in skype and has been migrated"
			
			$Body_LocalMigrated = @(
				[pscustomobject]@{
					Description = $var.DESCRIPTION
					ExternalForwarding = $var.'Forward All Destination 1'
					ExternalMask = $var.'EXTERNAL PHONE NUMBER MASK 1'
					Extension = $var.'DIRECTORY NUMBER 1'
					PhoneNumber = $Phone
				}
			)
			$TableLocalMigrated.Add($Body_LocalMigrated)
		}
		Else
		{
			Write-Verbose "$Phone is not present in skype and needs to be migrated"
			
			$Body_LocalToMigrate = @(
				[pscustomobject]@{
					Description = $var.DESCRIPTION
					ExternalForwarding = $var.'Forward All Destination 1'
					ExternalMask = $var.'EXTERNAL PHONE NUMBER MASK 1'
					Extension = $var.'DIRECTORY NUMBER 1'
					PhoneNumber = $Phone
				}
			)
			$TableLocalToMigrate.Add($Body_LocalToMigrate)
		}
	}
}

#CTI Route Points (Remote Users)
$vars = Import-Excel -Path "C:\Export\Master.xlsx" -WorkSheetName RemoteUsers
Foreach ($Var in $Vars)
{
	#Only parse numbers that have an external number and not just internal extensions, cannot migrated numbers that are just local extensions
	If (($var.'EXTERNAL PHONE NUMBER MASK 1').length -gt 0)
	{
		
		$Phone = (($var.'EXTERNAL PHONE NUMBER MASK 1') -Split ("X") | select-object -first 1) + $var.'DIRECTORY NUMBER 1'
		
		If (($SkypePhoneNumbers).ID -match $Phone)
		{
			Write-Verbose "$Phone is present in skype and has been migrated"
			
			$Body_RemoteMigrated = @(
				[pscustomobject]@{
					Description = $var.DESCRIPTION
					ExternalForwarding = $var.'Forward All Destination 1'
					ExternalMask = $var.'EXTERNAL PHONE NUMBER MASK 1'
					Extension = $var.'DIRECTORY NUMBER 1'
					PhoneNumber = $Phone
				}
			)
			$TableRemoteMigrated.Add($Body_RemoteMigrated)
		}
		Else
		{
			Write-Verbose "$Phone is not present in skype and needs to be migrated"
			
			$Body_RemoteToMigrate = @(
				[pscustomobject]@{
					Description = $var.DESCRIPTION
					ExternalForwarding = $var.'Forward All Destination 1'
					ExternalMask = $var.'EXTERNAL PHONE NUMBER MASK 1'
					Extension = $var.'DIRECTORY NUMBER 1'
					PhoneNumber = $Phone
				}
			)
			$TableRemoteToMigrate.Add($Body_RemoteToMigrate)
		}
	}
}

Write-Verbose "EXPORTING RESULTS TO EXCEL"
Write-Verbose "Exporting Local Migrated Users"
$TableLocalMigrated | Export-Excel "C:\Export\Master.xlsx" -WorksheetName LocalUsers_AlreadyMigrated -AutoSize
Write-Verbose "Exporting Local UnMigrated Users"
$TableLocalToMigrate | Export-Excel "C:\Export\Master.xlsx" -WorksheetName LocalUsers_NotMigrated -AutoSize
Write-Verbose "Exporting Remote Migrated Users"
$TableRemoteMigrated | Export-Excel "C:\Export\Master.xlsx" -WorksheetName RemoteUsers_AlreadyMigrated -AutoSize
Write-Verbose "Exporting Remote UnMigrated Users"
$TableRemoteToMigrate | Export-Excel "C:\Export\Master.xlsx" -WorksheetName RemoteUsers_NotMigrated -AutoSize -Append
Write-Verbose "Done!"

LastLogonTimeStamp Lookup

Luckily for me, the DESCRIPTION field in the Cisco Call Manager was set as the users first name and last name. So I would like to leverage the ActiveDirectory module to do a lookup on the user, query the last logon timestamp, do DateMath to figure out how many days ago that was, and then add that to the worksheet.

DisplayName Formatting

The DisplayName in my organization is following a Lastname, FirstName format. Since the Description field in Cisco Call Manager was set up as FirstName LastName I will need to do some string manipulation to change the format.

                $Name = $Var.DESCRIPTION
		$FirstName = $Name -split " " | Select-Object -First 1
		$SurName = $Name -split " " | Select-Object -Last 1
		
		$DisplayName = "$Surname," + " " + $FirstName

LastLogonTimeStamp Script

$TableLocalMigrated = New-Object 'System.Collections.Generic.List[System.Object]'
$TableLocalToMigrate = New-Object 'System.Collections.Generic.List[System.Object]'
$TableRemoteMigrated = New-Object 'System.Collections.Generic.List[System.Object]'
$TableRemoteToMigrate = New-Object 'System.Collections.Generic.List[System.Object]'

$VerbosePreference = "Continue"
$Workbook = Import-Excel -Path "C:\Export\Master.xlsx"
$SkypePhoneNumbers = Import-Excel "C:\Export\Master.xlsx" -WorkSheetName SkypeNumbers

$vars = Import-Excel -Path "C:\Export\Master.xlsx" -WorkSheetName LocalUsers
Foreach ($Var in $Vars)
{
	#Only parse numbers that have an external number and not just internal extensions, cannot migrated numbers that are just local extensions
	If (($var.'EXTERNAL PHONE NUMBER MASK 1').length -gt 0)
	{
		$Phone = (($var.'EXTERNAL PHONE NUMBER MASK 1') -Split ("X") | select-object -first 1) + $var.'DIRECTORY NUMBER 1'
		#Get the name in the Description field and do an Active Directory lookup to see when the users last logon was
		$Name = $Var.DESCRIPTION
		$FirstName = $Name -split " " | Select-Object -First 1
		$SurName = $Name -split " " | Select-Object -Last 1
		
		$DisplayName = "$Surname," + " " + $FirstName
		$User = Get-ADUser -Filter { displayName -like $DisplayName } -Properties LastLogonTimeStamp -ErrorAction SilentlyContinue | Select-Object -First 1
		If ($null -ne $User)
		{
			$lastLogon = [DateTime]::FromFileTime($User.lastLogonTimeStamp)
			$Daysinactive = ((Get-Date) - $lastLogon).Days
		}
	
		If (($SkypePhoneNumbers).ID -match $Phone)
		{
			Write-Verbose "$Phone is present in skype and has been migrated"
			If ($null -ne $User)
			{
				$Body_LocalMigrated = @(
					[pscustomobject]@{
						Description = $var.DESCRIPTION
						ExternalForwarding = $var.'Forward All Destination 1'
						ExternalMask = $var.'EXTERNAL PHONE NUMBER MASK 1'
						Extension   = $var.'DIRECTORY NUMBER 1'
						PhoneNumber = $Phone
						LastLogonDaysAgo = $Daysinactive
					}
				)
			}
			Else
			{
				$Body_LocalMigrated = @(
					[pscustomobject]@{
						Description = $var.DESCRIPTION
						ExternalForwarding = $var.'Forward All Destination 1'
						ExternalMask = $var.'EXTERNAL PHONE NUMBER MASK 1'
						Extension   = $var.'DIRECTORY NUMBER 1'
						PhoneNumber = $Phone
						LastLogonDaysAgo = 'N/A'
					}
				)
			}
			$TableLocalMigrated.Add($Body_LocalMigrated)
		}
		Else
		{
			Write-Verbose "$Phone is not present in skype and needs to be migrated"
			
			If ($null -ne $User)
			{
				$Body_LocalToMigrate = @(
					[pscustomobject]@{
						Description = $var.DESCRIPTION
						ExternalForwarding = $var.'Forward All Destination 1'
						ExternalMask = $var.'EXTERNAL PHONE NUMBER MASK 1'
						Extension   = $var.'DIRECTORY NUMBER 1'
						PhoneNumber = $Phone
						LastLogonDaysAgo = $Daysinactive
					}
				)
			}
			Else
			{
				$Body_LocalToMigrate = @(
					[pscustomobject]@{
						Description = $var.DESCRIPTION
						ExternalForwarding = $var.'Forward All Destination 1'
						ExternalMask = $var.'EXTERNAL PHONE NUMBER MASK 1'
						Extension   = $var.'DIRECTORY NUMBER 1'
						PhoneNumber = $Phone
						LastLogonDaysAgo = 'N/A'
					}
				)
			}
			$TableLocalToMigrate.Add($Body_LocalToMigrate)
		}
	}
}

#CTI Route Points (Remote Users)
$vars = Import-Excel -Path "C:\Export\Master.xlsx" -WorkSheetName RemoteUsers
Foreach ($Var in $Vars)
{
	#Only parse numbers that have an external number and not just internal extensions, cannot migrated numbers that are just local extensions
	If (($var.'EXTERNAL PHONE NUMBER MASK 1').length -gt 0)
	{
		
		$Phone = (($var.'EXTERNAL PHONE NUMBER MASK 1') -Split ("X") | select-object -first 1) + $var.'DIRECTORY NUMBER 1'
		
		#Get the name in the Description field and do an Active Directory lookup to see when the users last logon was
		$Name = $Var.DESCRIPTION
		$FirstName = $Name -split " " | Select-Object -First 1
		$SurName = $Name -split " " | Select-Object -Last 1
		
		$DisplayName = "$Surname," + " " + $FirstName
		$User = Get-ADUser -Filter { displayName -like $DisplayName } -Properties LastLogonTimeStamp -ErrorAction SilentlyContinue | Select-Object -First 1
		If ($null -ne $User)
		{
			$lastLogon = [DateTime]::FromFileTime($User.lastLogonTimeStamp)
			$Daysinactive = ((Get-Date) - $lastLogon).Days
		}
		
		If (($SkypePhoneNumbers).ID -match $Phone)
		{
			Write-Verbose "$Phone is present in skype and has been migrated"
			If ($null -ne $User)
			{
				$Body_RemoteMigrated = @(
					[pscustomobject]@{
						Description = $var.DESCRIPTION
						ExternalForwarding = $var.'Forward All Destination 1'
						ExternalMask = $var.'EXTERNAL PHONE NUMBER MASK 1'
						Extension   = $var.'DIRECTORY NUMBER 1'
						PhoneNumber = $Phone
						LastLogonDaysAgo = $Daysinactive
					}
				)
			}
			Else
			{
				$Body_RemoteMigrated = @(
					[pscustomobject]@{
						Description = $var.DESCRIPTION
						ExternalForwarding = $var.'Forward All Destination 1'
						ExternalMask = $var.'EXTERNAL PHONE NUMBER MASK 1'
						Extension   = $var.'DIRECTORY NUMBER 1'
						PhoneNumber = $Phone
						LastLogonDaysAgo = 'N/A'
					}
				)
			}
			$TableRemoteMigrated.Add($Body_RemoteMigrated)
		}
		Else
		{
			Write-Verbose "$Phone is not present in skype and needs to be migrated"
			If ($null -ne $User)
			{
				$Body_RemoteToMigrate = @(
					[pscustomobject]@{
						Description = $var.DESCRIPTION
						ExternalForwarding = $var.'Forward All Destination 1'
						ExternalMask = $var.'EXTERNAL PHONE NUMBER MASK 1'
						Extension   = $var.'DIRECTORY NUMBER 1'
						PhoneNumber = $Phone
						LastLogonDaysAgo = $Daysinactive
					}
				)
			}
			Else
			{
				$Body_RemoteToMigrate = @(
					[pscustomobject]@{
						Description = $var.DESCRIPTION
						ExternalForwarding = $var.'Forward All Destination 1'
						ExternalMask = $var.'EXTERNAL PHONE NUMBER MASK 1'
						Extension   = $var.'DIRECTORY NUMBER 1'
						PhoneNumber = $Phone
						LastLogonDaysAgo = 'N/A'
					}
				)
			}
			$TableRemoteToMigrate.Add($Body_RemoteToMigrate)
		}
	}
}

Write-Verbose "EXPORTING RESULTS TO EXCEL"
Write-Verbose "Exporting Local Migrated Users"
$TableLocalMigrated | Export-Excel "C:\Export\Master.xlsx" -WorksheetName LocalUsers_AlreadyMigrated -AutoSize
Write-Verbose "Exporting Local UnMigrated Users"
$TableLocalToMigrate | Export-Excel "C:\Export\Master.xlsx" -WorksheetName LocalUsers_NotMigrated -AutoSize
Write-Verbose "Exporting Remote Migrated Users"
$TableRemoteMigrated | Export-Excel "C:\Export\Master.xlsx" -WorksheetName RemoteUsers_AlreadyMigrated -AutoSize
Write-Verbose "Exporting Remote UnMigrated Users"
$TableRemoteToMigrate | Export-Excel "C:\Export\Master.xlsx" -WorksheetName RemoteUsers_NotMigrated -AutoSize -Append
Write-Verbose "Done!"

And now we will have a LastLogon column

Brad Wyatt

My name is Bradley Wyatt; I am a 5x Microsoft Most Valuable Professional (MVP) in Microsoft Azure and Microsoft 365. I have given talks at many different conferences, user groups, and companies throughout the United States, ranging from PowerShell to DevOps Security best practices, and I am the 2022 North American Outstanding Contribution to the Microsoft Community winner.

The Lazy Administrator

Finding ways to do the most work with the least effort possible