Accessing 3CX Call Data Record (CDR) PostgreSQL database

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

Read More »

Creating calls and conferences using 3CX Call Control API

By default, 3CX provides the HTTP API to allow users to make calls on the PBX programmatically as well as changing some other extension settings. However, the HTTP API is pretty limited and a better way is to use the Call Control API to have more controls on how calls are created.

Although the Call Control API is available on both 3CX version 12 and 11, the library DLLs (3cxpscomcpp2.dll and sl.dll) used by the DLL are not interchangeable between the two versions. This means you will probably need to build two versions of your application referencing the different versions of the libraries if you wish to target both 3CX version 11 and 12. Also it is advised to keep a local copy of 3cxpscomcpp2.dll (and sl.dll) in your application and add a reference to the local copy, and not the version of the DLL found in the .NET Global Assembly Cache (GAC). In my test, referencing the GAC version may result in problems finding the 3CX assembly when the application is run on a different machine.

Making calls

To make a call connecting two extensions on the PBX, use the following method:

public void MakeCall(
    string dn_from,
    string number_to
)

The source number, dn_from can be any internal number on the PBX and number_to can be any internal or external number. 3CX will call dn_from waiting for the call to be answered, upon which number_to will be called. When number_to picks up the call, dn_from and number_to will be able to talk to each other. This process is also known as callback.

While number_to can be any internal or external number, dn_from can only be an internal number. This means you will not be able to use this method to connect two arbitrary PSTN numbers via 3CX.

Conferencing on 3CX version 11

To initiate the simplest conference you can use

MakeCall(701, 101)

where 701 is the first conference extension on the system and 101 is the extension number. If 701 does not currently have any conference in progress, this will make a call to 101 informing the extension that a new conference will be created with the extension being the first participant and other people calling 701 will be able to join this conference. If a conference is already in progress, extension 101 will be able to join the existing conference.

The number of maximum simultaneous conference can be configured inside Settings > Advanced:

The above settings will create 4 conference extensions 701-704 on the system, viewable from System Extension Status from the 3CX web portal. A default conference extension 700 will also be created but cannot be used to make conference calls programmatically. From the call control API, extension 700 will be treated as a ConferencePlaceExtension with IsGateway property set to TRUE. User can still call 700 to create a new conference.

To have more controls on the created conference, use the following method:

public void MakeCall(
    string dnNumber,
    Dictionary<string, string> parameters
)

where dnNumber is a conference extension which is not a gateway and parameters is dictionary containing the following parameters:

"tojoin" - number to dial. Specifies destination of call.
"PIN" - the pin of this conference. Can be empty.
"noname" - "0" or "1". Only for initial call. Conference place will not ask new members to pronounce name before entering this conference call but will ask to confirm participation. Will inform participants about joining and leaving but without name.
"instant" - "0" or "1". Only for initial call. Conference is instant. Conference place will not ask member to confirm participation

For example, to create a conference to extension 100 that begins immediately, does not ask members for name or PIN code, use the following set of parameters:

{"tojoin":"100", "PIN":"", "noname":"1", "instant":"1"}

Conferencing on 3CX version 12

On 3CX version 12, major changes have been implemented in the conference architecture and associated APIs. There are no longer separated conference extensions (e.g. 701-704) on the system. Rather, there is only a single conference extension (700) and each extension will have its own conference slot which can hold conference calls for that extension. The number of simultaneous conferences is also removed from 3CX settings:

To create a simple conference, you can use

MakeCall(700**101, 101)

to use the dedicated conference slot on extension 101 to initiate a new conference that has 101 as the first participant.

Unfortunately, according to 3CX support, the method

public void MakeCall(
    string dnNumber,
    Dictionary<string, string> parameters
)

is no longer supported as at 3CX version 12, despite still being mentioned inside the API documentation.

The ScheduleTheConference.cs sample provided with the API provides another method to create a new conference:

PhoneSystem ps = PhoneSystem.Root;
var stat = ps.CreateStatistics("S_SCHEDULEDCONF", args[1]);
stat.clearall();
stat["name"] = args[4];
stat["idstat"] = args[1];
stat["pin"] = args[3];
stat["startat"] = dt.ToUniversalTime().ToString(@"yyyy-MM-dd HH\:mm\:ss");
stat["target"] = args.Length > 5 ? args[5] : "";
stat["numbertocall"] = "";
stat["email"] = "";
stat["description"] = "";
stat["emailtext"] = "";
stat.update();

            

However, despite much effort, I could not get this method to work on my 3CX setup. No errors are returned and no related events are found inside the 3CX system activity log. Even if I could get it to work, it will not create a new conference effectively since the method intention is to schedule a new conference to run in the future, not to start it immediately and monitor its progress. This means there is currently no way to have full control over the created conference on 3CX version 12.

Surprisingly, the new version of 3CX Phone for Windows is able to support creating, scheduling and maintaining conference:

