SQL Query to pull devices not assigned with DN’s

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s