Examples of SQL Queries for ZPC Custom Reports

Owned by Benoit Desnoyers
Last updated: Feb 16, 2023 by Sean McDade
3 min read

About ZIRO SQL Queries

The ZIRO Control Module Custom SQL Reports allows administrators to create new reports to run with the click of a button. This feature allows you to run SQL queries against any CUCM in your environment and ZIRO will return the data in a formatted data table.

To get you started, we’ve build this short document with links to external Cisco documentation as well as a few examples of queries you can start with.

CUCM Database Schema

Cisco offers database schemas to all DevNET users for every versions of CUCM. Here is a list of CUCM versions and the link to their database schemas:

SQL Queries examples

Here are some examples of SQL queries compatible with all versions of CUCM to get you started:

Show the entirety of the Device Pools table

SELECT * FROM devicepool

List all the Device Pools IDs and names in the cluster

SELECT pkid, name FROM devicepool

List the description, device name and function key of the “owner User ID” field of Cisco phones where the MAC address starts with “DD”

SELECT name, description, fkenduser FROM device WHERE name LIKE 'SEPDD%'

List the description, device name and first name, last name and email from the owner User ID where the device name of any device ends with “DOE”

List all provisioned users and their Primary Extensions

List all devices and their assigned Device Pool

More advanced SQL Queries

List Phones phones with no ownerID that are associated to a single user

Find Count of Unused Templates

List Unused Templates

List Line Group Membership Ordered by Device Name

List Devices, their associated End User, and their Last Registered and Last Active Dates