Tuesday, August 30, 2016
3CX Call Data Record CDR output file format
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 arent 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