PDA

Voir la version complète : CDR ODBC/MySQL calldate toujours a 0000-00-00



survivant
16/11/2010, 05h12
bonjour

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



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` );




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>

therebel23
16/11/2010, 18h21
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 ;

survivant
17/11/2010, 03h03
bizarre... ca ne marche pas plus

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



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

survivant
17/11/2010, 03h32
bizarre.. voici la requete que fait Asterisk



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,bi llsec,disposition,amaflags,uniqueid) VALUES ('"SoftPhone" <1001>','1001','**34','Interne','SIP/1001-00000001','Hangup',2,2,'ANSWERED',3,'1289957640.1' )

survivant
17/11/2010, 04h02
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$

survivant
17/11/2010, 04h14
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 ?

survivant
17/11/2010, 04h39
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 :



48 Query INSERT INTO cdr (calldate,clid,src,dst,dcontext,channel,lastapp,du ration,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,ama flags,accountcode,uniqueid,userfield) VALUES ({ts '2010-11-17 02:37:52'},'\"SoftPhone\" <1001>','1001','**34','Interne','SIP/1001-00000000','','Hangup','',2,2,'ANSWERED',3,'','1289 961472.0','')
101116 18:38:06 35 Query select * from cdr





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