02-24-2022 06:25 AM
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
Solved! Go to Solution.
02-24-2022 03:57 PM - edited 02-24-2022 04:04 PM
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!
02-24-2022 03:57 PM - edited 02-24-2022 04:04 PM
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!
02-24-2022 06:05 PM
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.
03-01-2022 12:01 PM
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
03-01-2022 03:36 PM
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
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
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
03-02-2022 08:01 AM
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%"
Discover and save your favorite ideas. Come back to expert answers, step-by-step guides, recent topics, and more.
New here? Get started with these tips. How to use Community New member guide