In the interests of accurate accounting, Asterisk creates a Master.csv, logging all calls and a few things about them.
This page on wiki.asterisk.org has a good breakdown of what all the fields mean.
I put together this MySQL table so that the data can be processed for data-analysis, accounting, or whatever it is that the data is needed for.
-- Code to create MySQL table for Asterisk CDR data
-- Field descriptions from:
-- https://wiki.asterisk.org/wiki/display/AST/CDR+Fields
CREATE TABLE cdr (
accountcode VARCHAR(256) COMMENT 'What account number to use, (string, 20 characters)',
src VARCHAR(80) COMMENT 'CallerID number',
dst VARCHAR(80) COMMENT 'Destination extension',
dcontext VARCHAR(80) COMMENT 'Destination context',
clid VARCHAR(80) COMMENT 'CallerID with text',
channel VARCHAR(80) COMMENT 'Channel used',
dstchannel VARCHAR(80) COMMENT 'Destination channel if appropriate',
lastapp VARCHAR(80) COMMENT 'Last application if appropriate',
lastdata VARCHAR(80) COMMENT 'Last application data (arguments)',
tsstart DATETIME COMMENT 'Start of call (date/time)',
tsanswer DATETIME COMMENT 'Answer of call (date/time)',
tsend DATETIME COMMENT 'End of call (date/time)',
duration INT(11) COMMENT 'Total time in system, in seconds (integer), from dial to hangup',
billsec INT(11) COMMENT 'Total time call is up, in seconds (integer), from answer to hangup',
disposition ENUM('ANSWERED', 'NO ANSWER', 'BUSY') COMMENT 'What happened to the call',
amaflags ENUM('DOCUMENTATION', 'BILL', 'IGNORE') COMMENT 'What flags to use, specified on a per channel basis like accountcode.',
uniqueid VARCHAR(32) COMMENT 'Unique Channel Identifier',
userfield VARCHAR(256) COMMENT 'user field: A user-defined field, maximum 255 characters'
) ;
The second-last field is not present on all installations, but it is on mine. The version I’m on is:
asterisk:~# asterisk -V
Asterisk 1.8.10.1~dfsg-1ubuntu1
There are several methods to import CSV data into MySQL. In this case I used LOAD DATA INFILE.