Monday, September 9, 2013

Finding Orphaned User Device Profiles for EM

This installment of the SQL query series is a response to a question posed by a reader. The reader posted a comment on the blog entry: Using SQL to Validate CUCM Extension Mobility. The reader asked if it was possible to find "orphaned" extension mobility profiles using SQL? 

This is definitely possible and is actually pretty straightforward. 

Primer

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

This Week's Query

For this week's installment we are going to walk through a query that will provide a list of User Device Profiles (i.e. Extension Mobility profiles) that are not associated with any users.

    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.
    • EndUserDeviceMap: This table is a mapping table that stores data on end user associates with devices and device profiles.

    The Query
    To get right at the data we want, we can use a relatively simple query on the device table. You can run the query from the CLI using run sql [insert query]:


    select d.name 
    from device d 
    where (0=(select count(edm.pkid) from enduserdevicemap edm where edm.fkdevice=d.pkid)) and d.tkclass=254
    order by d.name
    


    Basically, this query is going to kick out a list of device profiles that are not associated to any end user object. Device profiles are stored in the Device table and end user objects are stored in the Enduser table. When an administrator adds a controlled device (or device profile) to an end user, a mapping is added to the EndUserDeviceMap table. We can use a where clause that looks for exactly zero (0) associations for a specific device PKID in the the EndUserDeviceMap table. This will dump ALL devices that are not associated to users.

    Of course, that would be erroneous data and not what the question is looking to answer. We only want to see associations (or lack thereof) for user device profiles. To address this filter requirement, we search for device type class (tkclass) of 254. 

    Considerations

    There isn't much more to it. We could (and probably will) explore other ways to report on end user and device associations. The queries can get interesting. If you'd like to see a particular view, let me know via the comments.




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

    10 comments:

    1. Hi, very good post. This is what I looking for also. Have you time to check my other SQL Q about the never regsiter phones?

      Thx
      Joerg

      ReplyDelete
      Replies
      1. Joerg,

        I believe I did respond to your question on another comment thread. Check this link: http://www.ucguerrilla.com/2012/03/cucm-sql-queries-series.html?showComment=1378905199359#c5846732364553118504

        Let me know if there is a problem accessing the link.


        -Bill (@ucguerrilla)

        Delete
    2. Works great!

      Very timely. We have a conversion coming up, and this will save us some trouble calls on conversion day.

      ReplyDelete
    3. Could this be taken one step farther and only spit out those profiles that are also not assigned as the logout profile on a device?

      ReplyDelete
    4. This is very handy, what would need to be added to the query to have it display both the device profile name and description?

      ReplyDelete
    5. just found the answer ...

      run sql select d.name, d.description from device d where (0=(select count(edm.pkid) from enduserdevicemap edm where edm.fkdevice=d.pkid)) and d.tkclass=254 order by d.name

      ReplyDelete
    6. Hi - Is it possible to get the DN displayed along with above query
      please let me know

      Thanks

      ReplyDelete
      Replies
      1. Yes. You can join the device table, devicenumplanmap table, and numplan table. For example:

        http://www.ucguerrilla.com/2012/03/cucm-sql-queries-installment-3.html

        HTH
        -Bill (@ucguerrilla)

        Delete
    7. When I run this query against my CUCM cluster, I get device profiles that look like this:
      ModelProfileForfd14444c-0593-0726-ba2e-ad7272e70f6

      What does that mean?

      ReplyDelete
      Replies
      1. Looks like, "Model Profile" for "Fixed Device" + pkid perhaps?

        Delete