First Attempt with SQL Commands: Search and Change Speed Dials via CUCM CLI

Ok, so I recently was asked how to locate all devices in a cluster that had a specific speed dial number configured.  It was a hypothetical question for a very large enterprise cluster with thousands of users.  We very quickly boiled it down to two possibilities.

1. Export all phones to excel and search for the speed dial
2. Use an SQL command to interrogate the CUCM's database directly.

Number 2 sounded more fun to me.  (Note:  I had to giggle just a little bit when I typed that, after all, men never really grow up.)

After some research in the great Internet, it was quickly obvious that William Bell (@ucguerrilla) was one of the top experts on this topic in the small world of Cisco voice engineers.  I found some examples on his website that almost did exactly what I wanted - but not quite.  That's OK though, I am game for a challenge.  I spoke with him briefly via Twitter and he gave me some great ideas that I just had to test out.  Btw, he has a very good article to get the juices flowing here.

I also hypothesized that once I had the list of devices with a given speed dial, I might want to change them!  This could be a very real operational problem if an organization, external vendor or other critical contact changes their phone number. 

So, I proceeded to test on a lab CUCM to see if I could find success with what HAD TO BE a fairly simply request.  After some problems with typos and syntax, I did find success!  (and a new found attraction to finding new ways to leverage this powerful method of manipulating the CUCM database!)

See the actual commands below.  Thanks again to William for his expertise!

STEP 1:  Search all devices for a specific speed dial number

This command will list all devices that have a given speed dial configured. In this case, the 314 number.

admin:run sql 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 where speeddialnumber like '13145551212'

Output below (shows only one match):

name            description     speeddialindex label      speeddialnumber

=============== =============== ============== ========== ===============

SEP009C028CFA48 Baba Booey 1000 1              Cell Phone 13145551212  

STEP 2:  Change all speed dials from one number to another

This command will change all speed dial entries from one number to another number (explicit match). Note: After the command is run, all phones that had the speed dial changed will reset.

DISCLAIMER:  CHANGING SPEED DIAL IN THIS MANNER WILL RESET THE PHONE IMMEDIATELY.

admin:run sql update speeddial set speeddialnumber = '12125551212' where speeddialnumber = '13145551212'

Rows: 1

It seemed like the command worked, but let's make sure!

STEP 3:  Confirm that the correct changes have been made

This command will display all speed dials for ALL devices. It’s not a very useful command in an enterprise, but it can be manipulated with additional “where” qualifiers. In this case, I just wanted to confirm that step 2 above changed only the speed dial desired. Note: it is the same as the command from step 1, just without the “where” qualifier.

admin:run sql 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

Output below:

name            description       speeddialindex label      speeddialnumber

=============== ================= ============== ========== ===============

SEP009C028CFA48 Baba Booey 1000   1              Cell Phone 12125551212     <-

SEP009C028CFA48 Baba Booey 1000   2              TAC        18005532447    

SEP009C028CFA48 Baba Booey 1000   3              Zebar      13091234567    

SEP009C028CFA49 David Holman 1002 1              Cell Phone 16141234567    

SEP009C028CFA49 David Holman 1002 2              Baba       15851234567    

SEP009C028CFA49 David Holman 1002 3              Baba 2     16141234567