Aurus Blog

This blog is to share our expertise in Cisco UCM, UCCX/UCCE and Cisco Telepresence.

  • Archive

    «   September 2018   »
    M T W T F S S
              1 2
    3 4 5 6 7 8 9
    10 11 12 13 14 15 16
    17 18 19 20 21 22 23
    24 25 26 27 28 29 30
                 

Cisco UCM PBX database tables overview

This article describes the practical details of handling the databases in Cisco Unified Communications Manager (CUCM) PBX and Voice Operating Systems (VOS) in general.

The article is recommended for engineers with more than a year’s CUCM administration experience.

In 2006, when CUCM 5.0.1 (the first VOS-based version) was released, IBM Informix Database was chosen for a configuration storage.

The database contains a few hundred tables that contain the dialing plan as well as the configuration data for the phones, gateways and users. An administrator can use Tomcat web server to make changes in the database tables. It’s highly recommended not to use SQL requests to modify the data directly.

Technical support engineers often have to create SQL SELECT requests to obtain the required information and export it in text format. I’ll briefly describe the table types and give examples of the most frequently used requests.

Note that since version 7.0 when the main PBX process starts, the database configuration is being copied into RAM (in-memory database, IMDB) to speed up. The main process (Cisco Call Manager) basically uses the data from IMDB instead of the database tables.

We would mark out a few types of database tables for VOS products:

  • type (dictionary)
  • system
  • dynamic
  • functional
  • mapping

This classification is to be used for educational purposes only.

We recommend to copy the output of SQL commands to Notepad++ or a similar text editor to look through it.

Type tables

The contents of «type» tables are being created during the system installation and cannot be altered by the administrator or users. These tables contain data formatted as key-value and can be referred by the tables of other types.

A few examples are given below. The first one (typemodel) shows the list of phone models supported by a specific CUCM version.

admin:run sql SELECT enum,name,moniker FROM typemodel WHERE enum IN ('110','90','131','437','493')
enum name moniker
==== ======================= ================
110 Media Termination Point MODEL_MTP
90 Route List MODEL_ROUTE_LIST
131 SIP Trunk MODEL_SIP_TRUNK
437 Cisco 7975 MODEL_CISCO_7975
493 Cisco 9971 MODEL_Cisco_9971
...

The following example shows the types of entries in the enumeration order:

admin:run sql SELECT enum,name FROM typepatternusage
enum name
==== ======================================
0 CallPark
1 Conference
2 Device
3 Translation
4 Call Pick Up Group
5 Route
...

The protocols supported on Cisco UCM server:

admin:run sql SELECT enum,name FROM typedeviceprotocol
enum name
==== ============================
0 SCCP
1 Digital Access PRI
2 H.225
3 Analog Access
4 Digital Access T1
5 Route Point
6 Unicast Bridge
7 Multicast Point
8 Inter-Cluster Trunk
9 RAS
10 Digital Access BRI
11 SIP
12 MGCP
...

System tables

System tables are the ones like typetableinfo, typefieldinfo, treecontrolgroupnodes.

The first two tables form the database schema, describing the tables and their fields. The third one stores web server pages.

admin:run sql SELECT enum,tablename FROM typetableinfo
enum tablename
==== ================================
1 Device
...
13 DevicePool
...
18 H323Device
22 MediaMixer
24 NumPlan
...
46 TypeDeviceProtocol
...
77 DeviceNumPlanMap
79 TypeModel

Dynamic tables

The contents of dynamic tables can be altered by a system administrator as well as by the end users (which actually happens more often). The users can utilize such features as Call Forwarding, DND (do not disturb), hunt group, extension mobility and other. The current call forwarding status for a selected user’s line number is stored in the database along with all the other parameters.

The following request shows all the line numbers (it happens to be a single line 1411) with unconditional forwarding (CFA) to the line 5000.

