How to extract unanswered calls from Asterisk's CDR table (mysql)?

1
votes

I'm trying to make a web page which lists unanswered calls from CDR. On incoming call all softphones and some mobile phones ring simultaneously. Asterisk version is 1.8.5.

Here is what gets written to CDR table for a single ANSWERED call:

╔═════════════════════╦══════╦═════════╦═════╦══════════╦═══════════════════════════╦═══════════════════════════╦═════════╦═══════════════════════╦══════════╦═════════╦═════════════╦══════════╦═════════════╦═══════════╦════════════════╦══════════╗
║      calldate       ║ clid ║   src   ║ dst ║ dcontext ║          channel          ║        dstchannel         ║ lastapp ║       lastdata        ║ duration ║ billsec ║ disposition ║ amaflags ║ accountcode ║ userfield ║    uniqueid    ║ imported ║
╠═════════════════════╬══════╬═════════╬═════╬══════════╬═══════════════════════════╬═══════════════════════════╬═════════╬═══════════════════════╬══════════╬═════════╬═════════════╬══════════╬═════════════╬═══════════╬════════════════╬══════════╣
║ 2012-02-06 12:40:45 ║      ║ 5020971 ║ 595 ║ OUTGOING ║ Local/595@OUTGOING-5d9a;2 ║ SIP/help.desk-000000b8    ║ Dial    ║ SIP/help.desk/595,,Tt ║        8 ║       0 ║ ANSWERED    ║        3 ║             ║           ║ 1328524845.301 ║        0 ║
║ 2012-02-06 12:40:45 ║      ║ 5020971 ║ 599 ║ OUTGOING ║ Local/599@OUTGOING-038b;2 ║ SIP/help.desk-000000b9    ║ Dial    ║ SIP/help.desk/599,,Tt ║        8 ║       0 ║ NO ANSWER   ║        3 ║             ║           ║ 1328524845.303 ║        1 ║
║ 2012-02-06 12:40:44 ║      ║ 5020971 ║ s   ║ to_tech  ║ SIP/help.desk-000000b6    ║ Local/595@OUTGOING-5d9a;1 ║ Queue   ║ TECH,Tt,,,300         ║      111 ║     110 ║ ANSWERED    ║        3 ║             ║           ║ 1328524844.298 ║        0 ║
╚═════════════════════╩══════╩═════════╩═════╩══════════╩═══════════════════════════╩═══════════════════════════╩═════════╩═══════════════════════╩══════════╩═════════╩═════════════╩══════════╩═════════════╩═══════════╩════════════════╩══════════╝

The SQL to get the unanswered calls is this:

SELECT * FROM cdr WHERE disposition = 'NO ANSWER' AND imported='0'

Needless to say, I'm getting false positives with calls that are actually answered. :-)

They only link I see is that the NO ANSWER and ANSWERED lines have very short time interval between them (and they share the same src number).

Now, before I go ahead and start checking if there are some ANSWERED calls from the same number with a close time interval (like 3 seconds), I'd like to hear if someone knows a better way to solve this.

I don't speak extensions.conf very fluently and the guys who actually put our dialplan up are long gone but I noticed this in extensions.conf:

[class1]
exten => s,n,SET(CDR(accountcode)=${UNIQUEID})
include => accounts
include => OUTGOING

If I get this straight, the exten line there is supposed to set accountcode field in the CDR table to a value of the incoming call's uniqueid? Anyway it is not working as the accountcode field is never filled in the table.

3
asterisk

3 Answers

3
votes

If I am reading that output correctly, it seems that the call came in and was put into a queue, then the Queue() app transferred the call to an agent (599) who did not answer, so the call was then transferred to another agent (595), who then answered the call.

In addition, it seems that each time the call is handled by the Queue() application, it is given a new call id, so it makes it very difficult to track.

What I would suggest, for this specific issue with Queues, is to use the QueueLog functionality, which can be integrated with SQL. You can then track the call in the QueueLog table, and track each step and transfer it made in the QueueLog SQL Table.

