Connect 3CX live data to Excel

Discussion in '3CX Phone System - General' started by dan_00, Apr 17, 2018.

Thread Status:
Not open for further replies.
  1. dan_00

    Joined:
    Apr 17, 2018
    Messages:
    9
    Likes Received:
    0
    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.
     
    #1 dan_00, Apr 17, 2018
    Last edited: Apr 17, 2018
  2. mariosM_3CX

    mariosM_3CX Support Team
    Staff Member 3CX Support

    Joined:
    Nov 1, 2017
    Messages:
    406
    Likes Received:
    38
    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/
     
  3. voiptoys

    voiptoys Active Member

    Joined:
    Feb 13, 2013
    Messages:
    727
    Likes Received:
    125
    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.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  4. dan_00

    Joined:
    Apr 17, 2018
    Messages:
    9
    Likes Received:
    0
    Thanks for the info.
    Could you please define what you mean by a socket?
     
  5. mariosM_3CX

    mariosM_3CX Support Team
    Staff Member 3CX Support

    Joined:
    Nov 1, 2017
    Messages:
    406
    Likes Received:
    38
    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.
     
  6. dan_00

    Joined:
    Apr 17, 2018
    Messages:
    9
    Likes Received:
    0
    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?
     
  7. mariosM_3CX

    mariosM_3CX Support Team
    Staff Member 3CX Support

    Joined:
    Nov 1, 2017
    Messages:
    406
    Likes Received:
    38
    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.
     
  8. voiptoys

    voiptoys Active Member

    Joined:
    Feb 13, 2013
    Messages:
    727
    Likes Received:
    125
    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.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  9. dan_00

    Joined:
    Apr 17, 2018
    Messages:
    9
    Likes Received:
    0
Thread Status:
Not open for further replies.