admin:run sql SELECT c.pkid,c.cfadestination,n.dnorpattern line,n.fkroutepartition pt,c.datetimestamp FROM callforwarddynamic c INNER JOIN numplan n ON n.pkid==c.fknumplan WHERE c.cfadestination='5000'
pkid cfadestination line pt datetimestamp
==================================== ============== ==== ==== =============
7ded9e18-f706-4d7b-8f96-aa54fa3898c6 5000 1411 NULL 1493195338

Configuration tables

The last type of tables is for storing the current system configuration. It can only be altered by the system administrator.

This request shows all phone numbers configured by the administrator, i.e. phone routing table.

admin:run sql SELECT pkid,dnorpattern num,fkroutepartition pt,tkpatternusage FROM numplan
pkid num pt tkpatternusage
==================================== =========== =========== ==========================
00ee7548-6ac0-1828-0b6c-d542d63f445a 1110 NULL 2
87398763-efa2-8a8c-9693-745300db3b72 134X NULL 5
f2856fc5-dcbe-1a7a-f4dc-a7685d4ac0cd 1411 NULL 2
a4dd3b81-dc2e-8b59-6ae1-032f5df8e829 1461 NULL 2
fb0471f2-01d0-2a2a-f1cc-d45b16bb5573 5111 NULL 7
7916b98d-3974-6e8e-5dc8-6e1aa4ec6ecd 1120 NULL 2
2c30de37-cf1d-6060-dc0c-7e2959e34c9a 5102 NULL 2
4d3de192-6692-37d5-aaad-63cfc5cfdd24 1100 NULL 2
a16d29ba-b0c0-c0cf-35ec-63abbaa38009 1011 NULL 2

Another example that shows the list of configured devices: phones, SIP trunks, CTI Route Points and media resources.

admin:run sql SELECT pkid,name,tkmodel,tkdeviceprotocol proto FROM device WHERE pkid NOT IN ('7248bc9-53a8-4940-90df-1a2335c76a72','1ecbc4f3-eef3-45c5-9be1-7756bbdf1bd8','59765051-833a-435d-9d9d-783eab0297a6','27abd0a0-c549-4656-9f96-67c8308f610c','07248bc9-53a8-4940-90df-1a2335c76a72')
pkid name tkmodel proto
==================================== ==================== ======= =====
ee4c9f70-ea2b-45d5-8ffe-31fb4e9681a6 MTP_2 110 6
0d874bc4-c7bc-4949-a140-58e640d94629 CFB_2 50 6
47bb29fe-4e71-4dd9-b057-4520c1b6c548 ANN_2 126 0
f1c32bf7-e309-47db-931c-a6219a0c792a MOH_2 70 0
474729f0-f3da-ffcd-d289-271f441445a3 std-hl 90 0
2633e474-b883-4cbe-8f07-3a7dddd4f7bc SEP005056996F7E 30016 0
9f86b82f-89c4-72bd-c04b-a24cc3adaee8 7962-em-5102 404 0
d76b4ce1-3da3-cd1c-6f07-841fdc3302e0 ICM-TR-Trig1380 73 0
34de5b13-d98a-47f6-a35b-9427c1c2f5eb SEPA45630BB07AF 437 0
5a358a31-80b7-6a8a-cfe3-487624d49a09 ICM_RP_1120 73 0
4f03ff2f-d89a-415b-8262-2d775230dddc SEPA45630BB06F9 437 0
36f07a4d-74f8-2ee0-5779-460caa1ed923 SEP0024142DDF24 437 0
87655d65-0317-5ac1-1a4e-7a7bbc1b278f ccx15_1464 72 0
00984b02-3c20-46ae-aa5d-bd3ed3b2179d ccx15_1465 72 0
3e17cd58-1731-cfb8-92b2-707f4b34b8ab ccx215_1460_aa 73 0
0aa53d21-b192-436d-8d56-f77cd508fca5 SEPA45630BB0387 437 0

Mapping tables

Mapping tables can be mentioned as a sub-type. They are necessary for associating lines with phones and users with phones, and in some other cases as well.

