ShoreTel Professional Services Custom Reports
Our Professional Services group is often asked about creating custom reports from their ShoreTel system, so I thought I’d explain a bit about them.
First some background on the data store itself:
ShoreTel uses the MySQL database management system to store both configuration data (since ShoreTel version 8) and dynamic call data (since ShoreTel version 7). Since ShoreTel version 10, each of these has its own “instance” within MySQL – each is a separate database responding on a specific IP port configured within the MySQL system. Before that, they shared the same instance using the default port. Each database contains a number of tables, views, and stored procedures.
The configuration database generally contains all the data you have configured via ShoreTel Director. The CDR database contains dynamic data related to phone calls, Workgroup activity, etc. This CDR database is the one on which all the ShoreTel reports are based.
The schema (the map to understanding the tables) for the configuration database is not open or published. The schema for the CDR database is open and is explained in appendix B of the System Administrator’s Guide.
So, what if a customer wants a report beyond the set of built-in reports available in Director?
Easiest is to first examine the set of 7 existing enhanced reports offered by our Professional Services group to see if one or more may meet the need. These are popular pre-packaged reports, available for purchase, created due to prior customer requests.
If none of the existing reports suffice, any customer or third party can go directly to the CDR database to mine the data via SQL queries (manually or programmatically) or using a commercially available report generation program (e.g. Crystal Reports). This is recommended only for those customers that have experience with these techniques. For those versed in this area, the connection information is:
SERVER=<The ShoreTel Director server IP address>
PORT=4309 (Omit this to use default port if your ShoreTel version is prior to 10.2)
(If using the archive DB, substitute server and database of the archive)
For manually accessing databases, there are several free programs that allow powerful features to display and query data. Two I recommend are MySQL Workbench and SQLyog. Using one of these programs along with the CDR documentation from the System Administrator’s Guide is the best way to get familiar with the data.
Of course, our Professional Services group can also create customized Crystal based or Web based reports under contract. For more information, contact ShoreTel Professional Services at ProfessionalServices@shoretel.com or +1 800-425-9385 x3331.