My guess is that it uses a proprietary communication protocol to communicate with the 3CX server directly. Knowing that the phone application is built as a .NET Silverlight application, I tried to use Reflector to decompile it and although the source code is not obfuscated, the decompiled code still looks far too complicated. At this point it is not worth the time and efforts for me to attempt to identify the conference mechanism this way. 

See also:
Using 3CX Call Control API in a .NET application

Read More »

3CX Call Data Record (CDR) output file format

UPDATE: Refer to my latest article on how to access the 3CX CDR PostgreSQL database directly and extract meaningful call history information from it using SQL. This approach is much better than parsing the CDR output file.

Although 3CX, the most common software PBX for Windows, comes with a few ways for user to generate detailed call reports and usage statistics in various different formats, many advanced users often find the reporting feature inadequate due to the need to generate custom reports which aren't supported by default. To do so one would need to retrieve the raw CDR data, either by accessing the call database directly or by analyzing the CDR text files generated by 3CX, and generate their own reports.

Direct access to the 3CX call database

The 3CX database, containing call records and various other PBX settings, is in PostgreSQL format and will be accessible via a PostgreSQL client such as pgAdmin. The authentication credentials can be retrieved from the file 3CXPhoneSystem.ini found in the C:\Program Files\3CX PhoneSystem\Bin folder.

Once connected to the server, the 3CX database is located at Servers>3CX>Databases>phonesystem>Schemas>public>tables. Call information is consolidated into 3 tables, namely calldetails, callhistory2, callhistory3. For general call history statistics, records from table calldetails would be sufficient.

Although not officially documented, various online resources describing the database format are available. Refer to this for more information on the database schema.

Interestingly, the credential provided in the 3CXPhoneSystem.ini cannot be used to access other tables in the database. I do not yet know how to access other tables.

Analyzing the CDR text files

If you do not wish to connect to the database, another approach is to read the CDR text files that are generated by 3CX as calls are made. These files are found in the C:\ProgramData\3CX\Data\Logs\CallHistory folder. (And for those who are interested, the call recordings WAV files, if recording is enabled, are found in C:\ProgramData\3CX\Data\Recordings, with recording for each extension saved in a subfolder having the same name as the extension number)

The default format of the CDR output is quite straightforward. Each line in the log file is comma separated and will have at least 8 fields. For each call from the initiating to completion state, several lines will be written to the CDR log file as the call progresses. The description of the fields are below:

Field #0 – State of the call. Possible value are Connecting = 1, CallEstablished = 2, PartyAdded = 3, PartyRemoved = 4, PartyChanged = 5, Disconnected = 6, DestNoAnswer = 7, DestIsBusy = 8, DestNotAvail = 9, RecordingInfo = 10
Field #1 – The time of the call state change, in the format yyyymmddhhmmss.### where ### is the number of milliseconds
Field #2 – History ID of the call on the PBX
Field #3 – Internal source number of the call
Field #4 – Internal destination number of the call
Field #5 – External source number of the call if the call originates from an external number, otherwise, same as Field #3. Or if Field #0 is 10, this field will contain RecON to indicate the start of a call recording, or RecOFF to indicate that recording has been completed.
Field #6 – External destination number of the call if the call terminates on an external number, otherwise, same as Field #4. Or if Field #0 is 10, this will contain the path to the recorded wave file.
Field #7 – Type of call (1 = voice call, 0 = fax call)
Field #8 (Optional) – Any additional information about the call. This is often the name of the 3CX call queue if the call is involved in a queue.

For example the following line

1,20130414105548.819,00000BD538E62E50_2539,80001,10011,012345678,10011,1,"Customer Service Queue::*"

