CUCM database query for add-on module information

[Martin Sloan]

Greetings,

I'm trying to collect some information from the CUCM database via SQL query to establish 1) What is the relationship between add on modules and the devices that support them (how to know that a particular phone model only supports certain add on modules) and 2) What is the maximum supported number of add on modules for a particular device.

In the case of the first item, while I'm still looking for this info, I haven't come across this mapping yet but thought someone might know.  My question here is prompted more by the second item (max number of modules), which in the database dictionary says that "The number of Add-on Modules supported by a device (phone) is indicated in the MaxAddOnModules column of the TypeProduct table".  However when I query for the MaxAddOnModules field in the typeproduct table I get a response that it wasn't found, and I also don't see it listed under the typeproduct table in the data dictionary.  I've run tests on UCM v11.5 and 12.5.

Anyone have experience working with these relationships?  Any help is appreciated.

Thanks,

Marty

[dstaudt · Accepted solution]

I think the queries below should cover what you're looking for:

A) Supported Expansion Module feature types and max KEMs for a given phone model (e.g. Cisco 8861=685):

SELECT typesupportsfeature.enum, productsupportsfeature.param
FROM productsupportsfeature, typesupportsfeature
WHERE productsupportsfeature.tksupportsfeature=typesupportsfeature.enum
AND typesupportsfeature.enum IN (SELECT enum FROM typesupportsfeature WHERE name LIKE "%Expansion%")
AND productsupportsfeature.tkmodel=685

Results:

enum name                                         param 
==== ============================================ ===== 
148  BEKEM 36-Button Line Expansion Module        3     
171  CP-8800-Audio 28-Button Key Expansion Module 3  

B) Expansion Module models corresponding to the results from A):

SELECT typemodel.enum, typemodel.name
FROM typemodel, productsupportsfeature
WHERE productsupportsfeature.tkmodel=typemodel.enum
AND productsupportsfeature.tksupportsfeature=86
AND productsupportsfeature.param IN (148, 171)

Results:

enum name
===== ============================================
36049 BEKEM 36-Button Line Expansion Module
36257 CP-8800-Audio 28-Button Key Expansion Module

C) Expansion modules configured for a specific device (e.g. SEP886100000000):

SELECT deviceaddonmodulemap.moduleindex, deviceaddonmodulemap.tkmodel, typemodel.name
FROM deviceaddonmodulemap, typemodel, device
WHERE deviceaddonmodulemap.fkdevice=device.pkid
AND deviceaddonmodulemap.tkmodel=typemodel.enum
AND device.name="SEP886100000000"

Results:

moduleindex tkmodel name                                         
=========== ======= ============================================ 
1           36257   CP-8800-Audio 28-Button Key Expansion Module 
2           36257   CP-8800-Audio 28-Button Key Expansion Module 

The tricky part was mapping tksupportsfeature to a KEM model type.  It appears that records in productsupportsfeature with tksupportsfeature=86 are mapping records, where the productsupportsfeature.param field is the typesupportsfeature record and tkmodel is the KEM model (see query B) ).

Hope this helps!

[Martin Sloan]

Wow!  David, you are *the man*!  Thank you very much for this information.  I haven't dabbled in the *supportsfeature tables but they are chock-full of goodies.  This definitely covers what I needed, and more.  Once again, thank you.

[Martin Sloan]

Hi David,

One follow up if you happen to get a chance....  I massaged the first two queries into one and it looks like I'm getting everything back but I noticed that some of the "max supported modules" records from the productsupportsfeature.param field are empty.  For instance, the 7975 model lists maximum 2 for the 7914 EM, but is empty for 7915 and 7916.  Any thoughts on why some wouldn't have a value?

SELECT psf.param max, tm.name model, tp.name protocol, tm2.name module
FROM typesupportsfeature tsf 
JOIN productsupportsfeature psf ON psf.tksupportsfeature = tsf.enum
JOIN typemodel tm ON tm.enum = psf.tkmodel
JOIN typedeviceprotocol tp ON tp.enum = psf.tkdeviceprotocol
JOIN productsupportsfeature psf2 ON psf2.param IN (tsf.enum)
JOIN typemodel tm2 ON tm2.enum = psf2.tkmodel
WHERE tsf.name LIKE "%Expansion%"
AND psf2.tksupportsfeature = 86

