Complete Guide to Call Detail Records (CDR)

Introduction

This guide provides an exhaustive explanation of how the Call Detail Record (CDR) is structured within the cdroutput table, enabling dashboard creation from Grafana and other BI Tools.

Identification & Structure Fields

These fields define the overall call flow and provide unique identification for each CDR.

  • cdr_id
  • Description: A unique identifier for the specific CDR record.
  • Example: 00000000-01db-aef6-c109-60ec0000004c
  • call_history_id
  • Description: A unique identifier for the entire call flow. A call flow comprises a set of CDRs that share the same call_history_id.
  • Example: 00000000-01db-aef6-ba9a-df6b0000000b
  • main_call_history_id
  • Description: Identifier for joined call flows (same as call_history_id if not joined).
  • source_participant_id and destination_participant_id:
  • Description: Identifiers indicating call routing between participants.
  • Example: 00000000-01db-aef6-ba9b-33830000004d

Note: All these identifiers are provided in UUID format, where the first 4 bytes are 0, followed by 8 bytes of UTC timestamp, followed by 4 bytes of internal auto incrementing identifier assigned by the call manager. The internal identifier is reset to 1 on restart.

Relationship & Routing Fields

These fields establish the relationship between CDRs, showing how new CDRs relate to previous ones in the call flow. This is crucial for understanding call transfers, routing changes, and other dynamic call scenarios.

  • base_cdr_id
  • Description: The identifier of the previous CDR (by cdr_id) that is being modified by the current CDR. This links the current CDR to its immediate predecessor in the call flow.
  • originating_cdr_id
  • Description: The identifier of the CDR (by cdr_id) that initiated a new routing branch. Used to trace back the chain of routing requests.
  • continued_in_cdr_id
  • Description: The identifier of the CDR (by cdr_id) of this record's immediate successor in the call flow. Used to trace the chain of routing requests.

Creation Reason Fields

These fields explain why a CDR was created, providing context for the call flow.

  • creation_method
  • creation_forward_reason

Participant Information Fields (Source & Destination)

  • source_entity_type and destination_entity_type
  • Description: Type of participating entity (e.g. extension, queue, IVR, external_line).
  • Possible Values List
  • source_dn_type and destination_dn_type
  • source_dn_number and destination_dn_number
  • Description: Directory Number
  • In the case where entity_type or dn_type is "script", this can contain the dialcode for the Call Processing Script
  • Example: 800
  • source_dn_name and destination_dn_name
  • Description: Directory Name for Queues, Ring Groups, IVRs, Extensions, Trunks
  • Examples: "Marketing Queue" or "John Doe"
  • source_participant_name and destination_participant_name
  • Description: Derived from SIP Display Name headers or from contact lookup; for local entities, this will be equal to source_dn_name or destination_dn_name
  • source_participant_phone_number and destination_participant_phone_number
  • Description: Derived from SIP User headers
  • for local participants, this will be equal to source_dn_number or destination_dn_number
  • for external participants on outbound calls, this will be the called number
  • for external participants on inbound calls, this will be the caller id
  • In the case where entity_type or dn_type is "script", this can contain the dialcode for the Call Processing Script
  • Examples: "1004" (for a local extension), "2055544422" (for an external number)
  • source_participant_trunk_did and destination_participant_trunk_did
  • Description: DID number for inbound trunk calls
  • Note: External participants have two phone numbers: participant_phone_number and participant_trunk_did.
  • Example: "12223334444" as a DID on a trunk
  • source_participant_is_incoming and destination_participant_is_incoming
  • Description: Boolean field indicating the direction of the call for the participant
  • source_participant_is_already_connected and destination_participant_is_already_connected
  • Description: Boolean field indicating whether the participant was already connected at the beginning of the CDR
  • Examples: TRUE if already connected, FALSE if not already connected
  • source_participant_group_name and destination_participant_group_name
  • Description: Department name for the participant; in Multi-Company mode, this is effectively Tenant name
  • source_participant_billing_suffix and destination_participant_billing_suffix
  • Description: Billing details for accounting purposes
  • source_presentation
  • Description: Display name shown to destination; the alternative presentation of the source participant's information (e.g., display name) as it was presented to the destination; important when the source's display name is substituted for the destination.
  • Example: "Jonathan Donovan"

Termination Reason Fields

These fields explain why a CDR was terminated.

  • termination_reason
  • termination_reason_details
  • terminated_by_participant_id
  • Description: Participant who terminated the call.

Call Timestamp Fields

