Call Detail Records - Caller Number

Status
Not open for further replies.

kleaksa

New Member
Nov 8, 2018
8
0
1
42
Hi community!
Hope you can help me to sort out with the following problem : somehow now in Call Detail Records as Caller number i see the Outbound Caller id (which is set to external number in extension properties) instead of 4digit Extension and because of this i have empty Extension Summary statistics.
But im sure that some time ago it was showing me "extension id" instead of Outbound one..
Can you help me please to identify why?
Thanks!
 
Last edited:

kleaksa

New Member
Nov 8, 2018
8
0
1
42
As a try added Number alias under extension, didnt helped - the extension summary for OUTBOUND is still empty...
Any help?
 

Kenny Riley

Active Member
Nov 1, 2017
243
39
28
36
I noticed the same thing... sorta. The CDR records show the outbound caller ID of the extension number, rather than the extension number itself. My extension summary has data though and looks fine.. this change just makes searching through the CDR's now a bit cumbersome since I can't see the extension number that made the call anymore without digging down into the CDR records in detail.

I'm running on the master branch, however.
 

kleaksa

New Member
Nov 8, 2018
8
0
1
42
So, just had to review the source code.
Finally sorted a little workaround using extension "alias" configured in Extension properties and modified the SQL query in xml_cdr_extension_summary.php
For those who needs here is the SQL


$sql = "SELECT \n";
$sql .= "e.domain_uuid, \n";
$sql .= "d.domain_name, \n";
$sql .= "e.extension, \n";
$sql .= "e.number_alias, \n";

$sql .= "COUNT(*) \n";
$sql .= "FILTER( \n";
$sql .= " WHERE c.domain_uuid = e.domain_uuid \n";
$sql .= " AND ((\n";
$sql .= " c.caller_id_name = e.extension \n";
$sql .= " OR \n";
$sql .= " c.destination_number = e.extension) \n";
$sql .= " OR ( \n";
$sql .= " e.number_alias IS NOT NULL and ( \n";
$sql .= " c.caller_id_name = e.number_alias \n";
$sql .= " OR \n";
$sql .= " c.destination_number = e.number_alias))) \n";
$sql .= " AND (\n";
$sql .= " c.answer_stamp IS NOT NULL \n";
$sql .= " and \n";
$sql .= " c.bridge_uuid IS NOT NULL) \n";

if ($this->include_internal) {
$sql .= " AND (direction = 'inbound' OR direction = 'local')) \n";
}
else {
$sql .= "AND direction = 'inbound') \n";
}
$sql .= "AS answered, \n";

$sql .= "COUNT(*) \n";
$sql .= "FILTER( \n";
$sql .= " WHERE (( \n";
$sql .= " c.caller_id_name = e.extension \n";
$sql .= " OR \n";
$sql .= " c.destination_number = e.extension) \n";
$sql .= " OR (\n";
$sql .= " e.number_alias IS NOT NULL \n";
$sql .= " AND ( \n";
$sql .= " c.caller_id_name = e.number_alias \n";
$sql .= " OR \n";
$sql .= " c.destination_number = e.number_alias))) \n";
$sql .= " AND ( \n";
$sql .= " c.answer_stamp IS NULL \n";
$sql .= " AND \n";
$sql .= " c.bridge_uuid IS NULL) \n";
if ($this->include_internal) {
$sql .= " AND (direction = 'inbound' OR direction = 'outbound'))";
} else {
$sql .= " AND direction = 'inbound')";
}
$sql .= "AS missed, \n";

$sql .= "COUNT(*) \n";
$sql .= "FILTER( \n";
$sql .= " WHERE (( \n";
$sql .= " c.caller_id_name = e.extension \n";
$sql .= " OR \n";
$sql .= " c.destination_number = e.extension) \n";
$sql .= " OR ( \n";
$sql .= " e.number_alias IS NOT NULL \n";
$sql .= " AND ( \n";
$sql .= " c.caller_id_name = e.number_alias \n";
$sql .= " OR \n";
$sql .= " c.destination_number = e.number_alias))) \n";
$sql .= " AND c.hangup_cause = 'NO_ANSWER' \n";
if ($this->include_internal) {
$sql .= " AND (direction = 'inbound' OR direction = 'local') \n";
}
else {
$sql .= "AND direction = 'inbound' \n";
}
$sql .= ") AS no_answer, \n";

