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

Question

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

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

Answer 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次。