Accessing the 3CX database

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/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"

Unsuccessful outgoing calls report

List of unsuccessful outgoing calls to external subscribers who did not pick up the phone


{"query":"SELECT segment_id, segment_start_time, segment_end_time, segment_action_id, segment_type, src_id, src_extended_display_name, src_display_name, src_internal, src_external, src_dn_type, src_dn, src_caller_number, src_participant_id, src_rec_id, dst_id, dst_extended_display_name, dst_display_name, dst_internal, dst_external, dst_dn_type, dst_dn, dst_caller_number, dst_participant_id, dst_rec_id, call_time, call_answered FROM public.call_history_view where segment_action_id = '400' and src_dn_type = 0 order by segment_start_time desc",
"count":"2"
}

Answer


{
    "result": [
        [
            "4292",
            "6/13/2023 7:37:12 AM",
            "6/13/2023 7:37:29 AM",
            "400",
            "1",
            "6022",
            "Charles Dickens (101)",
            "Charles Dickens",
            "True",
            "False",
            "0",
            "101",
            "Ext.101",
            "6023",
            "",
            "6023",
            "0629759787",
            "0629759787",
            "False",
            "True",
            "13",
            "0629759787",
            "0629759787",
            "6024",
            "",
            "00:00:17",
            "False"
        ],
        [
            "4282",
            "6/12/2023 6:39:29 PM",
            "6/12/2023 6:39:36 PM",
            "400",
            "1",
            "6006",
            "Charles Dickens (101)",
            "Charles Dickens",
            "True",
            "False",
            "0",
            "101",
            "Ext.101",
            "6007",
            "",
            "6007",
            "0629759787",
            "0629759787",
            "False",
            "True",
            "13",
            "0629759787",
            "0629759787",
            "6008",
            "",
            "00:00:07",
            "False"
        ]
    ]
}

Calls lost in the voice menu (IVR)

The list of subscribers whose calls were completed at the stage of the voice menu


{"query":"SELECT segment_start_time, src_extended_display_name, src_display_name,  src_caller_number,  dst_extended_display_name, dst_display_name, dst_caller_number, call_time FROM public.call_history_view where segment_action_id = 5 and dst_extended_display_name like '%IVR%' order by segment_start_time desc", 
"count":"2"
}

Answer


{
    "result": [
        [
            "6/12/2023 5:07:19 PM",
            "381629759787:RS (381629759787)",
            "381629759787:RS",
            "381629759787",
            "IVR (809)",
            "RS Welcome",
            "Ext.809",
            "00:00:22"
        ],
        [
            "6/12/2023 5:04:29 PM",
            "381629759787:RS (381629759787)",
            "381629759787:RS",
            "381629759787",
            "IVR (809)",
            "RS Welcome",
            "Ext.809",
            "00:00:33"
        ]
    ]
}

The main 3CX DB Views

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.