$sql .= "COUNT(*) \n";
$sql .= "FILTER( \n";
$sql .= " WHERE (( \n";
$sql .= " c.caller_id_name = e.extension \n";
$sql .= " OR \n";
$sql .= " c.destination_number = e.extension) \n";
$sql .= " OR ( \n";
$sql .= " e.number_alias IS NOT NULL \n";
$sql .= " AND ( \n";
$sql .= " c.caller_id_name = e.number_alias \n";
$sql .= " OR \n";
$sql .= " c.destination_number = e.number_alias))) \n";
$sql .= " AND \n";
$sql .= " c.hangup_cause = 'USER_BUSY' \n";
if ($this->include_internal) {
$sql .= " AND (direction = 'inbound' OR direction = 'local')) \n";
}
else {
$sql .= " AND direction = 'inbound') \n";
}
$sql .= "AS busy, \n";

$sql .= "SUM(c.billsec) \n";
$sql .= "FILTER ( \n";
$sql .= " WHERE (( \n";
$sql .= " c.caller_id_name = e.extension \n";
$sql .= " OR \n";
$sql .= " c.destination_number = e.extension) \n";
$sql .= " OR ( \n";
$sql .= " e.number_alias IS NOT NULL \n";
$sql .= " AND ( \n";
$sql .= " c.caller_id_name = e.number_alias \n";
$sql .= " OR \n";
$sql .= " c.destination_number = e.number_alias))) \n";
if ($this->include_internal) {
$sql .= " AND (direction = 'inbound' OR direction = 'outbound') \n";
}
$sql .= " ) / \n";
$sql .= "COUNT(*) \n";
$sql .= "FILTER ( \n";
$sql .= " WHERE (( \n";
$sql .= " c.caller_id_name = e.extension \n";
$sql .= " OR \n";
$sql .= " c.destination_number = e.extension) \n";
$sql .= " OR ( \n";
$sql .= " e.number_alias IS NOT NULL \n";
$sql .= " AND ( \n";
$sql .= " c.caller_id_name = e.number_alias \n";
$sql .= " OR \n";
$sql .= " c.destination_number = e.number_alias))) \n";
if ($this->include_internal) {
$sql .= " AND (direction = 'inbound' OR direction = 'outbound') \n";
}
$sql .= " ) AS aloc, \n";

$sql .= "COUNT(*) \n";
$sql .= "FILTER ( \n";
$sql .= " WHERE (( \n";
$sql .= " c.caller_id_name = e.extension \n";
$sql .= " OR \n";
$sql .= " c.destination_number = e.extension) \n";
$sql .= " OR ( \n";
$sql .= " e.number_alias IS NOT NULL \n";
$sql .= " AND ( \n";
$sql .= " c.caller_id_name = e.number_alias \n";
$sql .= " OR \n";
$sql .= " c.destination_number = e.number_alias))) \n";
if ($this->include_internal) {
$sql .= " AND (direction = 'inbound' OR direction = 'local')) \n";
}
else {
$sql .= " AND direction = 'inbound') \n";
}
$sql .= "AS inbound_calls, \n";

$sql .= "SUM(c.billsec) \n";
$sql .= "FILTER ( \n";
$sql .= " WHERE (( \n";
$sql .= " c.caller_id_name = e.extension \n";
$sql .= " OR \n";
$sql .= " c.destination_number = e.extension) \n";
$sql .= " OR ( \n";
$sql .= " e.number_alias IS NOT NULL \n";
$sql .= " AND ( \n";
$sql .= " c.caller_id_name = e.number_alias \n";
$sql .= " OR \n";
$sql .= " c.destination_number = e.number_alias))) \n";
if ($this->include_internal) {
$sql .= " AND (direction = 'inbound' OR direction = 'local')) \n";
}
else {
$sql .= " AND direction = 'inbound') \n";
}
$sql .= "AS inbound_duration, \n";

