Accessing 3CX Call Data Record (CDR) PostgreSQL database

UPDATE (May 2018): See my latest article for more information on how to manage calls programmatically on 3CX v15. The article below was written for 3CX 11 and some information may not be applicable for the latest version of 3CX.

In my previous post I described a method to read the 3CX CDR information by parsing the CDR log files created by 3CX.  Although this approach may look straight forward from a programming point of view, as the number of call grows, problems will arise due to the reliance on the undocumented CDR file format and the needs to read and parse hundreds of CDR text files just to get the call statistics. As a result, I decided to attempt to read the database directly to see if better performance can be achieved and this article will share some of my findings.

Database configuration

As 3CX uses a PostgreSQL database to store its CDR information, you will need a tool such as pgAdmin to open the database. The database logon credentials can be found among the last few lines of the 3CXPhoneSystem.ini file located in the C:\Program Files\3CX\PhoneSystem\Bin folder:

[CallReports]
USERNAME=logsreader
DATABASE=phonesystem
PORT=5480
DRIVER=PostgreSQL Unicode
ReadOnly=1
SERVER=localhost
PASSWORD=*******************

Add a new server in pgAdmin using the above credentials and you should be able to connect:

Database tables
 
The logsreader account only has access to some tables in the database, mostly tables with call history information and some other tables with information on the 3CX setup configuration, which perhaps can also be retrieved using the Call Control API.

The tables that contain the CDR are calldetails, callhistory2 and callhistory3. In my tests, table callhistory2 is always empty despite a large number of calls on the PBX. The remaining two tables, calldetails and callhistory3, store call information and are described below.

Table calldetails

This table contains information on each leg of the call, including the caller, destination number, timestamp and call status. Most useable columns should be self-explanatory from their names. Other important columns are described below:

• idcalldetails: primary key of the table
• idcallhistory2: the ID that identifies the same call in table callhistory2 and table callhistory3
• is_tooutside: TRUE if the call targets an external number which is not an extension on the PBX (and has to be terminated via a configured gateway)
• is_compl: whether the call was completed successfully, from the PBX point of view. For most calls (even for unanswered calls), this value will be TRUE. It will only be FALSE if an unexpected problem on the PBX occurred during the call.
• status: the status of the call. Possible values are:

Connecting = 0,
Answered = 1,
DestBusy = 2,
DestNoAnswer = 3,
DestNotAvailable = 4,
NotAnswered = 5,
Completed = 6,
DstUnknown = 7

Table callhistory3

This table stores overall information for each call. Important columns are described below:

• idcallhistory3: primary key of the table
• callid: the history ID of the call. This is the same ID that you will find in the 3CX CDR. It will look something like “00000BD4DFFBDF88_1″.
• is_answ: whether the call was answered by at least one party. If a call reaches a digital receptionist (IVR), is_answ is probably TRUE immediately after the calls reaches the PBX. If a call reaches a queue, is_answ will probably not be TRUE until it is diverted to a queue member. If an answered call is later forwarded to another party which does not answer the call, the is_answ flag remains TRUE.
• is_fail: TRUE if the call could not be completed. For most calls, this flag should probably be FALSE unless an unexpected error occurred during the call.
• is_compl: TRUE if at least one segment of the call is completed successfully.
• is_fromoutside: TRUE if the call is from an external number which is not an extension on the PBX. For example, from a user who calls a DID number assigned to a configured gateway on the PBX.
• callerid: the number of the originating number of the call. If is_fromoutside is TRUE, this will be the external caller number.
• group_no: the number of the queue from which the call originates. If the call is not from a queue, this field is empty.
• callchain: the semicolon separated list of all participants involved in the call
• rec_file: path to the WAV recording of the call, if recording is enabled.

Difference between callhistory3 and calldetails tables

While table callhistory3 stores overall information for each call, table calldetails stores information for each leg of the call. To illustrate the difference, if extension 10009 called the queue number on 80001 and was diverted to extension 10010 which is a member of the queue, you will see the following 2 records from table calldetails:

And only 1 record from table callhistory3 showing overall information about the call:

The 2 records from table calldetails have idcallhistory2=19903 to indicate that they belong to the same call having a history ID of 00000BDA221D1253_4 in the callhistory3 table.

The status column is missing from table callhistory3, as different segments of the calls might have different statuses. However, from table callhistory3, you can still check whether the call was answered (is_answ and answertime column) and whether it was completed without errors (is_compl and is_fail columns).

Useful SQL queries

