Thursday, March 15, 2012

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! 








16 comments:

  1. Hi, I would love to see an run sql command for checking out QOS issues from a calling number to a called number via a 3rd party conference number rather than on net if that makes sense. Have an issue where our end conference gets disconnected and know for a fact not our end but need to prove it

    ReplyDelete
    Replies
    1. What version of CUCM are you using? The syntax for accessing the CAR db changed around version 8.0 (I believe).


      -Bill

      Delete
  2. Hi, I'm trying to retrieve informations from the CAR tables of a 8.5 CUCM and I think that the server does not return all the rows (I have made a comparison whith a dump of the CDR table).

    I am wondering if there is a kind of limitation due to a parameter like a buffer size limit that could explain that.

    If so, do you know which parameter should be modified and how ?

    Regards,

    Didier.

    ReplyDelete
    Replies
    1. Hi,
      I am answering my own question.
      The problem didn't come from the informix settings but from the python code I use to retrieve the output of the sql query in a buffer.
      Sorry for the silly question.

      Regards,

      Didier

      Delete
  3. Hello , is there a way in CUCM 8.6 to export the complete informix database ? If yes what tools are available for this ?

    ReplyDelete
  4. The closest you can come to exporting the Informix DB is DRS as it exports all data from the database.

    HTH

    -Bill

    ReplyDelete
  5. Hi, how can I list of phones with a specific firmware version?
    For instance: I want to know how many phones 7942 are using SCCP42.9-3-1SR3-1S.
    Is there way to run sql to find out this info? Thanks!

    ReplyDelete
    Replies
    1. Hello,

      I think the answer to your question can be found in the following blog entry:

      http://www.ucguerrilla.com/2013/10/checking-peer-firmware-sharing-using-sql.html


      HTH

      -Bill (@ucguerrilla)

      Delete
  6. Nice work Bill. Thought you deserved some credit for such a detailed intro.

    ReplyDelete
    Replies
    1. Thanks for the feedback! I really appreciate it.

      -Bill (@ucguerrilla)

      Delete
  7. Hello

    I would be interested in a query which shows the status of the "Mobile Phone" and the "Enable Mobile Connect" checkboxes for a given Remote Destination.

    Any help in this direction is highly appreciated.

    Thank you

    Regards

    Roman

    ReplyDelete
    Replies
    1. Roman,

      You need to work with two tables:

      remotedestination
      remotedestinationdynamic

      To get the values of the "Mobile Phone" and "Enable Mobile Connect" checkboxes for a given destination you can use a query similar to the following:

      select r.name,rd.ismobilephone,rd.enablesinglenumberreach from remotedestinationdynamic rd inner join remotedestination r on rd.fkremotedestination=r.pkid where r.name='YourRemoteDestinationName'

      If you want to see all remote destinations then drop the where clause.

      HTH

      -Bill (@ucguerrilla)

      Delete
  8. Bill,
    This is a great post, but I am not the greatest at building SQL statements.

    What I am looking for is a list of DNs, Users (Description field of the phone), Userid if it has one, and the phone mask.
    I used two statements (one that you wrote on another site) and then leveraged Excel to vlookup the informaiton. The problem is the second query only pulled back about 450 entries, while I have over 1600 DNs.

    run sql select numplan.dnorpattern, routepartition.name, devicenumplanmap.e164mask from numplan
    inner join routepartition on numplan.fkroutepartition = routepartition.pkid
    inner join devicenumplanmap on numplan.pkid = devicenumplanmap.fknumplan
    inner join description on device.pkid
    where numplan.tkpatternusage = 2 order by routepartition.name, numplan.dnorpattern

    run sql select eu.userid, d.name, d.description, n.dnorpattern as DN, rp.name as
    partition from device as d inner join devicenumplanmap as dnpm on dnpm.fkdevice =
    d.pkid inner join devicenumplanmapendusermap as dnpeum on dnpeum.fkdevicenumplanmap=
    dnpm.pkid inner join enduser as eu on dnpeum.fkenduser=eu.pkid inner join numplan as
    n on dnpm.fknumplan = n.pkid inner join routepartition as rp on n.fkroutepartition=
    rp.pkid and d.tkclass = 1

    Any help would be greatly appreciated.

    Chris

    ReplyDelete
    Replies
    1. Chris,

      I suspect that your use of "inner join" is what is contributing to a data set containing fewer records than you expected. Inner join means that there must be a mapping between the two tables involved in the join. If you wanted to perform a join but show all data in one of the tables then you should use an outer join (left outer join or right outer join).

      But that may not get you to your final goal. When you say you are looking for "user id" do you mean you are looking for "owner ID", users associated with the device, or users associated with the line? Your second query is sort of going after the latter. If that is what you want then I'd suspect that you do not have users associated to the line appearance. Which means that your 400 records is an accurate representation of the data you are looking for.

      If you want to see users associated to a Device then you should look at the "enduserdevicemap" table. The owner ID is referenced by the fkenduser field directly in the "device" table. I know, it gets kind of hairy. The key question is what relationship between user/device/line are you trying to capture?

      HTH.

      -Bill (@ucguerrilla)

      Delete
  9. Hi Bill,

    Do you know what is method or a way to deleting Call Manager configuration from 3rd party middleware ?

    some users or company have pain on how to housekeeping Call Manager configuration. and I just wondering, I have a middleware that connect to LDAP and Call Manager, when one user resign, i just delete the users form my middleware so that users database in LDAP deleted while extension and device phone (or any related users configuration in Call Manager) also deleted.

    Any help for this idea is highly appreciated

    Thanks

    ReplyDelete
    Replies
    1. Sure. You can use the standard AXL schema commands for managing phones and numplan elements or you can use the update SQL API command to remove things via SQL "delete" commands. I suspect that most developers would prefer to use the standard AXL schema commands.

      See DevNet reference: https://developer.cisco.com/site/axl/develop-and-test/documentation/latest-version/axl-soap.gsp

      HTH

      -Bill

      Delete