SQL Query to Pull device model and Firmware load from CUCM

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

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