Sometimes CUCM lacks needed search criteria — Here are some SQL queries that will help

Far too often I need to search for phones or extensions based on a criteria that CUCM doesn’t provide a way to search for. This could be the DN associated to an end user, or the Caller ID mask on a DN. Luckily, just because the Web GUI doesn’t provide the ability, doesn’t mean we are completely SOL. I’ve found some queries on other websites (I apologize if I don’t credit you, I’ve had these in a notepad forever), and some of these I developed myself using the CUCM database table guide.

Feel free to use these whenever you need them. They’ve served me well.

Find End Users with a specific Directory 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 '5727' ORDER BY telephonenumber

Run the above SQL query on the CUCM publisher CLI and replace the LIKE ‘5727’ with LIKE ‘[insert number]’

This will find all end users assigned a specific telephone number.

Find Directory Numbers associated with a specific External Phone Number Mask

Below, edit the phone number “1234567890” 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= "1234567890"

Find Directory Numbers with a specific Line Text Label

Below, edit “%Jaron%” with a name, capitalization specific to find a DN that has a specific line text label set. The % on either side of the name searches with spaces. Without that symbol, it will assume you are search for an exact match. For Example “Jaron” would only match a Line Text Label that reads exactly “Jaron”, however “%Jaron%” would match “Jaron Davis”.

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 "%Jaron%"

Determine what phones are actively logged into a hunt group

This query checks all line groups and displays back all extensions with a True or False indicator (t/f) if they are logged into their respective hunt groups. It isn’t very refined at the moment and just displays ALL directory numbers in ALL line 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

Get a list of all phones and the device pools they are in

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%'

Get a list of all phones along with their DN’s

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