Here’s an example of an association based on keys. Note the device id (fkdevice) and the number if (fknumplan) and compare with the output of the commands above.

admin:run sql SELECT pkid,fkdevice,fknumplan FROM devicenumplanmap WHERE fkdevice='34de5b13-d98a-47f6-a35b-9427c1c2f5eb' AND fknumplan='f2856fc5-dcbe-1a7a-f4dc-a7685d4ac0cd'
pkid fkdevice fknumplan
==================================== ==================================== ====================================
018ab5e8-9218-48ef-a55d-9dcc84ec8f81 34de5b13-d98a-47f6-a35b-9427c1c2f5eb f2856fc5-dcbe-1a7a-f4dc-a7685d4ac0cd

Here’s an example of the same association that’s more convenient for the administrator. There’s a phone with MAC A45630BB07AF. The phone’s 1st line is associated with the line 1411 in partition NULL.

admin:run sql SELECT m.pkid,d.name device,n.dnorpattern line,m.numplanindex pos,p.name FROM devicenumplanmap m INNER JOIN device d ON d.pkid=m.fkdevice INNER JOIN numplan n ON n.pkid=m.fknumplan LEFT JOIN routepartition p ON p.pkid=n.fkroutepartition WHERE n.dnorpattern LIKE '%1411%'
pkid device line pos name
==================================== =============== ==== === ====
018ab5e8-9218-48ef-a55d-9dcc84ec8f81 SEPA45630BB07AF 1411 1 NULL

Frequently used requests

Below you can find a few helpful SQL requests that can be used to analyze different problems.

Show all phone lines with their current call recording parameters:

admin:run sql select rd.pkid,n.dnorpattern,n.fkroutepartition AS pt,trec.name AS recflag from recordingdynamic AS rd INNER JOIN devicenumplanmap AS mdn ON mdn.pkid==rd.fkdevicenumplanmap INNER JOIN numplan AS n ON n.pkid==mdn.fknumplan INNER JOIN typerecordingflag AS trec ON trec.enum==rd.tkrecordingflag WHERE rd.pkid!='4ef381b2-ff24-47f9-bdb2-022b7e8fcaf9'
pkid dnorpattern pt recflag
==================================== =========== ==== ================================
c5611b10-c5d4-4365-b55c-23edb300fbbb 101X NULL Call Recording Disabled
f52ea03c-fffe-4787-a4d6-b070a580ce4e 5035 NULL Call Recording Disabled
c6c3263e-281c-4bb4-b75b-e5ad8fef0506 5034 NULL Automatic Call Recording Enabled
88d28cd8-0580-46cb-a523-724f69dd9998 3014 NULL Automatic Call Recording Enabled

Show the current CUCM logging parameters:

admin:run sql select ts.name,p.servername,p.enable AS traceon,p.tracelevel,p.numfiles,p.maxfilesize,p.numlines,p.numminutes from processnodeservice AS p INNER JOIN typeservice AS ts ON ts.enum==p.tkservice
name servername traceon tracelevel numfiles maxfilesize numlines numminutes
================================================= ============ ======= ========== ======== =========== ======== ==========
Cisco CallManager 10.48.47.143 t 1 1 2 10000 1440
Cisco CallManager 10.48.47.136 t 127 1 2 10000 1440
Cisco Tftp 10.48.47.143 t 127 1 2 10000 1440
Cisco Tftp 10.48.47.136 t 127 1 2 10000 1440

The phones subscribed to XML services:

admin:run sql select mt.pkid,d.name as device,mt.servicename,mt.fktelecasterservice from telecastersubscribedservice as mt INNER JOIN device as d ON d.pkid==mt.fkdevice
pkid device servicename fktelecasterservice
==================================== ==================== =========== ====================================
ab11d152-fdba-0cc6-a6dc-b6658e8a8ae7 7962-em-5102 EM 49ce7246-de87-1eb4-e768-ddc34f2b9ccf
a2c8399c-89a0-3169-d914-93eebaceb948 SEPA45630BB07AF FIPPA dbb3533c-7301-8175-7e03-616b9e4f100f
491a4ec1-2d4b-c2b0-93c4-edf97df84c09 milnagy 8861 Profile EM 49ce7246-de87-1eb4-e768-ddc34f2b9ccf

