Guest

Cisco Unified Communications Manager (CallManager)

CUCM SQL Queries That Associate Device Information with an Application User

Document ID: 117780

Updated: Jun 20, 2014

Contributed by Anirudh Mavilakandy, Cisco TAC Engineer.

   Print

Introduction

This document provides Structured Query Language (SQL) queries that can be run in the CLI in order to get the device information associated with an application user.

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 Version 8.X and later, but might work for earlier versions.

Tables

The SQL queries are formed with the use of data from these tables:

  • Device - This table contains device level information such as IP phones, gateways, and so on.
  • NumPlan - This table contains information about all patterns configured in CUCM.
  • DeviceNumPlanMap - This table contains the mapping between data in the Device table and the NumPlan table.
  • RoutePartition - This table contains the information about all partitions configured in CUCM.
  • ApplicationUser - This table contains information about application users.
  • ApplicationUserDeviceMap - This table contains the mapping between data in ApplicationUser table and Device table.
  • DirGroup - This table contains information about Permission Groups.
  • ApplicationUserDirGroupMap - This table contains the mapping between data in ApplicationUser table and DirGroup table.

More information about these tables can be found in the CUCM Data Dictionary for the respective version.

SQL Queries

For clarity, the queries are written to find one association at a time.

List Application User and Device Association

run sql select au.name as ApplicationUser, d.name as Device, d.description as DeviceDescription from applicationuser as au inner join applicationuserdevicemap as audm on audm.fkapplicationuser=au.pkid and isstandard ='f' inner join device as d on d.pkid=audm.fkdevice order by au.name

admin:run sql select au.name as ApplicationUser, d.name as Device, d.description
as DeviceDescription from applicationuser as au inner join
applicationuserdevicemap as audm on audm.fkapplicationuser=au.pkid and isstandard
='f' inner join device as d on d.pkid=audm.fkdevice order by au.name
applicationuser device devicedescription
=============== =============== ============================
Test1 SEP503DE57D7DAC 8501
Test1 SEPA40CC3956C5C Line 1 - 1213; Line 2 - 1212
Test2 CTI1 CTI1

This query lists only those application users that are created after the installation of the server. The default ones are not listed and these are removed with the use of the isstandard='f' clause. In order to get the list of all users, either remove the _and isstandard='f'_ clause or change 'f' for false to 't' for true.

In this example there are two application users:

  • Test 1 is associated with two phones
  • Test 2 is associated with a Computer Telephony Integration (CTI) Route Point

Note: Any field can be filtered to include only information that is desired. For example, To filter on Devices: To List the Phones with Device Names that begin with SEP, add where d.name like 'SEP% at the end of the query, but just before 'order by au.name'. The format is where <column name> like '<value>%'.

List Application User with Permissions and Device Association

run sql select au.name as ApplicationUser, d.name as Device, d.description as DeviceDescription, dg.name as PermissionGroup from applicationuser as au inner join applicationuserdevicemap as audm on audm.fkapplicationuser=au.pkid and isstandard ='f' inner join ApplicationUserDirGroupMap as audgm on audgm.fkapplicationuser=au.pkid inner join DirGroup as dg on audgm.fkdirgroup=dg.pkid inner join device as d on d.pkid=audm.fkdevice order by au.name

applicationuser device          devicedescription            permissiongroup                           
=============== =============== ============================ =========================================
Test1 SEP503DE57D7DAC 8501 Standard CCM Admin Users
Test1 SEPA40CC3956C5C Line 1 - 1213; Line 2 - 1212 Standard CCM Admin Users
Test2 CTI1 CTI1 Standard CTI Allow Control of All Devices
Test2 CTI1 CTI1 Standard CTI Enabled

This query lists only those application users that are created after the installation of the server. The default ones are not listed and these are removed by using the isstandard='f' clause. In order to get the list of all users, either remove the _and isstandard='f'_ clause or change 'f' for false to 't' for true.

Note: Any field can be filtered to include only information that is desired. For example, To filter on Devices: To List the Phones with Device Names that begin with SEP, add where d.name like 'SEP%' at the end of the query, but just before 'order by au.name'. The format is where <column name> like '<value>%'.

List Application User with Device and Device Related information

