Querying IP Phone Service Subscriptions

In this installment of the SQL query series I wanted to explore IP phone service subscriptions. An administrator provisions IP phone services on the Cisco Unified Communications Manager (CUCM). To leverage these services on an IP phone, the phone must subscribe to the appropriate service, with the appropriate parameters. On this initial foray into the IP phone service arena, we will focus on reporting on service subscriptions.

I typically leverage this type of query when doing deployments or upgrades. For deployments, I have had instances where I needed to double check how devices or device profiles were provisioned. For upgrades where we are migrating to a new CUCM version, we'll use queries like this to prepare for the data move. We also query phone services when doing clean up.

Primer

A brief primer is provided in the first blog of this series.

This Week's Query

For this week's query we are going to look at a basic query to dump a list of services that are associated to devices. This type of query can come in handy when you want to look at devices that are associated to a specific service or list out services associated to devices. Using various where clauses, you can drill into a specific group of devices or services.

The Moving Parts

From a db perspective the following Informix database tables store information relevant to our queries:

• Device: This table stores all physical and logical devices provisioned in the CUCM cluster.
• TelecasterSubscribedService: This table is a mapping table that stores the subscription, which links devices to IP phone services.

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 d.name, d.description, tss.servicename 
from device as d 
   inner join TelecasterSubscribedService as tss on tss.fkdevice=d.pkid
order by d.name, tss.servicename

The following is a sample of the output one could expect from the above query:

name              description                            servicename
================= ====================================== ====================
SEP001192C11111   Test Phone 1                           Enterprise Directory
SEP0019E7A11111   Test Phone 2                           PhoneMessenger
SEP001AA2311111   Jim Jones                              Call Record
SEP0021A0822222   Peter Parker                           Call Record
SEP0021A0822222   Peter Parker                           Enterprise Directory
SEP0021A0822222   Peter Parker                           Extension Mobility
SEP0021A0822222   Peter Parker                           cm7corpdir
SEP002333433333   Bill 7925                              Extension Mobility
SEP00235EB44444   Bruce Banner                           Call Record
SEP002584144444   Nick Fury                              Enterprise Directory
SEP00260BD55555   Professor Xavier                       Enterprise Directory
SEP00260BD55555   Professor Xavier                       Extension Mobility
SEP3037A6144444   Legion                                 Enterprise Directory
Test 7926 Profile                                        Extension Mobility
Test 7926 Profile                                        Nurse Call Coverage

The output provided above is a simple dump of devices and the associated services. Given how we used the inner join, we are only seeing devices that have subscriptions to at least one IP phone service.

Wrap it up

Dumping a full list of devices and services may be overkill in most situations. Sometimes you are trying to find a handful of records. You could easily query on specific services, or filter on device pools, description fields, etc. I have used several variations of the above query to provide summary reports on distributions, validate configurations, and troubleshoot problems. 

In the interest of time I won't go into variations here. Though I do plan on exploring a couple of the more interesting queries I have used in the next installment. Stay tuned! 

---

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