These fields provide timestamps for various stages of the call.

  • cdr_started_at
  • Description: The UTC time when the CDR was initiated.
  • cdr_ended_at
  • Description: The UTC time when the CDR was finished.
  • cdr_answered_at
  • Description: The UTC time when the connection between source and destination was established.

Note: All these timestamp fields are in UTC, provided in the format "YYYY-MM-DD HH:MM:SS.mmmmmm+00". For example: "2025-04-16 17:41:05.348291+00"

System Fields

  • processed
  • Description: Flag indicating if the CDR has been processed.
  • migrated
  • Description: Flag indicating if the CDR has been migrated.
  • offload_id
  • Description: Identifier for external storage offloading.

Possible Values Lists

These fields provide possible values for the fields describing the CDR.

source_entity_type and destination_entity_type

  • Description: defines the type of the entity participating in the call flow;  the entity_type is a superset of the dn_type i.e. it can be specified as any value of dn_type or can be set as the entity which works on behalf of the dn
  • Values:
  • echo_test - the echo test endpoint
  • endcall - this is the entity which represents the destination reported by the call manager when it wants to say farewell message (a.k.a. play busy prompt).
  • extension - internal extension
  • external_line - The destination is external line (trunk) the type is specified in dn_type
  • fax - The participant is leaving a fax
  • inbound_routing - Destination participant for the call_init CDR when a call is initiated by a trunk.
  • ivr - Digital Receptionists
  • outbound_rule - This entity does not have an associated DN
  • paging_group - Ring Group with Paging strategy
  • queue - Queues
  • ring_group_hunt - Ring Group with Hunt strategy
  • ring_group_ring_all - Ring Group with Ring All strategy
  • script - Call Processing Scripts
  • service_call - Set when the call manager implements internal processing of a dialcode
  • unknown - The destination does not exist or is not identified
  • vmail_console - Voicemail special menu
  • voicemail - The participant is leaving a voicemail

source_dn_type and destination_dn_type

  • Description: The type of directory number.
  • Values:
  • bridge - Bridges (Master & Slave Bridges)
  • callback_test - Call Back Test
  • conference - Conference Server
  • echo_test - Echo Test Call
  • extension - Internal Extension
  • specialmenu - System Extension Voicemail
  • fax - Fax includes 3CX Main Fax and other FAX
  • group - Departments
  • ivr - Digital Receptionists
  • paging_group - Ring Group with Paging strategy
  • parking_orbit - Parking Orbits
  • provider - Trunks (Providers & Gateways)
  • queue - Queues
  • ring_group_hunt - Ring Group with strategy hunt.
  • ring_group_ring_all - Ring Group with strategy all.
  • script - Call Processing Scripts
  • shared_parking - Shared Parking
  • unknown - The destination does not exist or is not identified
  • vmail_console - Voicemail special menu

creation_method

  • Description: The action that caused the CDR to be created.
  • Values:
  • call_init - The initial CDR for a new call.
  • pickup - A participant picked up a call.
  • divert - A call was diverted (redirected).
  • route_to - A call was routed to a new destination.
  • transfer - A call was transferred.
  • join - A participant was joined into an existing call (attended transfer).
  • barge_in - A participant barged into a call.
  • fork - A new call flow was created based on base_cdr_id; the CDR of the call flow can be forked into two independent call flows.

creation_forward_reason

  • Description: The reason provided by a Transfer, Route To, or Divert API request. The creation_forward_reason specifies why the participant of originating_cdr_id initiates a new connection.
  • Values:
  • none - No specific reason was given.
  • no_answer - The destination participant did not answer.
  • busy - The destination participant was busy.
  • holiday - The call was redirected due to a holiday.
  • office_time - The call was redirected due to office hours.
  • out_of_office - The call was redirected because the recipient is out of the office.
  • break_time - The call was redirected because the recipient is on break.
  • polling - The CDR is created by trying to poll the agent of the replaces_participant_id in base_cdr_id
  • by_caller_id - Is specified by inbound CallerID routing
  • by_did - Redirection is specified by inbound DID routing
  • no_destinations - when an automated call distribution endpoint such as ring group or queue does not have destinations for polling (all agents are unreachable) this reason is reported instead of no_answer). It provides a distinguished report about “nobody answered”=no_answer, and “nobody was called”=no_destinations
  • user_requested - This reason has no sense, because it is effectively "not specified".
  • forward_all - The call was unconditionally forwarded by the originator.
  • not_registered - Redirection has happened because the originator destination has no registered devices.
  • callback_requested - Queue callback has been requested. This reason is reported by the queue manager when it replaces the caller with a callback entity, (QCB). It is not designated to queue only.
  • callback - The route to the callback number. When an agent accepts a call, the queue is replacing QCB with the callback number requested by the caller.

