Querying Fast Dials Using SQL Queries in CUCM

We suffered a lapse in our weekly series on Cisco Unified Communications Manager (CUCM) SQL Queries. Getting back on track, I wanted to take a spin off of the installment I presented on querying speed dials and dive into a related CUCM feature: Fast Dials and its partner in crime Personal Address Book (PAB).

I typically leverage this query when doing upgrades for people. I also query this data when customers feel they need to do some sort of house cleaning. Fast Dials have this uncanny way of being forgotten. I can see why. Anyway, on with the show...

Primer

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

This Week's Query

This week's query is pretty straightforward. We want to dump all of the fast dials and PAB entries provisioned for each user in our CUCM cluster. Unlike speed dials, PAB and fast dials are associated to users not devices. 
So, when would you need a query like this? This came up recently when one of my customers said they wanted to change their off-net dialing prefix from a "9" to an "8". The customer engineer was just going to modify route patterns and call it a day. 
Well, that would be a mistake! It is common for people to forget about all of the moving parts that make a system work seamlessly from end-to-end. Consider a change like modifying the dialing prefix and what is impacted: speed dials, fast dials, call forwarding, Unity Connection notification, Unity Connection forwarding, MeetingPlace dialing rules, UCCX scripts, etc. 
With a proper system and dial-plan design, you can certainly control the impact of a change like this, but that isn't the focus of today's blog. Today, we are looking at getting information that can help avoid service disruption to a user. 


I also leverage this query when doing upgrades for people. Upgrades you say? Yes, I am a believer that two-step CUCM upgrades (i.e. 6.1 to 7.1 to 8.5 and the like) are usually (but not always) annoying and a waste of time. Especially if the customer is upgrading hardware platforms or, better yet, moving to a virtual environment. Anyway, when it makes sense I prefer to export data, process it, and import it into the new system. It gives me ultimate control over the situation and the data as it moves from one system to another. Of course, when doing this I don't run queries in the same way I demonstrate in the blog series. I have a tool I developed which runs queries in a batch and does the heavy lifting for me.

The Moving Parts

From a db perspective the following Informix database tables store information relevant to our queries:

• Enduser: This table stores all end users provisioned on the system.
• Personalphonebook: This table stores the "Fast Dial" entries associated to end users.
• Personaladdressbook: As you may have guessed, this is where the "Personal Address Book" PAB entries are managed.

We also want to incorporate one of the type tables: typePersonalphonenumber. This table resolves a type enumerator to an understandable format like "Work".


The Query
Now that we have some information, we can build the query. You can run the query from the CLI using run sql [insert query]:

select uid.userid, uid.firstname, uid.lastname, fd.personalfastdialindex, fd.phonenumber as fastdialdest,
         tppn.name as TypeofFastDial, pab.firstname as AddressBookFirstName, pab.lastname as AddressBookLastName
from personalphonebook as fd inner join enduser as uid on fd.fkenduser=uid.pkid
     inner join typepersonalphonenumber as tppn on fd.tkpersonalphonenumber=tppn.enum
     left outer join personaladdressbook as pab on fd.fkpersonaladdressbook=pab.pkid
order by uid.userid, fd.personalfastdialindex

The following is a sample of the output one could expect from the above query:

userid firstname lastname personalfastdialindex fastdialdest typeoffastdial addressbookfirstname addressbooklastname
====== ========= ======== ===================== ============ ============== ==================== ===================
wjb    William   Bell     0                     93011031000    Home           Ren                  Hoek
wjb    William   Bell     0                     917031041000   Mobile         Stimpson             Cat
wjb    William   Bell     0                     917031031000   Home           Stimpson             Cat
wjb    William   Bell     0                     93011041000    Mobile         Ren                  Hoek
wjb    William   Bell     1                     92025551212    Raw            NULL                 NULL
wjb    William   Bell     2                     94105551212    Raw            NULL                 NULL
wjb    William   Bell     3                     917035551212   Raw            NULL                 NULL

From the above we can see that there are two contacts in my PAB, Ren Hoek and Stimpson J. Cat. The output also shows that I added home and mobile phone information for Ren and Stimpson. We also see three numbers listed as type "Raw". This means that these entries are straight fast dials without any associated PAB entry.

If you have followed the entire SQL query series, by now you should recognize the various techniques used in the above query.  Where pivoting on the personalphonebook table. Since each entry in the PAB table must have a type we are using an inner join with the typePersonalphonenumber table. It isn't required that we join this table. If you don't care whether an entry is a home phone, mobile phone, work phone or "raw" entry then just omit that join and the associated columns in the select clause. The left outer join between the personalphonebook and personaladdressbook tables is necessary because, as shown in the example, not every fast dial is associated with a PAB entry.

Wrap it up

There really isn't much more to add here. I will note that in the sample query we are showing more columns than may be necessary to do a job. I show the additional columns for illustrative purposes. By all means, if you want to streamline the query and show specific information then tweak the various clauses. 

---

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