Using SQL to Validate CUCM Extension Mobility

In this installment of the SQL query series I wanted to expand on the last installment where we explored a query that listed phone subscriptions to Cisco IP Phone Services . An administrator provisions IP phone services on the Cisco Unified Communications Manager (CUCM). 

For this week's queries, we are going to take a look at ways to view a summary of how services are provisioned and an example leveraging SQL queries to identify configuration issues.

Primer

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

This Week's Query Queries

For this week's installment we are going to look at two queries. The first is a basic query to report on distribution of IP Phone Service subscriptions. This type of query can come in handy when you want a high level view of how services are provisioned on your CUCM cluster.
The second query will provide an example of how we can use SQL queries to identify issues with service subscriptions.

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
The first query we are going to discuss is a simple query to get a count of devices that are associated with phone services that are provisioned on the CUCM cluster. You can run the query from the CLI using run sql [insert query]:

select count(d.name), tss.servicename 
from device as d 
   inner join TelecasterSubscribedService as tss on tss.fkdevice=d.pkid
group by tss.servicename

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

(count) servicename
======= ====================
11      Nurse Call Coverage
1       cm7corpdir
12      Enterprise Directory
3       Call Record
1       PhoneMessenger
4       Extension Mobility

Given how we used the inner join, we are only seeing services that have at least one device subscription. It is worth noting that phone services with the "Enterprise Subscription" parameter enabled are not listed by the above query. 


Another Example...EM Service
The next query we want to discuss is an example of creating a consolidated report of a service's subscriptions where the back-end data is somewhat complicate. I am thinking about Extension Mobility (EM) here. EM is a service that has several moving parts. There are parameters on devices (EM enabled, subscribed service), parameters on device profiles (subscribed service), and parameters on users. 

I had a deployment where EM configuration inconsistencies was a constant headache. The challenge was that only "certain" phones were supposed to have the EM service enabled and the subgroup of phones that had this "privilege", within a particular deployment group, was a moving target. The techs doing the deployment tried to catch the changes but there were always a couple of changes missed and we would have phones with EM service enabled but no service subscription. Another problem was device profiles that were not subscribed to the EM service.


Enter in SQL...


So, the first query I will discuss was used check the following rules:

1. A physical phone that was EM enabled must be subscribed to the EM phone service
2. A user device profile must also be subscribed to the EM phone service

The following query checks these rules:

select d.name, d.description, d.allowhotelingflag, d.tkclass
from device d 
where ((d.tkdeviceprofile!=3) 
        and (d.allowhotelingflag='t' or d.tkclass=254) 
        and (0=(select count(*) 
             from device ddup inner join telecastersubscribedservice tss on
                         tss.fkdevice=ddup.pkid 
                   where (ddup.pkid=d.pkid and tss.servicename='Extension Mobility')))) 
order by d.tkclass

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

name                description         allowhotelingflag tkclass
=================== =================== ================= =======
SEPDDDDDF57986F     John Doe            t                 1
SEPDDDDD55D18CF     Jane Doe            t                 1
SEPDDDDD8FD2996     L. FemaleDeer       t                 1
UCGuerrilla Profile UCGuerrilla Profile f                 254
7926 Profile2       7926 Profile2       f                 254

Yuck is right! That query is a beast but sometimes you gotta break out the big guns. Befor dissecting the query, let's discuss the results. Based on the above output, there are three (3) IP phones that have the EM feature enabled on the device configuration page but are not subscribed to the Extension Mobility phone service. We also see two user device profiles (tkclass value of 254) that are not subscribed to the Extension Mobility service.

Now, back to the query. It should be noted that the query is not the complete picture (as we'll discuss in a moment). Let's break things down to get an idea of what is going on:

• We are displaying
• name: name of the device in the Device table
• description: the description (optional) applied to the device
• allowhotelingflag: This is the "Extension Mobility Enabled" option on a device, it has been called the allowhotelingflag since EM was a "new" feature (historical facts for ya)
• tkclass: We could resolve the typeClass to the "human readable" name but there is no point to do an unnecessary join. typeClass of "1" is a physical phone and "254" is a device profile.
• Our where clause is a compound clause:
• ((d.tkdeviceprofile!=3) We want to ignore the "hidden" device profiles that Cisco adds to the Device table. We do this by excluding device profile types where the type enum is "3".
• and (d.allowhotelingflag='t' or d.tkclass=254) We want to check for phones that have the hoteling flag enabled ("t") or where the device typeClass has an enum value of 254. We are using an "OR" clause here because the objects we care about are either phones with the EM option enabled or device profiles.
• and (0=(select count(*) ... This is where it gets interesting, we are using a sub-query as one of our conditions. This sub-query is basically asking: is this device subscribed to a service named 'Extension Mobility'
• Lastly, we are ordering the output by tkclass so that we have phones listed before user device profiles. You could add other sorting criterion here as well (like device name, description, etc.).

Let's talk a little more about the sub-query in the where clause. This part of the query is a lot to chew on, so let's peel back the onion a little. Our query will dump a record when the following conditions are met:

• The goal is to get a count and see if that count is 0 (the device or device profile is not subscribed to the EM service) or greater than 0 (the device or device profile has more than one subscription to the EM service. Yes, this is possible albeit usually invalid.)
• We are executing another select statement on the Device table. As such, we should declare a new alias (ddup) for the 2nd instance.
• To ensure we are checking for the EM service on the same device reference by the current record of the main select statement, we are going to compare pkid's in the where clause of the sub-query. The concept is pretty simple:
• Let's say that in the main select statement there are 500 records and the first record is device SEPAAABBBCCCDDD with pkid {uniquevalue1}
• In the where clause of the sub-query we are basically saying: where (ddup.pkid='{uniquevalue1}' and tss.servicename='Extension Mobility')
• Since pkid is always a unique value, that second where clause is either going to return a single record or no records (based on the tss.servicename constraint).
• We are doing an inner join with the TelecasterSubscribedService table (tss). Note that the join is with the foreign key reference to the ddup alias. 

As big as the above query is, it doesn't provide the complete picture. It is possible to have a subscription to the Extension Mobility phone service but NOT have the EM service option (i.e. hotelingflag) enabled. I usually query this separately:

select d.name, d.description, d.allowhotelingflag, d.tkclass 
from device d 
where ((d.allowhotelingflag='f' and 
           (0<(select count(*) from device ddup 
               inner join telecastersubscribedservice tss on tss.fkdevice=ddup.pkid
               where (ddup.pkid=d.pkid and tss.servicename='Extension Mobility')))))

Another beast. The main differences in the above query are that we are triggering on devices where the "allowhotelingflag" is false (i.e. EM feature unchecked on device page) YET the device has a subscription to the Extension Mobility service.


Considerations

One of the considerations you should ponder when using queries to validate EM configurations is that you may want to apply a more selective filtering method. You can do this via the where clause. For instance, maybe you only want to check devices in a certain device pool, devices with a certain DN range, or devices with a certain description field. Multiple variations are possible and most will require joining another table in the from clause. I may provide more examples at a later date based on any requests made via the comments on this blog.

For EM, there is another configuration mistake that people make: failing to associate the device profile to the end user. Given that we have covered some heavy queries in this installment, I am going to let you go get coffee now and will plan to revisit that query at a later installment. Stay tuned! 

---

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