SQL Query to list the Enduser, Controlled Devices,Extension Number & the device Type

The below SQL Query can be used to pull the details of Enduser-ID, controlled devices , their DN and the device Type.

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
  • Enduser – This table contains information about all Enduser in CUCM.
  • Enduserdevicemap – This table contains the mapping between data in Enduser table and Device table.

Sql Query.

run sql select device.name, enduser.userid ,numplan.dnorpattern , TypeProduct.name as PhoneType from device , numplan , devicenumplanmap, enduser,devicenumplanmapendusermap, enduserdevicemap , TypeProduct where enduserdevicemap.fkdevice = device.pkid and enduserdevicemap.fkenduser = enduser.pkid and devicenumplanmap.fkdevice = device.pkid and devicenumplanmap.fknumplan = numplan.pkid and devicenumplanmapendusermap.fkenduser = enduser.pkid and typeproduct.tkmodel = device.tkmodel order by enduser.userid

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