I am going to assume that the reader of this blog has some idea on what Structured Query Language (SQL) is and some of the basic syntax rules. If not, then you may want to poke around this Informix resource. Since CUCM uses Informix, this resource is most applicable.
I myself am not a SQL guru by any stretch. I know more than someone who has never touched it but I am not the expert. Though, I do have a solid handle on the CUCM database schema which helps out quite a bit. Anyway, shall we begin?
Starting with CUCM 5.x, Cisco has done a decent job on posting data dictionaries for CUCM on the CCO site. The dictionaries are in PDF format with basic hyperlinks. So they are not the most navigation friendly beasts but they are decent resources, particularly if you already have a good feel of the data structure from the Microsoft SQL days. If you go to http://www.cisco.com/ and search for “data dictionary” you will see the latest CUCM data dictionaries. A new dictionary is not published for each release of CUCM. They are published when the schema is changed in some significant way.
In 2012 I launched another blog site hosted at http://ucguerrilla.com where I maintain a series that is focused on providing regular installments of SQL query examples for use with Cisco UC applications. If you want to get at a lot of data, really fast then you may want to check it out.
Most of the time I find that the CUCM tables are pretty aptly named, even if you are surprised by what is in them. For instance, IP phones are stored in the device table. You will also find H323 gateways, SIP trunks, MOH, MTP, conference bridges, etc. in this same table. Interestingly enough, you will also find Hunt Lists and Route Lists in the device table.
From my point of view, there are basically three types of tables that you will need to deal with on a regular basis. I don’t know if Cisco makes a distinction, but I do. One type is the standard object table like device or numplan. I place tables which contain the core data values of a particular object like a phone or a route pattern in this type.
A second type is the “enumeration” table. These tables contain (for the most part) easily recognizable names for a particular enumerator. For instance a typeModel enumerator of 30016 is actually the “Cisco IP Communicator” soft phone.
A third type is the mapping table. A mapping table basically connects unique records from one table to unique records in another table.
“Enumeration” tables (btw, that is how I describe them) are used by other tables to resolve enumerator IDs to some name or moniker. That is understating their purpose somewhat, but from a SQL query point of view you will use type tables mainly to resolve enumerator values to a human readable format. An example would be nice I think. You can follow along by using the SQL interpreter built into the CUCM shell (run sql).
Example 1: Let’s first look at a simple query on the device table. We will grab the device name, description field, and model type:
!Query 1! admin: run sql select name,description,tkmodel from device where description like 'Bill%'
name description tkmodel ============ =========== ======= SEP0019B9859AAD Bill's CIPC 30016
So, we grab three fields from the device table. The name field is the name of the device and it should be easily recognizable. The description field is the description as configured in CCMAdmin. The tkmodel field is numeric ID that identifies the device type. Sure the description field gives it away but what exactly is tkmodel 30016?
To find out, we can check the appropriate enumeration table:
!Query 2! admin: run sql select enum,name from typemodel where enum=30016 enum name ===== ===================== 30016 Cisco IP Communicator
Some tables of interest:
- device: Contains information about devices such as IP phones, voice gateways, etc.
- callmanager: Contains information on cluster nodes configured for call processing
- numplan: Table that contains all digit patterns configured in the cluster
- devicenumplanmap: Table that maps line appearances on IP phones to devices and digit patterns
- telecastersubscribedservice: Maps phone services to device subscriptions
- enduser and credential: Tabls that capture information about end user and associated credential information
- processnode: Shows members of the cluster and is used to map cluster nodes to software versions, service parameters, enterprise parameters, etc
So, now we know that typeModel 30016 is a “Cisco IP Communicator” device. Hooray for us. But what if you didn’t want to do this in two steps? Thanks to “join” you do not need to, we could get all of these data with one query:
!Query 3! admin: run sql select d.name,d.description,tm.name as devtype from device as d inner join typemodel as tm on d.tkmodel = tm.enum where d.description like 'Bill%' name description devtype ============ =========== ===================== SEP0019B9859AAD Bill's CIPC Cisco IP Communicator
What this query does is grab two fields from the device table and one field from the typeModel table. Since there is a “name” field in both tables, I decide to specify the typeModel.name field as ‘devtype’. You could call it “George” if you wanted. The important piece is the “inner join” clause. This is where we are telling the SQL interpreter to pull data from one table based on a data value in another.
A SQL “Join” clause basically combines data from two or more tables into a recordset. You have “inner joins”, “outer joins”, “right” joins, and “left” joins. As you get more complex with your SQL queries you may very well use all of these at some point. For the most part, “inner joins” are the staple. So, read up!
Query 3 also provides a glimpse into the naming conventions used in the CUCM database tables. Let’s show one more example:
!Query 4! admin: run sql select d.name,d.description,dmap.numplanindex, n.dnorpattern from device as d inner join devicenumplanmap as dmap on dmap.fkdevice=d.pkid inner join numplan as n on dmap.fknumplan = n.pkid where d.description like 'Bill%' name description numplanindex dnorpattern ============ =========== ============ =========== SEP0019B9859AAD Bill's CIPC 1 4105551234
Query 4 show a very common join set: Device – DeviceNumPlanMap – Numplan. We’ll expand on this triple threat shortly, for now we want to focus on the table fields that are used to achieve the joins in Query 3 and Query 4.
In the CUCM database the developers have done a good job of instituting a consistent naming convention. Once you get some practice in you will be able to figure out where to go intuitively. Well, mostly – I must admin that there are some odd ball fields floating around. But it is somewhat rare.
Whenever a field in a table is linked to another table it will always use the following field name convention:
- fk(x): This is a foreign key (unique value) in another table. That table will be named (x) and the foreign key field will always be pkid.
- fk(x)_(y): This is a foreight key (unique value) in another table. Same rules as above. The _(y) usually indicates that in a given record there is more than one field pointing to the same foreign table.
- tk(x): This is a type key (enumerator) in another table. That table will be named type(x) and the foreign key will always be enum.
- ik(x): This is an internal key, which means it is pointing to a unique row in the same table.
In Query 3 we have an example of how we can pull data from a type table:
inner join typemodel astm on d.tkmodel = tm.enum
So, when you see a field named tkSomething, you know there is a table called typeSomething and that the foreign value used for the inter-table relationship will be named “enum”.
In Query 4 we have two examples of how tables are linked by foreign keys. The deviceNumPlanMap table is one example of a table that actually joins values from multiple tables. We can “marry up” the relationship between a phone and a directory number using multiple joins:
inner join devicenumplanmap as dmap on dmap.fkdevice=d.pkid inner join numplan as n on dmap.fknumplan = n.pkid
Notice that when you see a field named fkSomething, you know there is a table called Something and that the foreign key used for the inter-table relationship will be named “pkid”. It is pretty consistent.
Sometimes, you will find a table that has multiple fields that point to the same foreign table. A good example of this is the numplan table. The numplan table is responsible for storing all digit patterns in the dial plan. For example, it stores all directory numbers programmed in the system. You may recall that directory numbers have call forwarding configurations. For example: Call Forward All, Call Forward Busy, Call Forward No Answer. Each one of these settings on a phone line has a unique calling search space. Calling search spaces are stored in the callingSearchSpace table and are unique records. So, when a record in the numlan table has multiple values that point to another table like callingSearchSpace there needs to be a naming convention. For example:
The above is a subset of fields in a numplan record that point to the same table (callingSearchSpace). The _(y) value in the name gives a clue as to the purpose. For instance “cfb” is CallForwardBusy and “cfbint” is CallForwardBusy Internal.
The data dictionary provided by Cisco has a section called Common Table Relationships. It is a decent section and will give you some ideas of how different tables are linked together. It may help to see one or two more examples.
Fun with SQL
Most of my first SQL queries revolved around the device, numplan, and devicenumplanmap tables. The three amigos. Learning how these tables interact with each other sets the foundation for working with some of the more complicated relationships in the database.
Some handy queries with numplan:
Checking CFA Destinations on IP phone Lines
select d.name as device, n.dnorpattern, cfd.cfadestination from device as d inner join devicenumplanmap as dmap on dmap.fkdevice=d.pkid inner join numplan as n on dmap.fknumplan=n.pkid inner join callforwarddynamic as cfd on cfd.fknumplan=n.pkid where (cfd.cfadestination!='')
Checking MWI Status on IP Phone Lines
select n.dnorpattern,tlb.name, from numplan as n inner join numplandynamic as npd on npd.fknumplan=n.pkid inner join typelampblinkrate as tlb on npd.tklampblinkrate_messagewaiting=tlb.enum
Checking Patterns that have Partition Assigned
select count(*) as count from numplan where fkroutepartition is null
Basic Dump of Device Lines
select d.name,d.description,dmap.numplanindex,n.dnorpattern,r.name as partition from device as d inner join devicenumplanmap as dmap on dmap.fkdevice=d.pkid inner join numplan as n on dmap.fkdevice=n.pkid left outer join routepartition as r on n.fkroutepartition=r.pkid order by d.name,dmap.numplanindex
So, I threw in a few new items such as count(*). Sometimes you just want a total count on something or you may want to show distribution such as how many route patterns are assigned to partition MyRoutePT. You can also use standard SQL clauses like order by to sort your output. Try dumping the device lines without the ordering.
The most interesting may be the left outer join. This is an example of using an outer join and it is a handy tool. I am using it in the above query because I don’t want to lose a record in my output just because digit pattern doesn’t have an assigned partition. For example, in your CUCM cluster create a bogus pattern (non-overlapping/non-conflicting) and do not assign a partition to it. Then run the following queries. I am presenting some test output for demonstration purposes. In my lab cluster there are 77 patterns in the numplan table and one of them does not have an assigned partition:
/*first identify how many patterns on in the numplan table*/ admin: run sql select count(*) as count from numplan count ===== 77 /*next identify how many patterns are using the null partition*/ admin: run sql select count(*) as count from numplan where fkroutepartition is null count ===== 1 /*now run a snippet of our 'dump device lines' to see what happens with an inner join*/ admin run sql select count(*) as count from numplan as n inner join routepartition as r on n.fkroutepartition=r.pkid count ===== 76 /*something missing*/ /*now run the same query using left outer join*/ admin: run sql select count(*) as count from numplan as n left outer join routepartition as r on n.fkroutepartition=r.pkid count ===== 77
You will want to be frugal when using outer joins as performance issues could arise if used incorrectly and you could be dumping more data than you are anticipating.
I knew when I started working with Cisco CallManager that learning the database schema would be a critical factor in being successful in designing, installing, supporting, and maintaining a CM deployment. It gives you a richer understanding of how things are connected and you will be able to get at data quicker than clicking away at the web interface. It is worth the effort in my opinion. Until next time.
NOTE: All example queries in this article were tested on CUCM 7.1(3). Queries should also work on CUCM 6x appliances.
I am probably not done yet, but for now this series continues in the following blogs/articles:
- Running SQL Queries on CUCM Using AXL SOAP toolkit part 2: Here we discuss the actual plugin, downloading it, and using it
- Running SQL Queries on CUCM Using AXL SOAP toolkit part 3: Here we use an example scenario (modifying LDAP) to show how the toolkit can read and write data into the database.
4 responses to “Running SQL Queries on CUCM 6x/7x Using AXL SOAP Toolkit – Part 1”
More excellent stuff! I was a bit thrown off by the use of alias in your select statements but once I figured that out and understood the data directory format I was able to follow. As you say, "I am going to assume that the reader of this blog has some idea on what Structured Query Language (SQL) is and some of the basic syntax rules". The Informix reference you listed is also excellent.
If anyone else is getting hung up on the naming conventions used I would suggest they read the Informix reference at least up to the use of alias in the select statement.
Thanks again for your outstanding contributions to the CUCM community.
Thank you very much for taking the time to post all of this valuable information. I’ve been slowly migrating away from using the standard axl/soap for queries that can be done more directly via executeSQLQuery. Not being a dba, I’m finding a steep learning curve coming up with the right queries.
As an example, the following query works fine, except the ikdeviceprimaryphone field is actually the guid of the phone rather than the device name itself. Would it be possible to get the actual device name without using a 2nd query? (i.e. select name from device where ikdeviceprimaryphone=’xxxx’)
select d.name,d.ikdeviceprimaryphone,eu.userid as ownerid from device as d inner join enduser as eu on d.fkenduser = eu.pkid where d.name=’XXXX’
select d.name, ddup.name as primaryphone,eu.userid as ownerid from device as d inner join enduser as eu on d.fkenduser=eu.pkid inner join device as ddup on ddup.pkid=d.ikdevice_primaryphone
You can use a join on the same table. Just use a different tag name.
Thanks for the heads up. I updated the URL with a different link. Hopefully this one will have a longer shelf life. http://publib.boulder.ibm.com/infocenter/ids9help/topic/com.ibm.pdfs.doc/25122850.pdf