Call journaling : [Name] empty since july

[MG_VOIP]

Hi,

We record the calls in a mysql database, via call journaling.

Request :

INSERT INTO journal (CallType, Num_appelant, Nom_appelant, ID_appelant, Num_agent, Nom_Agent, Timestamp_Start, Timestamp_Est, Timestamp_End)
VALUES ('[CallType]', '[Number]', '[Name]', '[EntityId]', '[Agent]','[AgentLastName] [AgentFirstName]', '[[CallStartTimeLocal].ToString("yyyy-MM-ddTHH:mm:ssZ")]', '[[CallEstablishedTimeLocal].ToString("yyyy-MM-ddTHH:mm:ssZ")]', '[[CallEndTimeLocal].ToString("yyyy-MM-ddTHH:mm:ssZ")]')

It was working fine but since July the Name field is empty. All calls are well recorded.
Before, we had the number or Anonymous, or File+number, etc. Why not anymore? Any idea? Any changein 3CX?
Thank you

Log :

2022/10/19 13:41:45.502|23691|0092|Verb|CRM: Triggering Call Journaling with values - CallType='Inbound' - PhoneNumber='+33123456789' - ContactName='' - AgentExtension='007' - AgentFirstName='Machin' - AgentLastName='TRUC' - AgentEmail='[email protected]' - ContactRawData='' - StartTime='19/10/2022 11:41:37' - EndTime='19/10/2022 11:41:45' - EstablishedTime='19/10/2022 11:41:37'
2022/10/19 13:41:45.503|23691|0092|Verb|CRM: Processing scenario 'ReportCall'.
2022/10/19 13:41:45.503|23691|0092|Verb|CRM: Executing SQL Query using ConnectionString 'Server=x.x.x.x;Port=xx;Database=xxxxx;User Id=xxxx;Password=xxxxxx' and Query Statement 'INSERT INTO journal (CallType, Num_appelant, Nom_appelant, ID_appelant, Num_agent, Nom_Agent, Timestamp_Start, Timestamp_Est, Timestamp_End) VALUES ('Inbound', '+33123456789', '', '', '007','TRUC Machin', '2022-10-19T13:41:37Z', '2022-10-19T13:41:37Z', '2022-10-19T13:41:45Z')'.

What we want : we need to know the called number (number, or name or name of the called queues)

 

[Evolute IT]

Hi,

We record the calls in a mysql database, via call journaling.

Request :



It was working fine but since July the Name field is empty. All calls are well recorded.
Before, we had the number or Anonymous, or File+number, etc. Why not anymore? Any idea? Any changein 3CX?
Thank you

Log :


What we want : we need to know the called number (number, or name or name of the called queues)

EntityId is empty as well. Seems like the matching isn't made anymore on the contact.

What's your matching strategy set to in Advanced > Contacts > Options?

 

[edossantos]

It seems there is no matched contact in this case. The [Name] variable contains "The name of the matched contact":
https://www.3cx.com/docs/server-side-crm-template-xml-description/#h.vcivldjsa1lz

The integration with MySQL will log calls even if these are not matched to a database contact.

 

[MG_VOIP]

Exact, EntityId is empty too, but it has always been empty (and not useful). I'll have to clean up one day ;-).

Matching strategy :
- Match at least 5 number of characters

And it work before July.
I check : same number with same contact in db contact -> Name (or number or name+file, etc) before July; empty after July.
No change during this period (No admin here).
I'd have to check if there was a version change.


One test later...
there is at least one change : now it's 6 digits minimum for matching strategy (I tried 4 then to put 5, error each time).
But no change with 6, Name is always empty

 

[edossantos]

The change in the behavior could be related to your PBX being updated from v16 to v18. But in any case, the truth is that now the contact is not being matched, and that's why you don't have the name. What SQL statement are you using for Call Journaling? Are you using a variable with the Contact:: prefix?

 

[MG_VOIP]

if the contacts did not match, there would be no contact name (or queue) displayed on the application (Desktop app or even in the logs), right?

if I understand the question correctly : the statement is the one marked in the initial post.
I I have never seen "Contact::" prefix , do you have an example (or a page explaining it)?
Thnak you

 

[edossantos]

if the contacts did not match, there would be no contact name (or queue) displayed on the application (Desktop app or even in the logs), right?

No contact name. If you receive the call through a queue, the queue name is shown.

if I understand the question correctly : the statement is the one marked in the initial post.
I I have never seen "Contact::" prefix , do you have an example (or a page explaining it)?

This is correct, the statement is fine as it is. Just the contact is not being matched, you need to understand why. You will need to enable verbose logs, make a test call to reproduce the issue, and check the 3cxSystemService.log. There you will see the database query and the results retrieved, and that will give you an idea of why the contact is not being matched.

 

[MG_VOIP]

No contact name. If you receive the call through a queue, the queue name is shown.

But we have contact name, like this exemple. So 3CX managed to find a result that matches, agree?
But Name exported to MySQL is empty.

 

