Asterisk CDR Tables: Difference between revisions

From KlavoWiki
Jump to navigationJump to search
 
(10 intermediate revisions by the same user not shown)
Line 1: Line 1:
= Call Detail Records =
= Call Detail Records =
== Create Database ==
= Create Database =
The following information is based on Asterisk 1.6.x.<br>
The first thing required is to create a database for the CDR to be stored, and then we need to create a table.
The first thing required is to create a database for the CDR to be stored, and then we need to create a table.
<pre>
<pre>
Line 9: Line 8:
</pre>
</pre>
Once the database is created and we have selected it for use, we can now create the required tables(s).
Once the database is created and we have selected it for use, we can now create the required tables(s).
= Asterisk 10.x onwards =
<pre>
CREATE TABLE cdr_test (
  sequence int(10) unsigned NOT NULL AUTO_INCREMENT,
  start datetime NOT NULL default '0000-00-00 00:00:00',
  answer datetime NOT NULL default '0000-00-00 00:00:00',
  end  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 '',
  dstchannel varchar(80) NOT NULL default '',
  channel 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 (sequence)
);
</pre>
= Asterisk 1.8.x and previous =
<pre>
<pre>
CREATE TABLE cdr (
CREATE TABLE cdr (
Line 30: Line 57:
</pre>
</pre>


= Combined =
I'm now using a new table which combines the new and old for Asterisk version 10 and onwards which works with the aCDR reporting.
<pre>
CREATE TABLE cdr (
  sequence int(10) unsigned NOT NULL AUTO_INCREMENT,
  start datetime NOT NULL default '0000-00-00 00:00:00',
  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 '',
  PRIMARY KEY (sequence)
);
</pre>
= Authentication =
Asterisk 1.6.x seems to deny logon to mysql using the root account.  You will need to create a username and password for the asteriskcdr database so Asterisk can logon and make the required changes.<br>
Asterisk 1.6.x seems to deny logon to mysql using the root account.  You will need to create a username and password for the asteriskcdr database so Asterisk can logon and make the required changes.<br>
It's good practise to use a username and password.<br>


Change cdruser and logit to the username and password that you would like to use.
Change cdruser and logit to the username and password that you would like to use.
Line 65: Line 123:
</pre>
</pre>


Make sure you have a look at [[CDR_Analyser|CDR Analyser]] so you can view your CDR via a WEB page.
Make sure you have a look at [[Asterisk_CDR_Viewer | Asterisk CDR Viewer]] so you can view your CDR via a WEB page.




[[Category : Asterisk]]
[[Category : Asterisk]] [[Category : CDR]]

Latest revision as of 22:44, 9 August 2014

Call Detail Records

Create Database

The first thing required is to create a database for the CDR to be stored, and then we need to create a table.

mysql
create database asteriskcdr;
use asteriskcdr;

Once the database is created and we have selected it for use, we can now create the required tables(s).

Asterisk 10.x onwards

CREATE TABLE cdr_test (
  sequence int(10) unsigned NOT NULL AUTO_INCREMENT,
  start datetime NOT NULL default '0000-00-00 00:00:00',
  answer datetime NOT NULL default '0000-00-00 00:00:00',
  end  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 '',
  dstchannel varchar(80) NOT NULL default '',
  channel 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 (sequence)
);

Asterisk 1.8.x and previous

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


Combined

I'm now using a new table which combines the new and old for Asterisk version 10 and onwards which works with the aCDR reporting.

CREATE TABLE cdr (
  sequence int(10) unsigned NOT NULL AUTO_INCREMENT,
  start datetime NOT NULL default '0000-00-00 00:00:00',
  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 '',
  PRIMARY KEY (sequence)
);

Authentication

Asterisk 1.6.x seems to deny logon to mysql using the root account. You will need to create a username and password for the asteriskcdr database so Asterisk can logon and make the required changes.

It's good practise to use a username and password.

Change cdruser and logit to the username and password that you would like to use.

GRANT ALL ON *.* to cdruser@localhost identified by 'logit';
flush privileges;

Configure Asterisk

Edit the Asterisk cdr_mysql.conf file and enter the appropriate database connection details.

[global]
hostname=127.0.0.1
dbname=asterisk
table=cdr
password=passw0rd
user=cdruser

With the Asterisk CLI reload the cdr module

module reload cdr_mysql.so


Once the module is reloaded you can test connectivity by typing in:

cdr mysql status

You should get a response like

Connected to databse-name@localhost, port 3306 using table table-name for 2 seconds.

Make sure you have a look at Asterisk CDR Viewer so you can view your CDR via a WEB page.