run sql select au.name as ApplicationUser, d.name as Device, d.description as DeviceDescription, np.dnorpattern as DN, rp.name as partition, dp.name as DevicePool from applicationuser as au inner join applicationuserdevicemap as audm on audm.fkapplicationuser=au.pkid and isstandard ='f' inner join device as d on d.pkid=audm.fkdevice inner join devicenumplanmap as dnpm on dnpm.fkdevice = d.pkid inner join devicepool as dp on dp.pkid=d.fkdevicepool inner join numplan as np on dnpm.fknumplan = np.pkid inner join routepartition as rp on np.fkroutepartition=rp.pkid order by au.name

applicationuser device          devicedescription            dn   partition   devicepool 
=============== =============== ============================ ==== =========== ==========
Test1 SEP503DE57D7DAC 8501 8501 Internal_PT HQ
Test1 SEPA40CC3956C5C Line 1 - 1213; Line 2 - 1212 1213 Internal_PT HQ
Test1 SEPA40CC3956C5C Line 1 - 1213; Line 2 - 1212 1212 Internal_PT HQ
Test2 CTI1 CTI1 1945 Internal_PT HQ

This query will not list the Application User when the associated Device's Distinguised Name (DN) is in a partition. In order to include the ones in the None partition,
run sql select au.name as ApplicationUser, d.name as Device, d.description as DeviceDescription, np.dnorpattern as DN, dp.name as DevicePool from applicationuser as au inner join applicationuserdevicemap as audm on audm.fkapplicationuser=au.pkid and isstandard ='f' inner join device as d on d.pkid=audm.fkdevice inner join devicenumplanmap as dnpm on dnpm.fkdevice = d.pkid inner join devicepool as dp on dp.pkid=d.fkdevicepool inner join numplan as np on dnpm.fknumplan = np.pkid order by au.name.

This query lists only those application users that are created after the installation of the server. The default ones are not listed and these are removed by using the isstandard='f' clause. In order to get the list of all users, either remove the _and isstandard='f'_ clause or change 'f' for false to 't' for true.

Note: Any field can be filtered to include only information that is desired. For example, to filter on DN add where np.dnorpattern like '8%' at the end of the query, but just before 'order by au.name'. This lists all the entries which have the DN that begins with 8. The format is where <column name> like '<value>%'. In order to filter on Devices: To List the Phones with Device Names that begin with SEP, add where d.name like 'SEP%' at the end of the query, but just before 'order by au.name'.

List Application User with Permissions and Device Related Information

run sql select au.name as ApplicationUser, d.name as Device, d.description as DeviceDescription, np.dnorpattern as DN, rp.name as partition, dp.name as DevicePool, dg.name as PermissionGroup from applicationuser as au inner join applicationuserdevicemap as audm on audm.fkapplicationuser=au.pkid and isstandard ='f' inner join ApplicationUserDirGroupMap as audgm on audgm.fkapplicationuser=au.pkid inner join DirGroup as dg on audgm.fkdirgroup=dg.pkid inner join device as d on d.pkid=audm.fkdevice inner join devicenumplanmap as dnpm on dnpm.fkdevice = d.pkid inner join devicepool as dp on dp.pkid=d.fkdevicepool inner join numplan as np on dnpm.fknumplan = np.pkid inner join routepartition as rp on np.fkroutepartition=rp.pkid order by au.name

applicationuser device          devicedescription            dn   partition   devicepool permissiongroup                           
=============== =============== ============================ ==== =========== ========== =========================================
Test1 SEPA40CC3956C5C Line 1 - 1213; Line 2 - 1212 1213 Internal_PT HQ Standard CCM Admin Users
Test1 SEP503DE57D7DAC 8501 8501 Internal_PT HQ Standard CCM Admin Users
Test1 SEPA40CC3956C5C Line 1 - 1213; Line 2 - 1212 1212 Internal_PT HQ Standard CCM Admin Users
Test2 CTI1 CTI1 1945 Internal_PT HQ Standard CTI Allow Control of All Devices
Test2 CTI1 CTI1 1945 Internal_PT HQ Standard CTI Enabled

This query will not list the Application User when the associated Device's DN is in a partition. In order to include the ones in the None partition,
run sql select au.name as ApplicationUser, d.name as Device, d.description as DeviceDescription, np.dnorpattern as DN, dp.name as DevicePool, dg.name as PermissionGroup from applicationuser as au inner join applicationuserdevicemap as audm on audm.fkapplicationuser=au.pkid and isstandard ='f' inner join ApplicationUserDirGroupMap as audgm on audgm.fkapplicationuser=au.pkid inner join DirGroup as dg on audgm.fkdirgroup=dg.pkid inner join device as d on d.pkid=audm.fkdevice inner join devicenumplanmap as dnpm on dnpm.fkdevice = d.pkid inner join devicepool as dp on dp.pkid=d.fkdevicepool inner join numplan as np on dnpm.fknumplan = np.pkid order by au.name

