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!

OCS reporting GUI interface!

Saw this on the OCS team blog.

"The report allows you to enter the SIP URI of any 2 users that you want to view archived messages from. If you enter �Any User� (case sensitive) for either of the user input boxes, you are able to view any message from any user to a specific user as well as any user to any other user. You can use the Start Date and End Date to narrow down the search to a specific date range. Once you have entered all of the inputs, click on View Report."

Training and some personal updates

Been a very busy 2010 for me thusfar. After some long time off from work in December, I returned to our economy starting to turn around, and we are getting nice and busy again. I currently have three Exchange 2010 migrations, a few OCS related projects, and am in pre-sales with a bunch on new Exchange 2010 opportunities.

Last week, I went to Microsoft's Unified Communications Voice Ignite (UCVI 2.0) training in Dallas, TX. The class was really good, and I attended with one of our resident Cisco Voice consultants as well, so he was able to help bridge some gaps for me and vice versa me for him. Of course, while in Dallas they got a record breaking snowfall of 11" in 24 hours. Class wasn't canceled, but most everything else in Dallas was. Getting back to Austin felt great, and the next day it was 65 here, and I spent it in the hammock.

Well, this week, our MS UC partner sent us an offer for the beta exam for 71-404 - to go take it for free. If you are a Microsoft Partner, you should be able to take this offer. I am taking it next week, but I will be cramming for sure - there is a LOT of content from that class on this exam!

I also found this really cool OCS 2007 R2 PDF for wall display. So, will this mean more OCS blog entries? I think so. I usually blog based on things I am actually working on, as it makes the entries easier to write, and with all this new OCS knowledge, I don't see how it couldn't take us there some. However, don't worry, my Number One Subject Matter will stay Exchange for a long time I am sure.

Implementing integrated OCS in Exchange 2010

This entry is to show you how to integrate OCS 2007 R2 into your Exchange 2010 OWA experience. This is based on the following Technet article:
http://technet.microsoft.com/en-us/library/ee633458%28EXCHG.140%29.aspx

First, download and extract OCS 2007 R2 Web Trust Tool from http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=ca107ab1-63c8-4c6a-816d-17961393d2b8 Running and installing this will only extract these additional files. Each of these will need to be installed on each CAS server in your environment that you are enabling OCS Messaging on. Remember, there is no right click run as Administrator for MSI's - so run from an elevated command prompt if needed!

• Install the vc_redistx64
  
• Install UCMAredist.msi
  
• Install CWAOWASSP.msi
  

On your Exchange 2010 CAS server(s), edit c:\program files\Microsoft\Exchange\V14\ClientAccess\Owa\web.config - look for the IMPoolName field. Update the webconfig file as follows:

Field	Insert Value From	Example
IMPoolName	FQDN of OCS R2 Pool	ocsr2pool.domain.local
IMCertificateIssuer	DN of Issuer	CN=DigiCert Global CA, OU=http://www.digicert.com/, O=DigiCert Inc,C=US
IMCertificateserialNumber	Serial Number	01 F9 4E 46 AA 3C 4C 9E BD 8F 2C (include spaces between octets!)

Look for this:


And based on this (where thumbprint is the certificate your CAS server uses for IIS)
Get-ExchangeCertificate -Thumbprint BJBHDS78FG6D8GFYH49SDF34TH9 | ft Issuer, SerialNumber, subject

Change to this:


The "subject" gives us the common name that we use in a bit to configure OCS.

Additionally, if your Issuer has funky characters, you need to replace them as they will break your web.config file, causing generic IIS errors. Just removing those characters will make for application event log errors that the certificate was not found in your certificate store.

Since the web.config is an XML file, and you need to use XML character special escapes

