Working With The CUCM Database

Table of Contents

Cisco Unified Communications Manager uses an Informix database for data storage. Accessing this data directly using SQL queries to create custom reports or gather data for analysis has often come in handy. These days I work primarily with the SOAP API, and depending on the information I’m looking for, sending SQL queries via Thin AXL is sometimes more efficient.

This article aims to give you a quick introduction to the CUCM database. You don’t need to know anything about Informix. Still, a basic understanding of relational databases, some basic SQL and an offline copy of Cisco’s Data Dictionary will help you with creating any kinds of queries you might need.

Intro #

There are three main categories of tables in the CUCM Informix database - ‘main’ as in most-used; system tables, mapping tables, and enumeration tables. Then there are dynamic tables and configuration tables.


System tables	Regular tables containing data for a particular CUCM configuration object like devices, devicepools, end users, partitions, etc.
Mapping tables	Mapping primary keys/unique identifiers of one table to those of another. E.g. the DeviceNumPlanMap maps phones (device table) to directory numbers (numplan table).
Type tables	Used by other tables to resolve enumerator IDs to some name or moniker. E.g. contains the human-readable phone model name to the numeric tkmodel value of a phone in the device table. This table is static and cannot be altered.

Both standard tables and mapping tables use primary keys called pkid, whereas type tables use primary keys called enum.

The foreign keys used to link fields in one table with another table or another record within the same table follow a specific naming convention:


pkid	this is the primary key ID.
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 foreign key (unique value) in another table. The table will be named (x), and the “_(y)” is a descriptor which 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. The table will be named type(x), and the foreign key will always be an enum.
ik(x)	Fields that begin with the letters “ik” represent internal keys in the same table. The table will be named (x).

Show all tables in the database #

To navigate the database and understand what tables and data are available to us, we need to identify the tables by name and id. The table IDs between 1 and 100 are used for system information. Table IDs over 1000 are where CUCM stores CDR information.

SELECT 
  tabname, 
  tabid 
FROM 
  systables

SELECT 
  tabname, 
  tabid 
FROM 
  systables 
WHERE 
  tabid > 100 
  AND tabid < 1000

Output:

run sql SELECT tabname,tabid FROM systables WHERE tabid > 100 AND tabid < 1000
tabname                          tabid
================================ =====
dblschemaorder                   101
aarneighborhood                  102
aardialprefixmatrix              103
alarmusertext                    104
availdialplan                    105
axlchangenotifyclient            106
carrierselectprofile             107
ccaprofile                       108
ccdhosteddngroup                 109
ccdhosteddn                      110
cdrmconfig                       111
cmcinfo                          112
codeclist                        113
confidentialaccesslevel          114
...

Show all columns of a specific table #

The first step is to identify the tabid of, e.g. the device table. We know from the previous example that we can get the tabid from systables.

SELECT 
  tabname, 
  tabid 
FROM 
  systables 
WHERE 
  tabname = "device"

Output:

run sql SELECT tabname,tabid FROM systables WHERE tabname="device"
tabname tabid
======= =====
device  753

Using the tabid, we can now get the colname and colno values from syscolumns.

SELECT 
  colname, 
  colno 
FROM 
  syscolumns 
WHERE 
  tabid = "753"

Output:

run sql SELECT colname, colno FROM syscolumns WHERE tabid="753"
colname                               tabid colno
===================================== ===== =====
pkid                                  753   1
name                                  753   2
description                           753   3
tkmodel                               753   4
tkdeviceprotocol                      753   5
tkprotocolside                        753   6
specialloadinformation                753   7
fkdevicepool                          753   8
fkphonetemplate                       753   9
fkcallingsearchspace                  753   10
ctiid                                 753   11
tkclass                               753   12
fkprocessnode                         753   13
defaultdtmfcapability                 753   14
fklocation                            753   15
...

Or combine the two queries into one.

SELECT 
  c.colname, 
  t.tabid, 
  c.colno 
FROM 
  systables AS t, 
  syscolumns AS c 
WHERE 
  t.tabname = "device" 
  AND t.tabid = c.tabid

Cisco Resources #

Cisco created Data Dictionaries for all CUCM releases. The Data Dictionary describes the data stored in the database in meticulous detail; every table, field, element, data type, and relationship.

The Cisco AXL documentation archive https://developer.cisco.com/docs/axl/#!archived-references contains CUCM Data Dictionaries for all releases since 7.1(3).