Lets learn some basics of SQl Queries and try it on CUCM.

What SQL?

SQL is used to communicate with a database. According to ANSI (American National Standards Institute), it is the standard language for relational database management systems. SQL statements are used to perform tasks such as update data on a database, or retrieve data from a database. Some common relational database management systems that use SQL are: Oracle, Sybase, Microsoft SQL Server, Access, Ingres, etc. Although most database systems use SQL, most of them also have their own additional proprietary extensions that are usually only used on their system. However, the standard SQL commands such as “Select”, “Insert”, “Update”, “Delete”, “Create”, and “Drop” can be used to accomplish almost everything that one needs to do with a database.

Selecting Data

The select statement is used to query the database and retrieve selected data that match the criteria that you specify. Here is the format of a simple select statement:     select “column1″  [,”column2”,etc]   from “tablename” 

The column names that follow the select keyword determine which columns will be returned in the results. You can select as many column names that you’d like, or you can use a “*” to select all columns.

The table name that follows the keyword from specifies the table that will be queried to retrieve the desired results.

CUCM Data Dictionary

CUCM Data Dictionary describes the Cisco Unified Communications Manager Database Schema (Tables, Fields, Data Types, and Relationships). To view the data dictionary locally, download the zip, unzip it, and open the HTML file in your browser.CUCM Data dictionary can be downloaded from below link.Previously This was under CCO login but now its has been moved to developer.cisco.com.

https://developer.cisco.com/docs/axl/#!archived-references

Just to start with :- Two Basic Application and End user Query which list the Username and their Ranking.

run sql select name, userrank from applicationuser

run sql select userid , userrank from enduser

CUCM Tables

The SQL queries are formed with data from these tables:

• EndUser – This table contains the information related to End Users.
• Applicationuser-  This table contains the information related to Application Users.

run sql select name, userrank from applicationuser

Using the select name, userrank from applicationuser, From table applicationuser i pull two Columns . Name and Userrank.

run sql select userid , userrank from enduser

Using the select userid , userrank from enduser, From table enduser i pull two Columns . Userid and Userrank.