termination_reason

  • Description: The general reason for the CDR's termination.
  • Values:
  • continued_in - The call flow is continuing in another CDR, indicated by the continued_in_cdr_id.
  • redirected - Used for routing replacements.
  • rejected - Set when the route is rejected by the destination
  • cancelled - The route is canceled by the call manager due to other routing activity
  • src_participant_terminated - Call flow terminated because of source participant disconnection
  • dst_participant_terminated - Call flow terminated because of destination participant disconnection

termination_reason_details

  • Description: Provides additional details about the termination reason.
  • Values:
  • timeout - Call setup failed because no response was received from the destination within the allowed time, often indicated by SIP error code 408 (Request Timeout).
  • deflected - The call was intentionally redirected to another destination, as indicated by a SIP 302 (Moved Temporarily) response.
  • not_found - The system could not locate the called party or resource, typically resulting in a SIP error code 404 (Not Found).
  • not_available - The called party is currently unavailable to take calls, often signaled with SIP error code 480 (Temporarily Unavailable).
  • target_disabled - The intended recipient's extension or device is administratively disabled and cannot receive calls. (Scenario: A calls B - B is a disabled extension)
  • caller_disabled - The originating caller's extension or device is administratively disabled, preventing them from making calls. (Scenario: A calls B - A is a disabled extension)
  • line_busy - All available channels on the outgoing trunk are currently in use, preventing the call from being placed. (Scenario: Attempting to make more outbound calls than the trunk's limit allows)
  • busy - The called party is currently engaged in another call and rejected the incoming call.
  • no_answer - The called party's phone rang for the duration of the timeout period without being answered.
  • terminated_by_originator - The caller ended the call before it was successfully connected.
  • license_limit_reached - The system has reached the maximum number of active licenses, preventing new calls from being established.
  • server_error - A problem occurred on the system's server that prevented the call from being processed. This is often indicated by SIP error codes in the 5xx range (e.g., 500 Internal Server Error, 503 Service Unavailable).
  • forwarding_loop - The call was detected to be in a continuous forwarding cycle, preventing it from ever reaching a final destination.
  • no_destinations - The called destination (like a Ring Group or Queue) has no available members to receive the call. (Scenario: Calling a Ring Group or Queue with no logged-in members)
  • external_call_disabled - The ability to make calls to external phone numbers has been disabled for the calling extension or the entire system. (Scenario: Attempting to dial out after external calls have been disabled on the extension)
  • no_route - The system does not have any defined path or rules to route the call to the dialed number. (Scenario: Dialing a number that doesn't match any outbound rules)
  • destination_prohibited - Calls to the specific dialed number or country are blocked by the system's outbound call restrictions. (Scenario: Dialing a number in a country not included in the allowed country codes)
  • caller_blacklisted - The caller's identification (caller ID) is on a list of blocked numbers, and the call was therefore rejected. (Scenario: Making a call with a caller ID that is on the system's blacklist)
  • feature_disabled - The requested calling feature (e.g., dialing a Queue in a free edition) is not enabled or available on the system. (Scenario: Attempting to dial a Queue when using the Free Edition)
  • pin_is_required - The dialed number requires a PIN for authorization, and none was provided or was incorrect. (Scenario: Trying to dial an external number when PIN protection is enabled without entering a PIN)
  • invalid_dialcode - The dialed number contains an unrecognized or incorrect prefix or code.
  • by_caller_id - The call was automatically terminated based on a predefined rule associated with the caller's identification. (Scenario: A global caller ID rule is configured to end calls based on the caller's ID)
  • by_did - The call was automatically terminated due to a specific exception or condition defined in the system's call handling rules. (Scenario: An exception rule is configured to terminate certain calls)
  • invalid_destination - The dialed number is not a valid or correctly formatted destination. (Scenario: Call forwarding is set to a number in a blocked country with 'Rebound' active, and a call is made to that initial extension)
  • source_line_busy - The specific incoming line (trunk) that the call arrived on has reached its maximum capacity for simultaneous calls. (Scenario: An incoming call arrives on a trunk that is already at its simultaneous call limit)
  • terminated_by_originator - The caller ended the call; the specific reason from the provider was "Not Found," often indicated by a 404 response from the external network.
  • disabled - Outbound calling has been specifically disabled on the trunk being used for the call. (Scenario: Making a call on a trunk where outbound calls are disabled)
  • no_access - The caller does not have the necessary permissions or privileges to make this type of call or access the requested feature, often indicated by a SIP error code 403 (Forbidden).
  • declined - The called party actively refused to answer the incoming call, often signaled by SIP error codes 486 (Busy Here), 600 (Busy Everywhere), or 603 (Decline).