$sql .= "COUNT(*) \n";
$sql .= "FILTER ( \n";
$sql .= " WHERE (( \n";
$sql .= " c.caller_id_name = e.extension \n";
$sql .= " OR \n";
$sql .= " c.destination_number = e.extension) \n";
$sql .= " OR ( \n";
$sql .= " e.number_alias IS NOT NULL \n";
$sql .= " AND ( \n";
$sql .= " c.caller_id_name = e.number_alias \n";
$sql .= " OR \n";
$sql .= " c.destination_number = e.number_alias))) \n";
$sql .= " AND \n";
$sql .= " c.direction = 'outbound') \n";
$sql .= "AS outbound_calls, \n";

$sql .= "SUM(c.billsec) \n";
$sql .= "FILTER ( \n";
$sql .= " WHERE (( \n";
$sql .= " c.caller_id_name = e.extension \n";
$sql .= " OR \n";
$sql .= " c.destination_number = e.extension) \n";
$sql .= " OR ( \n";
$sql .= " e.number_alias IS NOT NULL \n";
$sql .= " AND ( \n";
$sql .= " c.caller_id_name = e.number_alias \n";
$sql .= " OR \n";
$sql .= " c.destination_number = e.number_alias))) \n";
$sql .= " AND ( \n";
$sql .= " c.direction = 'outbound')) \n";
$sql .= "AS outbound_duration, \n";

$sql .= "e.description \n";

$sql .= "FROM v_extensions AS e, v_domains AS d, \n";
$sql .= "( SELECT \n";
$sql .= " domain_uuid, \n";
$sql .= " caller_id_name, \n";
$sql .= " destination_number, \n";
$sql .= " answer_stamp, \n";
$sql .= " bridge_uuid, \n";
$sql .= " direction, \n";
$sql .= " start_stamp, \n";
$sql .= " hangup_cause, \n";
$sql .= " billsec \n";
$sql .= " FROM v_xml_cdr \n";
$sql .= " WHERE domain_uuid = '".$this->domain_uuid."' \n";
$sql .= $sql_date_range;
$sql .= ") AS c \n";

$sql .= "WHERE \n";
$sql .= "d.domain_uuid = e.domain_uuid \n";
if (!($_GET['showall'] && permission_exists('xml_cdr_all'))) {
$sql .= "AND e.domain_uuid = '".$this->domain_uuid."' \n";
}
$sql .= "GROUP BY e.extension, e.domain_uuid, d.domain_uuid, e.number_alias, e.description \n";
$sql .= "ORDER BY extension ASC \n";

$prep_statement = $this->db->prepare(check_sql($sql));
print_r($prep_statement);
$prep_statement->execute();
$summary = $prep_statement->fetchAll(PDO::FETCH_NAMED);

Next step is to fix xml_cdr_statistics.php page because its also empty..
 

Saiful Alam

New Member
Mar 23, 2017
2
0
1
38
I worked on this and fixed on master and 4.4 branch updated and try again.

Hi,
I am having the same problem with version 4.3.1. Could you advise if this can be solved without fully upgrading to 4.4 branch?
Does it also require changes on database and freeswitch scripts?

My system is on production, and I am kind of scared to upgrade it at the moment. If it only require some changes on php files, can we replace those files from 4.4 branch?

Please advise what changes will solve this issue.

Regards,
Saiful Alam
 

Kenny Riley

Active Member
Nov 1, 2017
243
39
28
36
The issue is still persistent with my install on the master branch -- the outbound calls in the CDRs show the outbound caller ID of the caller (which is usually the main number of the organization) rather than the extension number of the caller.
 

markjcrane

Active Member
Staff member
Jul 22, 2018
447
162
43
49
That is because that is the Caller ID that was actually given for the outbound call so in that case its doing what it is supposed to do.
 
Status
Not open for further replies.