Monday, March 5, 2012

CUCM SQL Queries: Device Pool Allocations


I was talking to a colleague of mine about hosting a blog series which focused on providing SQL queries that I use when I need to get data from the CUCM server. The second query in this series is a simple one: displaying the number of devices allocated per device pool.

Primer

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

This Week's Query

For this week's post I am going to demonstrate a method for showing allocation summaries. Specifically, we are going to show the number of devices allocated to each device pool configured in CUCM.

    The Moving Parts

    From a db perspective the following Informix database tables store information relevant to our query:
    • Device.  This table will contain devices like the IP  phone.  Since users log into/out of line groups from a device level  (instead of the line level), you will need to know which phone you are  dealing with. Note, that Hunt Lists are also stored in the Device table.
    • DevicePool. As one could guess, this table contains device pool information.
    The Query

    Now that we have some information, we can build the query. You can run this query from the CLI using run sql [insert query].

    select count(d.name), dp.name as DevicePool
    from Device as d
    inner join DevicePool as dp on d.fkDevicePool=dp.pkid
    group by dp.name
    
    Basically, we are getting a count [count()] of a specific table element [e.g. device name]. When using this function we need to use the "group by" clause. The output of this query will be something like:

    (count) devicepool
    ======= ===============
    4       Default
    2       HQ_Media-GW_DP
    9       HQ_Media-Std_DP
    21      HQ_User-Std_DP
    4       HQ_User-VPN_DP
    8       RS_User-Std_DP


    Based on the above output, there are 21 devices associated with the HQ_User-Std_DP device pool, 9 devices associated with HQ_Media-Std_DP, and so on. What if we wanted to see the actual device types?

    To determine the specific device types allocation to a device pool along with the actual counts, you can use the following query:

    select count(d.name), dp.name as DevicePool, tc.name as DeviceType
    from Device as d
    inner join DevicePool as dp on d.fkDevicePool=dp.pkid
    inner join typeClass as tc on tc.enum=d.tkClass
    group by dp.name, tc.name
    order by dp.name
    


    The output from this query (using my CUCM 8.5 lab system):

    (count) devicepool      devicetype
    ======= =============== ========================
    1       Default         Gateway
    1       Default         Media Termination Point
    1       Default         Music On Hold
    1       Default         Tone Announcement Player
    2       HQ_Media-GW_DP  Gateway
    3       HQ_Media-Std_DP Trunk
    1       HQ_Media-Std_DP Media Termination Point
    2       HQ_Media-Std_DP Voice Mail
    1       HQ_Media-Std_DP Gateway
    2       HQ_Media-Std_DP Conference Bridge
    20      HQ_User-Std_DP  Phone
    1       HQ_User-Std_DP  Phone Template
    4       HQ_User-VPN_DP  Phone
    8       RS_User-Std_DP  Phone


    We added a new table to our query: typeClass. This table is an enumeration table that stores enumerators (or IDs) for device types. When adding another table column to a query where you are using the count() function, that column must be added to the group by clause.  I also added the order by clause in the above query to make the output more logical. 


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

    6 comments:

    1. Any suggestions on finding the *registered* devices against a DP as opposed to all *configured* devices?

      ReplyDelete
      Replies
      1. Sean,

        This is definitely possible but you can't get that data view from the Informix database. Real time information (such as registered state) is not stored in the database. To get data views of real time information on "modern" CUCM releases (i.e. 5.x and later) you can use the Serviceability API (http://www.cisco.com/en/US/docs/voice_ip_comm/cucm/devguide/8_0_1/serviceability.html).

        To filter data using DB elements you can use the AXL API to get a list of devices and the RisPort SOAP request to get the realtime information.

        AXL and RisPort are APIs and, as such, are not easily leveraged using the native user interfaces (UIs). You'll need some programming skills to leverage them.

        HTH

        -Bill (@ucguerrilla)

        Delete
    2. well I typed a long reply with code and all showing you how I used your notes above to save my company a lot of time and money.. then I had login issues and lost it all. suffice to say reading your notes helped me a ton and will net my company savings in both time and money! thanks! keep doing what you do!

      ReplyDelete
      Replies
      1. Outstanding! That is a huge complement and I am glad the content of this blog has been helpful.

        -Bill

        Delete
    3. Hi we want to total count for JAbber on CUCM what is command sql

      ReplyDelete
      Replies
      1. Hello,

        You may need to clarify your questions a bit. What do you mean by "Jabber on CUCM"? If you are looking presence enabled users then you can find that in CCMAdmin (User Management>Assign Presence users).

        Various devices can run Jabber: iPhone, Android, Tablet, CSF (Windows/Mac soft client).

        You can query for counts like this:
        run sql select tm.name as model, count(d.pkid) from device d inner join typemodel tm on d.tkmodel=tm.enum where d.tkmodel in (503,575,652,562) group by tm.name

        HTH

        -Bill (@ucguerrilla)

        Delete