Creating custom records and archiving Call Detail Records was once a difficult task. However, 3CX Exporter has now made the job easy for you. Moving Call Detail Records from 3CX to SQL Server or MySQL reduces the demands on your 3CX server, and allows you to use the tools you already know to create custom reports.
1.1. What is 3CX Exporter?
3CX Exporter automates the export of Call Detail Records (CDR) from 3CX’s internal PostgreSQL database to either Microsoft SQL server or MySQL. This small Windows service runs automatically in the background to keep your external database in sync with the data in 3CX.
1.2. Why 3CX Exporter?
1. Make it possible to create custom reports.
2. To integrate (utilize) that data in other applications (like CRM integrations).
1.3. How to use 3CX Exporter?
3CX Exporter is a onetime installation process. Once you install it, the product will automate the export of Call Detail Records according to the export frequency you set during the installation process. However, in future, you can change both the export frequency and database as well.
Before installing 3CX Exporter, you need to ensure that your computer meets the minimum requirements mentioned below:
1. 3CX Version 15.5 – 16
2. Windows 10 or above
3. Microsoft SQL Server 2014 or above (Including the free SQL Express edition), or MYSQL
4. Windows Server 2012 (64 bit) or above
5. .Net 4.7.2
6. If you are installing 3CX Exporter in a server other than 3CX Server, then you should install ‘C++ 2010 redistribution package (X86)’ in the 3CX Server. You can install it from the link: https://my.visualstudio.com/Downloads?pid=963
7. 3CX Relay Service
8. Installation on a domain controller may require additional assistance
You can order 3CX Exporter on-line at http://www.voiptools.com. Your order confirmation email will include your license key and a link to download the software.
There are a number of prerequisites that must be installed prior to installing 3CX Exporter. These prerequisites include Microsoft SQL Server.
Install 3CX Exporter
This User Guide contains step-by-step instructions to successfully install 3CX Exporter. Begin by extracting the installer from the downloaded zip file (Step 1 above), then run the setup program named 3cxExporterInstaller.exe
Register your License Key
You must register your license key to activate 3CX Exporter. Your license key is included in your order confirmation email, and can also be found in your order history on our website.
The following section guides you through a detailed walk-through of the installation process.
Step 1: Download 3CX Exporter
You can check for the software download link included in your order confirmation email. As an alternative, you can also find the link from your order history on our website.
Step 2: Extract the installer from the downloaded .zip file
The file you downloaded in the above step is a compressed .zip file.
1. Position the mouse on the zip file and right-click.
2. From the popup menu, select Extract All to extract the software.
Step 3: Start the installation process
1. From the extracted folder, find the file 3CXExporterInstaller.exe
2. Double-click the file to start the VoIPToys Setup Wizard
3. Click Next to continue.
Step 4: Download 3CX Relay (if required)
3CX permits one 3rd party application to communicate with the 3CX server at a time. To overcome this limitation, VoIPTools created the 3CX Relay Service. The Relay is a lightweight application that is installed directly on the 3CX server. The Relay exposes the 3CX Call Control API (and other VoIPTools services) to all our applications, and manages requests and events between by VoIPTools products and 3CX.
The Relay also makes it possible to install our VoIPTools products on a remote server (including over the internet).
1. If you have not previously installed the Relay on your 3CX Server, you can download the Relay installer by clicking on the Download Relay button. The Relay must be installed directly on the 3CX server.
2. If you are installing 3CX Exporter directly on the 3CX server, you can install the Relay simply by clicking the Install/ Upgrade Relay button and following the Relay install instructions.
Step 5: Test Relay Settings
1. In the Relay Type drop down, select Legacy Relay for old Relay or New Relay for new Relay based on the 3CX version of your application.
2. In the Relay Services Host field, enter “localhost” if installing ‘3CX Exporter’ on the 3CX server. If 3CX resides on a different server, enter the IP address or FQDN of the 3CX server.
3. In the Relay Services Port field, enter the value as 8700 for Legacy Relay or 8800 for New Relay.
4. Click Test Relay Settings to confirm ‘3CX Exporter’ can communicate with the Relay services running on the 3CX server.
5. If the ‘3CX Exporter’ is successfully able to communicate with the Relay services that run on the 3CX server, a Success message box appears. Click OK.
6. Click Next to continue the installation.
Step 6: Run the application
Once the installation is complete, click Run Application
The 3CX Exporter Settings configuration screen enables you to register software, setup the database, set export frequency value, get 3CX Settings, and start/stop 3CX Exporter service.
5.1. License tab
Registration is required to authorize your license key. Registering also makes it possible for VoIPTools to provide a replacement key if your license key is ever lost. Also, it enables VoIPTools to notify you of any product updates, and reminders if your key is reaching the renewal date.
1. Click License tab and enter registration information.
2. Click Register
3. Confirmation message box appears. Click OK.
5.2. SQL tab
The SQL tab enables you to setup the database.
1. In the SQL tab, select the radio button of appropriate server.
2. For MySQL Server, the database will be automatically setup based on the credentials provided.
3. For SQL Server, click Setup
b. Enter the login credentials.
i). If using Windows integrated security, select Use Windows Authentication. The VoIPTools installer will create a local windows user name as “VoIPToys” and will give sufficient rights to this user to create / update the SQL database.
ii). If SQL Server authentication is desired, select Use SQL Server Authentication. Be sure to select a SQL Login that has sufficient permissions to alter the database and create SQL objects. Enter the User name and Password, and select appropriate database value from the Select or enter a database name drop down.
c. Click Test Connection button to confirm you can connect to SQL
d. Click OK to close the test results dialog
5.3. Export tab
In the Export screen, you can setup the exporting of tables. From here, you can select and export the desired tables from the list. Exporting of tables from ‘3CX DB’ to ‘Exporter DB’ will take place in 3 scenarios:
(i) Based on the setup export frequency
(ii) When you click Export button
(iii) When you start 3CX Exporter service.
You can also truncate desired table(s) by checking the ‘Truncate’ check box for the desired table(s) and then by clicking the ‘Save’ button. By doing so, all the data in the truncated table(s) will be deleted (meaning, the truncated table(s) are now empty) in the Exporter db. For those tables where the data changes (rather than simply adding historical transactions), the truncate feature deletes the existing records and re-imports the records again so that updates are available. This would include the Extensions and Users tables
While exporting the tables from ‘3CX DB’ to ‘Exporter DB’, if you have selected truncated tables along with tables that you have not truncated, then the insertion of data from 3CX DB to Exporter DB is as follows:
(i) For truncated tables, the export of data will be an insertion of existing data (as in 3CX DB) to the empty table.
(ii) For tables that are not truncated, the export of data will be an insertion of new data (since last export) to the existing data in the table.
1. In the Export tab, set Export frequency (in seconds) value. This is the frequency for automatic synchronizing of updates from 3CX database to Exporter database.
2. Check the desired export tables that you want to delete.
3. If you want to truncate any table, then check the Truncate check box for the desired table(s).
4. Click Save
5.4. CDR Settings tab
Enter Domain Name, User Name and Password if the 3CX Exporter and PBX are on different servers, so that the web server (IIS) can access the Call Detail Records (CDR) and update the database.
1. To process CDR files, check Process CDR Files check box.
2. In the CDR Settings section, and in the Cdr Path field, enter the path where the cdr files should be stored in 3CX Console.
3. In the Delete Policy section, check the Delete processed CDRs older than check box if you want to enable the delete policy.<.p>
4. In the Days field, enter the number of days so that the processed cdr files older than the specified number of days will be deleted automatically at the scheduled Delete at timing.
5. In the Delete at time box, enter the time using up and down arrows so that the scheduler will delete the processed cdr files older than specified number of days at the scheduled time.
In addition to exporting data directly from the 3CX PostgreSQL database, Exporter can also export Call Detail Records (CDR) created by 3CX as text files. Instructions for configuring 3CX to create these optional files are outlined below. The CDR files contain useful information about every phone call including participants, duration, the call chain, etc. While the CDR files do not include the same level of detail available in the PostgreSQL database, the data structure is far easier to understand. If the CDR files contain all the information you need, it is recommended that you utilize the CDR files rather than accessing the PostgreSQL database.
1. Log in to 3CX Management Console with valid credentials.
2. On the left navigation pane, select Settings from the menu.
3. In the Settings screen, select the $CDR icon to display the CDR Settings screen.
4. Check Generate CDR checkbox
5. Select Separate files for each call from the combo box.
6. Under Configure CDR Output fields section, click Add CDR button.
7. In the Select CDR Output fields to add pop up, you can see a list of CDR Output fields that need to be added to the CDR table.
8. Select all the fields by holding down the shift button and clicking on each field, then press OK to add all the fields.
9. Scroll down to confirm all the new CDR Output fields are added to the list. To know the list of supported output fields and what they mean, click here.
10. Once you have added all the CDR fields, Scroll up to the top of the screen. Next to CDR Settings, press OK to save your changes.
CDR Records – Output Field Definitions
The following are the supported output fields and what they mean:
|historyid||This is the call number – each call has a number, internal to 3CX. It is useful for call tracking.|
|callid||This is the Call ID – the unique identifier of the call.|
|duration||Call Duration time.|
|time-start||Call Start time.|
|time-end||Call terminated time.|
|reason-terminated||Why the call was terminated.|
|from-no||Source caller number.|
|to-no||Destination To Number.|
|from-dn||Distinguished name of caller – this is 3CX Specific Can be omitted by 3rd party developers.|
|to-dn||Distinguished name of To – Destination – this is 3CX Specific Can be omitted by 3rd party developers.|
|reason-changed||The Reason why a change occurred in a call – example in a transfer you will see “ReplacedDst” which means that the destination was replaced with something else.|
|final-number||Final Number – the final number which was replaced (In a transfer this will be the final connected number).|
|final-dn||The Distinguished name of the final number entity – this is 3CX Specific and can be omitted by 3rd party dev.|
|bill-code||Billing code dialed after the destination (for example: **Sales).|
|bill-rate||The billing rate that matched the prefix of the rate. This is used to apply a cost calculation variance.|
|bill-cost||The Cost of the call calculated with simple proportion / minute.|
|bill-name||The rate name of the billing variance.|
|chain||All endpoints which were involved in the call.|
|final-type||Final destination type.|
|from-dispname||Source Display Name.|
|to-dispname||Destination Display Name.|
|final-dispname||Final destination Display Name.|
|missed-queue-calls||Number of missed calls for an extension in a queue.|
Once 3CX has been configured to generate CDR files, you need to configure Exporter to process the files and import the data into SQL or MySQL.
1. Start 3CX Exporter
2. Select the CDR Settings tab
3. Select the Process CDR Files check box
4. Press Save to save your changes
5. When asked whether to restart the service, chose Yes to restart the Exporter service
1. Connect to database server.
2. Under Object Explorer navigation pane, expand Databases, and then expand 3CX Exporter and Tables to see various tables.
3. Right click on any table to see the right click menu
4. Choose Select Top 1000 Rows to view data in the workspace area.
To view the exported data in Microsoft SQL Server database, install the SQL Server Management studio and follow the instructions below:
1. Connect to Microsoft SQL Server database server with valid credentials./p>
2. Under Object Explorer navigation pane, expand Databases, and then expand 3CX Exporter and Tables to see dbo.cdr table. You will see that the new CDR Output fields that you just added through 3CX Management Console are now added to the dbo.cdr table in the database server./p>
Figure 21: New CDR Output fields added to dbo.cdr table in the database server
Visit our website to access our on-line manuals and receive quick answers to most of your questions. Otherwise, you can contact us by phone, email, live chat and on the VoIPTools forums.
Our team is ready to assist you with answers to your technical questions, during normal business hours, which is Monday through Friday, 8:00 AM to 6:00 PM (Central Standard Time), and 8:00 AM to 12:00 AM (Indian Standard Time).
• Windows Event Log (Applications and Services ? VoIPTools)
The Windows Event Log lists various errors, information messages and warnings related to all our VoIPTools applications and services. The data here can be used for troubleshooting various application errors.
Our forum is intended for VoIPTools customers to collaborate on real-life challenges, solutions and best practices. You can participate on our forum at: https://www.voiptools.com/community/
• Submit a Bug
If you suspect a bug in any of our VoIPTools products, you can bring it to our notice by reporting your issue at: https://www.voiptools.com/report-a-bug/
• Live Chat
Our chat support team is ready to acknowledge your queries in real-time. You can start a live chat with us by simply clicking on the Live Chat button at the lower right corner of our web page.
You can email us at: firstname.lastname@example.org. Be sure to describe your issue in detail. We’ll get back to you within one business day of receiving your request.
You can call us at +1 801-642-4655 to reach a representative. VoIPTools will accept calls at this number from 8:00 AM to 6:00 PM (Central Standard Time).
• Support Services
If you purchased our products through a VoIPTools Partner, your Partner will provide first level support. Partners have access to 100% free technical support, provided they participate actively in the troubleshooting process.
VoIPTools customers have access to our on-line manuals and we will respond to emails (next business day) and live chat, but we will not login to your servers remotely.
If you purchase “Installation Support’ our support team can login to your servers and install and configure all our tools for you. This includes installing SQL Server Express and Internet Information Server if required. Our team will work with you to configure our applications to best meet your needs.
Whether through one of our commercial products, or a custom solution built to meet your specific needs, we can help you get the most out of your 3CX investment. Call us today!Contact Us