CDR / Power BI / MYSQL or PostgreSQL

[Nikala]

Hi all,
Needing some help here.

Im looking to build dashboards for my clients using Power BI and need the CDR sent to my SQL server as the first step. I’ve tried setting this up via CDR and sending to my database but whatever i do, I can’t get the active connection working.

Environment setup:
3cx Linux box, hosted on AWS lightsail. Created a MYSQL database on lightsail also and have also set up a postgresql on lightsail

Things I’ve done:
- set up sql database and columns to match the CDR fields
- configured CDR active connection on 3CXxto go to mysql database set up on AWS lightsail

Disclosure: I’m not a SQL expert and with limit documentation on setting up the SQL side of things. I’m flying very blind.

Anyone able to help with their set up and experience. Open to ideas but not necessarily wanting to be sold a product at this point.

 

[VoIPTools]

I strongly recommend you NOT use the IP socket approach to getting CDR data. If your socket goes down for any reason, the data will be lost and you will not be able to recreate the missing data without a significant amount of work.

Using the file approach ensures that you never lose any data, and you can catch up with transactions that took place while you are updating your service, or restoring internet connectivity, etc.

There are plenty of commercial synchronization tools that can replicate files from your 3CX server to your local server. I have used Filezilla which is free and works well.

Alternatively, you can take a look at our 3CX Exporter tool. Exporter can automatically process the CDR files and place the data into MySQL on a real-time basis. It can also replicate queue statistics, abandoned calls, queue log in/out times, etc. This is data not available via CDR.

If you want a resilient fully automated process, Exporter will do the entire job for you.