Running SQL Queries on CUCM 6x/7x Using AXL SOAP Toolkit – Part 1

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?

Cisco Resources

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.

Updated Information!

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.

Tables

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

Table Relationships

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.

The Conventions

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:

• fkcallingsearchspace_cfapt
• fkcallingsearchspace_cfb
• fkcallingsearchspace_cfbint
• fkcallingsearchspace_cfna
• fkcallingsearchspace_cfnaint

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.

The Series

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.