tells us that on 14 April 2013 at 10:55:48.819 (Field #1), the call having a history ID of 00000BD538E62E50_2539 (Field #2) was in the connecting (Field #0 = 1) state. The call was made from external number 012345678 (Field #5), reaches the 3CX digital receptionist on 80001 (Field #3), and was routed to extension 10011 (Field #4 = Field #6 = 10011). The call was a voice call (Field #7 = 1) on the Customer Service Queue (Field #8)  

Note: This is the default CDR output format. The format can be customized by editing the XML files located in C:\ProgramData\3CX\Data\CDRTemplates. Refer to this article for more details.

With knowledge of the file format, one would think that creating the call history from the CDR output would be an easy task. Unfortunately, this approach has a few challenges, with some being more critical then the rest:

1. The CDR output files are not updated immediately after a call is made, but rather, after a certain interval configurable from 3CX Admin Portal. My experience shows that even with the shortest possible interval set, some times the CDR output takes a while to be updated, resulting in outdated call history information. 
2. With each call, a few status lines are written to the CDR as the call progresses, e.g.  Connecting>CallEstablished>Disconnected. There could be more intermediate statuses if the call involes a transfer or is a conference call. Deducing the necessary information (e.g. call duration) could be tricky.
3. If an extension-to-extension call is made (e.g. inbound calls), for each status change, 2 almost similar records will be created for each extension involved - with only the extension number being different. Depends on the usage, it may be necessary to filter out such records after processing the CDR, which will slow down the performance of the code. 
4. If a call reaches a queue and is diverted to the agents involved in a queue, CDR records will be created as 3CX tries to find available agents in the queue. This means, if a 10-agent queue has only 1 available agent at the time of the call, you will see 9 records of unsuccessful calls created for the attempts to reach the unavailable agents before the actual successful call record. Again, filtering out these records could be tricky.

There could be more problems as there are more CDR records from complicated scenarios which I have yet to encounter.  However, in my case, with simple user requirements (knowing the total number of successful/failed calls, total duration, etc.), my usage of .NET LINQ to analyze the CDR so far seems adequate.

The best solution would be for 3CX to provide a method to retrieve the call history as part of the Call Control API, which is not yet possible as at 3CX version 12.

See also:
Accessing 3CX Call Data Record (CDR) PostgreSQL database
Integration of 3CX Phone System with Tariscope  - a third party reporting tool for 3CX.   

Read More »

Using 3CX Call Control API in a .NET application

In one of the projects at work I attempted to use the 3CX Call Control API (see this) from my .NET application and encountered unique challenges because the Call Control API is only available from a .NET application running on the same server as the 3CX machine. This means, even if the sample .NET application to demonstrate the API provided by 3CX is working well, it is of little usefulness if you want to expose the API to your custom application which undoubtedly must be running from the client machine and not on the same server.

Issues with 3cxpscomcpp2.dll when called from ASP.NET SOAP web service

My design is to write an API wrapper that runs on the 3CX server, receives client requests via HTTP, interacts with the 3CX Call Control API to perform the necessary actions and returns the API response back to the client also via HTTP.

With this design, the first attempt is to use an ASP.NET SOAP web service, which unfortunately does not work. First, the web service fails to start due to a BadImageFormatException once the API DLL 3cxpscomcpp2.dll, is added as a reference to the project

System.BadImageFormatException: Could not load file or assembly '3cxpscomcpp2.dll' or one of its dependencies. An attempt was made to load a program with an incorrect format

Knowing that this is because of the format of the DLL (32-bit vs. 64-bit) and the architecture of the project (x86 or x64), I tried to changed the project platform but neither x86 or x64 works. I also changed the application pool settings inside IIS following this article, which also does not help.

Next I noticed that the API DLL is a 64-bit DLL and attempted to install 3CX on a 32-bit machine to retrieve the 32-bit version of the DLL. This time, the error message when loading the web service changed:

Could not load file or assembly '3cxpscomcpp2' or one of its dependencies. Modules which are not in the manifest were streamed in. (Exception from HRESULT: 0x80131043)

The error message is not very useful and several Google searches did not return any working solutions. This has to do with the fact that the 3cxpscomcpp2.dll uses a native C++ dll named sl.dll. For the API to initialize properly, both DLLs are required. Despite substantial research, I could not find any reasons why sl.dll fails to be loaded and thus giving up integrating the DLL with ASP.NET web service.

The solution

My next attempt is to use a Windows Communication Foundation (or WCF) aplication instead, and not ASP.NET web service. The application will hook up to a HTTP port on the machine, listening to HTTP request via POST/GET and returning the response in JSON. This time, the application has no difficulties connecting to the API and things work as expected. Take note that you will need to host your WCF service in a console app, or as a Windows service. Hosting it under IIS and you will still encounter the same BadImageFormatException issue.

The next challenge is encountered when one of the developers using my APIs reported a strange error when calling my API from jQuery:

Origin http://localhost:8888 is not allowed by Access-Control-Allow-Origin.

To fix the error, one of the following must be done:

1. The browser must allow cross domain calls. For Chrome, you can launch chrome with the parameters --disable-web-security and cross domain calls will be allowed.
2. The server response must contain the following header to allow calls from any origin:
   Access-Control-Allow-Origin: * 
3. The server sends the response in JSONP

Because (1) is out of the question since the calling web site must be able to support different browsers while (3) is not possible yet from WCF, I have chosen (2). Luckily I found the following MSDN blog which proposes a solution that does not require any code changes:

1. Import the WebHttpCors DLL provided by the author
2. Modified app.config to add the tag to allow cross-domain calls.

It works well and the API can be called from jQuery with no issues.  However, during the project, I also noticed several limitations with the 3CX Call Control API:

1. There is no API to put a call on hold. The closest you can get is to transfer the call to a parked extension. To unhold the call, make a call to the parked number and you will be able to continue with the parked call.
2. There is no API to retrieve the call history and the application needs to manually parse the 3CX Call History log files located at C:\ProgramData\3CX\Data\Logs\CallHistory or read from the 3CX internal PostgreSQL database. Refer to this article for more details. In this aspect, the approach of using a WCF application instead of ASP.NET poses a major advantage because as a Windows application, WCF has no difficulties accessing files located on different paths on the server. This is also needed to read the call recording files located at C:\ProgramData\3CX\Data\Recordings

I hope 3CX will be able to introduce more APIs in the future to solve the above mentioned limitations. For more information on how to use 3CX API with your .NET application, read my other article.

Read More »