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
- 3CX
- 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.