Monday, January 13, 2014

Using SQL as a Phone Provisioning Methodology for CCIE Voice

"What is the fastest way to provision the phones for the CCIE Voice Lab?" That is a question I asked myself a lot during my preparation for the IE lab. I also saw this question posed by a fair number of fellow candidates on various forums. There are a lot of different methodologies that could be applied here, but the bottom line is you have to go with what you know. You need something you can execute consistently and rapidly. 

I tried a lot of different methods and came up with one that worked best for me. I originally planned to post an entry on my method and then stopped short because I thought the benefit of the method would be lost in blog format. When I thought about posting it again I was slammed busy and I figured that the new blue print is right around the corner. Timing is everything.  

However, recently I have received several requests from readers asking about the phone provisioning method I alluded to in other blog entries. So, I dusted off the draft and am presented it now. Hopefully, someone finds this information useful.

Choose Your Own Adventure

Provisioning the phones is a necessary evil in the current IE voice blue print. It can be a huge time drain if you aren't careful. So, coming up with a method that optimizes speed is key. I also think that your chosen method needs to be something you can do cleanly. Accuracy is very important, even if you have to sacrifice some speed. As with anything, there are a variety of methods you can apply to tackle this task.

Pure Manual

No need to really go into this one too deeply. You auto register phones and then plod through them one at a time. 'nuff said.

Super Copy

In the real lab, most of the phones will be the same model. So, one of the approaches you can use is creating a baseline phone template and then using Super Copy. You would first get the MAC addresses for your phones. Using "show cdp neighbor" will do the trick. Then you start configuring one of the phones. 

You configure the phone with the appropriate device settings. Add lines to the phone as required and save it. Then you use Super Copy to duplicate the station. Paste in the next phones MAC address and modify settings as appropriate. You keep copying (or SUPER copying) until you are done.

I know several people who used this method successfully. I did a couple of test runs with this approach and it wasn't for me. I felt like it got me in the weeds on the CCMAdmin web interface

Pure BAT

BAT has the ability to provision all of your phones and users for you. Anyone who is a professional services provider or system administrator should be very familiar with BAT. There was one guy I spoke to that said he used BAT 100% to provision phones and users in his IE attempts. 

I am open to experimentation and I gave it a go. It was just too finicky for me and I found this approach to be more susceptible to error. 

The Combo Platter

I am going to call my method the "combo platter" because, well, it is my method and I can call it what I want. 

It is worth noting that prior to dorking around with phone provisioning, I have already configured dial plan, phone services, voicemail profiles, and anything else that is going to be added to the phone. 

Step 1: Add Directory Numbers

In CUCM, you can create Directory Number without assigning them to anything. To help smooth out my process, I go ahead and create the Directory Numbers before I add the phones. I will add the DNs to the appropriate partitions, associate VM profiles, configure CFNA/CFB/etc.

What I like about this method is that you can add a range of numbers (if you want) or you can quickly copy/paste DNs. It is a pretty quick way to get the phone DNs "pre-staged".

Step 2: Auto Register Phones

When I am ready to configure my phones, I simply enable auto-registration, enable switch ports, etc.. If I have done my job with VLAN configs and DHCP then the phones should register fairly quickly. 

Step 3: Get Your Notepads Ready

What we are going to do here can get real ugly if you try to type out the SQL queries (yeah, SQL queries) directly on the CLI. You want to leverage copy/paste as much as possible and it is best to get all of your configs lined up in notepad so that you can bang 'em out quickly when you are ready to apply.

I open two notepads. One is going to hold all of the SQL queries I plan to apply (config notepad) and the other is used to do some search/replace functions (scratch pad). I'll try to illustrate as we go along.

Step 4: Set the Description Field

When a phone auto registers a new DN is created based on the range you specify in the CM configuration. The description field is also auto generated based on this DN. So, if your auto register range is 10 to 20 then the first phone to register gets DN 10 and a device description of "Auto 10". 

So, I start with setting the description field on the phones and I am going to use the existing description field to determine what the new field value should be. I am not looking at the CUCM web page at all during this process. I am looking at the phones. The phones are labeled already and I can see the line appearance (auto registered DN). 

In my config notepad I type out the first query. I start with HQ Phone 1.

run sql update device set description='HQ Phone 1' where description = 'Auto 10'

Then I will copy/paste that line in my scratch pad, creating all HQ Phones. Using my mouse and keyboard skills I make the changes necessary to set the appropriate description field on HQ Phone 2, HQ Phone 3, etc.. Make sure you change the "where" clause as well. For example:
run sql update device set description='HQ Phone 2' where description = 'Auto 11'
run sql update device set description='HQ Phone 3' where description = 'Auto 12'
run sql update device set description='HQ Phone 4' where description = 'Auto 13'

Once I get the other HQ phone queries set up, I cut/paste them over to the config notepad (remember, I was using the scratch notepad). 

