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!