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.
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.
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:
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
Thanks for reading. If there is a specific query you are interested in seeing, let me know using the comments!