CDR ODBC/MySQL calldate toujours a 0000-00-00

bonjour

je ne comprend pas pourquoi la colonne calldate n'est pas popullée. J'utilise Asterisk 1.8 et mySQL via ODBC

Code:

---

CREATE TABLE `cdr` (
`calldate` datetime NOT NULL default '0000-00-00 00:00:00',
`clid` varchar(80) NOT NULL default '',
`src` varchar(80) NOT NULL default '',
`dst` varchar(80) NOT NULL default '',
`dcontext` varchar(80) NOT NULL default '',
`channel` varchar(80) NOT NULL default '',
`dstchannel` varchar(80) NOT NULL default '',
`lastapp` varchar(80) NOT NULL default '',
`lastdata` varchar(80) NOT NULL default '',
`duration` int(11) NOT NULL default '0',
`billsec` int(11) NOT NULL default '0',
`disposition` varchar(45) NOT NULL default '',
`amaflags` int(11) NOT NULL default '0',
`accountcode` varchar(20) NOT NULL default '',
`uniqueid` varchar(32) NOT NULL default '',
`userfield` varchar(255) NOT NULL default ''
);

ALTER TABLE `cdr` ADD INDEX ( `calldate` );
ALTER TABLE `cdr` ADD INDEX ( `dst` );
ALTER TABLE `cdr` ADD INDEX ( `accountcode` );

---

Code:

---

mysql> select * from cdr
    -> ;
+---------------------+--------------------+------+------+----------+-------------------+------------+---------+----------+----------+---------+-------------+----------+-------------+--------------+-----------+
| calldate            | clid              | src  | dst  | dcontext | channel          | dstchannel | lastapp | lastdata | duration | billsec | disposition | amaflags | accountcode | uniqueid    | userfield |
+---------------------+--------------------+------+------+----------+-------------------+------------+---------+----------+----------+---------+-------------+----------+-------------+--------------+-----------+
| 0000-00-00 00:00:00 | "SoftPhone" <1001> | 1001 | **34 | Interne  | SIP/1001-00000001 |            | Hangup  |          |        3 |      3 | ANSWERED    |        3 |            | 1289875844.1 |          |
| 0000-00-00 00:00:00 | "SoftPhone" <1001> | 1001 | **34 | Interne  | SIP/1001-00000000 |            | Hangup  |          |        2 |      2 | ANSWERED    |        3 |            | 1289876820.0 |          |
+---------------------+--------------------+------+------+----------+-------------------+------------+---------+----------+----------+---------+-------------+----------+-------------+--------------+-----------+
2 rows in set (0.00 sec)

mysql>

---

Bonjour,
dans ma table cdr sous mysql, le champ calldate est un timestamp, pas un datetime. Tu as eu ou ta définition de table ?
Voici la mienne :

