CUCM SQL Queries: A Series

A couple of years ago I started a blog series on the AXL SOAP Toolkit that Cisco provides with Unified Communications Manager (CUCM). Recently, I was talking to a colleague of mine about starting a new series that simply focused on providing SQL queries that I use when I need to get data from the CUCM server. I figure, why not? So, I am going to go for a query a week. Let's see how it goes.

Primer

Anyone looking to build a custom query to run against their CUCM system may want to take a look at the following resources:

• Cisco Data Dictionaries
• Informix DBM resources

I classify tables in the CUCM databases into three (3) categories:

Standard Tables: Tables which contain the core data values of a particular object like devices, number patterns, end users, etc.

Enumeration or "type" Tables: Used by other tables to resolve enumerator IDs to some name or moniker.  That is understating their purpose somewhat, but from a SQL query point of view you will use type tables mainly to resolve enumerator values to a human readable format. 

Mapping Tables: Basically connects unique records from one table to unique records in another table.

Standard tables and mapping tables use a unique key that is always named: pkid. Enumeration or "type" tables also have a unique key that is always named: enum. Whenever a field in a table is linked to another table or another record in the same table, the following field name conventions are used:

• 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 enum.
• ik(x): This is used when a field is de-referencing another unique record in the same table as the source record. The table will be named (x).

For some basic examples on using the various table types, you can check out one of the posts I wrote on the Chesapeake NetCraftsmen blog site.

Disclaimer

Throughout this series, the examples are built using the CLI. Some of these queries can return rather bulky data sets and I have not tested performance impact of running these queries from the CLI. While I do run queries from the CLI in production and have not encountered issues, your mileage may vary. 

I recommend using a tool that leverages the AXL/API for production use. If you are going to use the CLI to execute queries in production, and are unsure of the resource impact, I recommend testing queries and monitoring your systems with RTMT to make sure you understand the impact. Further, I would not run queries from the CLI on call processing systems (i.e. Subscribers running CCM service) during core production hours.

  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.

The Weekly Query

Let's get down to the nitty gritty. For this week's post I am going to discuss a request I fielded where the user show line group members that are not logged in.

The Moving Parts

From a db perspective the following Informix database tables store information related to line groups and hunt lists:

• LineGroup This table defines the line group name, hunt algorithms, etc.
• LineGroupNumPlanMap This will map a line group to a specific numplan entry, such as a phone line directory number.
• NumPlan This is the master table for all digit patterns configured in your CUCM cluster. This includes phone lines, Hunt Pilots, translations, route patterns, etc.  For your purposes you need this value to get to identify a device and you may want to use it for display purposes.
• DeviceNumPlanMap This is another mapping table. It will match up phone line directory numbers in NumPlan to the IP phones in the Device table.
• Device  This table will contain devices like the IP  phone.  Since users log into/out of line groups from a device level  (instead of the line level), you will need to know which phone you are  dealing with. Note, that Hunt Lists are also stored in the Device table.
• DeviceHlogDynamic This table simply keeps trag of whether a device has the "HLOG" status  toggled.  True means that the phone lines are  logged into all line  groups.  False means that the lines are logged out of line groups.  I  use the term "phone lines" here simply because when a phone use toggles  HLog to false, they are logged out of all line groups and this includes  all lines on the phone.

The Query

Now that we have some information, we can build the query. You can run the query from the CLI using: run sql [insert query].

select lg.name as LineGroup, n.dnorpattern, dhd.hlog
from linegroup as lg 
inner join linegroupnumplanmap as lgmap 
   on lgmap.fklinegroup=lg.pkid
inner join  numplan as n on lgmap.fknumplan = n.pkid
inner join devicenumplanmap as dmap on dmap.fknumplan=n.pkid
inner join  device as d on dmap.fkdevice=d.pkid
inner join devicehlogdynamic as dhd on dhd.fkdevice=d.pkid
order by lg.name

Yeah, it is ugly but it works.  Essentially we are going to display the Line Group name, directory numbers for all LG members, and whether the HLog status for the device that owns the directory number. If we were to run this from the admin interface you may have something like this:

linegroup          dnorpattern hlog
================== =========== ====
DC_Operators_LG 2025553299  t
DC_Operators_LG 2025553801  t

DC_Operators_LG 2025553802  t

DC_Operators_LG 2025553803  t
DC_Operators_LG 2025550207 f
VM_CUC-A-PUB_LG    1091001     t
VM_CUC-A-PUB_LG    1091002     t
VM_CUC-B-Pub_LG    1092001     t
VM_CUC-B-Pub_LG    1092002     t
admin:

Based on the output, we can see that 2025550207 is not logged into the line group. More specifically, a device provisioned with this directory number has the "HLog" feature disabled. 

---

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