Friday, April 20, 2012

Querying Speed Dials Using SQL Queries on CUCM

In this week's installment of my Cisco Unified Communications Manager (CUCM) SQL Queries series I wanted to cover querying for speed dial configurations.

This type of query is handy whenever you are looking to make system wide dial plan changes. For instance, you are going to move from a 4-digit dialing solution to a 5-digit solution or you are going to change your off-net dialing prefix for some reason. Yeah, this actually happens. In fact, it is a recent query from a colleague about change off-net dialing prefixes that inspired this week's installment.

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 speed dials provisioned on each and every phone in our CUCM cluster. 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". This isn't the first time we have had this type of request! A related request I have fielded once or twice - changing abbreviate dialing string length from X to Y (e.g. 4-digit to 5-digit). 

When doing migrations or any system-wide dial plan change, I like to minimize disruption to end users as much as possible. Something that most people "forget" about are speed dials. If you change your abbreviated dialing solution or your off-net dialing prefix then you could break a lot of speed dials. You could just tell the users to reprogram the speed dials or you can fix them. Fixing speed dials can be simple or complicated depending on the need. We aren't going to cover the "fixing" this week but I will reveal some handy tricks in next week's installment.
    The Moving Parts

    From a db perspective the following Informix database tables store information relevant to our queries:
    • Device: This table stores all devices (e.g. IP Phones) provisioned on the system.
    • SpeedDial: This table stores all of the speed dials provisioned on the system with a referential link to the Device table.
    That's it. The information we are looking to present is pretty straightforward. We want to dump a list of devices and associated speed dials for further processing.


    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 d.name, d.description, sd.speeddialindex, sd.label, sd.speeddialnumber 
    from speeddial as sd 
       inner join device as d on sd.fkdevice=d.pkid
    order by d.name, sd.speeddialindex
    


    The following is a sample of the output one could expect from the above query:
    name            description                            speeddialindex label                   speeddialnumber
    =============== ====================================== ============== ======================= ===============
    SEP001AABBB0722 Charles Xavier                         1              Logan                   97035559333
    SEP001AABBB0722 Charles Xavier                         2              Erik Mobile             913105550101
    SEP001AABBB0722 Charles Xavier                         3              Beast Iphone            92025557755
    SEP001AABBB0722 Charles Xavier                         4              Bill "Main Man" Bell    918888041717
    SEP001B2BBB23CC Peter Parker                           1              Aunt May                4037
    SEP001B2BBB23CC Peter Parker                           2              MJ Home                 912125551000
    SEP001B2BBB23CC Peter Parker                           3              Gwen                    4133
    SEP001B2BBB23CC Peter Parker                           4              Felicia Hardy           914105551111
    SEP001CBBB0CED1 Nick Fury                              1              Tony Stark              4033
    SEP001CBBB0CED1 Nick Fury                              2              Luke Home               912125551000
    
    Based on the above output, we see that Charles, Peter, and Nick have speed dials to various on-net and off-net locations. We also see the Mary Jane Watson and Luke Cage share the same home phone. What is up with that? (yeah, I'm a dork.) 

    To be continued....

    We'll wrap up this week's installment up now. Next week, I will go into some SQL methods that can be used to bulk update speed dial entries lickity split.



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


    11 comments:

    1. Do you have any bulk update procedure for speed dials?

      ReplyDelete
      Replies
      1. Are you updating existing speed dials or looking to add new speed dials?

        -Bill

        Delete
    2. Hey Bill,
      I was wondering if you have a bulk add procedure. Moving from an old CM to a new one, I have all the SD's from the old sitting in an excel file. Been looking for a fast, not so painful way, of importing them. Thank you

      ReplyDelete
    3. Hey Bill,
      I am also interested in a bulk add procedure. These will be speed dials from and older CM being imported into a new CM.
      Thanks

      ReplyDelete
      Replies
      1. Phillip,

        The SDs are stored in the table "speeddial". The link to devices are done via a foreign key in the speeddial table (fkdevice). So, you can insert speed dials into the the speeddial table simply.

        What you'll need to handle is the phone button templates on the phone itself. That is a separate set of transactions. BAT would be a better tool for that bit.

        HTH.

        -Bill (@ucguerrilla)



        Delete
      2. Hi Bell, I am using CUCM version-9.1.2, Recently received speed dial change request for 315 phones with same SD number. I have checked in BAT update phone/line option but i couldn't able to see speed dial updation option. Could you please advise on this

        Delete
      3. Sathya,

        The approach depends on the specifics of what you are trying to do. If you are updating an existing SD number to a new number then the query is pretty straightforward.

        For example, say that I have SD number 5000 programmed on phones and I want to change that to 6000 then the following query will handle it:

        update speeddial set speeddialnumber='6000' where speeddialnumber='5000'

        Then, if you had to update label then the following would work:

        update speeddial set label='New Speed Dial Label' where speeddialnumber='6000'


        These examples assume the simplest requirements. Hopefully that helps you out. If you have different requirements then let me know.

        HTH.
        -Bill (@ucguerrilla)

        Delete
    4. Thanks for the info, here's an equivalent command for listing SD-BLFs on a given device which may also be of use:

      run sql select d.name, d.description, blf.blfindex, blf.label, blf.blfdestination from blfspeeddial as blf inner join device as d on blf.fkdevice=d.pkid order by d.name, blf.blfindex

      ReplyDelete
    5. following on from the above ...

      * To find assigned SD-BLFs with a specific DN:

      run sql select d.name, d.description, blf.blfindex, blf.blfdestination, blf.label from blfspeeddial as blf inner join device as d on blf.fkdevice=d.pkid where blf.blfdestination='NUMBER' order by d.name, blf.blfindex

      * To update ALL appearances of the name of assigned SD-BLFs with a specific DN:

      run sql update blfspeeddial set label='LABEL' where blfdestination='NUMBER'

      ReplyDelete
    6. Dan, this has been a tremendous help. My challenge is that the query only returns results if the number has been entered as a speed dial in the "Destination" field and not as a BLF in the Directory Number field. Any suggestions on the syntax for that? I've looked through the data dictionary but I'm at a loss.

      ReplyDelete