Practical SQL Examples

Agent Performance

1. Average Call Handling Time by Agent in "Marketing" Department

SELECT

    source_participant_name AS agent_name,  -- Or destination_participant_name, depending on your needs

    AVG(EXTRACT(EPOCH FROM (cdr_ended_at - cdr_answered_at))) AS average_handling_time_seconds

FROM cdroutput

WHERE (source_entity_type = 'extension' OR destination_entity_type = 'extension')  -- Assuming agents are extensions

    AND cdr_answered_at IS NOT NULL

    AND cdr_ended_at IS NOT NULL

    AND (source_participant_group_name = 'Marketing' OR destination_participant_group_name = 'Marketing')

GROUP BY agent_name

ORDER BY average_handling_time_seconds;

Dashboard Application: This query calculates the average time an agent from the "Marketing" department spends on a call from the moment it's answered until it ends.

2. Calls Handled per Agent

SELECT

    source_participant_name AS agent_name,

    COUNT(DISTINCT call_history_id) AS calls_handled

FROM cdroutput

WHERE (source_entity_type = 'extension' OR destination_entity_type = 'extension')

    AND cdr_answered_at IS NOT NULL

GROUP BY agent_name

ORDER BY calls_handled DESC;

Dashboard Application: This query counts the number of unique calls handled by each agent.

3. Agent Utilization Rate

WITH AgentCalls AS (

    SELECT

        source_participant_name AS agent_name,

        cdr_started_at,

        cdr_ended_at,

        cdr_answered_at,

        CASE

            WHEN cdr_answered_at IS NOT NULL THEN 1 ELSE 0

        END AS was_answered

    FROM cdroutput

    WHERE (source_entity_type = 'extension' OR destination_entity_type = 'extension')

)

SELECT

    agent_name,

    SUM(EXTRACT(EPOCH FROM (cdr_ended_at - cdr_started_at))) AS total_call_time_seconds,

    SUM(CASE WHEN was_answered = 1 THEN EXTRACT(EPOCH FROM (cdr_ended_at - cdr_answered_at)) ELSE 0 END) AS total_talk_time_seconds,

    (SUM(CASE WHEN was_answered = 1 THEN EXTRACT(EPOCH FROM (cdr_ended_at - cdr_answered_at)) ELSE 0 END) / SUM(EXTRACT(EPOCH FROM (cdr_ended_at - cdr_started_at)))) AS utilization_rate

FROM AgentCalls

GROUP BY agent_name

ORDER BY utilization_rate DESC;

Dashboard Application: This query calculates the percentage of time an agent was on a call, compared to the total time the call was ongoing.

Queue Performance

1. List all lost queue calls

SELECT c.*

FROM public.cdroutput AS c

WHERE c.destination_entity_type = 'queue'

AND c.termination_reason IN ('src_participant_terminated', 'dst_participant_terminated')

ORDER BY c.main_call_history_id DESC, c.cdr_id DESC

Dashboard Application: Display all abandoned queue calls

2. List all queue failed callbacks

SELECT agent.*

FROM public.cdroutput AS callback

INNER JOIN public.cdroutput AS agent ON

    agent.cdr_id = callback.continued_in_cdr_id

WHERE callback.destination_entity_type = 'ivr'

AND callback.destination_dn_number = 'QCB'

AND callback.termination_reason_details = 'polling'

AND agent.termination_reason != 'continued_in'

ORDER BY agent.main_call_history_id DESC, agent.cdr_id DESC

Dashboard Application: Display all failed callbacks for all queues in the system

Call Duration & Analysis

1. Calculate the average call duration for answered external calls

SELECT AVG(EXTRACT(EPOCH FROM (cdr_ended_at - cdr_answered_at))) AS average_duration_seconds

FROM cdroutput

WHERE source_entity_type != 'external_line'

    AND destination_entity_type = 'external_line'

    AND cdr_answered_at IS NOT NULL

    AND cdr_ended_at IS NOT NULL;

Dashboard Application: Understand the typical length of outbound calls to external numbers for cost analysis or efficiency monitoring.

2. Find the longest internal calls

SELECT call_history_id, source_dn_number, destination_dn_number,

    (cdr_ended_at - cdr_answered_at) AS duration

FROM cdroutput

WHERE source_entity_type != 'external_line'

    AND destination_entity_type != 'external_line'

    AND cdr_answered_at IS NOT NULL

    AND cdr_ended_at IS NOT NULL

