CUCM Pattern or Number Reference Finder

For some unknown reason you can't run dependency records on hunt pilots or route patterns, which makes tracking down references to them a pain if you've deleted or changed the number. Fortunately you can run SQL queries from the CLI to get around this. Below is an SQL query for CUCM 8.X+ that checks for CFA, CFB, CFNA, CFNC, CFUR, AAR Destination Mask or Called Party Transformation that reference a specified number (in this example 119063):


SELECT n.DNOrPattern, n.AlertingName, n.Description, n.tkPatternUsage FROM NumPlan AS n LEFT JOIN CallForwardDynamic AS cfd ON cfd.fkNumPlan = n.pkid WHERE n.CFAptDestination LIKE '119063' OR n.CFBDestination LIKE '119063' OR n.CFBIntDestination LIKE '119063' OR n.CFNADestination LIKE '119063' OR n.CFNAIntDestination LIKE '119063' OR n.PFFDestination LIKE '119063' OR n.PFFIntDestination LIKE '119063' OR n.CFURDestination LIKE '119063' OR n.CFURIntDestination LIKE '119063' OR n.AARDestinationMask LIKE '119063' OR n.CalledPartyTransformationMask LIKE '119063' OR cfd.CFADestination LIKE '119063' ORDER BY n.DNOrPattern ASC

The output will include the pattern, description, alerting name & pattern type for any dial plan elements that reference the given number:

admin: run sql SELECT n.DNOrPattern, n.AlertingName, n.Description, n.tkPatternUsage FROM NumPlan AS n LEFT JOIN CallForwardDynamic AS cfd ON cfd.fkNumPlan=n.pkid WHERE n.CFAptDestination LIKE '119063' OR n.CFBDestination LIKE '119063' OR n.CFBIntDestination LIKE '119063' OR n.CFNADestination LIKE '119063' OR n.CFNAIntDestination LIKE '119063' OR n.PFFDestination LIKE '119063' OR n.PFFIntDestination LIKE '119063' OR n.CFURDestination LIKE '119063' OR n.CFURIntDestination LIKE '119063' OR n.AARDestinationMask LIKE '119063' OR n.CalledPartyTransformationMask LIKE '119063' OR cfd.CFADestination LIKE '119063' ORDER BY n.DNOrPattern ASC

dnorpattern alertingname   description    tkpatternusage
=========== ============== ============== ==============
9063        Catarina Vidal Catarina Vidal 2 

The tkPatternUsage field lists the type of dial plan element, use the table below to interpret it:

tkPatternUsage	Dial Plan Element
0	Call Park
1	Conference
2	Directory Number
3	Translation Pattern
4	Call Pick Up Group
5	Route Pattern
6	Message Waiting
7	Hunt Pilot
8	Voice Mail Port
9	Domain Routing
10	IP Address Routing
11	Device template
12	Directed Call Park
13	Device Intercom
14	Translation Intercom
15	Translation Calling Party Number
16	Mobility Handoff
17	Mobility Enterprise Feature Access
18	Mobility IVR
19	Device Intercom Template
20	Called Party Number Transformation
21	Call Control Discovery Learned Pattern
22	Uri Routing
23	ILS Learned Enterprise Number
24	ILS Learned E164 Number
25	ILS Learned Enterprise Numeric Pattern
26	ILS Learned E164 Numeric Pattern
27	Alternate Number
28	ILS Learned URI
29	ILS Learned PSTN Failover Rule
30	ILS Imported E164 Number

HLog and 6900 Series Phones

Unfortunately the 6900 series phones don't have any visible notification of whether or not the phone is logged into hunt groups. This means that once the message displayed after pressing the HLog softkey disappears, there's no way of telling if your phone will take calls from hunt groups, other than to press HLog again. This is a rather bizarre omission given that queuing on hunt pilots is one of the big new features in CUCM 9.x & that in 9.0 it actually automatically HLogs you out if you don't answer a call from a hunt pilot with queuing enable. Thus it's easy to get into a situation where no one is receiving calls from hunt groups & they have no idea why.
Thankfully in CUCM 9.1 you can disable the auto HLog out of phones via "Automatically Logout Hunt Member on No Answer" within the line group settings. But this still leaves 6900 series phones with no indication of HLog status. The solution is to use a custom phone button template with one of the buttons being the HLog PLK, for example:

The LED on that button then indicates whether or not the phone is logged in to hunt groups. This is actually pretty handy for 7900 series phones too, as their "Logged out of Hunt Group" status message gets overwritten by the number of missed calls status message.