![CUCM 嵌套 SQL 查询用于链接设备池和许可](https://linux22.com/image/634470/CUCM%20%E5%B5%8C%E5%A5%97%20SQL%20%E6%9F%A5%E8%AF%A2%E7%94%A8%E4%BA%8E%E9%93%BE%E6%8E%A5%E8%AE%BE%E5%A4%87%E6%B1%A0%E5%92%8C%E8%AE%B8%E5%8F%AF.png)
Cisco 的许可管理器有一个缺点,即它按企业而不是按集群提供许可数据。我正尝试利用 SQL 查询来生成此数据。目前,我有两个查询提供了一些相关信息。
SELECT name,value FROM TABLE (FUNCTION LicenseTotals()) (pkid,name,value,UserValue,DeviceValue)
产生这个输出
name value
================= ===================
CUWL Standard 0
EnhancedPlus 0
Enhanced 2953
Basic 0
Essential 1349
TelePresence Room 0
TotalUsers 0
TotalDevices 4302
Timestamp 2014-06-06 11:45:21
ElmLastContact 1402044739
Elm XXXXXXXXXELM1
和
select typeproduct.name, typelicensedresource.name from typeproduct, typelicensedresource, typelicensedresourceproductmap where typelicensedresource.enum = typelicensedresourceproductmap.tklicensedresource and typelicensedresourceproductmap.tkproduct = typeproduct.enum
产生这个输出
name name
======================================== ============
Analog Phone Tin
Cisco 6901 Tin
Cisco 6911 Copper
Cisco 6921 Copper
H.323 Client Bronze
Cisco 30 SP+ Bronze
我想做的是以某种方式从设备池表中提取信息,但许可的唯一 ID 与设备池的唯一 ID 不匹配。我的最终目标是
Count Licensetype Site
========================================================
50 Tin Site 1
30 Tin Site 2
75 Copper Site 1
100 Copper Site 2
80 Bronze Site 3
110 Bronze Site 3
之前可以获取每个站点计数的一些代码是
run sql SELECT sum(analog_phones) as analog_phones, sum(ip_phones) as ip_phones, devicepool FROM (SELECT 0 AS analog_phones, count(d.name) AS IP_Phones, dp.name AS DevicePool FROM Device AS d INNER JOIN DevicePool AS dp ON d.fkDevicePool=dp.pkid INNER JOIN typemodel AS tm ON tm.enum=d.tkmodel WHERE (d.tkclass=1 AND dp.name LIKE '%PH%') GROUP BY dp.name UNION ALL SELECT count(d.name) AS Analog_Phones, 0 AS ip_phones, dp.name AS DevicePool FROM Device AS d INNER JOIN DevicePool AS dp ON d.fkDevicePool=dp.pkid INNER JOIN typemodel AS tm ON tm.enum=d.tkmodel WHERE (tm.name = 'Analog Phone') GROUP BY dp.name) a GROUP BY devicepool ORDER BY devicepool
返回此结果
analog_phones ip_phones devicepool
============= ========= ===============
12 0 BRLED-AGW-DP
0 36 BRLED-PHONES-DP
0 46 CRMBT-PHONES-DP
532 0 DRMC-AGW-DP
1 695 DRMC-PHONES-DP
我一直用来决定合适表格的指南是我的呼叫管理器版本的数据字典
答案1
花了一段时间才找到这些表格(我当时也出城了几个星期),但最终我还是这样做了。
run sql select count(typeproduct.name) as Count,
typelicensedresource.name as LicenseType,
dp.name AS Site from typeproduct,
typelicensedresource, typelicensedresourceproductmap, devicepool as dp,
device as d where typelicensedresource.enum = typelicensedresourceproductmap.tklicensedresource and typelicensedresourceproductmap.tkproduct = typeproduct.enum and
typeproduct.tkmodel = d.tkmodel and d.fkDevicePool=dp.pkid AND
(dp.name LIKE '%PH%' OR dp.name LIKE '%AGW%' OR dp.name LIKE '%FGW%') AND
typeproduct.name <> 'CTI Port' group by dp.name, typelicensedresource.name
ORDER by typelicensedresource.name
这将返回这些结果
count licensetype site
===== =========== ==============
793 Bronze NWMC-PHONES-DP
44 Bronze CORL-PHSRST-DP
98 Bronze NWMC-PHSRST-DP
119 Bronze UHMC-PHSRST-DP
4 Bronze CORL-PHONES-DP
2 Bronze UHMC-FGW-DP
1 Bronze ALSC-PHONES-DP
27 Bronze ALSC-PHSRST-DP
598 Bronze UHMC-PHONES-DP
20 Tin NWMC-PHSRST-DP
353 Tin NWMC-AGW-DP
409 Tin UHMC-AGW-DP
9 Tin NWMC-PHONES-DP
1 Tin WRMC-PHONES-DP
28 Tin UHMC-PHONES-DP
我有一些 powershell 代码,可以完成整理工作,将设备池转换为完整的站点名称,并将 AGW 和 PHSRST 等内容组合在一起,每个许可证类型总共 1 个计数。