3CX Call Data Record (CDR) output file format
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.
See also:
Accessing 3CX Call Data Record (CDR) PostgreSQL database
Integration of 3CX Phone System with Tariscope - a third party reporting tool for 3CX.
We are looking for a similar solution, where we need to pass on the incoming call phone number to our CRM web application through browser when ever an incoming call is being picked by call center agent using 3CX Phone Windows Client Version 11.00. Any help would be highly appreciated.
Thanks,
Ramesh
MRB Innovative Technologies Ltd.
http://www.mrbitl.com
Hi Ramesh Beeraka,
You can use the 3CX Call Control API which allows you to monitor call events on the 3CX PBX. You can refer to the 3CX documentation on this API at http://www.3cx.com/blog/docs/take-control-of-calls-on-3cx-with-the-call-control-api/ and my notes regarding using this API in a .NET application at http://www.toughdev.com/2012/12/using-3cx-call-control-api-in-net.html and http://www.toughdev.com/2013/06/creating-calls-and-conferences-using.html
Let me know if you have any questions.
Hi MD,
We are using 3CX Phone version 11.0 Free Edition (Clients only) and using Open Source Call Center Application to get incoming calls through the Telephone Service Provider. Where as the call control API is available only for commercial editions. I need some kind of mechanism where the Call history information is being tracked by other application. which is available under /history folder. These log files are being locked by 3CX phone when we try to open up the log file and it doesnt update the log file when it is opened up with other application, Once the log file is closed by other application, the 3CX phone writes the call log from its buffer location. We are having a hard time to resolve this issue.
Is there any solution? to work on?
Thanks,
Ramesh.
This comment has been removed by the author.
Hi Ramesh,
At the moment I do not yet have the time to investigate how to read the call history of the 3CX Phone client – all my attempts (including the CDR file format described in the article) have been on the 3CX servers only. However if you have access to the server you can try to read the Postgre SQL database of the server directly (some information is provided at the end of this article), which contains the call history information. The database is always accessible regardless of which edition of 3CX you are using. Maybe try to write an app residing on the server which reads this database and gives you the history information that you need – which is much cleaner comparing to reading the client call history.
With regards to accessing the locked history file of the 3CX Phone client, try to use Volume Shadow Copy – it may help you to parse a file which is being locked. See this http://tranxcoder.wordpress.com/2010/01/26/using-volume-shadow-services-to-copy-locked-files/. I know Dropbox and some other apps are using this to upload a file which is being opened (e.g. Word documents) to the server without interrupting the user.
Hi MD,
Any luck with a solution? I have tried contacting other technical person VOIPToys, They couldnt be helpful in this regard. Any help is being appreciated.
Thanks,
Ramesh.
Maybe you could contact this developers at http://www.tariscope.com
They are developing call accounting software working with 3CX Phone system.
Hi MD / SV,
Is there any other SIP software (Paid version) where it meets my requirement?
Thanks,
Ramesh.
Hi Ramesh,
At the moment I do not know of any other SIP software on WIndows that meets your requirements exactly. Probably you can contact TariScope (http://www.tariscope.com/en/home.html), a PBX solution company, and see if they have any similar software. I know that TariScope offers some customized accounting add-ins for 3CX.
can you give example of linq code to get stats
Hi MD
Is there a way through the Call Control API to get the URL for call recordings? I need to build a report and need to include a hyperlink that links to each call's recording.
I am using 3CX version 14 pro.
Many thanks
Hi MD
Is there a way through the Call Control API to get the URL for call recordings? I need to build a report and need to include a hyperlink that links to each call's recording.
I am using 3CX version 14 pro.
Many thanks
Hi,
As far as I know there is no such support. You would need to look through the 3CX database to see where the recording for each call is located, or check the actual recording history (C:\ProgramData\3CX\Data\Recordings) to find the recording wave file for your call.
Hi MD
Is there a way through the Call Control API to get the URL for call recordings? I need to build a report and need to include a hyperlink that links to each call's recording.
I am using 3CX version 14 pro.
Many thanks
Please share How to record phone calls on Android