[edossantos]

But we have contact name, like this exemple. So 3CX managed to find a result that matches, agree?

No, this is incorrect. The name can also come from a different source, like the 3CX phonebook. So having a name doesn't mean that the contact lookup in the database is working. Indeed, from the logs you sent, I can confirm that this lookup is not getting any contact. So you need to check the 3cxSystemService.log to understand why.

 

[MG_VOIP]

Oh Ok.
But we haven't any contact database except 3CX phonebook. Neither now nor before
CRM is configured to query CRM only if contact is not found in 3CX Phonebook.
(and that's 99% of your call; for the 1%, we have a Lookup By Number SQL Statement" that always return a result "unknow number" fully completed )

Here is the log :
As un understand "at least 1 phonebook entry already found. Skipping CRM sync..." : that matchs it's enough

2022/10/19 13:41:37.649|23691|0007|Verb|CRM: Not processed. Number=+33123456789, connection=51856, state=Dialing
2022/10/19 13:41:37.649|23691|0007|Verb|CRM: Add to processing. Number=+33123456789, connection=51856, state=Dialing
2022/10/19 13:41:37.649|23691|0007|Info|CRM: ProcessActiveConnections(). Attached entries to connection: 487
2022/10/19 13:41:37.649|23691|0007|Verb|CRM: LocalMatches=487. Number=+33123456789, connection=51856, state=Dialing
2022/10/19 13:41:37.649|23691|0007|Info|CRM: Synchronize(...) called
2022/10/19 13:41:37.649|23691|0070|Info|CRM: maxConcurrency=2
2022/10/19 13:41:37.650|23691|0070|Info|CRM: PhonebookPriorityOptions=NotQueryIfInPhonebookFound, at least 1 phonebook entry already found. Skipping CRM sync...
2022/10/19 13:41:37.650|23691|0070|Verb|CRM: Removed from processing. Number=+33123456789
2022/10/19 13:41:37.650|23691|0070|Info|CRM: processing of +33123456789 has been finished
2022/10/19 13:41:37.725|23691|0007|Verb|Enqueue Updated.CONNECTION.51856
2022/10/19 13:41:37.725|23691|0004|Verb|Dequeue Updated.CONNECTION.51856
2022/10/19 13:41:37.843|23691|0070|Info|Sending PUSH call-started for dn=007, replaces=CvKdeUCUvVBIXSX-6fwELw..;from-tag=25c89d69;to-tag=1c62f27b, callerid=+33123456789, callername=File pub:Test MyNameerso test, callid=16168, image=
2022/10/19 13:41:37.844|23691|0070|Info|Sending PUSH to extension 007 device XXXXXXXXXXX
2022/10/19 13:41:44.888|23691|0004|Info|Sending PUSH call-ended for dn=007, replaces=CvKdeUCUvVBIXSX-6fwELw..;from-tag=25c89d69;to-tag=1c62f27b, callerid=+33123456789, callername=File pub:Test MyNameerso test, callid=16168
2022/10/19 13:41:44.888|23691|0004|Info|Length of PUSH data to be sent to FCM: 518 bytes
2022/10/19 13:41:44.907|23691|0096|Info|Response from FCM: Status: OK, Reason: OK
2022/10/19 13:41:45.501|23691|0007|Verb|Enqueue Deleted.CONNECTION.51856
2022/10/19 13:41:45.502|23691|0092|Info|CRM: about to report 1 calls at maxConcurrency 1
2022/10/19 13:41:45.502|23691|0092|Verb|CRM: Triggering Call Journaling with values - CallType='Inbound' - PhoneNumber='+33123456789' - ContactName='' - AgentExtension='007' - AgentFirstName='MyName' - AgentLastName='My1Name' - AgentEmail='[email protected]' - ContactRawData='' - StartTime='19/10/2022 11:41:37' - EndTime='19/10/2022 11:41:45' - EstablishedTime='19/10/2022 11:41:37'
2022/10/19 13:41:45.502|23691|0004|Verb|Dequeue Deleted.CONNECTION.51856
2022/10/19 13:41:45.503|23691|0092|Verb|CRM: Processing scenario 'ReportCall'.
2022/10/19 13:41:45.503|23691|0092|Verb|CRM: Executing SQL Query using ConnectionString 'Server=x.x.x.x;Port=3306;Database=txxxxx;User Id=xxxx;Password=xxxxx' and Query Statement 'INSERT INTO journal (CallType, Num_appelant, Nom_appelant, ID_appelant, Num_agent, Nom_Agent, Timestamp_Start, Timestamp_Est, Timestamp_End) VALUES ('Inbound', '+33123456789', '', '', '007','My1Name MyName', '2022-10-19T13:41:37Z', '2022-10-19T13:41:37Z', '2022-10-19T13:41:45Z')'.
2022/10/19 13:41:45.519|23691|0096|Verb|CRM: The result of the SQL Query Execution is
'[]' .
2022/10/19 13:41:45.519|23691|0096|Verb|CRM: No matchings found.
2022/10/19 13:41:45.555|23691|0018|Trac|CHR: Receiver.OnReadyToRead()
2022/10/19 13:41:45.556|23691|0018|Trac|CHR: _sock.Receive == 4
2022/10/19 13:41:45.556|23691|0018|Trac|CHR: Received 3757 out of 3757 bytes
2022/10/19 13:41:45.556|23691|0018|Trac|CHR: callOnPacketReceived()
2022/10/19 13:41:45.556|23691|0018|Trac|CHR: Received 3757 bytes, start parsing
2022/10/19 13:41:45.556|23691|0018|Verb|CHR: Adding event: 2022-10-19 13:41:37.648|IncomingCall|00000183F00C8BEC_16168|8a949303c8aa/Participant DN.10000/Line dn-name='Test MyName' epname='+33123456789@(Ln.10000@Trunk FAYAT RENNES)'
>8a949303c8aa/Participant DN.10000/Line dn-name='Test MyName' epname='+33123456789@(Ln.10000@Trunk)'
did_number=+33222222222
disp_name=Test MyName
number=+33123456789
sip.contact=<sip:[email protected]:5070>
sip.from="+33123456789" <sip:[email protected]>;tag=gXD8vtDmXHy3g
sip.rl_uri=sip:[email protected]:5060;rinstance=b8da4f696ab5ad1a
sip.src_addr=x.x.x.x:5070
sip.to=<sip:[email protected]>
sip.user_ag=Sewan_TRUNKFSC13
target=810

 

[edossantos]

Correct, that log:

PhonebookPriorityOptions=NotQueryIfInPhonebookFound, at least 1 phonebook entry already found. Skipping CRM sync...

Means that the lookup in the database will not be made. And then you don't have the details when the call is logged. If you have the contacts in the database, maybe you can configure the integration to run the lookup always, and this way you will have the details you need in the journal table.

 

[MG_VOIP]

So since V18, a CRM contact database is mandatory if we want to write call journal in MySQL?
3CX can't anymore use its own contact information?
This was not the case before and as we do not need it (and don't want it), it is clearly a regression

 

[edossantos]

So since V18, a CRM contact database is mandatory if we want to write call journal in MySQL?

No, this is not mandatory, call logs are still being inserted into the database, however it will not read the name taken from the phonebook here, only the name obtained from contact lookup can be used.

 

[MG_VOIP]

Bad news.

We were using this field to retrieve the queue called and caller name since we have 3CX.
And too late to go back to V16...

The onlyphonebook must be the one of 3CX (no CRM in our company). And to have tested: if 3CX finds the result in the select query, the variable [name] transmitted contains only the name, not the name+queue as before.

Any tips to send to MySQL this information (calling name and called queue) without having a CRM or contact database outside 3CX?
Any chance that it will be possible again?
I can't post in the idea section.

 

[edossantos]

The onlyphonebook must be the one of 3CX (no CRM in our company). And to have tested: if 3CX finds the result in the select query, the variable [name] transmitted contains only the name, not the name+queue as before.

For the queue name there is a new variable in v18: [QueueExtension]

Any tips to send to MySQL this information (calling name and called queue) without having a CRM or contact database outside 3CX?

But you already have the external database, where you are logging the calls. You can just add a "contacts" table to this database, export the contacts from the 3CX phonebook, and import them there. I don't see a simpler solution than this.

 

[MG_VOIP]

"Just" adds a contact table implies :
- the need to maintain this list permanently (IN ADDITION to the list actually used in 3CX, and necessarily by a MySQL administrator unless you are doing development). Or 3CX pehaps offer an automatic 3cx Phonebook -> MySQL "contact" table sync but I doubt it? Just that, it's clearly a NO-GO.
- the need to find solutions for number formats (our contacts in the 3CX book are in the format "11 22 33 44 55", 3CX manages to associate them with incoming numbers whether they are +33122334455 or 0122334455; tested with MySQL, it doesn't always work)
- The loss of the information on the called queue... thanks for the information on the new field, I'll look into it (if there are other new variable, I'm interested Thank you!)

Really, 3CX was very efficient and appreciable on this point, too bad to lose that.

 

[edossantos]

- the need to find solutions for number formats (our contacts in the 3CX book are in the format "11 22 33 44 55", 3CX manages to associate them with incoming numbers whether they are +33122334455 or 0122334455; tested with MySQL, it doesn't always work)

If this is not working, that's because you're executing a wrong SQL query. For example, if you check for "equal" you will need to match the number exactly as it is stored in the database. On the other hand, you could use a "like" query instead, so you check for numbers "ending with". This is totally configurable in the query you define.

- The loss of the information on the called queue... thanks for the information on the new field, I'll look into it (if there are other new variable, I'm interested Thank you!)

Here you have all the available variables:
https://www.3cx.com/docs/server-side-crm-template-xml-description/#h.vcivldjsa1lz