I would call this a Deep Dive but since there are several hundred tables and we are only looking at a few I’ll call it a Shallow Dive.
There will occasions where when the SQL database needs to be queried for a variety of purposes. Cisco Call Manager 8.x uses an IBM Informix Database for storing data.
Below is a list of some of the tables that are frequently accessed when looking into Phones, Devices and Directory Numbers. These tables and the fields that are in them are defined in CUCM 8.6(1) Database Dictionary.
|Table Name (tabid)||Table Use Description|
|device (596)||This table contains device-level information like IP Phones and Gateways. This table lists every device from the Call Manager perspective.|
|numplan (634)||This table contains information about all directory numbers and number patterns in CUCM.|
|devicenumplanmap (646)||This table contains the mapping between data in Device table and NumPlan table. It is an ordered association of NumPlan records with a device (line appearance)|
|routepartition (549)||This table contains the information about all the Partitions configured in CUCM.|
|enduser (525)||This table contains the information related to End Users.|
|devicenumplanmapendusermap (647)||This table contains the mapping between data in the DeviceNumPlanMap table and the EndUser table.|
|endusernumplanmap (648)||This table contains the mapping between the EndUser table and the NumPlan table.|
|enduserdevicemap (623)||This table contains the mapping between data in the Device Table and EndUser table.|
|typeproduct (453)||This table is a complete list of products that can be configured in CUCM.|
|defaults (580)||Specific default attributes for devices in CUCM.|
|location (316)||This table is used to store the geographical location of devices.|
|callmanager (513)||Lists every Call Manager in the system|
|Term||Term Meaning, description|
|pkid||Primary Key Identifier for a specific table.|
|fk||Fields that begin with fk are the pkid in another table. These are like links between tables.|
|ik||Fields that begin with ik represent foreign keys in the same table.|
|tk||Fields that begin with tk represent enumerated types. This field is related to a table whose name begins with Type and ends with the name of the field following the prefix up to but not including an underscore character.|
systables is a system table in the databsae that has metadata about all the tables.
run sql select * from systables
The output from this command is overwhelming so the next query will show more relevant information.
run sql select tabname,tabid from systables
This only shows the table name and the table id. The table id field is linked to the foreign key (see above fk) in syscolums table.
NOTE: You will go back to this example over and over again and you explore the data that is in the different tables, columns, and fields.
In this example, we will show all the columns in the device table.
First, we will determine a unique identifier for the device table, in order to do that we will query the systables to find a unique id for the device table.
run sql select tabname,tabid from systables where tabname=’device’
The unique id is ‘596’. We know from the first example that the tabid is shared between systables and syscolumns. Now let’s write a query to determine all the columns in a table.
run sql select colname,tabid,colno from syscolumns where tabid='596'
This example can be used to determine all the columns in any table once you have the tabid from the systables table.
There are times when data is needed from columns in more than one table. This is an example of how to write that query.
In this example, we will
* Find a user in the enduser table.
* Determine their mapping in endusernumplanmap.
* Find the user's DN in numplan
* Write a query that shows the user's friendly name and DN.
* Show how to summarize table names using as and prefixes.
First, let’s find the user in enduser table. Looking at the list of columns in enduser we see that one of them is userid. Let’s try and hit that.
run sql select pkid,userid from enduser where userid='tglen'
Since we need the friendly name lets query that table again and request more info.
run sql select pkid,userid,firstname,lastname from enduser where userid='tglen'
Second, we will use that pkid GUID to find the user in the endusernumplanmap.
From the Commonly Used Terms table above we know that fk is just a link to a pkid in another table.
run sql select * from endusernumplanmap where fkenduser='79be17c9-ec81-4d4f-ba19-33267bab9a9c'
Third, we will the use the fknumplan GUID to search the numplan table for the users DN.
run sql select pkid,dnorpattern,description from numplan where pkid='5442da4d-d2ac-c7ef-6611-29749f0fd237'
Last, we will query the enduser table for the Friendly Name and the numplan table for the DN.
run sql select eu.firstname,eu.lastname,num.dnorpattern from enduser as eu, numplan as num where eu.userid='tglen' and num.dnorpattern='3692'
In this query we:
1. Summarized tables with a prefix
2. Added the table prefix to the fields.
This method allows us to query fields from multiple tables. These prefixes tell the Informix DB which table to look in for that field. This is a very important concept to understand as we build more complicated queries!
We first need to determine how to identify 7945 IP Phones in the device table, in order to do that we look in the TypeProduct table. From the output we see that Cisco 7945 has a tkmodel of 435. The field tkmodel can be found in several tables in the CUCM database.
run sql select * from TypeProduct where name like "%7945%"
The above query can easily be customized to find any Cisco IP Phone.
The next command will look for the default load for the 7945 IP Phone. This information is in the defaults table.
run sql select tkmodel,loadinformation from defaults where tkmodel=435
Sometimes you will have phones that are configured to use a different Phone Load than what is configured in Device Defaults. In this example, we will list all of those phones and their associated users. The load information can be found in the specialloadinformation field of the device table.
run sql select name,description,SpecialLoadInformation from device where specialloadinformation='SCCP45.9-4-2SR1-1S' and tkmodel='435'
In this example, we will locate a specific DN in the numplan table. We will then look in devices table and determine what device(s) the DN is configured on.
First review all the columns in the numplan table. The columns that we need to review are pkid, dnorpattern, description.
run sql select pkid,dnorpattern,description from numplan where dnorpattern='3692'
Next, we will find the record in the devicenumplanmap table. This table provides the mapping between device table and numplan table. We will use the pkid from the above query as the reference. We know from other queries and from the table column prefix that the field we will match on is fknumplan.
run sql select pkid,fkdevice,fknumplan from devicenumplanmap where fknumplan='5442da4d-d2ac-c7ef-6611-29749f0fd237'
Next, we will find the record in the device table. We will use the fkdevice from the above query as the reference.
run sql select pkid,name,tkmodel from device where pkid='c06febfa-3970-ce63-45f1-c2200a2e4dbf'
This shows that there is one device that the DN is associated with. We know from Example #4 that tkmodel is a 7945.
See here for the next document in this CM SQL series.
Information in this document has been taken from TAC cases and from other documentation sources.
Please, help me make this document better. Please comment! Please rate. Thank you.