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.
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.
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]:
The following is a sample of the output one could expect from the above query:
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.
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 912125551000Based 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!
Do you have any bulk update procedure for speed dials?
ReplyDeleteAre you updating existing speed dials or looking to add new speed dials?
Delete-Bill
Hey Bill,
ReplyDeleteI 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
Hey Bill,
ReplyDeleteI am also interested in a bulk add procedure. These will be speed dials from and older CM being imported into a new CM.
Thanks
Phillip,
DeleteThe 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)
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
DeleteSathya,
DeleteThe 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)
Thanks for the info, here's an equivalent command for listing SD-BLFs on a given device which may also be of use:
ReplyDeleterun 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
following on from the above ...
ReplyDelete* 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'
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.
ReplyDeleteWhich query are you using?
Delete