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.
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.
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:
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.
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 912125551000Based 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.
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!
Very interesting post, something I have been searching for is a way to modify the existing speed dials system wide with a search of this type. Using this methodology, can you remove "internal" 10 digit speed dials and force them to a "external 9.18XXXXXXXXX type string? If so could you comment on the string used to modify these? (note: this change is for a single number assigned to literally thousands of phones and being replaced with a single external number.)
ReplyDeleteDave,
DeleteIf I follow the requirement, you have a speed dial like 2025551212 and you want to change it to be something like 918025551212. This is like the last example above:
update speeddial set speeddialnumber = CONCAT('918', SUBSTRING(speeddialnumber FROM 2)) where (speeddialnumber = '2025551212')
Or something along those lines.
HTH
-Bill (@ucguerrilla)
Thank you Bill for the response. To be more specific in my question. I need to update internal number 2145558500 to 918006541236.
ReplyDeleteBill,
ReplyDeleteupon further research I have located the corrected string and tested in the lab..
update speeddial
set speeddialnumber = REPLACE('918774357547', speeddialnumber)
where (speeddialnumber like '2144388500%' and LENGTH(speeddialnumber) = 10)
Post running updates such as these, do device resets need to take place to push the updates to the phones? Basically looking for any post script actions that could be needed to finalize the changes that were performed directly in the database.
Thanks in advance
Dave.
Dave,
DeleteThat is a good question. Updates of Directory Numbers via SQL will definitely force a restart of the affected phones. Not sure about the speed dials.
-Bill