Now, we have other sites in the mix. Let's assume I have a Site B and there are three phones in Site B. I will copy the first three entries for HQ in the config notepad and paste them back into my scratch pad. Then I use a find/replace to replace all occurrences of "HQ" with "SB". I then edit the "Auto 11", "Auto 12", etc. to match the auto-registered DNs on my Site B phones. Finally, I cut/paste all of the Site B phone queries into my config notepad.

Do the same thing for remaining sites, as needed.

Step 5: Device Pool

Next, I create the queries needed to update the device pools and add them to the config notepad:

run sql update device set fkdevicepool = (select pkid from devicepool where name = 'HQ_DP') where description like 'HQ%'
run sql update device set fkdevicepool = (select pkid from devicepool where name = 'SB_DP') where description like 'SB%'
run sql update device set fkdevicepool = (select pkid from devicepool where name = 'SC_DP') where description like 'SC%'

I guess I should point out that I use the device CSS, AAR CSS, MRGL, etc. configured in the device pool instead of "hard coding" them on the phones. I only modify the CSS/MRGL on the phones if it is necessary to address one of the lab requirements.

Step 6: Associate the Line Appearances

To keep myself straight, I will insert a few line feeds between the "Description Field Queries" and the next batch of queries, just to give me a visual aid. Anyway, now we want to take the DNs we created in Step 1 and associate them to the phones. 

Let's look at a base query syntax example for HQ Phone 1:

run sql update devicenumplanmap set (fknumplan,e164mask,display)=((select pkid from numplan where dnorpattern='5001'),'+1202555XXXX','HQ Phone 1') where numplanindex=1 and fkdevice=(select pkid from device where description like 'HQ Phone 1')

What we are doing here is updating an existing entry in a table called devicenumplanmap. If you follow my SQL Query series then you know that this table maps device records to directory numbers. We are also updating the external mask and display values for the CLID requirements that are bound to be in the exam. 

Just like we did in Step 3, I copy/paste the base query into the scratch pad. Then I will search for the text string "1'" (that is 1 followed by a single quote) and replace it with "2'" (a 2 followed by a single quote). I copy the updated query to my config notepad and then search/replace again. This time I replace "2'" with "3'". Repeat as necessary.

For Site B, I will copy the HQ phones from the config notepad to the scratch pad. If Site B has two phones, I copy the first two HQ phones. Then I use search/replace and change "HQ" to "SB". Assume that HQ DNs begin with 5xxx and SB DNs begin with 6xxx. I use a second search/replace for "'5" (a single quote followed by 5) with "'6" (single quote followed by 6). I then search/replace on the external mask. NOTE: I am using example DN ranges, your DNs may vary.

I'll repeat for Site C. Then you can throw in the following if you want to be sure the grading script doesn't blind side you:

run sql update devicenumplanmap set displayascii = display where display != ''

As I move along my config notepad is growing. It is a good time to save things. Just sayin'.

Step 7: UCCX Lines (optional)

You may or may not need to add UCCX lines. In Step 6, we are updating existing lines (the auto generated DNs). Since auto registration only creates one line, we will need to insert a new entry in the devicenumplan table to map the UCCX DN to the appropriate device. 

The following query will do the trick:

run sql insert into devicenumplanmap (fkdevice,fknumplan,numplanindex,display,displayascii) values ((select pkid from device where description = 'HQ Phone 2'),(select pkid from numplan where dnorpattern='5102'),2,'HQ CCX','HQ CCX')

Using copy/paste and search/replace, I will duplicate the above as many time as required to ensure all of my UCCX agent lines are configured. Of course, I haven't applied anything to UCM yet. This is all done in my configuration notepad. 

Step 8: Apply the Configs

To apply the configurations you will SSH to the CUCM publisher node. Then it is a simply matter of using copy/paste. Note that the UCOS shell doesn't handle buffered input as gracefully as IOS. I found it works best to copy/paste one line at a time or put a line feed between each command. Either way, it isn't much of a time hit.

Step 9: Customize

I tested all kinds of provisioning methods and I even tested a process that leveraged SQL for more of the phone provisioning tasks. I found that configs such as phone services, soft keys, PB templates, etc. were quicker via the GUI. The SQL syntax was either just as fast or slower in some cases. 

A good example of the latter is provisioning end users. You can do all of the end user customization via SQL but since you have to run a half dozen queries per user, it is just easier to use the UI.

Wrap Up

The method I used in the lab is not for everyone. I found it to be quite natural for me because I am comfortable with using SQL to query and update the Informix DB. I tried multiple methods and this one was the fastest. With some practice, I was able to fully provision all phones (including Step 9) in 12 - 15 minutes.

I also think that it is less prone to error. All of the configurations are right there in your notepad. You can review before you apply. You don't have to jump from device to line and back again. 

Thanks for reading. If you have time, post a comment!


  1. Its definatly fast but if you mess with the sql in the lab and do a boo boo :( well then your pretty cooked ? aye ?

    1. To a degree. You could always change direction and recover but, yes, you'd lose a lot of time. Like anything else, you need to practice, practice, practice.