主页 / server / 问题 / 602444
Accepted
driz
Asked: 2014-06-07 10:57:42 +0800 CST

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

  • 772

思科的许可管理器有一个缺点，它提供的是每个企业而不是每个集群的许可数据。我正在尝试利用 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

我用来确定正确表格的指南是我的呼叫管理器版本的数据字典

sql

1 个回答

  1. Best Answer

    我花了一些时间在桌子上挖掘（我也在城外待了几个星期），但这就是我最终这样做的方式。

    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。

    • 0

相关问题