CUCM SQL Queries: Supplemental - Informix System Catalogs

I recently fielded a request on Twitter (@ucguerrilla) for Cisco documentation that covers the database schema and structure. This request is the inspiration for this supplemental entry to my series on CUCM SQL queries. In this supplement we are going to expand on the initial primer and demonstrate ways to use SQL queries to explore the system tables of the Informix database. When I have a need to get at data in Cisco Unified Communications Manager (CUCM) I leverage the Cisco's data dictionary along with some "exploration" of the Informix system tables. 

Primer (Redux)

I provided a brief primer in the first blog of this series but I think it is worthwhile adding some additional info as well as expanding on previously referenced resources:

Basic Syntax

Throughout this series I will present SQL queries as a command block such as:

select name,description
from device
where description like '%Guerrilla'

You can execute the above query directly from the CLI (which is assumed throughout this series) or by using the executeSQLQuery command via the AXL/SOAP interface. From the CLI the above would look like:

admin:run sql select name,description from device where description like '%Guerrilla'

Notice that we use the "run sql" command from the CLI.

Cisco Resources

Starting with CUCM 5.x, Cisco has done a good job on posting data dictionaries for CUCM on the CCO site.  The dictionaries are in PDF format with basic hyperlinks.  So, they are not what I would consider the most user friendly resources. Of course, we are talking about a data dictionary and there is only so much "flare" you can add to content covering db table fields, data types, references, etc.

You can find the data dictionaries for various CUCM releases using this link: CUCM Data Dictionaries. 

Informix Resources

Informix is the relationship database management system (RDBMS) used on the Cisco UCM appliance (as well as many other Cisco UC apps). You don't need to know anything about Informix to get started with exploring the CUCM database. However, you do need to understand some basics around SQL and, as you try to perform some advanced queries, you will need to learn a little about the specific syntax expected by this RDBMS. I started with this tutorial which is based on Informix IDS version 10.0 (which was EOL in 2010). That's OK, I still find the resource helpful. The current Informix version used by CUCM 8.x is 11.5. A great place for support info is provided by IBM at the Informix IDS 11.5 Information Center.

This week's query (or query set?) focuses on exploring the Informix database structure via the system tables. So, in the spirit of that goal we will look at our first query: how can you tell which version of the Informix IDS is running on your CUCM?

select DBINFO('version', 'full')
from systables
where tabid=1

Output from the above query on my CUCM 8.5(1)SU3 system:

admin:run sql select DBINFO('version', 'full') from systables where tabid=1
(constant)
=================================================
IBM Informix Dynamic Server Version 11.50.UC5XE

The QueryQueries

For this week's post I wanted to introduce some tools that may come in handy when trying to put together a query on the system. Cisco provides a Data Dictionary that anyone can download. This resource is handy when you are looking to build a query involving tables you haven't much familiarity with. However, I find it is often quicker (for me anyway) to query system tables directly from the CLI. This is definitely more efficient when I am trying to find the exact column name in a table (or set of tables) that I already have some familiarity.

The Example

If we want to keep this blog from meandering on forever, we need an example to walk through. Let's walk through our recent installment of this series: Displaying Devices and Assigned DNs.

The Moving Parts

We are going to be working with the Informix system catalogs to get at the information we need. Without going too deep into this, the system catalogs can be thought of as metadata for the database. We will be using the systables and syscolumns catalogs.

The systables table contains some very basic information about all of the tables in the Informix database. A simple query can be used to dump all of the system tables:

select tabname from systables

The above will list all of the table names within the database, including systables. If you are only interested in tables that were created by Cisco then you can use the following query:

select tabname
from systables
where tabid > 99

In Informix, the first new table added to the database after the database is created will start with the ID of 100.

The syscolumns table identifies all columns associated with a table. The systables and syscolumns table are linked by a foreign key known as tabid (which is the primary key for systables). So, if you wanted to see a list of tables and their associated columns, you could use this query:

select t.tabname, c.colname
from systables as t inner join syscolumns as c on c.tabid=t.tabid
order by t.tabname

Side Note: The above query can also be expressed as: select t.tabname, c.colname from systables t, syscolumns c where c.tabid=t.tabid order by t.tabname. In this series, I am not planning on digging into query styles too heavily, but I thought it was worth noting an alternative here.

The above may give you output similar to the following:

tabname                       colname
============================= =============================
aardialprefixmatrix           pkid
aardialprefixmatrix           fkaarneighborhood_from
aardialprefixmatrix           fkaarneighborhood_to
aardialprefixmatrix           dialprefix
aarneighborhood               pkid
aarneighborhood               name
alarmconfig                   pkid
alarmconfig                   fkprocessnodeservice
alarmconfig                   tkmonitor
alarmconfig                   tkalarmseverity
alarmconfig                   server
alarmconfig                   monitorstatus
alarmconfig                   enablealternativelog

[stuff deleted]

There's more but let's move on to the example. Assume we know the following:

