Using SQL to Fix CUCM Enterprise Services

In this installment of the SQL query series I wanted to actually explore some queries that came in handy during a CCIE-V mock lab. One of the lab questions was to disable the Corporate Directory look-up functionality on one of the phones in the lab. There are several ways to approach this. I did a write up discussing how to disable the Corporate Directory on my NetCraftsmen blog some time ago.  

It just so happens that one of my fellow classmates went the path of deleting the Enterprise Subscription completely. Of course, this broke other phones in the lab. So, what do you do? You re-add the Enterprise services and all is well, right? Not quite, this creates a problem that can only be resolved by using your handy-dandy SQL commands.

Primer

A brief primer on CUCM SQL is provided in the first blog of this series.

This Week's Query Queries

For this week's installment we are going to explore a recent "real world" scenario where someone has removed phone services with the Enterprise Subscription and then, realizing their mistake, added them back in only to find things are not presented exactly how they used to be.

We will look at two queries. The first is a basic query to demonstrate the problem. The second query will provide an example of how we can use SQL queries to fix the issue.

The Moving Parts

From a db perspective we only need to play with the following Informix database table:

• TelecasterService: This table stores relevant parameters for all Phone Services (Enterprise or other) that are provisioned on the system

The Issue

While the use case that prompted this blog entry occurred during a CCIE-V mock lab scenario. The requirements that led to the issue are common in production environments. In this case, I was at the IPexpert bootcamp (week 1) and we were working on a question to disable all directories (Corporate, Personal, Received, Missed, Intercom, and Placed calls) on one IP phone. 
This is actually pretty straightforward if you have done it before (as I discuss in my blog on disabling Corporate Directory). However, if you haven't done it before, you could easily run yourself into a problem, as one of my fellow classmates found out.

The problem was that he removed the services altogether and then added them back in with the Enterprise Subscription flag disabled. Once he realized that he broke directories on other phones, he removed them again and added them back in with the Enterprise Subscription flag enabled. This fixed the functionality problems for other phones but there was an interesting side effect. When someone selected Directories on the phone, they were listed in alphabetical order. Which, is not the order they are in "out of the box". In real life, this may or may not be an issue. In the CCIE-V lab, this could cost you points for sure.

The Query
Let's start at the moment my classmate realized that re-provisioning the Enterprise Subscriptions had an unwanted side effect. There is nothing in the CUCM admin portal to tell you what is happening. We have to look under the hood. 
The query I used is a simple query to take a peak at how the services are provisioned from a DB perspective. You can run the query from the CLI using run sql [insert query]:

select name, priority, urltemplate, pkid
from telecasterservice

The following was the output from this query:

name                priority urltemplate                          pkid
=================== ======== ==================================== ====================================
Missed Calls        50        Application:Cisco/MissedCalls        d0059763-cdcc-4be7-a2a8-bbd4aac73f63
Received Calls      50        Application:Cisco/ReceivedCalls      0061bdd2-26c0-46a4-98a3-48a6878edf53
Placed Calls        50        Application:Cisco/PlacedCalls        a0eed443-c705-4232-86d4-957295dd339c
Intercom Calls      4        Application:Cisco/IntercomCalls      27f92f3c-11ed-45f3-8400-fe06431c0bfc
Personal Directory  50        Application:Cisco/PersonalDirectory  4a9d384a-5beb-4449-b176-cea0e8c4307c
Corporate Directory 50        Application:Cisco/CorporateDirectory 7eca2cf1-0c8d-4df4-a807-124b18fe89a4
Voicemail           1        Application:Cisco/Voicemail          ca69f2e4-d088-47f8-acb2-ceea6722272e

I didn't include the field that flags whether the services are enabled or provisioned as Enterprise Subscriptions. That was unnecessary as I could see this plainly from the admin web portal. What is of interest here is the priority field. Notice that all of the directory services have an equal priority of "50".  This is where we need to make a hypothesis. Remember that the symptom is directories are being presented in alphabetical order. We have to assume that is because they have equal priority. 

Because we were doing the IE lab and exploration is the mode we were in, we did test removing and re-adding services in a different order just to see if that changed anything on the phone. It doesn't, the phone is ordering these services by name.

The Resolution
OK, so now we know what our problem is and we have a pretty solid hypothesis that the priority field is the culprit. We also know we can't tweak the priority using any standard UI. So, what next? If you have been following the CUCM SQL series, you should know my answer to this question by now: we use the SQL update facility to fix the priority fields. 

First, we copy/paste the pkid values from our first query into notepad. Then we build update queries using the following structure.

update telecasterservice
set priority=[priorityValue]
where pkid=[pkidValue]

So, using our scenario we create five update queries:

update telecasterservice set priority=1 where pkid='d0059763-cdcc-4be7-a2a8-bbd4aac73f63'
update telecasterservice set priority=2 where pkid='0061bdd2-26c0-46a4-98a3-48a6878edf53'
update telecasterservice set priority=3 where pkid='a0eed443-c705-4232-86d4-957295dd339c'
update telecasterservice set priority=5 where pkid='4a9d384a-5beb-4449-b176-cea0e8c4307c'
update telecasterservice set priority=6 where pkid='47eca2cf1-0c8d-4df4-a807-124b18fe89a4'

You can execute these update queries from the CLI or using the AXL API. It is also a good use for the Cisco AXL SQL Toolkit. 

Verification

Now, when we re-run our first query to list the services and priorities we see the following:

name                priority urltemplate                          pkid
=================== ======== ==================================== ====================================
Missed Calls        1        Application:Cisco/MissedCalls        d0059763-cdcc-4be7-a2a8-bbd4aac73f63
Received Calls      2        Application:Cisco/ReceivedCalls      0061bdd2-26c0-46a4-98a3-48a6878edf53
Placed Calls        3        Application:Cisco/PlacedCalls        a0eed443-c705-4232-86d4-957295dd339c
Intercom Calls      4        Application:Cisco/IntercomCalls      27f92f3c-11ed-45f3-8400-fe06431c0bfc
Personal Directory  5        Application:Cisco/PersonalDirectory  4a9d384a-5beb-4449-b176-cea0e8c4307c
Corporate Directory 6        Application:Cisco/CorporateDirectory 7eca2cf1-0c8d-4df4-a807-124b18fe89a4
Voicemail           1        Application:Cisco/Voicemail          ca69f2e4-d088-47f8-acb2-ceea6722272e

Considerations

This fix is fairly simple and while I think it would be hard to mess things up I should provide a disclaimer. As with any update operation, you should make sure you are paying attention to what you are doing. If you aren't comfortable with the commands, get some stick time on a lab or lower environment system.

---

Thanks for reading. If there is a specific query you are interested in seeing, let me know using the comments!