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

A colleague of mine asked if I would do a blog on using the AXL/SOAP interface to execute custom queries for administrative purposes.  I thought that this would be an interesting topic to cover.  Though, it isn't something that can be fully addressed in one blog.  So, I think this will need to be a series of blogs/articles.  For Part 1 I think we will discuss the basic table structures and some key tables that are commonly queried by admins.

 

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:

 

 

 

 

 

Comments (16)Add Comment
feedSubscribe to this comment's feed
0
...
written by Mark, June 16, 2010
Hi Bill,

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.

Mark
0
inner join on same table?
written by Will, July 07, 2010
Hi Bill,

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'


thanks,
Will
William Bell
Re: Inner join on same table?
written by William Bell, July 16, 2010
Will,

Try:
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.

HTH.

Regards,
Bill
0
Network Designer
written by Pete Hall, October 15, 2010
Hi Bill, we upgraded our old call manager to 7.1(3). If I remember right, I think that there was a field to show what the phone load software was. Is there a way on the new call manager to use the run sql command to get the phone load version info on our devices?

Thanks Pete
William Bell
Re: Network Designer
written by William Bell, October 18, 2010
Pete,

From the database, you can check for custom phone firmware assigned to a device using this query:

admin: run sql select name,description,specialloadinformation from device where specialloadinformation ''

You can also get this list by going to the Firmware Load Information page (Under Device Settings).

If you want to see the default load information, you could go to the Device Defaults page, or run this query:

admin: run sql select tm.name as model,d.loadinformation from defaults as d inner join typeModel as tm on tm.enum=d.tkModel order by model

Now, these queries and associated web pages will only show what is configure, it does not show what is running on the phone. The CUCM does not store this information, you actually need to get that information from the IP phone or CDP neighbor table on access switches (assuming you are using Cisco switches). We typically use a custom script that will browse to the HTTP server on IP phones and "scrape" the firmware information in the HTML.

HTH.

Regards,
Bill
0
Network Designer
written by Pete Hall, October 19, 2010
Thanks Bill for posting that. I did try run sql select name,description,specialloadinformation from device where specialloadinformation and the result I got was:
admin:run sql select name,description,specialloadinformation from device where specialloadinformation
Result of a boolean expression is not of boolean type.

The other one worked.

There is a spot on every device page on the call manager:
Registration Registered with Cisco Unified Communications Manager 10.255.248.3
IPv4 Address 10.77.48.109
Active Load ID Unknown (But this spot looks like the last load of the phone from the call managers perspective)

Yes, we are running Cisco switches and the CDP info does work. The new CER does have the ability to run a snmp query from the command line, not sure if we could leverage that?

Thanks Pete
William Bell
Re: Network Designer
written by William Bell, October 19, 2010
Pete,

Looks like our blog comment engine has bitten me again. It doesn't like escape characters maybe. Anyway, in the query for checking device configurations, the where clause should be:

where specialloadinformation <> ''


That is a "less than" followed by a "greater than" symbol (as in "NOT EQUAL TO").

I am not sure if CER is going to store the firmware information that is exposed in the CDP tables.

If you want to see a record of the actual firmware running on the device then you need to web to the IP phone in question. The CUCM isn't going to display actual run time.

HTH.

Regards,
Bill
0
Informix Resource
written by Dami, March 22, 2011
Hi Bill,

The Informix Resource referenced in your post is no longer available.
I get the following "Firefox can't find the server at www.dbcenter.cise.ufl.edu."

Can you please point me to another URL.

Thanks Mate!!
William Bell
Re: Informix Resource
written by William Bell, March 24, 2011
Dami,

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/...122850.pdf

HTH.

Regards,
Bill
0
Where/how should i run the SQL in CUCM ?
written by Rajinder, October 20, 2011
I have run some sql for testing could any one please tell me where (on what console) should i run the queries.
William Bell
Re: Where/how should i run the SQL in CUCM
written by William Bell, October 20, 2011
When you logon to the console you can run a SQL query as follows:

admin:run sql select * from processnode

HTH.

Regards,
Bill
0
Systems Engineer
written by Way, August 23, 2012
I am able to successfully run my query and see my result but how do I export that data out to a text file and possibly to another server (windows server).
William Bell
Re: Export data
written by William Bell, August 31, 2012
Way,

If you were running the command from the CLI (e.g. run sql query (query)) then you would have to log your terminal session or copy/paste the output. If you are using the AXL/SOAP toolkit then you can write the output to a file. The challenge is that the contents will be in XML format. So, you'll need to pre-process to parse.

HTH.

-Bill (@ucguerrilla)
0
VOIP Enginner
written by Liam, December 12, 2012
I want to query the number,css and srst refernce alltogther but the problem is that i can only get the number and css with following command. And if you could add in the IP Address of the phone that would be great

SELECT callingsearchspace.name, dnorpattern
FROM numplan, callingsearchspace
WHERE numplan.fkcallingsearchspace_sharedlineappear = callingsearchspace.pkid AND dnorpattern='48601
William Bell
Re: VOIP Enginner
written by William Bell, December 13, 2012
Liam,

To get the SRST reference you have to query the device pool type by way of the device table. Adding IP address information via SQL and AXL/SOAP is not possible. You need to invoke RISport API for that information (or SNMP). I'll look at the query and will put it in queue for my CUCM SQL Query series which is hosted on my personal blog site: http://ucguerrilla.com. I have lots of query examples on that site.

HTH.

-Bill (twitter: @ucguerrilla)
0
...
written by Mark, April 19, 2013
How can I make a sql query to Active Load ID?

Write comment

busy