Introduction
This document provides Structured Query Language (SQL) queries that can be run on the CLI in order to obtain the Directory Numbers (DNs) and partitions associated with IP Phones along with any End User association.
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 Versions 8.X and later, but might work for earlier versions as well.
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
The SQL queries are formed with data from these tables:
- Device - This table contains device-level information like IP Phones and gateways.
- NumPlan - This table contains information about all patterns configured in CUCM.
- DeviceNumPlanMap - This table contains the mapping between data in Device table and NumPlan table.
- RoutePartition - This table contains the information about all of the Partitions configured in CUCM
- EndUser - This table contains the information related to End Users.
- DeviceNumPlanMapEndUserMap - This table contains the mapping between data in the DeviceNumPlanMap table and the EndUser table.
- EndUserDeviceMap - This table contains the mapping between data in the Device table and EndUser table.
For more information about these tables, refer to the CUCM Data Dictionary for the respective version.
SQL Queries
The queries are written to find one association at a time in order to make them easier to understand.
List All of the DNs Associated with IP Phones
Here is a query that is designed to list all of the DNs associated with IP Phones.
run sql select dnorpattern as dn from numplan where pkid IN(select fknumplan from
devicenumplanmap where fkdevice IN (select pkid from device where tkclass =
1)) order by dn
Note: tkclass = 1 is for IP Phones. The dnorpattern is a column in the table numplan that has DNs associated with Phones / Hunt Pilots / VM Ports / Route Patterns / CTI RPs.
List the DNs and Their Associated IP Phones
Here is a query that is designed to list the DNs and their associated IP Phones.
run sql select d.name, d.description, n.dnorpattern as DN from device as d,
numplan as n, devicenumplanmap as dnpm where dnpm.fkdevice = d.pkid and
dnpm.fknumplan = n.pkid and d.tkclass = 1
name description dn
=============== ============================ ====
SEP0026CBBFF47E Line 1 - 7031; Line 2 - 7011 7011
SEP0026CBBFF47E Line 1 - 7031; Line 2 - 7011 7031
SEPC89C1DA3A5A9 Line 1 - 8021 8021
SEPA40CC3956C5C Line 1 - 1213; Line 2 - 1212 1213
SEPA40CC3956C5C Line 1 - 1213; Line 2 - 1212 1212
SEP503DE57D7DAC 8501 8501
SEP0008308B289A Line 1 - 8023 8023
SEP0008308BBEBD Line 1 - 8032; Line 2 - 8012 8012
SEP0008308BBEBD Line 1 - 8032; Line 2 - 8012 8032
The query can be modified if you add the tkmodel number in order to list details for specific model IP Phones. Here is a query for IP Phone Model 7945:
run sql select d.name, d.description, n.dnorpattern as DN from device as d,
numplan as n, devicenumplanmap as dnpm where dnpm.fkdevice = d.pkid and
dnpm.fknumplan = n.pkid and d.tkclass = 1 and d.tkmodel='435'
Here is a query to obtain the tkmodel value for all IP Phone Models:
run sql select name,tkmodel from TypeProduct
Note: Any field can be filtered to include only information that is desired. For example, in oder to filter on DN, add this at the end of the query: where n.dnorpattern like '8%'.
This query lists all entries that have a DN that begins with 8. The format is where <column name> like '<value>%'.
Note: Some other fields that provide useful information (user readable and not Primary Key Identifiers (PKIDs)) can be added to this query. Add them in between 'run sql select' and 'as DN'. These are the fields that can be added:
d.tkmodel - Use the query described in the previous note in order to obtain the value for each model.
d.tkdeviceprotocol - For Skinny Call Control Protocol (SCCP), the value is 0 and for Session Initiation Protocol (SIP), the value is 11.
List the Device Information and DNs with Associated Partitions
Here is a query that is designed to list the device information and DNs with associated partitions.
run sql select d.name, d.description, n.dnorpattern as DN, rp.name as partition
from device as d, numplan as n, devicenumplanmap as dnpm, routepartition as rp
where dnpm.fkdevice = d.pkid and dnpm.fknumplan = n.pkid and rp.pkid =
n.fkroutepartition and d.tkclass = 1
name description dn partition
=============== ============================ ==== ===========
SEPA40CC3956C5C Line 1 - 1213; Line 2 - 1212 1212 Internal_PT
SEP503DE57D7DAC 8501 8501 Internal_PT
SEPA40CC3956C5C Line 1 - 1213; Line 2 - 1212 1213 Internal_PT
SEP0008308B289A Line 1 - 8023 8023 Internal_PT
Note: All DNs that do not have a partition are not listed here.
As more tables are added in the query, it is easier to use joins. The previous query can also be written:
run sql select d.name, d.description, n.dnorpattern as DN, rp.name as partition
from device as d inner join devicenumplanmap as dnpm on dnpm.fkdevice = d.pkid
inner join numplan as n on dnpm.fknumplan = n.pkid inner join routepartition as
rp on n.fkroutepartition=rp.pkid and d.tkclass = 1
The query can be modified if you add the tkmodel number in order to list details for specific model IP Phones. For IP Phone Model 7945, add and d.tkmodel='435' to the end of the query.
In order to obtain the tkmodel value for all IP Phone Models, enter:
run sql select name,tkmodel from TypeProduct
Note: Any field can be filtered to include only information that is desired. For example, in order to filter on DN, add this at the end of the query: where n.dnorpattern like '8%'
This query lists all entries that have a DN that begins with 8. The format is where <column name> like '<value>%'.
Note: Some other fields that provide useful information (user readable and not PKIDs) can be added to this query. Add them in between 'run sql select' and 'as DN'. These are the fields that can be added:
d.tkmodel - Use the query described in the previous note in order to obtain the value for each model.
d.tkdeviceprotocol - For SCCP, the value is 0 and for SIP, the value is 11.
Find Phones that Have a Line-Level User Association
Here is a query that is designed to find phones that have a line-level user association.
run sql select eu.userid, d.name, d.description, n.dnorpattern as DN, rp.name as
partition from device as d inner join devicenumplanmap as dnpm on dnpm.fkdevice =
d.pkid inner join devicenumplanmapendusermap as dnpeum on dnpeum.fkdevicenumplanmap=
dnpm.pkid inner join enduser as eu on dnpeum.fkenduser=eu.pkid inner join numplan as
n on dnpm.fknumplan = n.pkid inner join routepartition as rp on n.fkroutepartition=
rp.pkid and d.tkclass = 1
userid name description dn partition
============ =============== ============= ==== ===========
AMavilakandy SEP0008308B289A Line 1 - 8023 8023 Internal_PT
This query only provides information when the DN is in a partition. In order to include the ones in None Partition, enter:
run sql select eu.userid, d.name, d.description, n.dnorpattern as DN from device
as d inner join devicenumplanmap as dnpm on dnpm.fkdevice = d.pkid inner join
devicenumplanmapendusermap as dnpeum on dnpeum.fkdevicenumplanmap=dnpm.pkid inner
join enduser as eu on dnpeum.fkenduser=eu.pkid inner join numplan as n on dnpm.fknumplan
= n.pkid and d.tkclass = 1
The query can be modified if you add the tkmodel number in order to list details for specific model IP Phones. For IP Phone Model 7945, add and d.tkmodel='435' to the end of the query.
In order to obtain the tkmodel value for all IP Phone Models, enter:
run sql select name,tkmodel from TypeProduct
There are some other fields that provide useful information (user readable and not PKIDs) that can be added to this query. Add them in between 'run sql select' and 'as DN'. These are the fields that can be added:
- eu.firstname
- eu.middlename
- eu.lastname
- eu.manager
- eu.department
- eu.telephonenumber
- eu.mailid
- eu.mobile
- eu.homephone
- eu.title
- d.tkmodel - Use the query mentioned previously in order to obtain the value for each model.
- d.tkdeviceprotocol - For SCCP the value is 0 and For SIP the value is 11.
Note: Any field can be filtered to include only information that is desired. For example, in order to filter on DN, add this at the end of the query: where n.dnorpattern like '8%'.
This query lists all entries that have a DN that begins with 8. The format is where <column name> like '<value>%'.
Find Phones that Do Not have a Line-Level User Association
Here is a query that is designed to find phones that do not have a line-level user association.
run sql select d.name, d.description, n.dnorpattern as DN, rp.name as partition
from device as d inner join devicenumplanmap as dnpm on dnpm.fkdevice = d.pkid
inner join devicenumplanmapendusermap as dnpeum on dnpeum.fkdevicenumplanmap!=
dnpm.pkid inner join numplan as n on dnpm.fknumplan = n.pkid inner join
routepartition as rp on n.fkroutepartition=rp.pkid and d.tkclass = 1
name description dn partition
=============== ============================ ==== ===========
SEPA40CC3956C5C Line 1 - 1213; Line 2 - 1212 1212 Internal_PT
SEP503DE57D7DAC 8501 8501 Internal_PT
SEPA40CC3956C5C Line 1 - 1213; Line 2 - 1212 1213 Internal_PT
This query only provides information when the DN is in a partition. In order to include the ones in None Partition, enter:
run sql select d.name, d.description, n.dnorpattern as DN from device as d inner
join devicenumplanmap as dnpm on dnpm.fkdevice = d.pkid inner join
devicenumplanmapendusermap as dnpeum on dnpeum.fkdevicenumplanmap!=dnpm.pkid
inner join numplan as n on dnpm.fknumplan = n.pkid and d.tkclass = 1
The query can be modified if you add the tkmodel number in order to list details for specific model IP Phones. For IP Phone Model 7945, add and d.tkmodel='435' to the end of the query.
In order to obtain the tkmodel value for all IP Phone Models, enter:
run sql select name,tkmodel from TypeProduct
There are some other fields that provide useful information (user readable and not PKIDs) that can be added to this query. Add them in between 'run sql select' and 'as DN'. These are the fields that can be added:
- eu.firstname
- eu.middlename
- eu.lastname
- eu.manager
- eu.department
- eu.telephonenumber
- eu.mailid
- eu.mobile
- eu.homephone
- eu.title
- d.tkmodel - Use the query mentioned previously in order to obtain the value for each model.
- d.tkdeviceprotocol - For SCCP, the value is 0 and for SIP, the value is 11.
Note: Any field can be filtered to include only information that is desired. For example, in order to filter on DN, add this at the end of the query: where n.dnorpattern like '8%'.
This lists all entries that have a DN that begins with 8. The format is where <column name> like '<value>%'.
Find Phones that Have a Device-Level User Association
Here is a query that is designed to find phones that have a device-level user association.
run sql select eu.userid, d.name, d.description, n.dnorpattern as DN, rp.name as
partition from device as d inner join devicenumplanmap as dnpm on dnpm.fkdevice =
d.pkid inner join enduserdevicemap as eudm on eudm.fkdevice=d.pkid inner join
enduser as eu on eudm.fkenduser=eu.pkid inner join numplan as n on dnpm.fknumplan =
n.pkid inner join routepartition as rp on n.fkroutepartition=rp.pkid and d.tkclass = 1
userid name description dn partition
============ =============== ============================ ==== ===========
AMavilakandy SEPA40CC3956C5C Line 1 - 1213; Line 2 - 1212 1212 Internal_PT
AMavilakandy SEPA40CC3956C5C Line 1 - 1213; Line 2 - 1212 1213 Internal_PT
AMavilakandy SEP0008308B289A Line 1 - 8023 8023 Internal_PT
This query only provides information when the DN is in a partition. In order to include the ones in None Partition, enter:
run sql select eu.userid, d.name, d.description, n.dnorpattern as DN from device
as d inner join devicenumplanmap as dnpm on dnpm.fkdevice = d.pkid inner join
enduserdevicemap as eudm on eudm.fkdevice=d.pkid inner join enduser as eu on
eudm.fkenduser=eu.pkid inner join numplan as n on dnpm.fknumplan = n.pkid and
d.tkclass = 1
The query can be modified if you add the tkmodel number in order to list details for specific model IP Phones. For IP Phone Model 7945, add and d.tkmodel='435' to the end of the query.
In order to obtain the tkmodel value for all IP Phone Models, enter:
run sql select name,tkmodel from TypeProduct
There are some other fields that provide useful information (user readable and not PKIDs) that can be added to this query. Add them in between 'run sql select' and 'as DN'. These are the fields that can be added:
- eu.firstname
- eu.middlename
- eu.lastname
- eu.manager
- eu.department
- eu.telephonenumber
- eu.mailid
- eu.mobile
- eu.homephone
- eu.title
- d.tkmodel - Use the query mentioned previously in order to obtain the value for each model.
- d.tkdeviceprotocol - For SCCP, the value is 0 and for SIP, the value is 11.
Note: Any field can be filtered to include only information that is desired. For example, in order to filter on DN, add this at the end of the query: where n.dnorpattern like '8%'.
This lists all entries that have a DN that begins with 8. The format is where <column name> like '<value>%'.
Feedback