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 Name | Alias |
| device | d |
| enduser | eu |
| routepartition | rp |
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
Please, help me make this document better. Please comment! Please rate. Thank you.