Thanks,

Marty

[dstaudt]

I think the key is that the productsupportsfeature.tksupportsfeature=42 rows means 'supports expansion modules - the param is how many'.  It appears phone models support the same max # of expansion modules regardless of the specific expansion module model.

So, have to include/exclude the tksupportsfeature=42 rows in various places to get the specific type of expansion module feature values desired.

Also simplified some of the queries I think.  Note D) from above should be unchanged.

A) Get the max number of expansion modules per protocol by phone model

• Some phone models support both SCCP and SIP
• tksupportsfeature=42 (if present for the phone model+protocol in question) holds the max expansion module value in param (applies to all expansion module types)

SELECT param, tkdeviceprotocol FROM productsupportsfeature WHERE tksupportsfeature=42 AND tkmodel=437

param tkdeviceprotocol 
===== ================ 
2     0                
2     11 

deviceprotocol: 0=SCCP, 11=SIP

B) Get the expansion module supported features per protocol by phone model

• Exclude the "max modules" feature 42 mapped onto param
• Products with tksupportsfeature=86 are themselves expansion modules

SELECT tksupportsfeature, tkdeviceprotocol FROM productsupportsfeature
WHERE tkmodel=437 AND tksupportsfeature IN 
(SELECT param FROM productsupportsfeature WHERE tksupportsfeature=86 AND NOT param=42)

tksupportsfeature tkdeviceprotocol 
================= ================ 
82                0                
83                0                
84                0                
85                0                
82                11               
84                11               
83                11               
85                11  

C) Get the expansion module models/descriptions based on #2 results above

SELECT enum, name FROM typesupportsfeature WHERE enum IN (82, 83, 84, 85)

enum name
==== ====================================
82 7915 12-Button Line Expansion Module
83 7915 24-Button Line Expansion Module
84 7916 12-Button Line Expansion Module
85 7916 24-Button Line Expansion Module

[Martin Sloan]

Thanks again, David!  I ran some tests using tksupportsfeature=42 and it seems like that mapping only applies to 79xx models, since I only got results for those models when I scoped to that feature.  I was initially trying to be clever and get everything in one query, but I think it's prob best to send two (not accounting for the third to get the phones that have modules assigned).  Here's what I came up with, which seems to provide the max modules supported per model and also the assignable modules per phone model.  I reviewed all the output from both of these and the protocol doesn't seem to impact either query, so I removed it.  It's an assumption, but seems pretty safe.

Max Supported Expansion Modules Per Phone Model (exclude template devices):

SELECT DISTINCT tm.name model, psf.param max
FROM typesupportsfeature tsf 
JOIN productsupportsfeature psf ON psf.tksupportsfeature = tsf.enum
JOIN typemodel tm ON tm.enum = psf.tkmodel
JOIN typedeviceprotocol tp ON tp.enum = psf.tkdeviceprotocol
JOIN productsupportsfeature psf2 ON psf2.param IN (tsf.enum)
JOIN typemodel tm2 ON tm2.enum = psf2.tkmodel
WHERE tsf.name LIKE "%Expansion%"
AND psf2.tksupportsfeature = 86
AND psf.param != ""
AND tm.name LIKE "Cisco%"

Phone Model Supports Expansion Modules (exclude template devices):

SELECT DISTINCT tm2.name module, tm.name model
FROM typesupportsfeature tsf 
JOIN productsupportsfeature psf ON psf.tksupportsfeature = tsf.enum
JOIN typemodel tm ON tm.enum = psf.tkmodel
JOIN typedeviceprotocol tp ON tp.enum = psf.tkdeviceprotocol
JOIN productsupportsfeature psf2 ON psf2.param IN (tsf.enum)
JOIN typemodel tm2 ON tm2.enum = psf2.tkmodel
WHERE tsf.name LIKE "%Expansion%"
AND psf2.tksupportsfeature = 86
AND tm.name LIKE "Cisco%"