Normalizing Phone Numbers to E.164 format in Excel

Recently, I had the need to import some users for a large company. In order to populate as much of their Active Directory as possible, they wanted their phone numbers to be in a standardized format. Both Microsoft and Cisco have standardized on E.164 (additional information here) as a numbering standard, which basically starts with + [country code] + phone number.

This particular customer is US based only, so all the numbers in their spreadsheet had a US country code of 1. If I had a multinational organization, some additional coding would need to be done to account for other country codes.

My major need was to simply re-input all the different numbering standards the various internal organizations had inputted their phone numbers as. In other words, normalization. This helps to set up AD for later integration of OCS, or other VoIP systems, as well as Exchange 2007 or Exchange 2010 UM.

Either way, the Excel formula I was using here was the following:
=CONCATENATE("+1",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE((SUBSTITUTE(A2,"(","")),")","")," ",""),"-",""),".",""),"x",";ext="))

In logical order..

1. Replace ( with null
   
2. Replace ) with null
   
3. Replace space with null
   
4. Replace hyphen with null
   
5. Replace period with null
   
6. Replace x with ";ext=" (which is the E164 standard for non-DID numbers)
   
7. Concatenate the +1 country code
   

Here again without the horrible color:
=CONCATENATE("+1",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE((SUBSTITUTE(A2,"(","")),")","")," ",""),"-",""),".",""),"x",";ext="))

The end result, computer readable phone numbers!

Best Practices for Active Directory Schema changes

Part of my job is to extend AD Schemas to support new versions for products like Exchange and OCS, and this is part of what I do prior to Schema changes for customers as well as internally.

First off, a quick review of AD schema, and what it is and the function it performs. The Schema is essentially the "database" that AD resides in, so when we say things like "extending the schema" we mean the same thing any SQL DBA would mean - we are adding additional objects attributes to AD. These new additions allow for features in products that were not previously there to store their settings in Active Directory.

Some of the recent Schema extensions you will see:

• Exchange 2007 SP2 requires schema extension.
  
• Exchange 2010 requires schema extension.
  
• OCS 2007 R1 or R2 require schema extension.
  

Additionally, while not an extension, these best practices also apply before raising your forest or domain functional levels.

Step One - Determine your Schema Master FSMO role holder

1. On any domain controller, click Start, click Run, type Ntdsutil in the Open box, and then click OK.
   
2. Type roles, and then press ENTER.
   
3. Type connections, and then press ENTER.
   
4. Type connect to server <servername>, where <servername> is the name of the server you want to use, and then press ENTER.
5. Type q to return to the fsmo maintenance prompt.
   
6. At the FSMO maintenance: prompt, type Select operation target, and then press ENTER again.
   
7. At the select operation target: prompt, type List roles for connected server, and then press ENTER again.
   
8. This will display all 5 FSMO roles. The one that has Schema is the one we need to back up.
   
9. Type q 3 times to exit the Ntdsutil prompt.
   

Step Two - Ensure you have your DSRM password

1. Most of the time, even if this is known, it has not been changed in a long time and is likely due.
   
2. Follow instructions to reset DSRM password from KB322672
   
3. This allows your backup to be authoritatively restored in the case you need to. Without this password being correct, your backup may not be usable.
   

Step Three - Take a system state backup (or two)

1. I recommend taking an ntbackup.exe (Windows 2003) or Windows Server Backup (Windows 2008) if you are more comfortable with Microsoft restore procedures.
   
2. I recommend taking another backup using whatever third party vendor product you typically use, if you are more comfortable with their restore procedures.
   
3. I usually recommend taking BOTH of the above for the Schema Master FSMO role holder.
   

While I have YET to run into any issues or problems with Schema extensions, if I ever did, I know I want a really good backup or two!

Using ADModify to ensure inheritable permissions are set on user objects

Most Exchange migrations I might find 1-2 of these that can be addressed as one off issues, but today I had customer that had a lot more than usual.

I searched for a good adsiedit, ldp, or other query - no good, its an ACL, not an AD user object property, I did find some .NET examples that I did not want to venture down that road.

And then, I found admondify.net:
http://technet.microsoft.com/en-us/library/aa996216(EXCHG.65).aspx

This requires a PSS call (or google) to download. Not an install, just an unzip and use utility.

Click Modify Attributes

Select domain, select a DC, Hit the big green arrow

Then select the root or OU you want and enter a custom LDAP query of (objectClass=*) and select add to list

Then Ctrl-a or control select user objects and hit next.

Then you can go to the account tab (normally would be security->advanced in ADUC) and finally select the "allow inheritable permissions to propagate to this object" checkbox and go.

Quick, easy, and best of all, whatever you do generates an XML log file that you can also use to "undo" your changes!