简介
本文档提供可在CLI中运行的结构化查询语言(SQL)查询，以便获取与应用用户相关的设备信息。
先决条件
要求
思科建议您了解思科统一通信管理器(CUCM)。
使用的组件
本文档中的信息基于CUCM 8.X版及更高版本，但可能适用于早期版本。
表
SQL查询使用以下表中的数据形成：
- 设备 — 此表包含设备级别信息，例如IP电话、网关等。
- NumPlan — 此表包含有关CUCM中配置的所有模式的信息。
- DeviceNumPlanMap — 此表包含Device表中的数据与NumPlan表中的数据之间的映射。
- RoutePartition — 此表包含有关CUCM中配置的所有分区的信息。
- ApplicationUser -此表包含有关应用程序用户的信息。
- ApplicationUserDeviceMap — 此表包含ApplicationUser表中的数据与Device表中的数据之间的映射。
- DirGroup — 此表包含有关权限组的信息。
- ApplicationUserDirGroupMap — 此表包含ApplicationUser表和DirGroup表中数据之间的映射。
有关这些表的详细信息，请参阅相应版本的CUCM数据字典。
SQL查询
为清楚起见，编写查询是为了一次查找一个关联。
列出应用用户和设备关联
运行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， 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
此查询仅列出安装服务器后创建的那些应用程序用户。默认未列出，使用isstandard='f'子句可删除这些默认项。要获取所有用户的列表，请删除_and isstandard='f'_clause，或将false的'f'更改为true的't'。
在本示例中，有两个应用程序用户：
- 测试1与两台电话关联
- 测试2与计算机电话集成(CTI)路由点关联
注意：任何字段都可以过滤为仅包含所需信息。例如，要过滤设备：要列出设备名称以SEP开头的电话，请在查询末尾添加其中d.name如“SEP%”，但紧接在“order by au.name”之前。 格式是其中<column name>类似于“<value>%”。
列出具有权限和设备关联的应用程序用户
运行sql，选择au.name作为ApplicationUser，d.name作为DeviceDescription，d.description作为DeviceDescription，dg.name作为PermissionGroup from applicationuser作为au内部加入应用userdevicemap作为audm on audm.fkapplicationuser=au.pkid和isstandard = 'f'内部加入应用UserDirGroupMap作为audgm.fkapplicationuser=au.pkid的内部加入作为audgm.fkid=audm.fkid设备顺序的设备
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
此查询仅列出安装服务器后创建的那些应用程序用户。默认未列出，使用isstandard='f'子句可删除这些默认值。要获取所有用户的列表，请删除_and isstandard='f'_clause，或将false的'f'更改为true的't'。
注意：任何字段都可以过滤为仅包含所需信息。例如，要过滤设备：要列出设备名称以SEP开头的电话，请在查询末尾添加其中d.name（如“SEP%”），但是在“order by au.name”（按au.name排序）之前。 格式是其中<column name>类似于“<value>%”。
列出应用用户及设备和设备相关信息
运行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， isstandard ='f' inner join device as d on d.pkid=audm.fkdevice inner join devicenumplanmap as dnpm on dnpm.fkdevice = d.pkid join deviceinner numpool 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
当关联设备的区分名称(DN)位于分区中时，此查询不会列出应用用户。为了在None分区中包含这些分区，
运行sql select au.name作为ApplicationUser，d.name作为DeviceDescription，d.description作为DeviceDescription，np.dnorpattern作为DN，dp.name作为DevicePool from applicationuser as au inner join applicationuserdevicemap as audm on audm.fkapplicationuser=au.pkid，isstandard ='f' inner join device as d on d.pkid=audm.fkdevice inner join devicenumplanmap as dnpm on dnpm.fkdevice = d.fkid-pool inner join npm.fknumplan = np.pkid order by au.name。
此查询仅列出安装服务器后创建的那些应用程序用户。默认未列出，使用isstandard='f' 子句可删除这些默认值。要获取所有用户的列表，请删除_and isstandard='f'_子句，或将false的'f'更改为true的't'。
注意：任何字段都可以过滤为仅包含所需信息。例如，要按DN进行过滤，请在查询末尾添加np.dnorpattern（如“8%”），但紧接在“order by au.name”之前。 该表列出了DN以8开头的所有条目。格式为其中<column name>类似于“<value>%”。 要根据设备过滤：要列出设备名称以SEP开头的电话，请在查询末尾添加其中d.name（如“SEP%”），但紧接在“order by au.name”之前。
列出具有权限和设备相关信息的应用用户
运行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， isstandard ='f' inner join ApplicationUserDirGroupMap as audgm.fkapplicationuser=au.pkdirgroup=audgm kid 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 routpartition 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
当关联设备的DN位于分区中时，此查询不会列出应用用户。为了在None分区中包含这些分区，
运行sql select au.name(ApplicationUser)、d.name(Device)、d.description(DeviceDescription)、np.dnorpattern(DN)、dp.name(DevicePool)、dg.name(PermissionGroup)from applicationuser(au inner join application)userdevicemap(audm.fkapplicationuser=au.pkid，isstandard = 'f' inner join ApplicationUserDirGroupMap(audgm.fkapplicationuser=au.pkid inner join d device)上的dginner join dirGroup在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
此查询仅列出安装服务器后创建的那些应用程序用户。默认未列出，使用isstandard='f' 子句可删除这些默认值。要获取所有用户的列表，请删除_and isstandard='f'_clause，或将false的'f'更改为true的't'。
注意：任何字段都可以过滤为仅包含所需信息。例如，要根据DN进行过滤，请在查询末尾添加其中np.dnorpattern(如“8%”)，但紧接在“order by au.name”之前。 这将列出DN以8开头的所有条目。格式为其中<column name>类似于“<value>%”。 要根据设备过滤：要列出设备名称以SEP开头的电话，请在查询末尾添加其中d.name如“SEP%”，但紧接在“order by au.name”之前。
列出权限组中的权限角色
在frdgm.fkdirgroup=dg.pkid inner join functionrole=fr.pkid上运行sql select dg.name作为PermissionGroup，fr.name作为PermissionRole from DirGroup作为dg内部联接函数roledirgroupmap作为frdgm.fkdirgroup=dg.pkid内部联接函数role 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
反馈