☰

CUCM 嵌套 SQL 查询用于链接设备池和许可

tag-icon • tag-icon sql informix unified-communications
CUCM 嵌套 SQL 查询用于链接设备池和许可

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 个计数。

相关内容