Cisco Unified Communications Manager (CallManager)

Cisco CallManager: Using SQL Query Analyzer to Find Devices Associated with a Location

Document ID: 15266

Updated: Jun 15, 2005



This document explains how to run an SQL Query to find the devices that are associated with a location up until Cisco CallManager version 3.3. Devices are endpoints, such as IP phones and gateways. As of Cisco CallManager version 3.3, there is a new feature called Dependency Records.



Readers of this document should have knowledge of Cisco CallManager Administration.

Components Used

The information in this document is based on Cisco CallManager versions 3.x and 4.x.

The information in this document was created from the devices in a specific lab environment. All of the devices used in this document started with a cleared (default) configuration. If your network is live, make sure that you understand the potential impact of any command.


For more information on document conventions, refer to the Cisco Technical Tips Conventions.

Finding a Device Associated with a Location

These sections explain how to find a device that is associated with a location. Use SQL Query in Cisco CallManager versions earlier than 3.3, and use Dependency Records in Cisco CallManager 3.3 and later.

SQL Query

Follow the steps in this section to use SQL Query to find devices that are associated with a location.

Note: If a value of None appears in the Location field in the Cisco CallManager Configuration window, then the device has not been assigned to a specific location. Such devices that are not assigned to a location are not returned by this query.

  1. Select Start > Programs > Microsoft SQL Server > Query Analyzer on the Windows 2000 server console of the Cisco CallManager server, to run the SQL Server Query Analyzer application.

    The Connect to SQL Server window appears. (The SQL Query Analyzer window is dimmed in the background.)


  2. In the SQL Server field, type a period (.).

  3. Uncheck the Start SQL Server if it is Stopped check box.

  4. Click the Windows Authentication radio button.

  5. Click OK.

    The Query - (local) window appears. (The SQL Query Analyzer window is dimmed in the background.)

  6. In the DB field, click the drop-down arrow and choose the highest numbered Cisco CallManager database.

    Cisco CallManager databases are labeled with the format CCM03xx (where xx is the number of the database).

  7. Enter this SQL query in the body of the Query - (local) window:

    SELECT, Device.description
    FROM Device, Location
    WHERE Device.fkLocation=Location.pkid
    AND"enter the location name between these quotes"
  8. In the main Query Analyzer window, select Query > Execute to run the query.

    You can also click the green arrow on the toolbar or press F5, to run the query.

  9. When the results are done, close the SQL Server Query Analyzer window.

Dependency Records

In Cisco CallManager 3.3 and later, there is a new feature called Dependency Records. This feature allows you to see who is associated with a Location without the need to run a query.

  1. In the Cisco CallManager Administration window Location Configuration page, click Dependency Records.


    This window appears, which tells you that there is 1 phone and 1 directory number associated with it.


  2. If you click the Phone Record Type, you will see that there is currently an Analog Telephone Adaptor (ATA) that is configured with that location.


Related Information

Updated: Jun 15, 2005
Document ID: 15266