3CX Exporter User Guide

1.Introduction #

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.

1.4. When do you need to install the Exporter Proxy?

If you are installing the Exporter application on a separate server from the 3CX server, then you need to install the Exporter Proxy.

To know how to install the Exporter Proxy, click here

2.Prerequisites #

Before installing 3CX Exporter, you need to ensure that your computer meets the minimum requirements mentioned below:

1. 3CX Version 15.5

2. Windows 10 and above

3. SQL Server 2008 or above, or MYSQL

4. Windows Server 2012 (64 bit) and above

5. .Net 4.5

6. 3CX Relay Service

7. Installation on a domain controller may require additional assistance

3.Installation #


Download software

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.

 


Install Prerequisites

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.

  • When installing SQL Server, we recommend you install it in “Mixed Mode” to include SQL Authentication, and install SQL Management Studio
  • When installing IIS, be sure to install Basic Authentication, .Net Extensibility, and ASP.Net 4.X

4.Step by step Installation Procedure #

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.

If you are installing the Exporter application on a separate server from the 3CX server, then you need to install the Exporter Proxy.

To know how to install the Exporter Proxy, click here

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.


Figure 1: 3CX Exporter Setup Wizard

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.

If you have already installed the Relay, you need not install it again unless a newer version is required.

Step 5: Test Relay Settings

1. 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.

2. In the Relay Services Port field, enter the value as 8700

The default port number is 8700.

3. Click Test Relay Settings to confirm 3CX Exporter can communicate with the Relay services running on the 3CX server


Figure 2: Test Relay Settings

4. Click Next to continue the installation.


Figure 3: Installation In Progress

Step 6: Run the application

Once the installation is complete, click Run Application


Figure 4: Run Application

5.3CX Exporter Settings #

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


Figure 5: Register Software

3. Confirmation message box appears. Click OK.


Figure 6: Success message box

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.


Figure 7: Setup Database

3. For SQL Server, click Setup

a. In the Connection Properties dialog box, enter the SQL server name. If using the free SQL Express edition, the default SQL Instance name is <your-server-name>\SQLExpress.

    • If you want to use ‘SQL Authentication’, then install SQL Server in ‘Mixed‘ mode.

 

  • If SQL Server is installed on a server other than the server where 3CX Exporter is installed, you will need to configure SQL Server to allow remote connections


Figure 8: Connection Properties

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.


Figure 9: Windows Authentication

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.


Figure 10: SQL Server Authentication

The first time you install 3CX Recording Manager , DO NOT enter a database name. Choose Select or enter a database name and leave the database name blank. The installer will create the SQL database automatically and apply the necessary permissions. If upgrading 3CX Recording Manager from a previous version, select the existing database from the list.

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

1. In the Export tab, set Export frequency value. This is the frequency for automatic synchronizing of updates.


Figure 11: Setup Export Frequency

2. Click Save

5.4. 3CX tab

The 3CX tab enables you to get PBX Settings for 3CX Exporter.

1. In the 3CX tab, if you have installed 3CX Exporter on a server other than 3CX server then:

a. In the Server field, enter the IP address of the Exporter Proxy installed server.

b. In the Port field, enter the port number of the Exporter Proxy installed server.

c. In the Database field, enter the database name associated to the Exporter Proxy installed server.

d. In the Username and Password fields, enter valid credentials to log into the database that you have just mentioned.

2. If you have installed 3CX Exporter on 3CX server, then click Get Settings


Figure 12: Get PBX Settings

3. In the Consider message box, click Yes


Figure 13: Consider dialog box

4. Click Test to test PBX Settings


Figure 14: Test PBX Settings

4. Click OK


Figure 15: Success message box

5. Click Save

4. Restart the service. Click Yes

5.5. 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.


Figure 16: Configure CDR Settings

Instructions

1. Process CDR Files

a. To process CDR files, check Process CDR Files check box.

2. Configure CDR Path

a. If 3CX Exporter is installed on 3CX server then, configure the CDR path (Actual Path) as: C:\ProgramData\3CX\Instance1\Data\Logs\CDRLogs.

b. If 3CX Exporter is installed on a server other than 3CX server then, configure the shared path of ‘CDRLogs’ folder of 3cx server

3. Windows Authentication

If the server where ‘3CX Exporter’ is installed and the 3CX server both participate in the same Active Directory (AD) domain, you can enter an AD domain account. However, if both servers are not participating in the same AD domain, you will need to provide credentials for a local windows user that exists on the 3CX server.

• Active Directory User

