Dismiss Notice
We would like to remind you that we’re updating our login process for all 3CX forums whereby you will be able to login with the same credentials you use for the Partner or Customer Portal. Click here to read more.

Query call log (call history) in CFD?

Discussion in 'Call Flow Designer' started by SECOIT GmbH, Jul 9, 2017.

Thread Status:
Not open for further replies.
  1. SECOIT GmbH

    Joined:
    Apr 3, 2017
    Messages:
    66
    Likes Received:
    25
    Hello everyone,

    Is there a way to query the 3CX call log DB (call history) either directly from a CFD component ("Database Access") or from C#?
    I neither can find information for accessing the DB anywhere nor can I find any classes in the API doc that could do the job.

    Reason I'm asking:
    With all 3CX tests I have done I am missing a feature within the 3CX call queues which is on an incoming external call to prioritize the extension who last spoke to the currently incoming external caller.
    This is to improve processes in customer service by trying to connect a caller always to the customer service employee he/she spoke to during his/her last call. So basically the caller doesn't have to tell the whole story again when calling for the same reason as last time.
    That's a feature that two of my customers use with their PBX (other brand) and I think it's "cool" and very professional hence I'd like to implement it in CFD so I can demonstrate it in my test lab to potential customers which I'm sure most of them will find very handy.

    There are many easy ways I thought about how I could implemet it in CFD but all of them require access to the call log.
    Not sure what the table structure of the call log looks like but the (simplified) query could look something like this:
    select top 1 "To" from "call log" where "from"=currentCallerId and "Talking"<>"not answered" order by "Date/Time"
    Of course this would need to be extended by available agents in the CS queue, etc.

    Thanks
    Michael
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  2. edossantos

    edossantos Support Team
    Staff Member 3CX Support

    Joined:
    Jun 27, 2007
    Messages:
    1,597
    Likes Received:
    125
    Hi Michael,

    Accessing the 3CX database is an unsupported procedure. This can lead to unpredictable behavior in the PBX. If you accidentally make any change to the database, you might cause lots of troubles. The only service that should access the database is the 3CX database service. All the other modules should use the Configuration API instead. Also, the database structure can be updated by 3CX from version to version without notice, and if your app relies on that, it might stop working at any time, with any update, without notice. However, if you want to connect, you can do it, but there is no documentation so you would have to browser the tables and see by yourself from where you can take the information from.

    Said this, I know that the call records are not available through the API. So the official way to get previous calls details is by reading CDRs. Accessing the database can be easier and faster, however the recommended way is reading the CDRs. You can configure 3CX to output CDRs to text file or socket, and then your CFD app can read from there and decide the best agent depending on that.

    But, please note that if you transfer a call directly to an agent, the queue will not show statistics for that call.

    Kind regards.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  3. SECOIT GmbH

    Joined:
    Apr 3, 2017
    Messages:
    66
    Likes Received:
    25
    Hello Ernesto,

    Thank you for your help!

    Well, sounds like the only proper option would be to export the CDRs and load them into an external DB but that means having an extra system that runs the DB server and the PBX won't run standalone anymore (even though I must admit there is any form of DB server at nearly all my customers).
    Running the query from a file when a call comes in will very likely casue performance issues at some point I expect so I guess the live query needs to be from a proper DB server. So I'm slightly torn apart. Do it the proper way or the unsupported way.

    I might give it a try connecting to the CDR server socket.
    Do you happen to know what I need to send to the PBX to actually extract data if I use the 3CX CDR service in server mode? I enabled the CDR service (listening on tcp/3000), connected to this port using a telnet client but I can't figure out how to get any data from this server.
    This https://www.3cx.com/docs/cdr-call-data-records/ doesn't give the answer unfortunately.

    Best Regards,
    Michael
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  4. edossantos

    edossantos Support Team
    Staff Member 3CX Support

    Joined:
    Jun 27, 2007
    Messages:
    1,597
    Likes Received:
    125
    Hi Michael,

    Yes, I agree with you in that it's better to use a database, than reading the text files for every inbound call. Migrating the info from 3CX to an external database using the CDR interface is the way to go, specially to avoid problems if the 3CX database structure is changed.

    RE the CDR socket interface, you don't need to send any data. As soon as you connect to that port, you will start receiving records. Each record is a line of text, ending with the \n character. Each record contains the fields that you define, in CSV format. Please note that call records will be sent to this connection a few seconds after the calls are finished, so you might need to wait a few seconds for this to happen.

    Kind regards.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  5. SECOIT GmbH

    Joined:
    Apr 3, 2017
    Messages:
    66
    Likes Received:
    25
    Hi Ernesto,

    I'm tempted to add a new table into the 3CX DB but trying to be a good boy I'll go with the external DB then. ;)

    Thanks for confirming regarding CDR, that's what I have expected, too, but it didn't work.
    But in the meantime I found out the cause: When enabling CDR server in the GUI the system doesn't open the firewall port (iptables) on the 3CX Debian and therefore it didn't work. So I added the rule iptables -I INPUT 1 -i eth0 -p tcp --dport 3000 -j ACCEPT and now it's working as you described.
    Might be worth adding a comment to the web GUI when enabling CDR server to add the iptables rule to make it working.

    Thanks,
    Michael
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  6. edossantos

    edossantos Support Team
    Staff Member 3CX Support

    Joined:
    Jun 27, 2007
    Messages:
    1,597
    Likes Received:
    125
    Hi Michael,

    Actually, the doc already states the need to open the firewall, but it doesn't include how to do that in Linux. It states the following:
    Glad to hear that you managed to do it!

    If you want to add a table to the 3CX database, that's up to you. I would prefer creating a new database using the same PostgreSQL server, that way you avoid possible issues during 3CX migration.

    Regards.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
Thread Status:
Not open for further replies.