Introduction
This document describes the use of SQL queries in order to get the Route Pattern - Route List - Route Group Associations. Administrators might use this functionality to document a text-based readout of their Public Switched Telephone Network (PSTN) or external calling configuration.
Prerequisites
Requirements
Cisco recommends that you have knowledge of Cisco Unified Communications Manager (CUCM).
Components Used
The information in this document is based on CUCM Release 8.x and later, but might also apply to earlier releases of CUCM.
The information in this document was created from the devices in a specific lab environment. All of the devices used in this document started with a cleared (default) configuration. If your network is live, make sure that you understand the potential impact of any command.
Tables
Structured Query Language (SQL) queries are formed with data from these tables:
- Device - The Device table contains device information such as Route List and Route Group.
- NumPlan - The NumPlan table contains all Route Patterns configured in CUCM.
- DeviceNumPlanMap - The DeviceNumPlanMap table stores mapping between data in the Device table and the NumPlan table. This mapping is used to map the Route Pattern to the Route List.
- RoutePartition - The RoutePartition table contains partitions created in CUCM.
- RouteList - The RouteList table contains RouteList configuration details.
- RouteGroup - The RouteGroup table contains RouteGroup information.
- RouteGroupDeviceMap - The RouteGroupDeviceMap table contains mapping between data in the RouteGroup table and the Device table. This mapping is used in order to obtain devices selected in the Route Group.
More information about these tables is located in the CUCM Database Dictionary.
SQL Queries
Queries are written in order to find one association at a time.
Find the Route Pattern - Partition Association
This query lists the route pattern associations to their route partition names.
run sql select n.dnorpattern as RoutePattern, rp.name as Partition from numplan
as n, routepartition as rp where rp.pkid=n.fkroutepartition and n.tkpatternusage=5
routepattern partition
===================== =========
9.[2-9]XXXXXX pt-hq
91[2-9]XX.[2-9]XXXXXX pt-hq
9011.! pt-hq
911 pt-hq
9.[2-9]XXXXXX pt-sb
9011.! pt-sb
9011.!# pt-sb
911 pt-sb
9.XXXXXXXX pt-sc
900.! pt-sc
900.!# pt-sc
999 pt-sc
\+1.[2-9]XXXXXXXXX pt-cfur
Note: The tkpatternusage value is 5 for Route Pattern. Change the tkpatternusage value to 2 in order to obtain DN and Phone Association. Change the column name RoutePattern to DN and RouteList to Phone.
Find Route Pattern - Route List - Route Group Association
This query expands the route list configuration in order to include route groups:
run sql select n.dnorpattern as RoutePattern, rp.name as Partition, d.name as
RouteList, rl.selectionOrder, rg.name as RouteGroup from numplan as n inner join
routepartition as rp on rp.pkid=n.fkroutepartition inner join devicenumplanmap as
dnpm on dnpm.fknumplan=n.pkid inner join device as d on dnpm.fkdevice=d.pkid inner
join routelist as rl on rl.fkdevice = d.pkid inner join routegroup
as rg on rg.pkid=rl.fkroutegroup and n.tkpatternusage=5
route partition route selection routegroup
pattern list order
===================== ========= ============== =========== ======================
9.[2-9]XXXXXX pt-hq rl-hq-local 1 Standard Local Route Group
9.[2-9]XXXXXX pt-hq rl-hq-local 2 siteb
91[2-9]XX.[2-9]XXXXXX pt-hq rl-hq-ld 1 siteb
91[2-9]XX.[2-9]XXXXXX pt-hq rl-hq-ld 2 hq
9011.! pt-hq rl-hq-inter 1 Standard Local Route Group
911 pt-hq rl-slrg 1 Standard Local Route Group
9.[2-9]XXXXXX pt-sb rl-siteb-local 1 siteb
9011.! pt-sb rl-siteb-inter 1 siteb
9011.! pt-sb rl-siteb-inter 2 hq
9011.!# pt-sb rl-hq-inter 1 Standard Local Route Group
911 pt-sb rl-slrg 1 Standard Local Route Group
9.XXXXXXXX pt-sc rl-sc-local 1 Standard Local Route Group
900.! pt-sc rl-sc-inter 1 Standard Local Route Group
900.!# pt-sc rl-sc-inter 1 Standard Local Route Group
999 pt-sc rl-slrg 1 Standard Local Route Group
\+1.[2-9]XXXXXXXXX pt-cfur rl-hq-cfur 1 hq
Note: The selectionorder gives the priority of the Route Group in the Route List. In this query, 'and n.tkpatternusage=5' can be omitted as Route Pattern is the only entry in the dnorpattern table that can be associated with a Route Group. In the previous queries, tkpatternusage is required as the Route List is in the device table which contains other devices like Phones.
Find Members of a Route Group
run sql select rg.name as RouteGroup, rgdm.DeviceSelectionOrder, d.name as Gateway
from device as d, RouteGroup as rg, RouteGroupDeviceMap as rgdm where
rgdm.fkRouteGroup=rg.pkid and d.pkid=rgdm.fkDevice
routegroup deviceselectionorder gateway
========== ==================== ==================
hq 1 S0/SU0/DS1-0@hq
siteb 1 192.168.254.10
sitec 1 S0/SU0/DS1-0@sitec
Note: The deviceselectionorder gives the priority of the Devices in the Route Group. In this example, there is only one gateway in each Route Group.
route partition route selection route
pattern list order group
===================== ========= ============== ===== =======================
9.[2-9]XXXXXX pt-hq rl-hq-local 1 Standard Local Route Group
9.[2-9]XXXXXX pt-hq rl-hq-local 2 siteb
91[2-9]XX.[2-9]XXXXXX pt-hq rl-hq-ld 1 siteb
91[2-9]XX.[2-9]XXXXXX pt-hq rl-hq-ld 2 hq
9011.! pt-hq rl-hq-inter 1 Standard Local Route Group
911 pt-hq rl-slrg 1 Standard Local Route Group
9.[2-9]XXXXXX pt-sb rl-siteb-local 1 siteb
9011.! pt-sb rl-siteb-inter 1 siteb
9011.! pt-sb rl-siteb-inter 2 hq
9011.!# pt-sb rl-hq-inter 1 Standard Local Route Group
911 pt-sb rl-slrg 1 Standard Local Route Group
9.XXXXXXXX pt-sc rl-sc-local 1 Standard Local Route Group
900.! pt-sc rl-sc-inter 1 Standard Local Route Group
900.!# pt-sc rl-sc-inter 1 Standard Local Route Group
999 pt-sc rl-slrg 1 Standard Local Route Group
\+1.[2-9]XXXXXXXXX pt-cfur rl-hq-cfur 1 hq
Feedback