Overview
A friend at work needed a simple report detailed basic phone data and associated user information. This is a common requirement for many CUCM admins, so thought I’d give a quick SQL solution for this.
Getting phone information is easy – this can be taken from the “device” table. However, a problem comes in when determining how to “link” phones to users. Possible options include:
- Owner User ID
- Phone Association with End User
- End User and DN Associations
- Extension Mobility
- etc.
I will demonstrate in this post how to determine phone/user associations and owner userid associations. Both involve a basic SQL JOIN.
For my requirements, we wanted to return phones that DID NOT have user associations – a basic review of JOIN set theory can be helpful here
Queries
User/Phone Association
The phone association is contained in a “mapping” table “enduserdevicemap.” This table is simply joined to both “device” and “enduser” to extract the necessary information from both.
Additionally, we look up “typemodel” information from the enum value to display phone model information…
SELECT DISTINCT d.name AS device_name, tm.name AS device_type, eu.userid, eu.firstname, eu.lastname FROM device d LEFT OUTER JOIN enduserdevicemap eudmap on d.pkid=eudmap.fkdevice LEFT OUTER JOIN enduser eu on eudmap.fkenduser=eu.pkid INNER JOIN typemodel tm on d.tkmodel=tm.enum WHERE d.name LIKE 'SEP%' ORDER BY eu.userid
Phone Owner UserID
The owner userid association is on the device “fkenduser” foreign key…
SELECT DISTINCT d.name AS device_name, tm.name AS device_type, eu.userid, eu.firstname, eu.lastname FROM device d LEFT OUTER JOIN enduser eu on d.fkenduser=eu.pkid INNER JOIN typemodel tm on d.tkmodel=tm.enum WHERE d.name LIKE 'SEP%' ORDER BY eu.userid
Collating Both Queries
If you want to work with both sources and return a single report, a UNION statement can be used:
SELECT DISTINCT d.name AS device_name, tm.name AS device_type, eu.userid, eu.firstname, eu.lastname FROM device d LEFT OUTER JOIN enduserdevicemap eudmap on d.pkid=eudmap.fkdevice LEFT OUTER JOIN enduser eu on eudmap.fkenduser=eu.pkid INNER JOIN typemodel tm on d.tkmodel=tm.enum WHERE d.name LIKE 'SEP%' UNION SELECT DISTINCT d.name AS device_name, tm.name AS device_type, eu.userid, eu.firstname, eu.lastname FROM device d LEFT OUTER JOIN enduser eu on d.fkenduser=eu.pkid INNER JOIN typemodel tm on d.tkmodel=tm.enum WHERE d.name LIKE 'SEP%' ORDER BY eu.userid
Other Phone-Related Reporting Requirements
Other reporting requirements may need to consider extension mobility or DN associations to end users. The following two “map” may come in handy here:
- endusernumplanmap
- extensionmobilitydynamic
For this and all other CUCM db lookups, please consult the CUCM Database Dictionary…
Just wanted to say thank you, this helped me a lot. No way I could have written those queries haha…
LikeLike