SQL Query to pull device details with CSS from CUCM

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

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