Under Windows Authentication section, if you check Active Directory User check box, you will need to perform several tasks:

a. Change the 3CX Exporter IIS Application Pool login user.

b. Recycle the IIS Application Pool and Website.

c. Change the 3CX Exporter Windows Service login user.

d. Grant the user “Login as a service right”.

e. Restart the 3CX Exporter service.

Domain Name Windows Domain Name
User Name Windows Domain User Name
Password Windows User Password

• Remote Local User

If you check Remote Local User check box, a ‘Net Use’ command connects to the remote shared folder using the supplied credentials of a local user that exists on the 3CX server.

Unlike the “Active Directory User” option, this option will NOT make changes to the ‘3CX Exporter’ Windows service account or change the IIS application pool authentication.

Domain Name 3CX Server Name
User Name 3CX Server-Local User Name
Password 3CX Server-Local Password

6.Call Detail Records (CDR) Files #

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.

6.1.Configure 3CX to Automatically Create CDR Files #

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.


Figure 17: Settings menu

4. Check Generate CDR checkbox

5. Select Separate files for each call from the combo box.

It is important that you configure 3CX to save each call as an individual file.

Figure 18: Configure 3CX to automatically create CDR files

6. Under Configure CDR Output fields section, click Add CDR button.


Figure 19: Add CDR

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.

Be sure not to remove any fields from the list or change the order of the fields. Exporter expects all the fields to be exported in the default order. Therefore, order is important. Below is a list of all the fields in the proper order.

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.


Figure 20: Default Order of CDR Fields

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.


Figure 21: CDR Settings – Generate CDR

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-answered Answered 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.
dial-no Dialed number.
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.
from-type Source type
to-type Destination type.
final-type Final destination type.
from-dispname Source Display Name.
to-dispname Destination Display Name.
final-dispname Final destination Display Name.

6.2.Configure Exporter to Process CDR Files #

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


Figure 22: CDR Settings screen

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

Exporter will place the CDR data extracted from the 3CX CDR files into the table named cdr.

7.View 3CX Database Records #

1. Connect to database server.

2. Under Object Explorer navigation pane, expand Databases, and then expand 3CX Exporter and Tables to see various tables.


Figure 23: Object Explorer

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.

8.View Data in Microsoft SQL Server Database #

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 24: New CDR Output fields added to dbo.cdr table in the database server

9.Installation Procedure for Exporter Proxy #

If you are installing the Exporter application on a separate server from the 3CX server, then you need to install the Exporter Proxy.

Exporter Proxy has to install 3CX Server

Step 1:Create required folders and sub-folders

1. Navigate to the path: C:\Program Files\VoIPToys and create a sub-folder 3CX Exporter

2. Copy the file Exporter Proxy.zip from Installer

3. Navigate to the path: C:\Program Files\VoIPToys\3CX Exporter and paste the Exporter Proxy.zip file that you have just copied from the Installer and extract here.

Step 2:Run the .exe file

1. From the project setup files, look for ExporterProxyConfig.exe and run the file.


Figure 25: Run ExporterProxyConfig.exe

3. In the 3CX Exporter Proxy Setup window, click Install.


Figure 26: Install 3CX Exporter Proxy

4. Once the ‘3CX Exporter Proxy’ service is created in the 3CX Server, Complete message appears. Click OK.


Figure 27: Complete message box

5. On your desktop, go to Run and type services.msc and then click OK.


Figure 28: Services.msc

6. From the Services (Local) list, select 3CX Exporter Proxy and then click Start the service.


Figure 29: Start 3CX Exporter Proxy Service

Step 3: Enter Server & Port Details Manually

1. On the 3CX Exporter Settings configuration page, in the 3CX screen and in the Server field, enter IP address of the Exporter Proxy server.

2. In the Port field, enter 9999

3. In the User and Password fields, enter the details as mentioned in the 3CX Exporter Proxy Setup window (Figure: 26).


Figure 30: Enter Server and Port Details

4. Click Save to save the data.

10.Support #

If you need support, would like to request an enhancement to 3CX Exporter, or are interested in discussing a custom solution, you can contact as at the following:

Address:   2306 Shimmering Bay Lane, Cincinnati, OH 45244-2760

Hours:       Monday – Friday (8:00 A.M. – 6:00 P.M. Central Standard Time)

Phone:      USA: +1 801-642-4655

Skype:       VoIPToys

Email:        info@VoIPTools.com

Web:          http://www.VoIPTools.com

Suggest Edit

Get the Best Solution for your Business

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