Sql
CUCM 嵌套 SQL 查詢連結設備池和許可
思科的許可管理器有一個缺點，它提供的是每個企業而不是每個集群的許可數據。我正在嘗試利用 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我用來確定正確表格的指南是我的呼叫管理器版本的數據字典
我花了一些時間在桌子上探勘（我也在城外待了幾個星期），但這就是我最終這樣做的方式。
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。