Finding Phones With Incorrect CSS

SQL queries can be used to quickly report on common configuration mistakes that build up over time, such as having the wrong CSS on a phone for the device pool it is in, simply plug in the correct values & run the query from the CLI. Using like in the SQL query means that it's also possible to use certain wildcards (e.g. % being zero or more characters) to aid in the search.

SELECT d.name, d.description, dp.name, css.name FROM device AS d INNER JOIN devicepool AS dp ON d.fkdevicepool = dp.pkid INNER JOIN callingsearchspace AS css ON d.fkcallingsearchspace = css.pkid WHERE dp.name LIKE 'DP-Frankfurt-Phones' AND css.name NOT LIKE 'CSS-Frankfurt-Unrestricted' ORDER BY d.name

The output will include the device name, device description, device pool name and CSS name:

admin:run sql SELECT d.name, d.description, dp.name, css.name FROM device AS d INNER JOIN devicepool AS dp ON d.fkdevicepool = dp.pkid INNER JOIN callingsearchspace AS css ON d.fkcallingsearchspace = css.pkid WHERE dp.name LIKE 'DP-Frankfurt-Phones' AND css.name NOT LIKE 'CSS-Frankfurt-Unrestricted' ORDER BY d.name

name            description     name        name

=============== =============== =================== ==================

SEPF09E636E5656 SEPF09E636E5656 DP-Frankfurt-Phones CSS-Frankfurt-CoR6

SEPF09E636E5657 SEPF09E636E5657 DP-Frankfurt-Phones CSS-Frankfurt-CoR6

Finding Lines With a Specified External Phone Number Mask

Via the power of SQL queries you can quickly determine what devices & lines have a specified external phone number mask. Handy for homing in on possible causes of calls with incorrect caller ID. Using like in the SQL query means that it's also possible to use certain wildcards (e.g. % being zero or more characters) to aid in the search.

select d.name, d.description, n.dnorpattern, dmap.e164mask from device as d inner join devicenumplanmap as dmap on dmap.fkdevice = d.pkid inner join numplan as n on dmap.fknumplan = n.pkid where dmap.e164mask like '%2081234567' order by d.name

The output will include the device name, device description, DN & external phone number mask of any matching devices & lines:

admin:run sql select d.name,d.description,n.dnorpattern,dmap.e164mask from device as d inner join devicenumplanmap as dmap on dmap.fkdevice=d.pkid inner join numplan as n on dmap.fknumplan=n.pkid where dmap.e164mask like '%2081234567' order by d.name

name            description     dnorpattern e164mask

=============== =============== =========== =============

SEPF09E636E5656 SEPF09E636E5656 1000        +442081234567

SEPF09E636E5657 SEPF09E636E5657 1001        +442081234567

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