Accessing 3CX Call Data Record (CDR) PostgreSQL database

0.00 avg. rating (0% score) - 0 votes

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 Files3CX PhoneSystemBin 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 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. 

0.00 avg. rating (0% score) - 0 votes
ToughDev

ToughDev

A tough developer who likes to work on just about anything, from software development to electronics, and share his knowledge with the rest of the world.

13 thoughts on “Accessing 3CX Call Data Record (CDR) PostgreSQL database

  • October 22, 2015 at 4:35 pm
    Permalink

    SQL should be changed since in 3CX Phone System v14 data tables was changed (cl_segments, cl_calls etc).

    Reply
  • November 7, 2015 at 9:53 pm
    Permalink

    weird, in v14 I only see the default databases, phonesystem doesnt seem to exist…
    unless its on a different port like 5482

    Reply
  • November 29, 2015 at 4:59 pm
    Permalink

    Have you updated your scripts for v14 database schema? I'm working on that now.

    Reply
  • November 30, 2015 at 1:38 pm
    Permalink

    Hi,

    Sory I do not have the time to update the scripts for 3CX v14. The scripts I shared in the article were part of another project. Hope I will find some time to fine tune the scripts for v14 within the next few days. Do let me know if you managed to update the scripts – look forward to that!

    Reply
  • January 15, 2016 at 7:51 pm
    Permalink

    any1 knows which database used for v14? there is no phonesystem anymore

    Reply
  • January 15, 2016 at 8:17 pm
    Permalink

    Hi,

    Sorry I haven't touched 3CX in a while. :) What other databases do you see when you browse pgadmin?

    Reply
  • January 15, 2016 at 11:43 pm
    Permalink

    You should open file : C:\Program Files\3CX Phone System\Instance1\Bin\3CXPhoneSystem.ini

    You`ll see kind of this text :
    [QMDatabase]
    DBHost=127.0.0.1
    DBPort=5480
    DBName=database_single
    dbUser=logsreader_single
    dbPassword=123kjasdlkj3l

    Database name and login changed after upgrade.

    This I found out from a blog but then i've tried login, and could not grab any data from the db.

    Reply
  • January 16, 2016 at 11:09 am
    Permalink

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

    Above info, i'm afraid no longer allowed to be used… tested n i got password authentication failed. It works with version 12 & 12.5 but not with version 14. Correct me if i'm wrong any1.

    Reply
  • January 16, 2016 at 12:33 pm
    Permalink

    Thanks for sharing, let me try to see if I can connect to the db on my side

    Reply
  • January 29, 2016 at 10:14 pm
    Permalink

    Hi there, has anyone figured this out? I'm having the same issue, password authentication failes for user logsread, I'm using the credentials that show on the ini file, we have version 14

    Reply
  • January 29, 2016 at 10:15 pm
    Permalink

    Hi, I'm having the same issue, password authentication failes for user logsread, I'm using the credentials that show on the ini file, we have version 14, were you able to find a solution?

    Reply
  • April 7, 2016 at 9:57 pm
    Permalink

    Hi, it's weird but since v14 was released it became impossible to get a record file path via sql request. Now call history goes to myphone_callhistory_v14 table instead callhistory3 and there is no recfile field in it. CDR also doesn't have it. Is there any way to get file path?

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>