The beauty of sql query is that you can pull custom information from CUCM. Which is useful when preparing document and sharing details with your customer.

In this blog I share Three SQL queries. The first one to pull the device MAC address with Dn in the order of DN. Second one to pull the device Mac address with DN and the CSS configured on this DN. Third One to pull the device Mac address, device description, dn and CSS.

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.
• Callingsearchspace – This table contains the information about all of the CSS configured in CUCM

Sql Queries.

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

run sql select  device.name as DeviceMAC, numplan.dnorpattern as Extension, callingsearchspace.name as CSS from device ,numplan , devicenumplanmap , callingsearchspace where devicenumplanmap.fkdevice =device.pkid and devicenumplanmap.fknumplan =nUMPLAN.pkid and numplan.fkcallingsearchspace_sharedlineappear= callingsearchspace.pkid and tkclass =1  order by callingsearchspace.name

run sql select  device.name as DeviceMAC, device.description as enduser ,numplan.dnorpattern as Extension, callingsearchspace.name as CSS from device ,numplan , devicenumplanmap , callingsearchspace where devicenumplanmap.fkdevice =device.pkid and devicenumplanmap.fknumplan =nUMPLAN.pkid and numplan.fkcallingsearchspace_sharedlineappear= callingsearchspace.pkid and tkclass =1  order by callingsearchspace.name