• We know that devices are stored in the device table
• We know that directory numbers are stored in the numplan table
• We know that links from one system table to another will us the convention fkForeignTableName
• We know that system tables have a unique key of type GUID that is named pkid

The Process

First, we need to determine if the device and numplan tables are linked in any way. We can do this using the following query:

select count(*)
from systables as t, syscolumns as c
where t.tabid=c.tabid and t.tabname='device' and c.colname='fknumplan'

The output from the above will be: 0. So, we know that the device table is not referencing the numplan table. We can reverse the parameters to check whether numplan references device:

select count(*)
from systables as t, syscolumns as c
where t.tabid=c.tabid and t.tabname='numplan' and c.colname='fkdevice'

Again, we would get a big goose egg. Now, we know the data in device and the data in numplan need to marry up somewhere but how can we figure out the relationship? We can certainly use the Data Dictionary. We can also use a query that helps us zero in on the answer. Maybe a query like this one:

select t.tabname from systables as t
where ((select count(*) from syscolumns as c 
         where c.tabid=t.tabid and c.colname='fkdevice')>0) and
      ((select count(*) from syscolumns as cc 
         where cc.tabid=t.tabid and cc.colname='fknumplan)>0)

Oh boy that is ugly! Basically we are saying "show me tables that have a column named 'fknumplan' and another column named 'fkdevice'. The output from this query:

tabname
===================
batjob
blfdirectedcallpark
blfspeeddial
devicenumplanmap
intercomdynamic

At this point we have to use our powers of deduction (or risk iterating through each table blindly). So, let's make a hypothesis: We know we need the device table and the numplan table and we know we need to map them together. Hmmmm, devicenumplanmap! Now that looks promising. 

What other goodies are in this devicenumplanmap table? Let's ask the database:

select t.tabname, c.colname
from systables as t, syscolumns as c
where t.tabid=c.tabid and t.tabname='devicenumplanmap'

The output from the above query:



tabname          colname
================ ===============================
devicenumplanmap pkid
devicenumplanmap fkdevice
devicenumplanmap numplanindex
devicenumplanmap label
devicenumplanmap fknumplan
devicenumplanmap display
devicenumplanmap tkringsetting
devicenumplanmap ctiid
devicenumplanmap e164mask
devicenumplanmap dialplanwizardgenid
devicenumplanmap tkmwlpolicy
devicenumplanmap tkringsetting_consecutive
devicenumplanmap maxnumcalls
devicenumplanmap busytrigger
devicenumplanmap callinfodisplaymask
devicenumplanmap labelascii
devicenumplanmap displayascii
devicenumplanmap tkringsetting_activepickupalert
devicenumplanmap tkringsetting_idlepickupalert
devicenumplanmap tkpartitionusage
devicenumplanmap speeddial
devicenumplanmap fkrecordingprofile
devicenumplanmap fkcallingsearchspace_monitoring
devicenumplanmap tkstatus_audiblemwi
devicenumplanmap logmissedcalls

You can see from the results there are all sorts of opportunities for embellishing the query to enrich the data that is returned. At this time, we are going to keep things relatively simple. We at least know that the way we can join records in the device table and records in the numplan table is via devicenumplanmap. 


The next step in the process is to determine what fields we want to display. If we don't know what things are called then we may need to take a look at the columns in the device and numplan tables. These tables have many columns, so we have to keep that in mind. We could poke around these tables using the following query:

select t.tabname, c.colname
from systables as t, syscolumns as c
where t.tabid=c.tabid and (t.tabname='device' or t.tabname='numplan')
order by t.tabname

To conserve on screen space, I am not going to dump the output from the above query here but you can certainly run the query on your (lab?) system to follow along. We again have to use our powers of deduction. For instance, it seems obvious that the column name in the device table is probably the device name (e.g. SEPDEADBEEF0001) and that the column description is likely the description field (e.g. Bill's 9951). We can also infer that the dnorpattern column in numplan is the actual directory number (e.g. 12345). 


Now, we have enough information to kick out a query that shows numbers assigned to a specific device:

select d.name,d.description,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
order by d.name

Sample output:



name            description     dnorpattern
=============== =============== =============
SEP3037A61747C7 WJB 9951        \+13011055202
SEPDEADBEEF0002 Bria on iPod    \+13011055200
SEPDEADBEEF0003 Bria on Mac     \+13011055204
TCTWJBIPOD      WJB IPOD        \+13011055205

Conclusion

If you are familiar with SQL and are trying to get your mind wrapped around the CUCM database schema then you definitely want to go through the Data Dictionary that Cisco provides. Once you get to a point where you are comfortable with the tables but are sometimes missing some piece of information to finish your query build then you can poke around the system tables and columns directly on CUCM. It may not seem like it on the surface, but with enough background using the system collections is notably more efficient. Or, it could be just the way I am wired?

---

Thanks for reading. If there is a specific query you are interested in seeing, let me know using the comments!