Home / server / Questions / 602444
Accepted
driz
Asked: 2014-06-07 10:57:42 +0800 CST

CUCM Nested SQL query to link devicepool and licensing

  • 772

Cisco's licensing manager has a shortcoming in that it provides licensing data per enterprise instead of per cluster. I am attempting to utilize SQL queries to produce this data. Currently, i have two queries that provide some relevant information.

SELECT name,value FROM TABLE (FUNCTION LicenseTotals()) (pkid,name,value,UserValue,DeviceValue)

which produces this output

    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

and 

select typeproduct.name, typelicensedresource.name from typeproduct, typelicensedresource, typelicensedresourceproductmap where typelicensedresource.enum = typelicensedresourceproductmap.tklicensedresource and typelicensedresourceproductmap.tkproduct = typeproduct.enum

which produces this output

name                                     name
======================================== ============
Analog Phone                             Tin
Cisco 6901                               Tin
Cisco 6911                               Copper
Cisco 6921                               Copper
H.323 Client                             Bronze
Cisco 30 SP+                             Bronze

what i would like to do is somehow pull information from the devicepool tables but the unique id of licensing doesn't match up with the unique IDs of devicepool. my end goal is something like

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

some previous code that can get counts per site is

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

which returns this result

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

The guide i have been using to determine proper tables is the data dictionary for my version of call manager

sql

1 Answers

  1. Best Answer

    It took a while digging through the tables (i was also out of town for a couple weeks) but here is how i ended up doing this.

    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

    This returns these results

    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

    I have some powershell code that will finish tidying this to translate a devicepool into a full site name and combine things like AGW and PHSRST together for 1 total count per license type.

    • 0