The below SQL Query’s can be used to pull the devices which are not assigned with a DN

Tables used. 

• Device – This table contains device-level information like IP Phones and gateways.
• DeviceNumPlanMap – This table contains the mapping between data in Device table and NumPlan table.

If no DN’s are assigned, Device PKID wont be available on Devinumplanmap table. So i use Left Join option and list the devices not in the Devicenumplanmap Table

Sql Query.

Query to Pull phones without DN’s

run sql select device.name as Phone , devicenumplanmap.fkdevice as DN from device LEFT JOIN devicenumplanmap on device.pkid = devicenumplanmap.fkdevice where devicenumplanmap.fkdevice IS NULL and  tkclass =1

Note:- tkclass =1 is for phones

Query to Pull device profile without DN’s

run sql select device.name as Phone , devicenumplanmap.fkdevice as DN from device LEFT JOIN devicenumplanmap on device.pkid = devicenumplanmap.fkdevice where devicenumplanmap.fkdevice IS NULL and  tkclass =254

Note:- tkclass =254 is for device profiles