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:
- Starting with “55” followed by four numbers
- Starting with “01 or 07” followed by nine numbers
- Blank/Null values
- E.164 formatted numbers (No change required)
- 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:
- “^(01|07)\d{9}”
- “^55\d{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
jfrmilner's Tech Blog