The following request may help to solve certificate issues.

admin:run sql select c.pkid,c.servername,c.ipv4address,c.serialnumber,tctr.name AS role,tsc.name AS type,c.subjectname,c.issuername,c.timetolive from certificate AS c INNER JOIN certificatetrustrolemap as ctr ON ctr.fkcertificate==c.pkid INNER JOIN certificateservicecertificatemap AS sc ON sc.fkcertificate==c.pkid INNER JOIN typecertificateservice AS tsc ON tsc.enum==sc.tkcertificateservice INNER JOIN typetrustrole AS tctr ON tctr.enum==ctr.tktrustrole
pkid servername ipv4address serialnumber role type subjectname issuername timetolive
==================================== ========== ============ ====================================== ================================ ================= ================================================================================================================================================ ====================================================================================================================================================================== ==========
9e045d3a-be58-6e75-690e-0086541b1632 ucm11-1 10.48.47.143 360000005923e9c225e8f6d963000100000059 CallManagerTFTP CallManager CN=ucm11-1.allevich.local,OU=TAC,O=Cisco,L=Krakow,ST=Malopolskie,C=PL CN=allevich-DC12-CA,DC=allevich,DC=local NULL
9e045d3a-be58-6e75-690e-0086541b1632 ucm11-1 10.48.47.143 360000005923e9c225e8f6d963000100000059 CallManagerTFTP CallManager-trust CN=ucm11-1.allevich.local,OU=TAC,O=Cisco,L=Krakow,ST=Malopolskie,C=PL CN=allevich-DC12-CA,DC=allevich,DC=local NULL
9e045d3a-be58-6e75-690e-0086541b1632 ucm11-1 10.48.47.143 360000005923e9c225e8f6d963000100000059 CallManagerTFTP Phone-SAST-trust CN=ucm11-1.allevich.local,OU=TAC,O=Cisco,L=Krakow,ST=Malopolskie,C=PL CN=allevich-DC12-CA,DC=allevich,DC=local NULL
9e045d3a-be58-6e75-690e-0086541b1632 ucm11-1 10.48.47.143 360000005923e9c225e8f6d963000100000059 SAST CallManager CN=ucm11-1.allevich.local,OU=TAC,O=Cisco,L=Krakow,ST=Malopolskie,C=PL CN=allevich-DC12-CA,DC=allevich,DC=local NULL
9e045d3a-be58-6e75-690e-0086541b1632 ucm11-1 10.48.47.143 360000005923e9c225e8f6d963000100000059 SAST CallManager-trust CN=ucm11-1.allevich.local,OU=TAC,O=Cisco,L=Krakow,ST=Malopolskie,C=PL CN=allevich-DC12-CA,DC=allevich,DC=local NULL
9e045d3a-be58-6e75-690e-0086541b1632 ucm11-1 10.48.47.143 360000005923e9c225e8f6d963000100000059 SAST Phone-SAST-trust CN=ucm11-1.allevich.local,OU=TAC,O=Cisco,L=Krakow,ST=Malopolskie,C=PL CN=allevich-DC12-CA,DC=allevich,DC=local NULL
9e045d3a-be58-6e75-690e-0086541b1632 ucm11-1 10.48.47.143 360000005923e9c225e8f6d963000100000059 TFTP CallManager CN=ucm11-1.allevich.local,OU=TAC,O=Cisco,L=Krakow,ST=Malopolskie,C=PL CN=allevich-DC12-CA,DC=allevich,DC=local NULL

We hope this information will be useful, and not only to Cisco technical support engineers.