One of the objectives during my experiment is to write the SQL queries to extract the call reports in a format similar to the 3CX Web Report tool (available at http://localhost:5000/Reports). Suprisingly, with some understanding and experimenting with the database, this is a matter of tweaking some SQL queries to retrieve the information I need.

For example, to get the total calls made to the queue on the PBX reported by 3CX in Call Center Statistics Report > Detailed Queue Statistics – All Queues, we use the following query (assuming 80001 is our queue number):

SELECT * FROM calldetails WHERE  dest_dn = '80001' AND (status=5 OR status=6)

Take note that the number of calls made through the queue returned from the above query (and reported by 3CX) may not be correct depending on how your queue is configured. For example, if I configure my queue to forward to another digital receptionist announcing “High Call Volume” after 30 seconds of waiting, and divert the caller back to the queue after the announcement, each “High Call Volume” announcement will add 1 entry to the database, resulting in an exaggerated number of reported calls to the queue.  To retrieve the true list of unique calls made through the queue on the PBX, we improve the query as below:

SELECT * FROM callhistory3 WHERE idcallhistory3 IN 
(
SELECT DISTINCT idcallhistory2 FROM calldetails 
INNER JOIN callhistory3 ON calldetails.idcallhistory2=callhistory3.idcallhistory3
WHERE  calldetails.dest_dn = '80001' AND (calldetails.status=5 OR calldetails.status=6)
)

The following query returns the number of calls to the queue that were answered by an operator:

SELECT COUNT(DISTINCT idcallhistory2) FROM calldetails 
INNER JOIN callhistory3 ON calldetails.idcallhistory2=callhistory3.idcallhistory3
WHERE  calldetails.dest_dn = '80001' AND (calldetails.status=5 OR calldetails.status=6) AND (callhistory3.answertime IS NOT NULL)

Total number of abandoned calls, e.g. called to the queue that were never answered by an operator:

SELECT COUNT(DISTINCT idcallhistory2) FROM calldetails 
INNER JOIN callhistory3 ON calldetails.idcallhistory2=callhistory3.idcallhistory3
WHERE  calldetails.dest_dn = '80001' AND (calldetails.status=5 OR calldetails.status=6) AND (callhistory3.answertime IS NULL)

Total taktime for answered calls made through the queue:

SELECT sum(endtime - answertime) FROM
(
SELECT DISTINCT idcallhistory2, callhistory3.answertime, callhistory3.starttime FROM calldetails 
INNER JOIN callhistory3 ON calldetails.idcallhistory2=callhistory3.idcallhistory3
WHERE  calldetails.dest_dn = '80001' AND (calldetails.status=5 OR calldetails.status=6) AND (callhistory3.answertime IS NOT NULL) 
)
AS result

The returned value does not match the ‘Total Queue Talk Time’ parameter in the 3CX report as it counts the call duration from the moment the queued call is answered until the call is terminated, which may include time spent on other legs of the call if the queued call is diverted to other parties. On the other hand, the 3CX report only calculates the duration of the segment of the call which involves the queue number and does not include the time spending on other call segments.

To retrieve all calls that have been made on the PBX excluding calls made to the queue, use the following query (assuming 80001 is the queue number):

SELECT * FROM callhistory3 WHERE from_no <> '80001'

The result will be almost identical to the list of calls found in the Call Statistics Report > Call Logs section of the 3CX web report tool.

You will notice that the field status is missing from the returned value, as it is not available in the callhistory3 table. To get the detailed status of the call (e.g. answered, busy, no answer, invalid number, etc.), use the following query:

SELECT 
DISTINCT ON(idcallhistory3)
callid, from_no, callerid, to_no, is_answ, callchain, status, calldetails.is_compl,
is_fail, is_fromoutside, group_no, recfile FROM callhistory3
INNER JOIN calldetails ON calldetails.idcallhistory2 = callhistory3.idcallhistory3

What the query does is to join the returned result with table calldetails to get back the field status. Because a call may have multiple legs, with each leg having different status, we need to use the special DISTINCT ON keyword of PostgreSQL to only retrieve the status of the first leg of the call in the final result. If the call has multiple legs with different status, this field may not be reflective of the overall status of the call, in which case the is_compl, is_answ and is_fail fields will need to be examined.

Accessing the database from .NET

.NET does not have built-in support for PostgreSQL databases and the use of an ODBC driver is needed to access the 3CX database from a .NET application. You will then need to add a DSN under Control Panel > Administrative Tools > ODBC Data Sources that points to the 3CX database:

Queries can now be executed using the OdbcDataConnection class:

OdbcConnection connection = new OdbcConnection("DSN=3CXDB");
connection.Open();
string query = String.Format("SELECT COUNT(idcalldetail) FROM calldetails");
OdbcCommand command = new OdbcCommand(query, connection);
OdbcDataReader reader = command.ExecuteReader();
reader.Read();
int count = reader.GetInt32(0);
reader.Close();
connection.Close();

If your program is to be run as a Windows Service, it is best to add the DSN under System DSN (which the Local System account has access to), and not User DSN, otherwise accessing the DSN may fail with error “Data source name not found and no default driver specified” unless the service is configured to run as system administrator. Also it is advised to install the version of the ODBC driver that matches the architecture of your application. For example, if the application is 64-bit, the 64-bit PostgreSQL ODBC driver should be installed. In my case, my application is also using the 3CX Call Control API which uses the 64-bit libraries that come with the 3CX installation on a 64-bit Windows machine, and both the application and the ODBC driver have to be 64-bit versions.

See also:

3CX Call Control API on 3CX Phone System version 15
3CX Call Data Record (CDR) output file format
Using 3CX Call Control API in a .NET application
Integration of 3CX Phone System with Tariscope  - a third party reporting tool for 3CX.