SQL Queries That Will Help Get More Information Out of CUCM

Frustrated by the lack of reporting tools in Cisco Unified Communications Manager? There have been many times I need to find phones or extensions that can't be found in a CUCM search in the gui administration page. Maybe the external phone number mask on a DN or I need the Directory Number associated to an end user, or. Well, I'm here to show you that there are ways around it. Below are some queries that I have used before to help me in my career. Please use them if you feel they will be helpful to you.

Perhaps some of you enterprising DevNet types will be able to take these and use a python script to pull this information.

Find End Users with a specific Directory Number

As stated above, this query will find End Users with a specific Directory Number. You want to run this SQL query on the CUCM publisher CLI and replace the LIKE ‘4006’ with LIKE ‘Extension you're looking for’.This will find all end users assigned a specific telephone number.

run sql select userid,firstname,lastname,telephonenumber,department,enduser.allowcticontrolflag cti,device.name,device.tkmodel, numplan.dnorpattern Primary from enduser left join enduserdevicemap on(enduser.pkid=enduserdevicemap.fkenduser) left join device on(enduserdevicemap.fkdevice=device.pkid) left join endusernumplanmap on(enduser.pkid=endusernumplanmap.fkenduser) left join numplan on(endusernumplanmap.fknumplan=numplan.pkid) where telephonenumber LIKE '4006' ORDER BY telephonenumber

Please check out my YouTube Chanel

Do you need a list of all phones and their device pools?

run sql select Devicepool.name as DevicePool,device.name as DeviceName from Devicepool inner join Device on Device.fkDevicepool=DevicePool.pkid where device.name like 'SEP%'

List all phones with their DN's

How about a list of all phones with their DN’s. This query can be modified by adding a specific model number after d.tkclass = 1 and d.tkmodel='435'. '435' is a 7945. To get a list of the tkmodel value for all models run the following

run sql select name,tkmodel from TypeProduct
run sql select d.name, d.description, n.dnorpattern as DN, rp.name as partition
from device as d, numplan as n, devicenumplanmap as dnpm, routepartition as rp
where dnpm.fkdevice = d.pkid and dnpm.fknumplan = n.pkid and rp.pkid =
n.fkroutepartition and d.tkclass = 1

Find Phones with Line Level User Association

How about finding all phones that have a line-level user association. Useful for Jabber users.

run sql select eu.userid, d.name, d.description, n.dnorpattern as DN, rp.name as
partition from device as d inner join devicenumplanmap as dnpm on dnpm.fkdevice =
d.pkid inner join devicenumplanmapendusermap as dnpeum on dnpeum.fkdevicenumplanmap=
dnpm.pkid inner join enduser as eu on dnpeum.fkenduser=eu.pkid inner join numplan as
n on dnpm.fknumplan = n.pkid inner join routepartition as rp on n.fkroutepartition=
rp.pkid and d.tkclass = 1

Find Directory Numbers With a Specific External Phone Number Mask

This query will find Directory Numbers associated with a specific External Phone Number Mask

Below, edit the phone number “4085551212” with another 10-digit number to find DN’s with a specific External Phone Number Mask (aka caller ID)

run sql select dnorpattern, d.description, e164mask from devicenumplanmap inner join numplan on fknumplan=numplan.pkid join device d on fkdevice = d.pkid where e164mask= "4085551212"

Please check out my YouTube Chanel

List SIP Trunks with Their Destination

This one has been very useful for me. The Device -> Trunks page doesn't give you the destination ip address of your SIP Trunks. This query will list your SIP Trunks along with the destination of each one.

select d.name as device, d.description, std.sortorder, std.address, std.port from device d inner join sipdevice sd on sd.fkdevice=d.pkid inner join siptrunkdestination std on std.fksipdevice=sd.pkid order by d.name, std.sortorder

Find Phone Logged into a Hunt Group

Find which phones are currently logged into a hunt group. This query will loo into all line groups and give you all extensions indication a True or False. If the user is logged into a hunt groups.

run sql select lg.name as LineGroup,n.dnorpattern,dhd.hlog from linegroup as lg inner join linegroupnumplanmap as lgmap on lgmap.fklinegroup=lg.pkid inner join numplan as n on lgmap.fknumplan = n.pkid inner join devicenumplanmap as dmap on dmap.fknumplan = n.pkid inner join device as d on dmap.fkdevice=d.pkid inner join devicehlogdynamic as dhd on dhd.fkdevice=d.pkid order by lg.name

Translation Patterns with Their Called Party Transformations

Here's one that has helped me with translation patterns. The Translation patterns list doesn't give you what the translated number is without clicking on the pattern and looking at it. The query will give you all translation patterns with the associated called party transformation.

select n.dnorpattern as Translation, rp.name as Partition, ddi.name as DiscardDigits, n.prefixdigitsout, n.calledpartytransformationmask 
from numplan n left join routepartition rp on n.fkroutepartition=rp.pkid left join digitdiscardinstruction ddi on n.fkdigitdiscardinstruction=ddi.pkid 
order by n.dnorpattern

Directory Numbers with a Line Text Label

This query will help you find Directory Numbers with a specific Line Text Label. Edit “%Stephen%” with a name, this is case specific, to find a DN that has this line text label. The % searches with spaces. Without the %, the query will search for an exact match. For Example, “Stephen” will only match a Line Text Label of exactly “Jaron”, and “%Stephen%” will match a name like  “Stephen Martin”.

run sql select dnorpattern, d.description, display, label from devicenumplanmap inner join numplan on fknumplan=numplan.pkid join device d on fkdevice =d.pkid where label LIKE "%Stephen%"

I hope these help you in your endeavor to support CUCM. If you have any questions please comment below. And please check out my YouTube Chanel where I have many tutorial on installations, upgrades and other UC stuff.