Call Journaling - CRM Integration ( Database Postgresql )

[pierremueller]

Hello,

I am trying to log all the calls into a dedicated postgresql database for monitoring purposes.

My dedicated PostgresQL database is empty and contains 2 tables (calls, contacts).

From Settings -> Integrations -> CRM ( following this guide https://www.3cx.com/docs/sql-database-pbx-integration/) I have generated following template ( please see the attached data.xml)

The problem is I am not able to test it, nor any calls are being added in my database.

Is there anything I am missing? I do not want to create any contacts in my database. All I want is just to create entries with all calls with fields including ( call duration, agent, date .. ) in my dedicated PostgresQL DB.

Here is the error I receive when I try to test:


Here is my configuration:

 

[Evolute IT]

You need to configure the Lookup by Number query.

 

[pierremueller]

You need to configure the Lookup by Number query.

I have added following query:

SELECT id as contactid, first_name as firstname, last_name as lastname, email as email FROM contacts WHERE phone LIKE '%[Number]%'

Now, when I run the test ( with a phone number taken from my Users panel) I receive this message:

Started scenario with empty id
Request text: ConnectionString: Server=host_naem;Port=5432;Database=3cx;User Id=postgres;Password=SUPER_SECRET_PASSWORD
Statement: SELECT id as contactid, first_name as firstname, last_name as lastname, email as email FROM contacts WHERE phone LIKE '%01228910348%'

Response text: [{"contactid":2,"firstname":"First","lastname":"Name","email":"[email protected]"}]
Response finished
Variable ContactID set to 2
Variable FirstName set to First
Variable LastName set to Name
Variable CompanyName set to
Variable Email set to [email protected]
Variable PhoneMobile set to
Variable PhoneMobile2 set to
Variable PhoneHome set to
Variable PhoneHome2 set to
Variable PhoneBusiness set to
Variable PhoneBusiness2 set to
Variable PhoneOther set to
Variable FaxBusiness set to
Variable FaxHome set to
Variable Pager set to
Variable PhotoUrl set to
Finished scenario with empty id

Neverthless, I see no records in my `calls` table.
And do I must create contacts in my DB for all the phone numbers I have in 3CX?

As I said, my end goal is ONLY to record the call time.

 

[Nina Pashinina]

the output you posted is from 3CX-CRM-Test- Window - the call journaling scenario will be executed only after the call is ended. It's not possible to test call journaling with 3CX-CRM-Test-Tool.

 

[pierremueller]

the output you posted is from 3CX-CRM-Test- Window - the call journaling scenario will be executed only after the call is ended. It's not possible to test call journaling with 3CX-CRM-Test-Tool.

Thank you for your answer, I understand that.
However, this integration has been configured since yesterday, and modified today couple of times.
In all this time, my colleagues have used 3CX to call and to answer calls from our company's customers, therefore I expect I should already have some calls logged, but nevertheless, my 'calls' table is still empty.

Any idea what might have went wrong?

 

[Nina Pashinina]

I have added following query:

SELECT id as contactid, first_name as firstname, last_name as lastname, email as email FROM contacts WHERE phone LIKE '%[Number]%'

Now, when I run the test ( with a phone number taken from my Users panel) I receive this message:

Started scenario with empty id
Request text: ConnectionString: Server=host_naem;Port=5432;Database=3cx;User Id=postgres;Password=SUPER_SECRET_PASSWORD
Statement: SELECT id as contactid, first_name as firstname, last_name as lastname, email as email FROM contacts WHERE phone LIKE '%01228910348%'

Response text: [{"contactid":2,"firstname":"First","lastname":"Name","email":"[email protected]"}]
Response finished
Variable ContactID set to 2
Variable FirstName set to First
Variable LastName set to Name
Variable CompanyName set to
Variable Email set to [email protected]
Variable PhoneMobile set to
Variable PhoneMobile2 set to
Variable PhoneHome set to
Variable PhoneHome2 set to
Variable PhoneBusiness set to
Variable PhoneBusiness2 set to
Variable PhoneOther set to
Variable FaxBusiness set to
Variable FaxHome set to
Variable Pager set to
Variable PhotoUrl set to
Finished scenario with empty id

Neverthless, I see no records in my `calls` table.
And do I must create contacts in my DB for all the phone numbers I have in 3CX?

As I said, my end goal is ONLY to record the call time.

call journaling will be executed only for found contacts or for created contacts respectively.

 

[pierremueller]

call journaling will be executed only for found contacts or for created contacts respectively.

in my dedicated PostgresQL database, in the `contacts` table, I have added as contacts, my colleagues for which I need to log the calls for.
but that is not probably the correct way, right?
what I would be aiming for, is a way to save all calls (like in Reports -> Call log) in my dedicated DB ( see screenshot attached)

do you know how I could do this, is this something i can achieve with the CRM Database Integration?

 

[Nina Pashinina]

you'd better use Reports for this purpose, there is a possibility to schedule the calllog report, so you can download a .csv and process it the way you need.

 

[pierremueller]

you'd better use Reports for this purpose, there is a possibility to schedule the calllog report, so you can download a .csv and process it the way you need.

yes, we have been using reports so far but we would like to have some monitoring in real-time, and not something which gets updated once per day / half a day.

 

[Nina Pashinina]

than you have to implement lookup and create scenarios https://www.3cx.com/docs/crm-template-xml-description/#h.sby5dun21u5a
P.S. Contacts are not your colleagues, but your customers.

 

[Evolute IT]

yes, we have been using reports so far but we would like to have some monitoring in real-time, and not something which gets updated once per day / half a day.

DM me. Might have a solution.

 

[Evolute IT]

@edossantos Is the ReportCall in database scenarios executed without an EntityId? I don't see a condition in the XML for it.

 

[edossantos]

@edossantos Is the ReportCall in database scenarios executed without an EntityId? I don't see a condition in the XML for it.

Yes, ReportCall is executed, and then the template can skip it in case there is no EntityId. But this is up to the template.

 

[pierremueller]

than you have to implement lookup and create scenarios https://www.3cx.com/docs/crm-template-xml-description/#h.sby5dun21u5a
P.S. Contacts are not your colleagues, but your customers.

could you perhaps give me a bit more details?
I have created a custom template with only ReportCall scenario, but from what I can tell, the variables (Agent, Duration, CallDateTime) are missing.


I also believe that they are being passed through the 'StatementPasses=2' syntax from other scenarios.

Do you know exactly which scenario might output these variables?

 

[pierremueller]

topic can be closed.
Solution was to apply the hotfix update and to use a custom template, with only scenario ReportCall.

Thank you everyone for your help!