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…