Wednesday, May 16, 2012

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!

    5 comments:

    1. Hi William,
      Good stuff. I'm trasitioning into more of an analytics role so have been reading quite a bit of your blog.
      Quick question...I'm in a 1 pub 3 sub environment at cucm 8.5. Are there any rules of thumb re processor impact to the publisher when running cli queries and/or is there a way to stop a query? I'm in a moderately large environment (10k + devices) and don't want to distract the publisher too much.
      Dean.

      ReplyDelete
    2. Dean,

      That is an excellent question. I don't have specific Cisco guidelines on resource impact when running SQL via the CLI. In general, I am conservative. So, I would NOT run queries that return large data sets on a call processing node (i.e. Sub) in a cluster your size. Running on the publisher may be OK, it depends on what other services are running on that node.

      I have run queries (various data set sizes) from the CLI on production publishers in clusters approaching your size and I have not encountered issues. Though, in "real life" I typically use a tool I developed which leverages the AXL/SOAP API to process the queries. I use the tool primarily because it stores my query library and can parse the data to Excel.

      I do not have an answer for interrupting an in-process query.

      I will look into seeing if I can find good data on actual resource impact.

      Thanks,
      Bill (@ucguerrilla)

      ReplyDelete
    3. Thks Bill,

      I'll end up using an external app as well, but was wanting to run some queries when I read that post. I'll probably hold off.

      Dean.

      ReplyDelete
    4. Dean,

      Understood. I have been exploring some development platforms. I am thinking about converting the tools I have written for personal use into something that I may make available through UC Guerrilla. I am working through CCIE-V first and then will get to that. So, it will be a while.

      -Bill

      ReplyDelete
    5. Hello all,I am new and I would like to ask that what are the benefits of sql training, what all topics should be covered and it is kinda bothering me ... and has anyone studies from this course wiziq.com/course/125-comprehensive-introduction-to-sql of SQL tutorial online?? or tell me any other guidance...
      would really appreciate help... and Also i would like to thank for all the information you are providing on sql.

      ReplyDelete