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