Reformatting Telephone numbers to the E.164 format with PowerShell

Posted: 28/09/2011 in Active Directory, PowerShell
Tags: ,

This month we have a new Avaya phone system being implemented for one of my customers and one of the prerequisites to get full functionality is to have all telephone numbers in the popular E.164 format. This would be a pretty simple task if the data was in a consistent format but unfortunately this was not the case, let’s take a look at the state of the data*:

*For security reasons I have recreated the data in my test lab with random names, area codes and telephone numbers etc.

First I will store a collection of Users in a variable named $users, throughout this post I will use the Quest AD cmdlets. This is mainly because they are backwards compatible with 2003 based domains but if you are lucky enough to be 2008 you should be able to convert the commands to Microsoft’s AD Module with little effort.

$users = Get-QADUser -Description "E164 Sample Accounts"

And now display just the name and telephoneNumber properties:

$users | Select-Object Name, telephoneNumber | Format-Table -AutoSize
Name              telephoneNumber
----              ---------------
Georgina.Stewart  554861
Georgina.Stewart1 01234 12 34 56 (only call on Tues)
Eden.Morris       01234-55(4487)
Sarah.Patel       559642
Marley.Adams      550998
Mya.Lewis         (01234) 127075
Sophia.Baker      (55)2912
Filip.Rogers      (55)8996
Noah.Mason        (Part Time)-(01234) 897740
Isabel.Webb       (01234) 036428
Steven.Matthews   (01234) 957823
Courtney.Walker   (01234) 679860
Rayyan.Mitchell   07123 530656
Mary.Graham       07123  822057
Maisy.Barnes      07123 364461
Arabella.Thompson +441234844048
Macy.Adams
Grace.Robinson    (01234) 538794
Summer.Stevens    Call me before 11AM 550997
Frederick.Taylor  01234588239

As you can see we have quite a mix bag here. One of the first things to notice is that the telephoneNumber property has not been strictly used for just numbers and as such it is difficult to see any format patterns, let’s strip all non-digit characters away and take another look:

$users | Select-Object Name, @{name="telephoneNumberDigitsOnly";expression={$_.telephoneNumber -replace "\D"}} | Format-Table –AutoSize

The above line uses a replace with a single argument so this is effectively a remove. The replace uses a regular expression of Non-Digit contained within a calculated property.

Name              telephoneNumberDigitsOnly
----              -------------------------
Georgina.Stewart  554861
Georgina.Stewart1 01234123456
Eden.Morris       01234554487
Sarah.Patel       559642
Marley.Adams      550998
Mya.Lewis         01234127075
Sophia.Baker      552912
Filip.Rogers      558996
Noah.Mason        01234897740
Isabel.Webb       01234036428
Steven.Matthews   01234957823
Courtney.Walker   01234679860
Rayyan.Mitchell   07123530656
Mary.Graham       07123822057
Maisy.Barnes      07123364461
Arabella.Thompson 441234844048
Macy.Adams
Grace.Robinson    01234538794
Summer.Stevens    11550997
Frederick.Taylor  01234588239

Now that looks better with the exception of Summer.Stevens, it’s expected that some accounts will need manual effort so I will need to filter for those as well. We can see from the above table that there are two common number formats (1,2), blanks (3), numbers already in E.164 format and finally we’ll need a catch all(5) for everything else:

  1. Starting with “55” followed by four numbers
  2. Starting with “01 or 07” followed by nine numbers
  3. Blank/Null values
  4. E.164 formatted numbers (No change required)
  5. Neither of the above formats, for example Summer.Stevens

The next step is to create regular expressions to match these four values as the fifth will be a catch for non-matched values, like so:

  1. “^(01|07)\d{9}”
  2. “^55\d{4}”
  3. “^$”
  4. “^44\d{10}”

Now let’s put all of this together and get a feel for how things will look:

foreach ($user in $users) {
	switch -regex ($user | % { $user.telephoneNumber -replace "\D" } ) {
	"^(01|07)\d{9}"		{ Add-Member -InputObject $user -Name "E164Number" -MemberType NoteProperty -Value ([regex]::matches(($user.telephoneNumber -replace "\D"),"^(01|07)\d{9}")[0].Value -replace '^0','+44') ; continue }
	"^55\d{4}"			{ Add-Member -InputObject $user -Name "E164Number" -MemberType NoteProperty -Value ([regex]::matches(($user.telephoneNumber -replace "\D"),"^55\d{4}")[0].Value -replace '^55','+44123455') ; continue }
	"^$"				{ Write-Warning "Blank" ; continue } #Empty
	"^44\d{10}"			{ if ($user.telephoneNumber -match "^\+\d{12}") { Write-Warning "E.164: $($user.sAMAccountName), $($user.telephoneNumber)" } else { Write-Warning "No Match: $($user.sAMAccountName), `"$($user.telephoneNumber)`""} ; continue}
	default 			{ Write-Warning "No Match: $($user.sAMAccountName), `"$($user.telephoneNumber)`"" }
	}
}

As you can see from the above script block I have used a foreach loop that then uses a switch statement. The switch then checks to see if it can match one of the regular expressions patterns from the previous step and if successful adds a new note property named “E164Number” else it will display a warning. Let’s give it a try:

WARNING: E.164: Arabella.Thompson, +441234844048
WARNING: Blank
WARNING: No Match: Summer.Stevens, "Call me before 11AM 550997"

As predicted we have a few warnings, now let’s have a look at the projected telephone numbers:

$users | Select-Object Name, telephoneNumber, E164Number | Format-Table -AutoSize
Name              telephoneNumber                     E164Number
----              ---------------                     ----------
Georgina.Stewart  554861                              +441234554861
Georgina.Stewart1 01234 12 34 56 (only call on Tues)  +441234123456
Eden.Morris       01234-55(4487)                      +441234554487
Sarah.Patel       559642                              +441234559642
Marley.Adams      550998                              +441234550998
Mya.Lewis         (01234) 127075                      +441234127075
Sophia.Baker      (55)2912                            +441234552912
Filip.Rogers      (55)8996                            +441234558996
Noah.Mason        (Part Time)-(01234) 897740          +441234897740
Isabel.Webb       (01234) 036428                      +441234036428
Steven.Matthews   (01234) 957823                      +441234957823
Courtney.Walker   (01234) 679860                      +441234679860
Rayyan.Mitchell   07123 530656                        +447123530656
Mary.Graham       07123  822057                       +447123822057
Maisy.Barnes      07123 364461                        +447123364461
Arabella.Thompson +441234844048
Macy.Adams
Grace.Robinson    (01234) 538794                      +441234538794
Summer.Stevens    Call me before 11AM 550997
Frederick.Taylor  01234588239                         +441234588239

Very nice. Now to make this permanent we need to push the E164Number property into the telephoneNumber property on each of these users:

Backup first:

$users | Export-Csv C:\Support\MyBackup.csv -NoTypeInformation

Once the blackup task has been completed we can now go ahead and apply the new number. Notice that I used an if statement to check that there actually is a value in the E164Number property first and if there isn’t then it will skip that user.

 $users | ForEach-Object { if ($_.E164Number) {Set-QADUser -Identity $_ -PhoneNumber $_.E164Number}} 

Now to confirm the results:

$users | Get-QADuser | Select-Object Name, telephoneNumber | Format-Table -AutoSize
Name              telephoneNumber
----              ---------------
Georgina.Stewart  +441234554861
Georgina.Stewart1 +441234123456
Eden.Morris       +441234554487
Sarah.Patel       +441234559642
Marley.Adams      +441234550998
Mya.Lewis         +441234127075
Sophia.Baker      +441234552912
Filip.Rogers      +441234558996
Noah.Mason        +441234897740
Isabel.Webb       +441234036428
Steven.Matthews   +441234957823
Courtney.Walker   +441234679860
Rayyan.Mitchell   +447123530656
Mary.Graham       +447123822057
Maisy.Barnes      +447123364461
Arabella.Thompson +441234844048
Macy.Adams
Grace.Robinson    +441234538794
Summer.Stevens    Call me before 11AM 550997
Frederick.Taylor  +441234588239

Excellent, the majority of users now have an E164 formatted phone number.

All that’s left now is to identify users that still do not conform to this format so that they can be contacted, this can be achieved by using a Where-Object with another regular expression like so:

$users | Get-QADUser | Where-Object { $_.telephoneNumber -notmatch "^\+\d{12}" } | Select-Object name, email
Name           Email
----           -----
Macy.Adams     Macy.Adams@jfrmilner.lab
Summer.Stevens Summer.Stevens@jfrmilner.lab

Well that wraps up this post; I think you’ll agree it’s a nice real world example of using PowerShell with regular expressions to solve formatting issues.

Thanks for reading and until next time.

Regards,

jfrmilner

Advertisements
Comments
  1. Thats great stuff! I would be really helped with a regex that would convert as much as posible European inbound dids on our phonesystem to proper E.164, but we cant use php. So we need to cleanup what the provider sents to us. I can give u 50 bucks for it.

  2. Noah says:

    I tried to convert your script to use the get-aduser commands available in AD shell module. I am getting the following errors.

    Cannot add a member with the name “E164Number” because a member with that name already exists.

    Here is my script.

    $users = Get-ADUser -Filter * -Properties Name,telephonenumber -Description “E164 Sample Accounts”

    $users | Select-Object Name, telephoneNumber

    $users | Select-Object Name, @{name=”telephoneNumberDigitsOnly”;expression={$_.telephoneNumber -replace “\D”}}

    foreach ($user in $users) {
    switch -regex ($user | % { $user.telephoneNumber -replace “\D” } ) {
    “^(1)\d{10}” { Add-Member -InputObject $user -Name “E164Number” -MemberType NoteProperty -Value ([regex]::matches(($user.telephoneNumber -replace “\D”),”^(1)\d{10}”)[0].Value -replace ‘^1′,’+1’) ; continue }
    “^55\d{4}” { Add-Member -InputObject $user -Name “E164Number” -MemberType NoteProperty -Value ([regex]::matches(($user.telephoneNumber -replace “\D”),”^55\d{4}”)[0].Value -replace ‘^55′,’+44123455’) ; continue }
    “^$” { Write-Warning “Blank” ; continue } #Empty
    “^44\d{10}” { if ($user.telephoneNumber -match “^\+\d{12}”) { Write-Warning “E.164: $($user.sAMAccountName), $($user.telephoneNumber)” } else { Write-Warning “No Match: $($user.sAMAccountName), `”$($user.telephoneNumber)`””} ; continue}
    default { Write-Warning “No Match: $($user.sAMAccountName), `”$($user.telephoneNumber)`”” }
    }
    }

  3. Noah says:

    I was able to get my formatting correct for the RegEx and it works great. Now the only issue is the Set-ADUser. I’m not able to set the identity correctly yet.

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s