CUCM SQL Magic (well really just queries) - Part 1

Have you ever been trying to do a mass edit of some sort in CUCM and realized that the field or way you wanted to do it wasn't available to you via the Bulk Administration menu. Or more aptly like me today "Are you @#$@ing kidding me Cisco you are epic fail!". Well then you're in luck my suffering is your gain.

The Resources
The Data Dictionary
In order to work with the CUCM DB one must know thy enemy. That's where the data dictionaries come in. If you go to Cisco.com and do a search for "Data Dictionary" you'll come up with a list of hits select the data dictionary that applies closest to your version of CUCM. Each version of CUCM does not come with data dictionary only when the Devs decide to make schema changes do you get a new one. The horribly created PDF lists each table in the CUCM DB and each column in it. With descriptions of what, who, blah blah.

SQL Cheat Sheet
If you're also like me knowing SQL is near the bottom of things I'd like to know. I'm not a programmer I'm a hack programmer. I can take something rip it apart and hope for the best. That being the case I can't remember SQL syntax from Expect syntax to PHP to HTML, so on so forth and god only knows how many other things are floating around in my brain.

So why know when I can cheat. After all it's not about knowing the answers it's about knowing where to find them. The following site is a nice condensed command reference for SQL which we'll need as CUCM uses a SQL to Informix interrupter.
http://www.sql-tutorial.net/SQL-Cheat-Sheet.pdf

The Task
Look don't touch
Now I highly recommend before you go mucking about in your CUCM DB you 1) take a backup. You might BREAK THE WORLD. 2) Why not look around before we go hacking off limbs willy nilly. So with that in mind lets use the following example.

Find a users voicemail profile based on assigned DN. Example DN = 1234

1.  Login to the CUCM publisher via SSH. (if you don't know what I'm talking about this whole blog post isn't for you probably)
2. Build our query - the syntax in the CLI is as follows: run sql
3. So in our case we want to find the voicemail profile on lines that have DN 1234 assigned. So our statement would be:
   run sql select fkvoicemessagingprofile from numplan where dnorpattern = '1234'

admin:run sql select fkvoicemessagingprofile from numplan where dnorpattern = '1234'
fkvoicemessagingprofile            
====================================
16c584ea-6a81-4138-9ea1-af452de8f75a 

What we've said above is show me (select) the field fkvoicemessagingprofile from the numplan table, where the DN (dnorpattern) is equal to 1234.

This returned the system unique identifier for the lines voicemail profile. But that's not too helpful unless you happened to remember each ID in the system. But we can look this up with another query. Instead of just looking up one lets get a list of all of them in the system. The statement would be:
run sql select name,pkid from voicemessagingprofile where pkid like '%'

admin:run sql select name,pkid from voicemessagingprofile where pkid like '%'
name        pkid                              
=========== ====================================
Default     16c584ea-6a81-4138-9ea1-af452de8f75a
NoVoiceMail 00000000-1111-0000-0000-000000000000

What we can see is above we've said. select the fields Name and PKID(unique system identifier) from the voicemessagingprofile table where the PKID is like %. If you look up SQL operators you'll learn that % is a wildcard meaning one or more characters so anything really.

What we've learned here is our number above is assigned the default voicemail profile. as the PKID matches.

Part two will cover how we change this via the CLI. Stay tuned.... Part 2