Updating 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 build on last week's query with a special treat: Using the update SQL query to make changes to database entries.

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 concerning off-net dialing prefix changes that inspired this week's installment.

Primer

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

...and a disclaimer:

Be careful when using update queries as you could have unwanted side effects. Make sure you understand what you are updating, test thoroughly, and have a backup strategy. I am not responsible for any damage you wreak as a result of applying the samples in this blog. ;-)

This Week's Query

This week's query is a little on the tricky side simply because you can break a things if you aren't careful. So, please be careful. What we are going to do is make a system wide change to speed dial configurations (all) using a single SQL update from the CLI.

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.


Recap of last week's query...
Last week, we used a select query to dump the speed dials assigned to specific devices. Our example record set follows:

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 can infer that we are using a 4-digit internal dialing solution for extension-to-extension dialing and that we are using a "9" as an off-net dialing prefix.


The Update Queries - Abbreviated Dialing "Expansion"

OK, now that we are up to speed we can talk about some simple updates to our speed dials. Let's say that you wanted to modify any speed dials to internal extensions from a 4-digit dialing solution to a 5-digit solution. Also, let's keep it simple and assume you know that any 4-digit pattern that starts with 4xxx is in the 555 NXX and that your 5-digit dialing solution will be 54xxx.

Pretty straightforward. We can use the following query to make the change:

update speeddial 
set speeddialnumber = CONCAT('5', speeddialnumber)
where (speeddialnumber like '4%' and LENGTH(speeddialnumber) = 4)

If you run the above from the CLI (run sql update "query") then the response will be a one liner that says "Rows: X", where X is the number of records updated. So, let's dissect this a little. The update query type is exactly what it sounds like: we are updating an entry or set of entries in some table. We identify the table as speeddial because that is where our speed dials are stored. The set command is identifying the field we want to update. We are using the concatenation function CONCAT to basically concatenate two strings (i.e. prefix a 5 on the existing speed dial number). In Informix there is a concatenation operator too (||) but the UCOS CLI can't parse that. Finally, we are using a where clause to avoid a catastrophe (i.e. we update all speed dial numbers). Basically we are looking for speed dial entries that "start with 4" and are "4-digits long".
The Update Queries - Off-Net Access Code go Bye-Bye


OK. So now let's say we want to change the off-net access code from "9" to "8". We can use an update query to accomplish this task as well:

update speeddial
set speeddialnumber = CONCAT('8', SUBSTRING(speeddialnumber FROM 2))
where (speeddialnumber like '9%' and LENGTH(speeddialnumber) > 7)

This update query is similar to the abbreviated dialing conversion example. We have thrown in another function: SUBSTRING. Basically, we are chopping off the first digit in the speeddial number and sticking an "8" on the front of the remaining numbers. The WHERE clause is ensuring we are only catching numbers that begin with a "9" and are greater than 7-digits long (assuming a 7-digit local dialing NPA in the North American Numbering Plan - NANP).

Again, if your query works you will see output that identifies the number of records that were modified.

Conclusion
I wanted to reiterate that you should be extra cautious when doing update queries. They are deceptively simple (sometimes, anyway) and can do some real damage if you are not careful. So, use these (and future examples I throw out there) with caution. I am not responsible for any bad joojoo that should arise.
 

---

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