It will then be possible to construct an SQL query to join the QueueLog table with the cdr table to see which unique calls were unanswered, as opposed to a situation like the one you currently have.

EDIT:

Step-by-step instructions can be found at : http://www.voip-info.org/wiki/view/Asterisk+queue_log+on+MySQL . Queue events will then be inserted into a table, which looks like:

mysql> select * from queue_log;
+----+------------+--------------+------------------+-------+------------+-------+
| id | time       | callid       | queuename        | agent | event      | data  |
+----+------------+--------------+------------------+-------+------------+-------+
| 1  | 1198356717 | 1198356717.0 | voipsolutions.ru | NONE  | ENTERQUEUE | |serg |
| 2  | 1198356719 | 1198356717.0 | voipsolutions.ru | NONE  | ABANDON    | 1|1|2 |
+----+------------+--------------+------------------+-------+------------+-------+

You can then track the queue activity using this table.

Hope that helped.

0
votes

This Query for reports via email at 00:01 am )

<?php
/* Переменные для соединения с базой данных */ 
$hostname = "localhost"; 
$username = "asteriskcdruser"; 
$password = "asteriskcdrpass"; 
$dbName = "asteriskcdrdb"; 

/* Таблица MySQL, в которой хранятся данные */ 
$cdrtable = "cdr"; 

/* Переменные для определения вчерашней даты */ 
$time = mktime(date('H'), date('i'), date('s'), date('m'), date('d')-1, date('Y'));
$ydate = date("d.m.Y", $time);

/* создать соединение */ 
mysql_connect($hostname,$username,$password) OR DIE("Не могу создать соединение "); 

/* выбрать базу данных. Если произойдет ошибка - вывести ее */ 
mysql_select_db($dbName) or die(mysql_error());

/* запрос данных . номерация у нас трехзначная, поэтому LENGTH(  `src` ) >3, отсекаем исходящие вызовы */ 
$query = "SELECT  `dst` ,  `src` ,  `duration` ,  `dstchannel` ,  `calldate`
      FROM  `cdr`
      WHERE DATE_SUB( CURDATE( ) , INTERVAL 1 DAY ) <=  `calldate`
        AND CURDATE( ) >  `calldate`
        AND  `disposition` =  'NO ANSWER'
        AND LENGTH(  `src` ) >3"; 

/* Выполнить запрос. Если произойдет ошибка - вывести ее. */ 
$res=mysql_query($query) or die(mysql_error());

/* Как много нашлось строк */ 
$number = mysql_num_rows($res); 

/* заголовок письма */ 
$mes="Отчет о пропущенных вызовах за $ydate.\r\n\r\n";

/* готовим текст письма*/ 
if ($number == 0) { 
  $mes .= "Пропущенных вызовов не было"; 
} else { 

  /* Получать по одной строке из таблицы в массив $row, пока строки не кончатся */  
  while ($row=mysql_fetch_array($res)) { 
    $mes .= "   ".$row['calldate'].". От ".$row['src'];
    $mes .= " Абонент ".mb_substr($row['dstchannel'],4,3);
    $mes .= ". Ожидание  ".$row['duration']." сек.\r\n"; 
  } 
}

/* Отправляем письмо */ 
mail('[email protected]',      $ydate.' Missed calls report', $mes);    
echo "Отчеты были отправлены на почту...";
?>
-1
votes

For only missed calls use mysql request:

SELECT  `src`,`dst` , `duration` ,  `dstchannel` ,  `calldate` 
FROM  `cdr`  
WHERE DATE_SUB( CURDATE( ) , INTERVAL 1 DAY ) <=  `calldate` 
AND CURDATE( ) >  `calldate`
AND  `disposition` =  'NO ANSWER' 
AND LENGTH(  `dst` ) <4 AND LENGTH(  `src` ) >3 ;