entity	character	meaning
"	"	(double) quotation mark
&	&	ampersand
'	'	apostrophe (= apostrophe-quote
&lt;	<	less-than sign
&gt;	>	greater-than sign

So if your SSL provider's issuer field causes you a problem here, this should help you work around it.


In Powershell, configure OCS:
Get-OWAVirtualDirectory -server SERVER | set-owaVirtualDirectory -InstantMessagingType 1

(The above line *did* say -InstantMessagingType OCS, but RTM documentation says 1 for OCS - thanks to Brian Day for this!)

Restart IIS (IISreset is fine)

On your OCS R2 Pool server, under the server properties of your pool, on the Hosts Authorization tab, you need to add the Client Access server. This can be FQDN or IP. If you use FQDN, OCS will additionally authenticate the FQDN against the certificate names - the FQDN here has to match the "subject" we found above (NOTE: Not the whole string, just the FQDN common name given in the subject) Additionally, you can choose to use FQDN and then use a hosts file to ensure that OCS is communicating with the correct server/IP.





Now I am able to log into OWA 2010 and get the light CWA client as well:


Upper right allows me to see and update my presence, as well as see how many IM conversations I have active and switch between them as well.

OCS Voice Ignite Training - Registered!

Yay!

We have been trying for most of the year to get me a seat at one of these, and instead, I think we got two seats so I will get to tag along with one of our Cisco Voice guys as well (this should be helpful in backfilling voice knowledge for me!)

Pretty stoked to get there. Irving, TX in February!

Customer front end to OCS 2007 R2

A recent OCS deployment of mine deployed OCS as a replacement to the livemeeting service. They missed the web front end, and with enhanced presence, they were able to present customers with a list of available CSR's who could then give them a "meet now" request by support email to begin their online support experience. My customer contact, Gary D. at Professional Datasolutions, Inc researched the OCS SDK and SQL databases enough to get his web developers to make this code work. I thought this was a really excellent use of customizing OCS to fit their needs.

All users are a SQL database user stored in a table, not an AD account, manageable via web by CSR's as well.

Once logged in, you can see what CSR's are available from presence and request a meeting key then join.

Once you enter a valid key and click join meeting, you get the LM launcher.

Absolutely awesome. I haven't been able to dive in this deep and I'm psyched to see a customer using OCS to this extent!

OCS PIC changes coming

From the MS OCS Team blog comes this announcement:

• Starting October 1, 2009, the following licensing changes will be made:
  A PIC License will no longer be required for federation with American Online (AOL). Customers qualify for federation with AOL if they have Office Communications Server 2007 R2 Standard CAL or active Software Assurance on their current LCS/OCS license.
• Customers who want Yahoo! federation will continue to purchase PIC licenses. The price of PIC will be reduced by 50%, effective October 1, 2009, to reflect this change.
• New XMPP Gateway released that will allow presence and two party IM with Jabber based services, most notably, Google Talk

This also comes on the heels of their June announcement that Windows Live did not require a PIC license. 2009 has been a good year for federation to public instant messaging platforms!

Been busy, here's the brief updates!

Been a few weeks since my last post, lamentably. However, there have been some noteable posts on other blogs that I feel I would really be missing if I didn't mention here as well.

First off, The Exchange team released an Exchange supportability matrix that shows you what versions are supported/required for migrations. A great link to bookmark if you work with Exchange often.

Second, the OCS team released a planning document on OCS certificates. I still have not had the time to get through it, but it is on my reading list. I am hoping to gain a better understanding of how to approach the PKI needs of an OCS R2 implementation.

Third, Technet has been a resource to many IT Pros for years, and this year there is a sweet 25% off deal that Keith Comb's blogged about.

Now, what have I been so busy with? Work mostly. We have several large customers who we are doing Exchange 2010 designs for, as well as several Exchange 2007 implementations and migrations running concurrently. Add to that a 4 day weekend for labor day (where I managed to stay off email!) and it makes for not enough time to get it all done and blog (or read the guide above!)

Need to find more time! Off for now, preparing for a Pilot group migration for a customer in Alaska tonight!

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!

Microsoft Learning looking for feedback on MCITP for OCS

Microsoft Learning released this survey �to identify the different job roles that work with OCS in order to understand how to develop test content better. This is a good way to help the tests be good practical exams that are true to real life situations.

�
�

�
�

Preparing Windows 2008 x64 for OCS 2007 R2

This is hardly NEW information, but I was looking for a detailed document, found this one, and decided to re-blog it here.

Using ServermanagerCMD.exe, we can install all the needed s/w in one copy paste! I did trim down some of the things that error out that they are already installed from "web-server" as well, and I usually just do RSAT-ADDS first, just like I would on an Exchange implementation.

Servermanagercmd -i RSAT-ADDS

Servermanagercmd -i web-server

Servermanagercmd -i web-http-redirect

Servermanagercmd -i web-basic-auth

Servermanagercmd -i web-windows-auth

Servermanagercmd -i web-digest-auth

Servermanagercmd -i web-mgmt-compat

Servermanagercmd -i rsat-role-tools

Servermanagercmd -i MSMQ

OCS 2007 R2 - End User Training Resources

Microsoft released this back in May, and somehow I only recently found it....

UCHowto is an end user training tool. It is based on Silverlight, and the zip file has two things, a desktop version "rolodex.exe" and a web version that you can stick on your or your customer's Intranet site. I am a little perplexed why MS didn't just release this as a web app like 'help.outlook.com' is.

Either way, very neat tool to give as a deliverable upon a successful OCS 2007 R2 roll out to help users gain some functionality how-to's without having to ask someone.

There are even Videos on some subjects built in:

I now plan on including this on ALL OCS implementations as a deliverable! Very cool!

Some OCS R2 Updates and information

I haven't spoken OCS R2 on here in a bit since Exchange 2010 has been my main focus at both work and home. However, some very nice updates to share.

• OCS R2 downloadable CHM help file was released - Download
• On the heels of the Exchange Remote Connect Analyzer, the OCS team has released the OCS remote connectivity analyzer. Very handy for confirming your Access Edge is configured correctly - Use it!
• The OCS team has offered up a listing of OCS related blogs - Check it out! - I am both excited to see this blog on this list, and somewhat embarrassed as I haven't touched OCS R2 in about a month or two now. That will change soon, I have an R1 to R2 migration soon.

Enjoy and keep the UC push going!

OCS R2 Group Chat - issues with IIS metabase?

Thank you to Mike who commented on my blog post here that the Metabase issue was because UAC needs to be disabled, and the Adminsetup.exe should be Right clicked and ran as an Administrator!

I had completely forgotten that this is how I worked around this, and had not been able to re-walk the install since I posted that.

Again, Thanks Mike!
Chris

Forefront for OCS released!

http://communicationsserverteam.com/archive/2009/03/16/383.aspx

This appears it would fit only in an environment where OCS is federated. At least until someone writes malware for communicator.

OCS 2007 R2 Enterprise Edition and SQL requirements

I recently completed my first EE installation in a production environment. This was a migration from LCS 2005 SP1, and while LCS is not yet defunct, it so far has been VERY smooth.

This particular install was using a SQL 2005 cluster for the back end as well as the archive databases.

Things to know:

• Only SQL 2005 SP1 and SQL 2008 are supported.
  
• For Archiving, MSMQ and Directory integration are needed.
  

  
  

  Lessons learned:
  

• LCS 2005 didn't require a certificate to work properly at this particular customer. This prevented communications between OCS and LCS users. The choices were to fix this or just speed up the plans. Like I said above� completed :)
  

  
  

OCS Master program - here's what their labs look like

WOW.

Impressive. If it didn't almost require an EDU loan, I would love to take this.

http://blogs.technet.com/themasterblog/archive/2009/01/31/holy-hol-s-batman.aspx

Group Chat - Microsoft's take

So I have noticed the Group Chat stuff is getting a LOT of hits on here, and one of the questions I seem to see being asked a lot elsewhere is "how would we use this"

So I figured I would rely on Microsoft's devarketing team (ha!) to explain this for me some. These are from a series of OCS R2 videos I posted back in January. This one only had 500 hits when I posted it, so I think the "spin" on Group Chat usage will help some people get a better understanding of where it fits in to an organization.

OCS 2007 R2 Group Chat Installation - Part 3, Client install

Earlier, in Part One, we installed Group Chat Server and in Part Two, we learned how to connect to the admin tools and to create a channel and allow people in it. Now it is time to Deploy the client.

The Group Chat client requires the .NET Framework 3.5 SP1 and Visual C++ runtime installed. Unfortunately, Microsoft provided only an executable client install, so we cannot easily GPO this installation without an MSI file. There are the below options for the clientsetup.exe executable:

Working with this, I created a logon script to do this installation. If a client does not have .NET 3.5 SP1 installed, and you skip that install, the unattended install will give an error.

\\2008dc\netlogon\dotnetfx35setup.exe /qb \\2008dc\netlogon\Clientsetup.exe /Unattend

An added dose of fun - .NET requires a reboot, and /qb tries to force that reboot.

The actual manual install process is very simple - just take all the defaults.

Getting the software installed is just one part - getting it configured is another. First I will cover the manual configuration. Then I will discuss deploying these settings in an enterprise.

The more critical knowledge here is how to deploy this to clients and have the logins work out of the box. Using the "Automatic Configuration" provided, my first sign in was less than desirable. I got the below error:

And was unable to search for a channel (it just seems to hang there trying) so I created a new configuration named domain.com like I did in Part 2. I had to investigate further.

So I decided to try the Administrator account. It worked fine. So for some reason - the "Server Address" had to be "Administrator@domain.com" and then when I actually log on, I use my Chris@domain.com SIP URI. Very odd behavior. Above is the NON working config. Below is the working config.


Odd for sure. Again, when I sign into the application, I am clearly me, not admin:

Now, configuring another piece of client software might be easy for us, but not for most users, so it's time to learn how to deploy these settings.

So I downloaded the OCS 2007 R2 ADM files and went to create a new GPO, and quickly found that none of the GPO names in the XLS document for Group chat are in there.

It seems as is there should be an additional ADM coming, or an updated one with these settings.

I did find that you can manipulate these by dropping an XML file into the workstation directory of:

C:\Users\%username%\Application Data\Microsoft\Group Chat\Common\Accounts (on Vista) and by editing or replacing the file at:
C:\Users\chris\Application Data\Microsoft\Group Chat\Group Chat Console\Accounts\_default.account_.xml

So I made a second script:

copy \\2008dc\netlogon\_default.account_.xml "%userprofile%\Application Data\Microsoft\Group Chat\Common\Accounts"
copy \\2008dc\netlogon\Internal.xml "%userprofile%\Application Data\Microsoft\Group Chat\Group Chat Console\Accounts"

Since these folders won't exist until the application is installed, you may want to stagger your install and your configuration GPOs/Scripts.

If anyone has any better way to roll out the configuration, please let me know, I really do feel like these are some non-enterprise level workarounds.

OCS 2007 R2 Group Chat Installation - Part 2, Administrative Tools Installation

Earlier, in Part One, we installed Group Chat Server and now it is time to install the administrative tools. I have already done this a few times and ran into some oddities, that hopefully I can help you not run into!

In this instance, I am installing the tools on my OCS Standard pool server (which is also my group chat server) We can begin by running the 'AdminSetup' installer.

The installer will warn you if you do not already have MS Visual C++ redistributable installed. If you run their installer of this, do note, you will be cleaning up the root of your C drive.

Icky. Easy enough to clean up though. Once this is installed, its easy to click through all the defaults and complete the Admin tool console install.

Once installed, we can launch the Admin tools console. The "proper" name doesn't fit in the start menu, really.

So I attempt to logon:

I see "Connected to OCS server" and then I get this:

"Cannot sign in because of a problem with the chat room service. If the problem persists, please contact your system administrator"

Time to not trust Automatic Configuration. I will come back to this in the client deployment some more, but for the admin console, a manual configuration is OK.

Choosing Edit Accounts here, I made a new Account and used the below settings:

I then chose the Domain.com account and signed in without error. In the screen above, the Host is my OCS std pool, the domain.com is my AD FQDN and note the capitalization on Administrator to match my SIP URI.

Now that I am in the tool - lets create a test channel and set it up.

File>New>Chat Room brings up this dialog:
This gave an error. Channel names cannot have spaces. I removed this moving forward.

Once the room is created you need to add members to the channel. Since this is a lab domain, I decided to add my members to the Root of the server. In a production environment, I would likely be much more careful about security.

Now, Part 3 will come soon, and discuss the client installation, but I will give a brief preview here as the client is obviously needed to test this.


Here, you can see presence integration.

That's all for now. Check back soon for the client deployment, which I hope to include GPO settings to configure the client as well.

OCS 2007 R2 Group Chat Installation - Part 1, Server Installation

If there is one thing in Group Chat that is similar to OCS, it's definitely the icons. If you were not aware, the Group Chat functionality was an acquisition of a company named Parlano in the summer of 2007. Because of this, the integration with the rest of OCS R2 isnt quite where I would expect it to be. I expect in future SP's and versions this will become more tightly integrated.

A note, on Group Chat pre-requisites. I did this install on my existing standard pool which is not recommended. It should be on a separate server.

This is a screenshot of the IIS features I have installed. I have not yet found any article on group chat pre-requisites on a fresh server. I will need to do this soon and update this post.

Downloads:
ServerSetup
ClientSetup
AdminSetup

I will review OCS 2007 R2 Group Chat in three or possibly four parts.

• Server Installation
  
• Administation Installation and configuration
  
• Client deployment
  
• Usage, recomendations
  

Lets dig in with the Server Setup.exe. I wish that this was more OCS integrated from the start. We begin with a VERY non MS DOS window. This launches the Group Chat Server install, that looks and feels familiar.

From there, Accept terms, enter user/company, choose features� two choices, Chat Server and Compliance Service. Both install by default.

Get a warning about MSMQ not being installed. This might be due to my eval install rather than real standard. Either way, if you plan on using Group Chat, or archiving/monitoring, you need MSMQ.

And then�. I am removing backup files:

And I did this twice to be sure. Yep, that's a window popping under to install. And "Server configuration" with a new icon. Of course, these are the pains of integrating Parlano, whom Microsoft acquired only 2 years ago.

Choose server and DB name:

Note here: You do need to manually create this Database.

Next screen, you can choose a different DB for compliance (likely policy recommended)

I chose the same for technical ease.

It's around this point I noticed the menu on the left gave no knowledge of how many steps remained. Not a big deal, but would be nice to see that the list has a start and a finish.

Here is where we set our Super Users:

Enter your OCS pool name, then choose Browse to select the MTLS cert to use.

The next few screens, I am setting a few service accounts up. For simplicity, I used my Domain Admin account for most of these. In a production environment, I would ABSOLUTELY have separate accounts for these.


For some reason, the browse button on this dialog was not working for me. Not a big deal. This should be a UNC path. I made it on the same machine for my instance.

I did not delve into the Compliance Adapters at all.

Next screen was Web Service Settings. Same deal on the UNC share:

Final Overview before hitting Install:

When you hit Finish, you will be alerted if any of the service accounts are granted the log on locally or log on as a service right.

Then we flip back to the MS installer and we are done!

Once this is installed - you will only have the OCS R2 Group Chat icon in your administrative tools.

Launching this allows you to view status of services and stop/start them as well as modify the service accounts.

I am not yet sure why the Web Service is listed as stopped. The W3SVC is running in the Services control panel, however.

Within this, File - Configure Server Settings allow you to modify pretty much ANY of the settings you specified in the installation. I am not going to review each of these, as I think it would be repetitive.

Next up - the Administrative Tool!