Expanding on
@ChrisC_3CX post, the CDR data is an optional feature of 3CX. You have to turn the feature on and it will not go back and provide historical information. It starts generating data AFTER it is enabled. 3CX creates a text file that summarizes each phone call. The data is saved when the call is disconnected. While it includes very useful information, it does not include things like queue statistics. You enable the feature in the 3CX management console (Settings --> CDR).
I encourage you to turn on the feature and take a look at what information is provided. Be sure to add ALL the columns available. I recommend configuring CDR to create a separate file for each phone call. If this has all the information you need, and you don't need the data in real-time, this will be a good solution. There are some minor obstacles to overcome. You will need access to the files which can be complicated if your 3CX server is hosted in the cloud, but there are ways to access the files. The other minor challenge is automating the parsing of the data and getting the data into a database.
@ChrisC_3CX's characterization that 3CX generally recommends against connecting to the 3CX database is perhaps understated. In my experience, 3CX STRONGLY OBJECTS to you accessing the PostgreSQL database directly, and for good reason.
Let's take an example I have seen personally. The most common administrative tool used to connect to PostgreSQL is PGAdmin. This tool looks harmless at first glance, but in truth you have to be VERY careful with this tool. PGAdmin will let you connect to the database and view/edit the data. What is not initially obvious is that simply clicking on a table name locks the table and all the contents. Can you imagine the amount of problems this will create if 3CX is trying to write to this table and it cannot? At worst it could crash 3CX and at best it could prohibit the data from being written to the database. Either scenario can have some pretty severe consequences all because someone simply clicked on a table name.
For this reason, I encourage you to look closely at the CDR files and see if this data meets your needs. You have already concluded that exporting report data is insufficient for you, but I would encourage others to look at this option too.
The fact is, however, that the CDR data is just a summary of the phone call. It doesn't include all the legs of the call, nor does it contain any useful information about queue statistics. If you need that data, then there is really only one option -- get the data directly from the database.
Let me dispel one misunderstanding about the PostgreSQL data. It is not necessarily real-time data. I'm not certain of the specifics of how 3CX works in this regard, but by observation it is clear to me that 3CX will at times cache writes to the database. I have seen writes to the database delayed by several minutes on occasion. So, if you are looking for real-time data, accessing PostgreSQL is NOT the answer.
The following is a shameless plug for our
3CX Exporter tool. About 10+ years ago it became very obvious that access to the underlying 3CX data was going to be necessary if we were going to build custom reports. We have spent the last 10 years refining our tool to make sure we do not have any adverse impacts on 3CX. Exporter uses a carefully crafted read-only connection to 3CX. This refinement included throttling how much data we pull at any given time to ensure we don't impact 3CX performance. Further, we replicate the data over a secure TLS (HTTPS) connection which includes the need for JWT token based authorization. In addition, Exporter also parses the optional CDR data in real-time and replicates this data to SQL/MySQL thus eliminating the complexities of remote access and automation.
But let's address the elephant in the room. Does 3CX endorse the use of Exporter? 3CX does not have any certification process for 3rd-party tools, so they don't endorse any non-3CX tools. For our part, we have met many, many times with 3CX and discussed how Exporter works and even offered to discontinue the product if that would make them happy. In every case, the answer has always been "you do not need to do that". While I cannot speak for 3CX, I can can make an educated guess at their reasons for turning a blind-eye to Exporter. The alternative is to encourage everyone to figure out all the pitfalls we have addressed over the last 10 years themselves... and all the related issues that would cause for 3CX. In contrast, Exporter is a known product that has been in use for over 10 years all around the world without any issues.
3CX has announced a new REST API and Gateway service (scheduled to be released tentatively later this year). While Exporter is a valuable tool for our company, if the new 3CX tools provide direct access to the 3CX data, I'm all for it. It will make all of our lives better. I encourage 3CX to make the data available in a fully supported way.
if you need it now.