ORDER BY duration DESC

LIMIT 10;

Dashboard Application: Identify potentially lengthy internal discussions for process analysis or training opportunities.

Call Flow & Routing Analysis

Identify call transfers and the participants involved

SELECT c1.call_history_id,

    c1.source_participant_name AS original_caller,

    c1.destination_participant_name AS original_destination,

    c2.destination_participant_name AS transferred_to

FROM cdroutput c1

JOIN cdroutput c2 ON c1.call_history_id = c2.call_history_id

WHERE c1.creation_method = 'call_init'

    AND c2.creation_method = 'transfer'

    AND c2.base_cdr_id = c1.cdr_id;

Dashboard Application: Visualize call transfer patterns and identify frequent transfer points.

Specific Agent/Queue Performance

1. Calculate the number of calls handled by each queue

SELECT destination_dn_name AS queue_name, COUNT(DISTINCT call_history_id) AS calls_handled

FROM cdroutput

WHERE destination_entity_type = 'queue'

    AND cdr_answered_at IS NOT NULL

GROUP BY destination_dn_name

ORDER BY calls_handled DESC;

Dashboard Application: Monitor queue performance and workload distribution.

2. Identify calls where a specific extension was the source and the call was not answered

SELECT call_history_id, destination_dn_name, termination_reason

FROM cdroutput

WHERE source_dn_number = 'YOUR_EXTENSION_NUMBER'  -- Replace with the actual extension

    AND termination_reason IN ('no_answer', 'timeout');

Dashboard Application: Track outbound calls from specific users that were not answered.

3. Calculate the average time before agents answered calls within specific queues

SELECT

    destination_dn_name AS queue_name,

    AVG(EXTRACT(EPOCH FROM (cdr_ended_at - cdr_answered_at))) AS average_talk_time_seconds

FROM cdroutput

WHERE destination_entity_type = 'queue'

    AND cdr_answered_at IS NOT NULL

    AND cdr_ended_at IS NOT NULL

GROUP BY destination_dn_name

ORDER BY average_talk_time_seconds DESC;

Dashboard Application: Identify which queues took time before agents answered calls, which can indicate complexity or efficiency.

4. Count the number of inbound calls to queues that were abandoned (source terminated before being answered)

SELECT

    destination_dn_name AS queue_name,

    COUNT(DISTINCT call_history_id) AS abandoned_calls

FROM cdroutput

WHERE destination_entity_type = 'queue'

    AND source_entity_type = 'external_line'  -- Consider only external callers

    AND termination_reason = 'src_participant_terminated'

GROUP BY destination_dn_name

ORDER BY abandoned_calls DESC;

Dashboard Application: Highlight queues with high abandonment rates, indicating potential issues with wait times or staffing

5. List all calls that were transferred to a queue

SELECT

    c2.call_history_id,

    c1.source_participant_name AS original_caller,

    c1.destination_participant_name AS original_destination,

    c2.destination_dn_name AS transferred_queue

FROM cdroutput c1

JOIN cdroutput c2 ON c1.call_history_id = c2.call_history_id

WHERE c1.creation_method IN ('call_init', 'route_to')

    AND c2.creation_method = 'transfer'

    AND c2.destination_entity_type = 'queue'

    AND c2.base_cdr_id = c1.cdr_id;

Dashboard Application: Understand how often calls are being routed to specific queues via transfers.

Error and Failure Analysis

1. List calls that failed due to "no route"

SELECT call_history_id, source_participant_name, destination_participant_name

FROM cdroutput

WHERE termination_reason_details = 'no_route';

Dashboard Application: Identify routing configuration issues.

2. Count calls terminated due to license limits

SELECT COUNT(*) AS license_limit_terminations

FROM cdroutput

WHERE termination_reason_details = 'license_limit_reached';

Dashboard Application: Monitor license usage and potential bottlenecks.

Call Termination Analysis

1. Count the different termination reasons for all calls

SELECT

    termination_reason,

    COUNT(*) AS termination_count

FROM cdroutput

GROUP BY termination_reason

ORDER BY termination_count DESC;

Dashboard Application: Get an overview of how calls are typically ending in your system.

2. Drill down into specific termination reason details (e.g., "timeout")

SELECT

    call_history_id,

    source_participant_name,

    destination_participant_name

FROM cdroutput

WHERE termination_reason_details = 'timeout';

Dashboard Application: Investigate calls that timed out.

See Also

Last Updated

This document was last updated 27 May 2026

https://www.3cx.com/docs/call-detail-record-guide/