Call journaling

[taco2]

Hi,

I am currently integrating the MySQL integration. Everything is working as expected. When a call is finished the call is being inserted into the table 'calls', showing the duration of the call etc. However, if the duration of a call is an hour or more, the call is not being inserted. Below is the query for the call journaling:

INSERT INTO Calls (contactnumber, agentextension, description, calldatetime, callduration) VALUES ('[Number]', '[Agent]', '3CX PhoneSystem Call', '[DateTime]', '[DurationTimespan]')

Note that I have tried with different variables for the duration.

How can this be fixed?

Thanks in advance!

 

[edossantos]

Do you see any error in the log when 3CX tries to send this information? Or 3CX is not sending the information at all? You can check this in the 3cxSystemService.log file, with verbose logs enabled.

If you have an error log to see, maybe we can suggest some edit to your SQL statement.

 

[taco2]

Since I'm no expert in reading 3CX error logs, I extracted the part of the log to give you some insight in this. I hope this helps.

Thanks in advance.

 

[edossantos]

The contact lookup is not finding the record because the SQL query you're using is not returning all the fields that the integration requires:
2020/05/13 20:04:58.345|1740|0045|Verb|Executing SQL Query using ConnectionString 'Server=xxxxx;Port=;Database=xxxxx_db2;User Id=xxxxx;Password=xxxxx' and Query Statement 'SELECT UniqueID as contactid, Navn as firstname FROM Emner WHERE Nummer LIKE '%152532%''.
2020/05/13 20:04:58.784|1740|0037|Verb|The result of the SQL Query Execution is
'[{"contactid":10707,"firstname":"Peter Baun"},{"contactid":68705,"firstname":""}]' .

As you can see, the query is just returning the contactid and the firstname. If this query doesn't return the phone number being searched, the record will be skipped. If there is no record from contact lookup, the call journaling is skipped.

You need to change your SQL query. It must return at least:
1) The contactid
2) The firstname, lastname or companyname.
3) The phone number being searched in any of the phone number fields.