Skip to main content

Help with CDR in SQL

Posted by jaime@netline.cl on Tue, 11/13/2007

Hi, i'm trying to configure the CDR in MySQL with an Asterisk Busines Edition running on CentOs 4.5 and MySQL 3.22.
we compile'd the cdr_addon_mysql.so from asterisk-addons-1.2.8 with the asterisk-devel-B.2.2 sources, the module was loaded fine.

The CDR is saving ok in the MySQL, but the page viewcdr.cgi of the thirdlane-pbx don't display the calls.
The thirdlane-pbx only display the calls from CSV text file.

Thanks for any help.


mysql: describe cdr;
+-------------+--------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------------------+-------+
| calldate | datetime | | MUL | 0000-00-00 00:00:00 | |
| clid | varchar(80) | | | | |
| src | varchar(80) | | | | |
| dst | varchar(80) | | MUL | | |
| dcontext | varchar(80) | | | | |
| channel | varchar(80) | | | | |
| dstchannel | varchar(80) | | | | |
| lastapp | varchar(80) | | | | |
| lastdata | varchar(80) | | | | |
| duration | int(11) | | | 0 | |
| billsec | int(11) | | | 0 | |
| disposition | varchar(45) | | | | |
| amaflags | int(11) | | | 0 | |
| accountcode | varchar(20) | | MUL | | |
| userfield | varchar(255) | | | | |
+-------------+--------------+------+-----+---------------------+-------+
15 rows in set (0.01 sec)

mysql:

mysql: select * from cdr;
+---------------------+-----------+-----------+-----+----------------------------------------+-----------------------------+------------+---------+----------+----------+---------+-------------+----------+-------------+-----------+
| calldate | clid | src | dst | dcontext | channel | dstchannel | lastapp | lastdata | duration | billsec | disposition | amaflags | accountcode | userfield |
+---------------------+-----------+-----------+-----+----------------------------------------+-----------------------------+------------+---------+----------+----------+---------+-------------+----------+-------------+-----------+
| 2007-11-14 05:53:04 | 998740324 | 998740324 | s | from-outside-5626567997-All-Hours-test | SIP/201.219.131.71-082742c0 | | MeetMe | 3599| | 13 | 12 | ANSWERED | 3 | | test |
| 2007-11-14 05:53:24 | 998740324 | 998740324 | s | from-outside-5626567997-All-Hours-test | SIP/201.219.131.71-082742c0 | | MeetMe | 3599| | 21 | 20 | ANSWERED | 3 | | test |
| 2007-11-14 05:57:13 | 998740324 | 998740324 | s | from-outside-5626567997-All-Hours-test | SIP/201.219.131.71-082742c0 | | MeetMe | 3599| | 6 | 5 | ANSWERED | 3 | | test |
+---------------------+-----------+-----------+-----+----------------------------------------+-----------------------------+------------+---------+----------+----------+---------+-------------+----------+-------------+-----------+
3 rows in set (0.01 sec)


Submitted by thirdlane on Thu, 11/15/2007 Permalink

Hi Jaime,

I think the problem is that the table does not contain uniquied column (before userfield), and Thirdlane PBX can not recognize tenant id stored in userfield column.

Best regards,

Alex

+-------------+--------------+------+-----+---------------------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------------+--------------+------+-----+---------------------+-------+

| calldate | datetime | | MUL | 0000-00-00 00:00:00 | |

| clid | varchar(80) | | | | |

| src | varchar(80) | | | | |

| dst | varchar(80) | | MUL | | |

| dcontext | varchar(80) | | | | |

| channel | varchar(80) | | | | |

| dstchannel | varchar(80) | | | | |

| lastapp | varchar(80) | | | | |

| lastdata | varchar(80) | | | | |

| duration | int(11) | | | 0 | |

| billsec | int(11) | | | 0 | |

| disposition | varchar(45) | | | | |

| amaflags | int(11) | | | 0 | |

| accountcode | varchar(20) | | MUL | | |

| uniqueid | varchar(50) | | | | |

| userfield | varchar(255) | | | | |

+-------------+--------------+------+-----+---------------------+-------+

Submitted by mwolff on Wed, 12/12/2007 Permalink

Hello,

I would like to store and to use CDR in mysql database. By default the manager use csv files. How to change that ?

Thanks

Mathias