Friday, June 29, 2012

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!

    17 comments:

    1. Thanks for this series of postings. Any chance it's possible to pull current IP addresses for phones, and if so, could you demonstrate that?

      ReplyDelete
    2. The IP address (and other real time info) is NOT stored in the database. So, it is not possible to poll the IP address inventory using the method that I am exploring in this series.

      You can use SNMP or RisPort to get this information. SNMP requires polling mibs on each CUCM call processing node and then splicing together. That is an oversimplification but it is generally how it works.

      The RisPort is an API that exposes a lot of useful real time info. It is the same API used by RTMT. A while ago I did two short articles covering RisPort on my NetCraftsmen blog (http://www.netcraftsmen.net/resources/blogs/tags/RisPort/). These articles probably don't give you what you need, but if you were inclined to explore the API, there are few links that may help.

      There are 3rd party tools that can provide what you are looking for. I am not listing them hear because I haven't tested capabilities around phone inventory. Primarily because I developed my own tool a while ago and just haven't had the need.

      I have pondered ways to explore this deeper in an efficient and non-boring way (coding is boring for most, not me, but most ;-). Perhaps this is one of those topics that lends itself to a video walk through? I'll think on that more.

      Thanks for the feedback and interest.

      -Bill (@ucguerrilla)

      ReplyDelete
    3. This blog is awesome, am learning a lot. I would love to see a query that lists all calls in progress, or active calls.

      Many thanks,
      Mike

      ReplyDelete
    4. do you know of any way to pull a report of when the last time each extension mobility user/user device profile was used? We have 1500 dids and 500 phones that are used by medical staff. We would like to try to recover the ones that are not used.....

      ReplyDelete
      Replies
      1. Mike,

        I recently fielded a similar question on CSC, you can find the thread here: https://supportforums.cisco.com/thread/2158157

        An excerpt...

        You could look at these queries:

        Running the following query on your UCM cluster may give you some useful information:

        select eu.userid, emd.logintime, emd.loginduration, emd.datetimestamp
        from extensionmobilitydynamic emd inner join enduser eu on emd.fkenduser_lastlogin=eu.pkid
        order by eu.userid

        The above will dump out a list of end users and the emd.datetimestamp field is a value (stored as epoch time) of when that user logged into the device. The output will be a history of sorts. I do not know if the data is purged periodically or not. I haven't explored this close enough yet. Also, you may need to adjust the query to see current logged on users:



        select eu.userid, emd.logintime, emd.loginduration, emd.datetimestamp
        from extensionmobilitydynamic emd inner join enduser eu on emd.fkenduser=eu.pkid
        order by eu.userid

        HTH.

        -Bill (@ucguerrilla)

        Delete
    5. Is there a way to find unused userid/user device profiles? I have 1500 dids assigned to clinical staff used with 500 wifi phones. We would like to reclaim unused numbers.....

      ReplyDelete
    6. Hello Bill,

      The queries above do not seem to work on CUCM 8.X. For which CUCM version were they written?

      Thanks

      ReplyDelete
      Replies
      1. I just double checked and these queries work as written on my lab CUCM 8.5 system. Could you post the query you are attempting to execute and the error CUCM is kicking back?

        -Bill (@ucguerrilla)

        Delete
    7. Is there anyway to delete from ExtensionMobilityDynamic to do a logout using SQL?

      ReplyDelete
    8. Laith,

      No, this is not possible. The extensionmobilitydynamic table has a restriction on executing updates directly. If you wanted to log out a single device, you could do so from the CCMAdmin config page for the phone. OR look at using the following URL:


      https://[cmpubAddressOrName]:8443/ccmadmin/phoneLogout.do?key=[key]

      Where [key] is the pkid of the device, which you can get from the fkdevice field in the extensionmobilitydynamic table. I did a quick test and I can send an http get to the above URL (substituting my lab CM and phone pkid) and log a phone out remotely.

      If you want to log out users "in bulk" then you can use BAT to do this. Go to bulk update phones, run a query to select phones. Then select option "Logout Users Before Update". Don't change anything else and then run the job. It should log users out. I haven't tested this on all versions.

      HTH.

      -Bill (@ucguerrilla)

      ReplyDelete
    9. Hello Bill, Learning a lot, thanks for sharing this. I wonder if you could help by posting a query that may be helpful. We are using AD sync and extension mobility and would like to know if we could run a query to find out our "Orphaned Device Profiles"? As you know the user will be deleted from CUCM once the account is removed from AD and the garbage collection process has run but we cannot run a report on the device profiles not associated with a user. Do you thin this is possible? It may be of benefit as I would say this may be a common occurrence on CUCM now with this scenario.

      ReplyDelete
    10. Chris,

      Yes, this is totally possible. I just did a write up on this. A series installment that addresses your question should post by 10am PST on 9/9/2013.

      HTH.

      -Bill (@ucguerrilla)

      ReplyDelete
    11. Hi,

      Do we have a query to find out the list of users associated with a specific UDP?

      ReplyDelete
    12. kindly provide details of all tables in CUCM 9.1 with some deatils.

      ReplyDelete
      Replies
      1. Rajesh,

        That is a lot of data. So, I am not going to post that here. Fortunately, you can find the complete data dictionary here: http://www.cisco.com/c/dam/en/us/td/docs/voice_ip_comm/cucm/datadict/9_1_1/datadictionary_911.pdf

        You can also query local system tables to get table names and column names using the query:

        select t.tabname, c.colname from systables t, syscolumns c where t.tabid=c.tabid order by t.tabname

        HTH.

        -Bill (@ucguerrilla)

        Delete
    13. Hello, I'm trying to extract data of all User Device Profiles, Description, and Directory Number.

      The query below only shows data for currently logged in device profiles

      run sql select n.dnorpattern,d.name,p.name,em.datetimestamp,em.cdrtime from device d,device p,devicenumplanmap dnmp,numplan n,extensionmobilitydynamic em where em.fkdevice=d.pkid and em.fkdevice_currentloginprofile=p.pkid and p.pkid=dnmp.fkdevice and dnmp.fknumplan=n.pkid and d.name='SEP001122334455'

      ReplyDelete
      Replies
      1. I assume you are asking how you dump UDP information whether they are logged in or not. You do not need the extensionmobilitydynamic table to view UDPs. Query the device table, numplan, and devicenumplanmap and use a where clause that selects records where device.tkdeviceprofile = 1.

        HTH.

        -Bill (@ucguerrilla)

        Delete