This query lists only those application users that are created after the installation of the server. The default ones are not listed and these are removed by using the isstandard='f' clause. In order to get the list of all users, either remove the _and isstandard='f'_ clause or change 'f' for false to 't' for true.

Note: Any field can be filtered to include only information that is desired. For example, in order to filter on DN, add where np.dnorpattern like '8%' at the end of the query, but just before 'order by au.name'. This lists all entries which have the DN that begins with 8. The format is where <column name> like '<value>%'. In order to filter on Devices: To List the Phones with Device Names that begin with SEP, add where d.name like 'SEP% at the end of the query, but just before 'order by au.name'.

List the Permission Roles in the Permission Group

run sql select dg.name as PermissionGroup, fr.name as PermissionRole from DirGroup as dg inner join functionroledirgroupmap as frdgm on frdgm.fkdirgroup=dg.pkid inner join functionrole as fr on frdgm.fkfunctionrole=fr.pkid

permissiongroup                                                         permissionrole                                                          
======================================================================= =======================================================================
Standard CCM Super Users Standard CCMADMIN Administration
Standard CCM Super Users Standard SERVICEABILITY Administration
Standard CCM Super Users Standard CCM Admin Users
Standard CCM Super Users Standard Admin Rep Tool Admin
Standard CCM Super Users Standard AXL API Access
Standard CCM Super Users Standard EM Authentication Proxy Rights
Standard CCM Super Users Standard CUReporting
Standard CCM Admin Users Standard CCM Admin Users
Standard CCM Admin Users Standard CUReporting
Standard CCM Read Only Standard CCMADMIN Read Only
Standard CCM Read Only Standard CCM Admin Users
Standard CCM Read Only Standard SERVICEABILITY Read Only
Standard CCM Server Monitoring Standard CCMADMIN Read Only
Standard CCM Server Monitoring Standard SERVICEABILITY
Standard CCM Server Monitoring Standard CCM Admin Users
Standard CCM Server Maintenance Standard CCMADMIN Read Only
Standard CCM Server Maintenance Standard CCM System Management
Standard CCM Server Maintenance Standard CCM Feature Management
Standard CCM Server Maintenance Standard CCM Service Management
Standard CCM Server Maintenance Standard System Service Management
Standard CCM Server Maintenance Standard CCM Admin Users
Standard CCM Phone Administration Standard CCMADMIN Read Only
Standard CCM Phone Administration Standard CCM Phone Management
Standard CCM Phone Administration Standard CCM Admin Users
Standard CCM Gateway Administration Standard CCMADMIN Read Only
Standard CCM Gateway Administration Standard CCM Gateway Management
Standard CCM Gateway Administration Standard CCM Admin Users
Standard CTI Enabled Standard CTI Enabled
Standard CTI Allow Calling Number Modification Standard CTI Allow Calling Number Modification
Standard CTI Allow Call Park Monitoring Standard CTI Allow Call Park Monitoring
Standard CTI Allow Control of All Devices Standard CTI Allow Control of All Devices
Standard CAR Admin Users Standard Admin Rep Tool Admin
Standard EM Authentication Proxy Rights Standard EM Authentication Proxy Rights
Standard CCM End Users Standard CCM End Users
Standard CCM End Users Standard CCMUSER Administration
Standard Packet Sniffer Users Standard Packet Sniffing
Standard Packet Sniffer Users Standard CCM Admin Users
Standard CTI Secure Connection Standard CTI Secure Connection
Standard CTI Allow Reception of SRTP Key Material Standard CTI Allow Reception of SRTP Key Material
Standard TabSync User Standard AXL API Access
Standard CTI Allow Call Monitoring Standard CTI Allow Call Monitoring
Standard CTI Allow Call Recording Standard CTI Allow Call Recording
Standard RealtimeAndTraceCollection Standard RealtimeAndTraceCollection
Standard Audit Users Standard Audit Log Administration
Standard CTI Allow Control of Phones supporting Connected Xfer and conf Standard CTI Allow Control of Phones supporting Connected Xfer and conf
Standard CTI Allow Control of Phones supporting Rollover Mode Standard CTI Allow Control of Phones supporting Rollover Mode
AXL_User Standard AXL API Access
Updated: Jun 20, 2014
Document ID: 117780