Is it possible to create a query that can retrieve only the extensions which are able to make PSTN calls?
I am currently querying device and numplan to get all devices whose device.name is like SEP%. However this will only retrieve IP phones.
I need to create a query than can retrieve all other types regardless of what their device name is like. I plan to run this on CUCM 10.5 and 11.0
One of the prerequisites for this kind of report is fully defining what is meant by an 'offnet/PSTN' call. For example, the designation of offnet vs. onet can be designated by the administrator when creating a Route Pattern, but Calling Search Spaces, partitions and other dial plan configurations associated with the phone device and/or individual lines on the device can further control which particular destinations to user can dial.
Once you are able to gather from the user how they want to define 'offnet' calls, then you can zero in on how to create queries that return the devices that meet the criteria. In sort of the simplest scenario - if you can say that certain Calling Search Spaces are equivalent to 'offnet' - then you can query numplan:fkcallingsearchspace_sharedlineappear for lines that have a particular CSS, and device:fkcallingsearchspace for devices that are assigned a particular CSS. Even this may take a couple of queries to get to:
select pkid from callingsearchspace where name='A CSS which allows offnet calls'
This gives the unique key of the CSS you are looking for
select name from device where fkcallingsearchspace = [pkid from above]
This gives the device name of all devices assigned the CSS
select device.name from device, devicenumplanmap, numplan where devicenumplanmap.fkdevice=device.pkid and devicenumplanmap.fknumplan=numplan.pkid and fkcallingsearchspace_sharedlineappear = [pkid from step 1]
This gives a list of all devices that have a line appearance which allows calls to the CSS
Thanks David for such a comprehensive answer. Your solution is pretty much what I needed.
However, after giving this much thought I'd rather switching to selecting all DN's that are capable of dialing a number regardless of this being a PSTN call or not. Correct me if I am wrong but I think filtering the devices based on their typeclass should achieve that. This should return DNs of softphones, hardphones, jabber clients, ATA's etc, correct.
Any updates on this last point?
That will probably get you pretty close, they it may get a little tricky identifying which device classes can 'dial'. FYI a dump of the typeclass table from CUCM 10.5:
select * from typeclass
enum name moniker
==== =================================== =========================================
1 Phone CLASS_PHONE
2 Gateway CLASS_GATEWAY
4 Conference Bridge CLASS_CONF_BRIDGE
5 Media Termination Point CLASS_MTP
7 Route List CLASS_ROUTE_LIST
8 Voice Mail CLASS_VOICE_MAIL
10 CTI Route Point CLASS_CTI_ROUTE_POINT
12 Music On Hold CLASS_MUSIC_ON_HOLD
13 Simulation CLASS_SIMULATION
14 Pilot CLASS_PILOT
15 GateKeeper CLASS_GATEKEEPER
16 Add-on modules CLASS_ADDON_MODULES
17 Hidden Phone CLASS_HIDDEN_PHONE
18 Trunk CLASS_TRUNK
19 Tone Announcement Player CLASS_ANN
20 Remote Destination Profile CLASS_REMOTE_DESTINATION_PROFILE
248 EMCC Base Phone Template CLASS_EMCC_TEMPLATE
249 EMCC Base Phone CLASS_EMCC
250 Remote Destination Profile Template CLASS_REMOTE_DESTINATION_PROFILE_TEMPLATE
251 Gateway Template CLASS_GATEWAY_TEMPLATE
252 UDP Template CLASS_UDP_TEMPLATE
253 Phone Template CLASS_PHONE_TEMPLATE
254 Device Profile CLASS_DEVICE_PROFILE
255 Invalid CLASS_INVALID