Thursday, 12 February 2015

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 inner 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:

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

No comments:

Post a Comment