Friday, June 8, 2012

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.


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


    1. Thank you this is great! Is there a list of device types? I would like to run this report and only pull up device profiles that are used for extension mobility along with the DN assigned to them.

      1. Sure. You can get phone model information from the typemodel table. There is a cross-referenced field in the device table called tkmodel. For example:

        select as device, as model
        from device d
        inner join typemodel tm on d.tkmodel=tm.enum


        -Bill (@ucguerrilla)