cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
835
Views
5
Helpful
0
Comments
Tim Glen
Cisco Employee
Cisco Employee

Please see the first shallow dive for a primer on the DB tables, columns & terms.  You can find that document here. As both of these documents only look at a few tables and present a few queries we are still playing in the kiddie pool, we aren't on the high dive yet. 

 

Summary

There will occasions where when the SQL database needs to be queried for a variety of purposes.  Cisco Call Manager 8.x uses an IBM Informix Database for storing data.

Examples

1. Using Aliases in SQL Statements

There will be times that you query fields from multiple tables. In order to do this, you need to use table.fieldname in the query so CM knows what field in which table you are querying. You can use the full table name or you can use an alias.  I will show examples of both. 

Note: The alias is one that the SQL Programmer (you) assigns and therefore can be anything.

Table NameAlias
deviced
endusereu
routepartitionrp

 

Here are some simple queries. 

Example 1

With Alias

run sql select d.name from device as d

Without Alias

run sql select device.name from device

Example 2

With Alias

run sql select eu.userid from enduser as eu

Without Alias

run sql select enduser.userid from enduser

Example 3

With Alias

run sql select rp.name,rp.description from routepartition as rp

Without Alias

run sql select routepartition.name,routepartition.description from routepartition

d.name  = device table and name field.  

eu.userid = enduser table and userid field.

rp.name = routepartition table and name field

rp.description = routepartition table and description field. 

I know the above queries are fairly simple. Please understand that they are only to show the most simple example of how to use an alias. We will get more complicated in the next few examples.   

2. Simple Query of Two Tables Using Alias

In this query, we query both the enduer and device tables and we use aliases. 

We are assuming that the userid and the phone name is known in this query.

 

run sql select eu.firstname,eu.lastname,d.description from enduser as eu, device as d where eu.userid='tglen' and d.name='SEP3CCE73AC397F'

3. Using INNER JOIN to Display Data from Multiple Tables

The INNER JOIN keyword selects records that have matching values in both tables.

Here is where the fun starts. Basic INNER JOIN syntax is below

SELECT column_name(s)
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;

Let's go hunting.... In this example, we know the name of a device (phone) and we want to find out the Calling Search Space that the phone is in.

First, let's find the phone in the device table.  

run sql select d.name,d.description,fkcallingsearchspace from device as d where d.name='SEP3CCE73AC397F'

 

So we see that the fkcallingsearchspace is a GUID and we know that just means it's a link to the pkid in the callingsearchspace table.  So let's query that. 

run sql select css.name from callingsearchspace as css where pkid='06616d1e-ea25-6374-081d-7cd57ba4a755'

 

Now we can join the two queries together using INNER JOIN.  One query with one line of output. 

run sql select d.name,d.description,css.name from device as d inner join callingsearchspace as css on d.fkcallingsearchspace = css.pkid where d.name='SEP3CCE73AC397F'

 

This query can be used to find all Devices and their configured Calling Search Space. 

run sql select d.name,d.description,css.name from device as d inner join callingsearchspace as css on d.fkcallingsearchspace = css.pkid

4. List the Number (count) of XX Model Phone

First, we need to determine the model that Cisco uses.  This is in the typeproduct table, we know that table id is 453.

Let's query the typeproduct table for the 7945 model phone. 

Since we don't know exactly what the name is we use the variable % signs.  In SQL % is a wildcard.

run sql select enum,name,tkmodel,devicenameformat from typeproduct where name like '%7945%'

tkmodel is a unique number that identifies the 7954.  Each model phone will have its own unique tkmodel. 

Now that we know the unique ID, let's go back to the device table and count the number of 7945's. 

run sql select count(tkmodel) from device where tkmodel='435'

You could also search for other model devices or terms.

run sql select enum,name,tkmodel,devicenameformat from typeproduct where name like '%6921%'
run sql select enum,name,tkmodel,devicenameformat from typeproduct where name like '%VG%'

 

5. List all Known Communication Manager Servers

run sql select name,description from callmanager

 

 

 

References

W3 Schools SQL Tutorial

 

Please, help me make this document better.  Please comment! Please rate.  Thank you.

Getting Started

Find answers to your questions by entering keywords or phrases in the Search bar above. New here? Use these resources to familiarize yourself with the community:

Recognize Your Peers
Quick Links