CREATE TABLE IF NOT EXISTS `cdr` (
`id` int(10) unsigned NOT NULL auto_increment,
`calldate` timestamp NOT NULL default '0000-00-00 00:00:00',
`clid` varchar(80) NOT NULL default '',
`src` varchar(80) NOT NULL default '',
`dst` varchar(80) NOT NULL default '',
`dcontext` varchar(39) NOT NULL default '',
`channel` varchar(80) NOT NULL default '',
`dstchannel` varchar(80) NOT NULL default '',
`lastapp` varchar(80) NOT NULL default '',
`lastdata` varchar(80) NOT NULL default '',
`answer` timestamp NOT NULL default '0000-00-00 00:00:00',
`end` timestamp NOT NULL default '0000-00-00 00:00:00',
`duration` int(10) unsigned NOT NULL default '0',
`billsec` int(10) unsigned NOT NULL default '0',
`disposition` varchar(9) NOT NULL default '',
`amaflags` tinyint(2) unsigned NOT NULL default '0',
`accountcode` varchar(20) NOT NULL default '',
`uniqueid` varchar(32) NOT NULL default '',
`userfield` varchar(255) NOT NULL default '',
PRIMARY KEY (`id`),
KEY `cdr__idx__calldate` (`calldate`),
KEY `cdr__idx__clid` (`clid`),
KEY `cdr__idx__src` (`src`),
KEY `cdr__idx__dst` (`dst`),
KEY `cdr__idx__channel` (`channel`),
KEY `cdr__idx__dstchannel` (`dstchannel`),
KEY `cdr__idx__duration` (`duration`),
KEY `cdr__idx__disposition` (`disposition`),
KEY `cdr__idx__amaflags` (`amaflags`),
KEY `cdr__idx__accountcode` (`accountcode`),
KEY `cdr__idx__userfield` (`userfield`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=8784 ;

bizarre... ca ne marche pas plus

J'avais trouvé le script ici : http://www.voip-info.org/wiki/view/Asterisk+cdr+mysql

Code:

---

mysql> show tables
    -> ;
+--------------------+
| Tables_in_asterisk |
+--------------------+
| cdr                |
+--------------------+
1 row in set (0.00 sec)

mysql> drop table `cdr`;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
Empty set (0.00 sec)

mysql> CREATE TABLE `cdr` (
    -> `id` int(10) unsigned NOT NULL auto_increment,
    -> `calldate` timestamp NOT NULL default '0000-00-00 00:00:00',
    -> `clid` varchar(80) NOT NULL default '',
    -> `src` varchar(80) NOT NULL default '',
    -> `dst` varchar(80) NOT NULL default '',
    -> `dcontext` varchar(80) NOT NULL default '',
    -> `channel` varchar(80) NOT NULL default '',
    -> `dstchannel` varchar(80) NOT NULL default '',
    -> `lastapp` varchar(80) NOT NULL default '',
    -> `lastdata` varchar(80) NOT NULL default '',
    -> `duration` int(11) NOT NULL default '0',
    -> `billsec` int(11) NOT NULL default '0',
    -> `disposition` varchar(45) NOT NULL default '',
    -> `amaflags` int(11) NOT NULL default '0',
    -> `accountcode` varchar(20) NOT NULL default '',
    -> `uniqueid` varchar(32) NOT NULL default '',
    -> `userfield` varchar(255) NOT NULL default '',
    -> PRIMARY KEY (`id`)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> ALTER TABLE `cdr` ADD INDEX ( `id` );
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE `cdr` ADD INDEX ( `calldate` );
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE `cdr` ADD INDEX ( `dst` );
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE `cdr` ADD INDEX ( `accountcode` );
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from cdr
    -> ;
+----+---------------------+--------------------+------+------+----------+-------------------+------------+---------+----------+----------+---------+-------------+----------+-------------+--------------+-----------+
| id | calldate            | clid              | src  | dst  | dcontext | channel          | dstchannel | lastapp | lastdata | duration | billsec | disposition | amaflags | accountcode | uniqueid    | userfield |
+----+---------------------+--------------------+------+------+----------+-------------------+------------+---------+----------+----------+---------+-------------+----------+-------------+--------------+-----------+
|  1 | 0000-00-00 00:00:00 | "SoftPhone" <1001> | 1001 | **34 | Interne  | SIP/1001-00000000 |            | Hangup  |          |        3 |      3 | ANSWERED    |        3 |            | 1289955656.0 |          |
+----+---------------------+--------------------+------+------+----------+-------------------+------------+---------+----------+----------+---------+-------------+----------+-------------+--------------+-----------+
1 row in set (0.00 sec)

---

j'utilise Asterisk 1.8 et mysql, et les drivers ODBC et pas le addon Mysql

bizarre.. voici la requete que fait Asterisk

Code:

---

101116 17:34:07          36 Connect        asterisk@localhost on asterisk
                  36 Query        SET NAMES latin1
                  36 Query        SET character_set_results = NULL
                  36 Query        SET SQL_AUTO_IS_NULL = 0
                  36 Query        INSERT INTO cdr (clid,src,dst,dcontext,channel,lastapp,duration,billsec,disposition,amaflags,uniqueid) VALUES ('"SoftPhone" <1001>','1001','**34','Interne','SIP/1001-00000001','Hangup',2,2,'ANSWERED',3,'1289957640.1')

---

j'ai réussi a avoir une date.. mais il a fallu que je crée un fichier de plus dans mes config

demo@ubuntu:/etc/asterisk$ cat cdr_odbc.conf
[global]
dsn=asterisk
username=asterisk
password=admin
loguniqueid=yes ;; Required to use the userfield
dispositionstring=yes ;; Required to use disposition like ANSWERED and FAILED

demo@ubuntu:/etc/asterisk$

ouin.. sauf que là.. il insère en double dans la DB. Probablement à cause du cdr_odbc.conf et le cdr_adaptive.

quelle est la raison que le adaptive n'insère pas calldate ?

Citation:

---

Envoyé par survivant

ouin.. sauf que là.. il insère en double dans la DB. Probablement à cause du cdr_odbc.conf et le cdr_adaptive.

quelle est la raison que le adaptive n'insère pas calldate ?

---

j'ai trouvé.

il faut ajouter un alias

; cdr_adaptive_odbc.conf
[adaptive_connection]
connection=asterisk
table=cdr
usegmtime=yes
alias start => calldate

et à la fin.. on obtient :

Code:

---

                  48 Query        INSERT INTO cdr (calldate,clid,src,dst,dcontext,channel,lastapp,duration,billsec,disposition,amaflags,uniqueid) VALUES ({ ts '2010-11-17 02:37:52' },'"SoftPhone" <1001>','1001','**34','Interne','SIP/1001-00000000','Hangup',2,2,'ANSWERED',3,'1289961472.0')
                  48 Query        SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
                  48 Query        INSERT INTO cdr (calldate,clid,src,dst,dcontext,channel,dstchannel,lastapp,lastdata,duration,billsec,disposition,amaflags,accountcode,uniqueid,userfield) VALUES ({ts '2010-11-17 02:37:52'},'\"SoftPhone\" <1001>','1001','**34','Interne','SIP/1001-00000000','','Hangup','',2,2,'ANSWERED',3,'','1289961472.0','')
101116 18:38:06          35 Query        select * from cdr

---

Code:

---

|  3 | 2010-11-17 02:37:52 | "SoftPhone" <1001> | 1001 | **34 | Interne  | SIP/1001-00000000 |            | Hangup  |          |        2 |      2 | ANSWERED    |        3 |            | 1289961472.0 |          |
|  4 | 2010-11-17 02:37:52 | "SoftPhone" <1001> | 1001 | **34 | Interne  | SIP/1001-00000000 |            | Hangup  |          |        2 |      2 | ANSWERED    |        3 |            | 1289961472.0 |          |

---