In this blog I share two SQL queries. The first one to pull the device MAC address with Dn and the device type . Second one to pull the device Mac address with DN , active load and inactive load.

Tables used.

• Device – This table contains device-level information like IP Phones and gateways.
• NumPlan – This table contains information about all patterns configured in CUCM.
• DeviceNumPlanMap – This table contains the mapping between data in Device table and NumPlan table.
• TypeProduct – This table contains the information about the device type
• Defaults– This table contain all information about device defaults.

Sql Queries.

run sql select  device.name as DeviceMAC , device.description as enduser, numplan.dnorpattern as Extension, TypeProduct.name  as PhoneType from device ,numplan  , devicenumplanmap, TypeProduct where devicenumplanmap.fkdevice =device.pkid and devicenumplanmap.fknumplan =nUMPLAN.pkid and tkclass =1 and  typeproduct.tkmodel = device.tkmodel order by Numplan.dnorpattern 

run sql select  device.name as DeviceMAC, numplan.dnorpattern as Extension, run sql select  device.name as DeviceMAC , numplan.dnorpattern as Extension, TypeProduct.name  as PhoneType , defaults. loadinformation as phoneload , defaults.inactiveloadinformation as inactiveload from device ,numplan  , devicenumplanmap, TypeProduct , defaults  where devicenumplanmap.fkdevice =device.pkid and devicenumplanmap.fknumplan =nUMPLAN.pkid and tkclass =1 and  typeproduct.tkmodel = device.tkmodel  and defaults.tkmodel =device.tkmodel order by Numplan.dnorpattern