Getting 3CX Raw Call Detail Data

3CX comes with a number of different ways to get call detail reports which will work fine for most people. However, if you need to do some custom reports that are not included you will need to get the raw data and create your own reports. Once you have the data you can import it into Excel or a tool like Crystal Reports. We aren’t going to go into detail about creating the reports themselves but we are going to show you how to get the raw data into a usable format that you can use however you choose to.

Getting Started

First off you will need a tool that can connect to 3CX’s Postgres database, we recommend pgAdmin (link) since it is free and works quite well. Second, you will need the connection information for the database, this can be found in the 3CXPhoneSystem.ini file which is located in the Program Files folder:
Windows 32 bit
C:Program File3CX PhoneSystemBin3CXPhoneSystem.ini
Windows 64 bit
C:Program Files (x86)3CX PhoneSystemBin3CXPhoneSystem.ini
At the bottom of this file will be a block of data that will look like this:

[CallReports]
USERNAME=logsreader
DATABASE=phonesystem
PORT=5480
DRIVER=PostgreSQL Unicode
ReadOnly=1
SERVER=localhost
PASSWORD=Sk7/6_oWK2=p?Mb84%Jz

We will need the USERNAME, DATABASE, PORT, and PASSWORD information to connect to the 3CX Database.
Once you launch pgAdmin you will need to connect to the database. Click on the connect icon to create the connection profile to the system and enter all of the appropriate information as in this example:

To get to the correct tablesmay be a little confusing at first, the correct tree will look like this:

Servers

Databases

phonesystem

Schemas

public

tables

The two tables that contain cell records are calldetails and callhistory3. Each one is a little different so depending on your needs you may need to use one or the other.

Getting the Data

In the calldetails table, the fields that have the most use to you are going to be:

dest_num = destination number
starttime = call start time
dur = call duration
answertime = time call was answered
status = call status

Connecting = 0
Answered = 1
DestBusy = 2
DestNoAnswer = 3
DestNotAvailable = 4
NotAnswered = 5
Completed = 6
DstUnknown = 7

dest_dn = outgoing port or incoming extension
otherparty = opposite end of the call

For an example, we can run a report for a particular day. For this example I want to see all the records for yesterday. If today is 10/19/2011 I then want anything from the beginning of 10/18 but prior to 10/19 so my SQL Query would look like this:
select starttime,dur,dest_num,answertime,status,is_tooutside,dest_dn,otherparty from calldetails where starttime > ‘2011-10-18’ and starttime < ‘2011-10-19’ order by starttime
This would give me an output that looks like this:

If the data looks good, you can now go to the File menu and export the output as a CSV file.
If we use the table callhistory3 we can get some different information such as

Starttime
Answertime
Endtime
Duration
Is Answered
Is Fail
Is From Outside
From Number
To Number
Caller ID
Dialed Number
Last Dialed Number
Group Number
Port Number

The query for the same time frame would now be:
select starttime, answertime, endtime, duration, from_no, to_no, callerid, dialednumber, lastcallerid, lastdialednumber from callhistory3 where starttime > ‘2011-10-18’ and starttime < ‘2011-10-19’ order by starttime
This query will give us the following data:

Again, you can go to the File menu and export this data as a CSV file.

Summary

Using Excel or Crystal Reports is well beyond the scope of this article but if you know how to use tools like that to create the reports and just needed to get to the raw data, this should put you on the right track.

We would really like to see what creative reports people have come up with or can get using this technique. If you have a report you would like to share, please feel free to post it in the comments or contact your sales representative.