New Call Data Records - A Deep Dive

[nikolascx]

Major upgrade to Call Data Records (CDR) System in 3CX.​

Following on from yesterday’s V20 Update 6 Alpha release, this post explains the new Call Data Records (CDR) system in 3CX where we’ve streamlined CDR into a single table: cdr_output. We break down the old structure, reveal the new design, and spotlight the technical wins that ar...

Continue reading the Original Blog Post.

 

[rlg]

Really good news !
One question, will the CDR log files (available in /var/lib/3cxpbx/Instance1/Data/Logs/CDRLogs/) also have their output format updated ?
Or will they remain the same ?
Thank you !

 

[nikolascx]

Really good news !
One question, will the CDR log files (available in /var/lib/3cxpbx/Instance1/Data/Logs/CDRLogs/) also have their output format updated ?
Or will they remain the same ?
Thank you !

Hi thanks for your question
We have not updated the CDR log files output. Remains as it is for now. Configurable via Advanced > CDR

 

[Gioal]

Hi,
So I can connect a BI software on 3CX DB and create reports directly from a simple table. Am I correct?

 

[nikolascx]

Precisely..
This will be our next target. We will be working on a bunch of templates with popular BI tools.
Look for a table named "cdroutput". that is the main table
The new tables are the ones below.



Feel free to share your progress and any issues you encounter..

 

[illcsales]

What happened to the historical call data after upgrade to Alpha, we only have one day of calls in both incoming and outgoing reports even when selecting year? seems odd that the report can't look back before the upgrade will this be fixed or is it related to the new table structure?

 

[jed]

What happened to the historical call data after upgrade to Alpha, we only have one day of calls in both incoming and outgoing reports even when selecting year? seems odd that the report can't look back before the upgrade will this be fixed or is it related to the new table structure?

Have a look here: https://www.3cx.com/community/threa...-call-data-records-reports.133024/post-633946

 

[nikolascx]

What happened to the historical call data after upgrade to Alpha, we only have one day of calls in both incoming and outgoing reports even when selecting year? seems odd that the report can't look back before the upgrade will this be fixed or is it related to the new table structure?

Hello Team
Good question
If you query data for this year for example, you should get the following:

When the Reporter engine sees that you have a range that needs to draw data from before and after the update, then it will get the old data from the old tables (as it was doing before) and will craft SQL to create data from the new tables.

After the results are collected, there will be an Order by on the data and you get them on screen.

One might argue that at this point there is no real optimization - Correct - but as we move forward, there will be, because the old data will become less and less queried shifting the reporter engine to draw data from the new tables only. (the optimized ones)

So this works - and you have a screenshot example. Here I do a call logs report and I select "This Year"
For this instance the update was made last last week in preparation for yesterday's release.
You can see that yesterdays calls are displayed together with the old call (from the old tables)



Can you check one more time - maybe there are no calls in the old tables or they were purged.
Thanks for reporting.

 

[illcsales]

I can confirm that if we do an incoming and outgoing call report, there is no data prior to the update just data after.

Tried last month and this year as a filter.

The call log has plenty of information.

No purge was run.

 

[Benedikt Machens]

One might argue that at this point there is no real optimization - Correct - but as we move forward, there will be, because the old data will become less and less queried shifting the reporter engine to draw data from the new tables only. (the optimized ones)

Are you planning to add a migration script in beta or RC, that will move the old sql tables to the new one? Filling the gaps with empty data, so that nobody has to care about the old data structure

 

[nikolascx]

I can confirm that if we do an incoming and outgoing call report, there is no data prior to the update just data after.

Tried last month and this year as a filter.

The call log has plenty of information.

No purge was run.

I can explain.
The reports which have "New" label next to them, draw data only from the new tables. So it is expected.
(We did not want to create new reports and make them take old cdr data.)
Make calls, let it work, and you will see the instant benefits once you get some calls in the new tables..
Also this is part 1. We are now working on some other technology which will be plugged into this process. Stay tuned for more info.

To all Business analysts, BI power users, designers and also SQL devs, stay tuned

 

[nikolascx]

Are you planning to add a migration script in beta or RC, that will move the old sql tables to the new one? Filling the gaps with empty data, so that nobody has to care about the old data structure

Good point - but making migration data for years and years of CDR telephony data is tough.
The complexity of reconciling old/new schemas, filling gaps safely, and handling edge cases (with call chains for each pbx) makes this a high-risk, low-reward task.

Whatever we attempt to do, the chances are that the customer will still complain. So it is a High Risk, with little or no reward.

We are thinking to provide dumps and you can import them in a bi tool and make reports..
Also moving forward, this already "Seen" data will be less and less interesting. We are still early in the year - so 2025 will already have 9 months with new structured data.
For now it is in the database still and you can see it with the reports.So it is accessible.

If we provide you with a function that can create an export of this data in CSV, would that work?
You can then get this csv file and dump it in a BI tool..

 

[triwardhana]

Would it also include new "hold time" at CDR?

 

[nikolascx]

Hi team
We have a clear understanding of "Waiting time" at the moment - but this applies to Queues.
The initial waiting time is recorded but not subsequent Holds.

 

[Benedikt Machens]

If we provide you with a function that can create an export of this data in CSV, would that work?
You can then get this csv file and dump it in a BI tool..

Taking the call_history_view from DB is still an option for BI

In my mind I just want to take the short way to get rid of this old data sheme
Now we have the long way on early 2026, when the yearly reports will be generated. Happily its just a one time problem.

 

[jhn]

Will the changes in call data records affect hotel integration (Fidelio protocol) in any way?
focused on "billing" string pushed from 3CX to Opera when guest makes outbound call from room phone

 

[jhn]

Hi thanks for your question
We have not updated the CDR log files output. Remains as it is for now. Configurable via Advanced > CDR

"Remains as it is for now" - does that imply that it might change in the future?
We use CDRs (configured via "Configurable via Advanced > CDR") for pushing billing data to hotel PMS when using Mitel SX2000 protocol.
If there should be any changes in the CDR log files, we would like to know in advance.
thank you

 

[nikolascx]

"Remains as it is for now" - does that imply that it might change in the future?
We use CDRs (configured via "Configurable via Advanced > CDR") for pushing billing data to hotel PMS when using Mitel SX2000 protocol.
If there should be any changes in the CDR log files, we would like to know in advance.
thank you

Yes Definitely
No changes were made in this section. Everything is the same.
I think we will not need to update it - There is no need. But you can never say.
Nevertheless, we will definitely announce and if changes are required, we will ensure to respect the format. (Anyway we must because the PMS and Mitel format is structured and defined).

 

[jhn]

Yes Definitely
No changes were made in this section. Everything is the same.
I think we will not need to update it - There is no need. But you can never say.
Nevertheless, we will definitely announce and if changes are required, we will ensure to respect the format. (Anyway we must because the PMS and Mitel format is structured and defined).

Thanks Nikolas, appreciate the response

 

[triwardhana]

Hi team
We have a clear understanding of "Waiting time" at the moment - but this applies to Queues.
The initial waiting time is recorded but not subsequent Holds.

Hi Nikolas, just to make sure: when agent press hold button in the queue for the first time, the hold time will be recorded on the report correct? but will not recorded if the agent hold for the 2nd, 3rd at the same call session?