3CX Custom SQL reports

3CX uses PostgreSQL for saving all data.

For getting access 3CX PostgreSQL:
  • Windows: C:\Program Files\3CX Phone System\Bin\config.json
  • Linux: /var/lib/3cxpbx/Instance1/Bin/3CXPhoneSystem.ini

By default access granted only from localhost (127.0.0.1).

"DbHost": "127.0.0.1",
"DbPort": "5480",
"DbUser": "phonesystem",
"DbPassword": "XZg9CE3GJRCfh",
"DbName": "database_single"
callcent_queuecalls_view 

SELECT q_num, time_start, time_end, ts_waiting, ts_polling, ts_servicing, ring_time, reason_noanswercode, reason_failcode, call_history_id, from_userpart, from_displayname, to_dn, cb_num, is_answered, is_callback
    FROM public.callcent_queuecalls_view order by time_start desc;
Selecting the last 50 conversations that have passed through any of the queues:

SELECT * FROM callcent_queuecalls ORDER BY idcallcent_queuecalls DESC LIMIT 50
Selection of calls for a week with grouping by queue:

select
    count(*) as sum,
    dst_display_name
from(
  select
  distinct (c.call_id), c.dst_display_name
  from public.cl_segments_view c
    where cast(c.start_time as date)> (now() - '7 days'::interval)
     and (c.dst_display_name in (select name FROM public.queue ) )

  limit 100) as sub

  group by dst_display_name
Selection of 3CX records

SELECT id_recording, cl_participants_id, recording_url, start_time, end_time, transcription, archived, archived_url, call_type
	FROM public.recordings
	order by start_time desc;
3CX strongly discourages accessing the database and using any SQL queries. All examples are given for academic purposes.