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.
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 Files3CX PhoneSystemBin 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:ProgramData3CXDataLogsCallHistory folder. (And for those who are interested, the call recordings WAV files, if recording is enabled, are found in C:ProgramData3CXDataRecordings, 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:ProgramData3CXDataCDRTemplates. 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:
- 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.
- 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.
- 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.
- 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.
Accessing 3CX Call Data Record (CDR) PostgreSQL database
Integration of 3CX Phone System with Tariscope - a third party reporting tool for 3CX.