In the Last blog we learned about select method and some basic SQl queries on CUCM. Today will learn Join and Where Clause.

JOIN

JOIN is a query clause that can be used with the SELECT, UPDATE, and DELETE
data query statements to simultaneously affect rows from multiple tables. There
are several distinct types of JOIN statements that return different data result sets.
Joined tables must each include at least one field in both tables that contain
comparable data. For example, if you want to join a Customer table and a
Transaction table, they both must contain a common element, such as
CustomerID column, to serve as a key on which the data can be matched. Tables
can be joined on multiple columns so long as the columns have the potential to
supply matching information. Column names across tables don’t have to be the
same, although for readability this standard is generally preferred.
When you do use like column names in multiple tables, you must use fully
qualified column names. This is a “dot” notation that combines the names of
tables and columns. For example, if I have two tables, Customer and Transaction,
and they both contain the column CustomerID, I’d use the dot notation, as in
Customer.CustomerID and Transaction.CustomerID, to let the database know
which column from which table I’m referring.

Where

The where clause specifies which data values or rows will be returned or displayed, based on the criteria described after the keyword where.

To start with :- SQL Query to List All of the DNs Associated with IP Phones

run sql select device.name, device.description, numplan.dnorpattern  from device , numplan , devicenumplanmap  where devicenumplanmap.fkdevice = device.pkid and devicenumplanmap.fknumplan = numplan.pkid and device.tkclass = 1

CUCM Tables

The SQL queries are formed with data from these tables:

• 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.

device.description

device.name

device.pkid

numplan.dnorpattern

numplan.pkid

devicenumplanmap.fkdevice

devicenumplanmap.fknumplan

Run SQL Query.

If you want to see the fknumplan,pkid, fkdevice, Run below Queries.

run sql select fknumplan from devicenumplanmap
run sql select pkid from numplan
run sql select pkid from device
run sql select fkdevice from devicenumplanmap

tkclass = 1 is for IP Phones

run sql select name,tkmodel=1 from TypeProduct

Here is a query to obtain the tkmodel value for all IP Phone Models:

run sql select name,tkmodel from TypeProduct