Connect 3CX live data to Excel

[dan_00]

Hello,

I would like to connect live data from 3CX to Excel spreadsheets. This would allow us to have live reports and dashboards in Excel, showing live call data.

How can this connection be done? In Excel, do we need to use oData or...? Also, how is the connection made from 3CX?

It would be great if the 3CX Call Log page could be connected to Microsoft Excel using oData.

Thanks for your help.

 

[mariosM_3CX]

Hello @dan_00

I'm afraid that there is not an easy way to do this. However, the PBX is able to output CDR logs for every call. It can output to files or sockets. So what you can do, is configure the PBX to output CDRs to a socket for example and create a small application that will connect to the PBX and read the CDRs. You can then for example implement an oData feed and connect it to excel.

You can find more about CDR here https://www.3cx.com/docs/cdr-call-data-records/

 

[VoIPTools]

To expand on what mariosM_3CX has said, the CDR files are written after the call is finished. It is a simple comma separated file that contains the basic information about a call including who the participants were, start and end times, duration, and all the legs of the call. It's NEAR real-time data.

You can capture real-time data using the 3CX Call Control API and listening to all the events, but that's a pretty verbose (complicated) approach to getting the data.

The CDR files, and the API are the only supported ways of getting call data.

The 3rd approach is to pull the data directly from the 3CX PostgreSQL database. This is not supported, and highly discouraged by 3CX support. The primary concern (I believe) is that accessing the database directly could adversely impact the operations of 3CX. And indeed I've seen this in our test environments. If you are not very careful, you can inadvertently place a lock on tables while you access the data and THAT wold be a very bad thing on a production server.

You might consider looking at our 3CX Exporter add-on. Exporter can automatically process the CDR files and place the data into a MySQL or SQL server database (the free Express edition works fine). Once the data is in SQL, then you can easily process the data from Excel.

If you want to go the API approach, look at our 3CX Monitor add-on which collects all of the events and writes the data to a database.

And as a lost-resort option, if you simply can't get the data you need any other way, you can pull the data directly out of the 3CX PostgreSQL database using our Exporter add-on. Exporter utilizes a very carefully crafted read-only connection to the 3CX database and synchronizes the data with MySQL or SQL server. Once the data is in SQL you can do whatever you want without impacting 3CX. Happily, Exporter has been in use for about 7 years with no reported issues. But this is not supported by 3CX.

 

[dan_00]

Thanks for the info.

configure the PBX to output CDRs to a socket

Could you please define what you mean by a socket?

 

[mariosM_3CX]

Hi @dan_00

When enabling the CDR generation on the PBX, you'll need to select an IP address and a TCP port. Think of the socket as the combination of the ip/port selected where together, they constitute the endpoint for communication with other applications across a network. It is on that endpoint or socket that your application needs to connect in order to receive the CDRs.

 

[dan_00]

Ok, thanks for this. This info is getting a bit deep for me!

However I'd like to know - if CDRs are can be created for each call within 3CX, why can't this information then simply be taken and created into a table within 3CX? Then by using something like oData, a quick and easy connection from this table to Excel could be set up.

Or is this table already there - the Call Log? If the Call Log is simply a table showing the CDR info, then could functionalities such as oData be added to the Call Log page please?

 

[mariosM_3CX]

Call information is stored internally within 3CX' database where is used by call log and reports. I'm afraid though that live feed with the format needed for lets say oData is not implemented.
CDR is a good approach, though it will require some coding knowledge to make it fit your needs.

You can post this however as a feature request in the Ideas part of our Forum here https://www.3cx.com/community/forums/3cx-ideas/ where it can be upvoted by the community and be considered for implementation by the dev team.

 

[VoIPTools]

While connecting via a socket is indeed an option, it really only applies (in my view) when working with a Hotel PMS or similar application that is requiring a socket connection to communicate. If you are building a solution from scratch, I would not use a socket simply because if your connection goes down, you loose the data that would have been sent over the socket. In contrast, using the CDF file approach, you will never miss any transactions regardless of whether your service goes down for a time because the files will still be there waiting for you to process them. Exporter will put the call detail into a table, then you can use oData to access the data.

 

[dan_00]

I've posted the idea of having oData abilities on the CDR page as an idea: https://www.3cx.com/community/threa...ion-from-cdr-page-to-excel-using-odata.55899/
Votes would be really appreciated!