简介
本文档提供可以在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 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
此查询仅列出安装服务器后创建的那些应用程序用户。未列出默认路由，使用isstandard='f'子句可删除这些路由。要获取所有用户的列表，请删除_and isstandard='f'_子句，或者将false的“f”更改为true的“t”。
在本示例中，有两个应用用户：
- 测试1与两部电话关联
- 测试2与计算机电话集成(CTI)路由点关联
注意：可以过滤任何字段，使其仅包含所需信息。例如，要按设备过滤：要列出设备名称以SEP开头的电话，请在查询末尾添加d.name(如“SEP%”)，但紧接在“order by au.name”之前。格式是其中<column name>类似于“<value>%”。
列出具有权限和设备关联的应用程序用户
运行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， isstandard = 'f' inner join ApplicationUserDirGroupMap as audgm.fkapplicationuser=au.pkid inner join DirGroup as dg on audgm.fkid=dg.pkid inner join device 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
此查询仅列出安装服务器后创建的那些应用程序用户。默认规则不会列出，使用isstandard='f'子句可以删除这些规则。要获取所有用户的列表，请删除_and isstandard='f'_子句，或者将false的“f”更改为true的“t”。
注意：可以过滤任何字段，使其仅包含所需信息。例如，要按设备过滤：要列出设备名称以SEP开头的电话，请在查询末尾添加d.name如“SEP%”，但紧接在“order by 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.fkid inner join devicenumplanmap as dnpm on dnpm.fkdevice = d.pkid inner join devicepool as dp on dp作为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 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在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'_ 子句或将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.pkinner join DirGroup=dg 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 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
当关联设备的DN位于分区中时，此查询不会列出应用用户。为了在None分区中包含这些分区，
运行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， isstandard = 'f' inner join ApplicationUserDirGroupMap as audgm.fkapplicationuser=au.pinner join dirGroup as dg on audgm.fkid join device在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'_子句，或者将false的“f”更改为true的“t”。
注意：可以过滤任何字段，使其仅包含所需信息。例如，要根据DN进行过滤，请在查询末尾添加其中np.dnorpattern like '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 as fr on frdgm.fkdirgroup=fr.pkid inner join functionrole as fr on frdgm运行sql select dg.name as PermissionGroup，fr.name as PermissionRole from DirGroup as dg inner join functionrole=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
反馈