SQL query for extension able to make off net calls

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