Asterisk CDR csv conversion mysql

I hope this can help others, so this is it.
Use it at your own risk. I have test it on 3 separate systems without any problem.

Take care to edit the following files taking into consideration your own settings.
If you have all the CDR info in the Master.csv too, then delete all the data from the 'cdr' table in MySQL before running the script below in order to prevent duplicate records.

In my example, I have the following config:
CDR database: asteriskcdrdb
CDR table: cdr
CVS file: /var/log/asterisk/cdr-csv/Master.csv

1. Create a file named 'impcdr2sql' with the following content:

  1. !/bin/bash
  2. make a copy of the original Master.csv file to Master.csv.mod
cp -vf /var/log/asterisk/cdr-csv/Master.csv /var/log/asterisk/cdr-csv/Master.csv.mod
  1. format the file to comply with the MySQL data (delete '"' chars when needed)
  2. use a VIM script (nofielddelims.vim) for this purpose
ex /var/log/asterisk/cdr-csv/Master.csv.mod -c ":sourcenofielddelims.vim" -c ":exit"
  1. run the MySQL commands from the cmd.sql file
mysql < cmd.sql

2. Enter the command to make the script executable:

chmod 755 impcdr2sql

3. Create a file named 'nofielddelims.vim' with the following content:

"
" Delete '"' chars at the beginning of the line
"
:%s/^"//
"
" Delete '"' chars at the end of the line
"
:%s/"$//
"
" Delete '"' chars near the ',' char
"
:%s/",/,/g
:%s/,"/,/g
"
" Replace '""' by '"'
"
:%s/""/"/g

4. Create a file named 'cmd.sql' with the following content:

use asteriskcdrdb;
ALTER TABLE `bit_cdr` ADD `tmp1` VARCHAR(30) DEFAULT "x" NOT NULL;
ALTER TABLE `bit_cdr` ADD `tmp2` VARCHAR(30) DEFAULT "y" NOT NULL;
LOAD DATA INFILE '/var/log/asterisk/cdr-csv/Master.csv.mod'
replace INTO TABLE cdr
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(accountcode,src,dst,dcontext,clid,channel,dstchannel,lastapp,lastdata,calldate,tmp1,tmp2,duration,billsec,disposition,amaflags,uniqueid,userfield);
ALTER TABLE `bit_cdr` DROP `tmp1`;
ALTER TABLE `bit_cdr` DROP `tmp2`;

5. Keep all the files in the same directory. All you need to do is to run the script:

./impcdr2sql

as root or as an user with full rights on the asteriskcdrdb database and cdrtable
E... voila!
All your old data from Master.csv is now in the MySQL database in thecorrect format (I hope).

Please feel free to make any improvements you want.
I'm not a Linux expert.

Best regards to you all,
Dan

Another solution that's much simpler and is currently used to update the database each 5 minutes is this:

ard@patty:~$ cat cdr.sql 
create database asterisk;
use asterisk;
create table cdr (
        accountcode varchar (30),
        src varchar(64),
        dst varchar(64),
        dcontext varchar(32),
        clid varchar(32),
        channel varchar(32),
        dstchannel varchar(32),
        lastapp varchar(32),
        lastdata varchar(64),
        calldate timestamp NOT NULL,
        callpickup timestamp ,
        callhangup timestamp ,
        duration int(8) unsigned default NULL,
        billsec int(8) unsigned default NULL,
        status varchar(32),
        userfield varchar(128),
        PRIMARY KEY (clid,channel,calldate)
);
ard@patty:~$ cat read-master-csv 
#!/bin/sh
ssh 192.168.1.10 cat /var/log/asterisk/cdr-csv/Master.csv|
sed 's/,,/,"",/;s/\(^.*$\)/REPLACE INTO cdr VALUES\(\1\);/'|
mysql -u root asterisk

ard@patty:~$ crontab -l
*/5 * * * * /home/ard/read-master-csv
ard@patty:~$ 

Well, you get the idea.
(Of course this is only temporarily until asterisk does it straight away. On the other hand, having text files as originals is never a bad idea.)



http://kaneda.bohater.net/files/asterisk_csv2mysql_converter.sh

  1. !/bin/bash
  2. simple asterisk csv2mysql converter
  3. Before insert - CHECK 2 times everything :]
  4. Works for my configuration on 1.2.18/1.4.2/1.4.13 asterisk
  5. ver 20071115
  6. http://kaneda.bohater.net


if [ -z "$1" ] ; then
echo "Error: Choose csv file"
echo "Example: $0 ./Master.csv"
exit
fi

export IFS='
'

for linia in `cat $1|sed s/'",,"'/'","","'/g` ; do

clid=`echo $linia|awk -F '","' '{print $2}'| sed s/'"'//g`
src=`echo $linia|awk -F '","' '{print $5}'| sed s/'"'//g`
dst=`echo $linia|awk -F '","' '{print $3}'| sed s/'"'//g|awk '{print $1}'`
dcontext=`echo $linia|awk -F '","' '{print $4}'| sed s/'"'//g`
channel=`echo $linia|awk -F '","' '{print $6}'| sed s/'"'//g`
dstchannel=`echo $linia|awk -F '","' '{print $7}'| sed s/'"'//g`
lastapp=`echo $linia|awk -F '","' '{print $8}'| sed s/'"'//g`
lastdata=`echo $linia|awk -F '","' '{print $9}'| sed s/'"'//g`
start=`echo $linia|awk -F '","' '{print $10}'| sed s/'"'//g`
answer=`echo $linia|awk -F '","' '{print $11}'| sed s/'"'//g`
end=`echo $linia|awk -F '","' '{print $12}'| sed s/'"'//g`

duration=`echo $linia|awk -F ',"DOCUMENTATION' '{print $1}'|awk -F "," '{print $(NF-2)}'`
billsec=`echo $linia|awk -F ',"DOCUMENTATION' '{print $1}'|awk -F "," '{print $(NF-1)}'`
disposition=`echo $linia|awk -F ',"DOCUMENTATION' '{print $1}'|awk -F "," '{print $NF}'`

  1. DOCUMENTATION == 3
amaflags="3"
accountcode=""
userfield=""

echo "insert into cdr values ('$start','$clid','$src','$dst','$dcontext','$channel','$dstchannel','$lastapp','$lastdata','$duration','$billsec','$disposition','$amaflags','$accountcode','$userfield');"

done


MYSQL Native Import

Setup SQL Table
create table cdr (
accountcode varchar (30),
src varchar(64),
dst varchar(64),
dcontext varchar(32),
clid varchar(32),
channel varchar(32),
dstchannel varchar(32),
lastapp varchar(32),
lastdata varchar(64),
calldate timestamp NOT NULL,
answerdate timestamp NOT NULL,
hangupdate timestamp NOT NULL,
duration int(8) unsigned default NULL,
billsec int(8) unsigned default NULL,
disposition varchar(32),
amaflag varchar(128),
uniqueid varchar(128),
custom varchar(128),
PRIMARY KEY (clid,channel,calldate,uniqueid)
);



USE asterisk
LOAD DATA LOCAL INFILE '/var/log/asterisk/cdr-csv/Master.csv'
INTO TABLE asterisk1.cdr
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(accountcode, src,dst, dcontext, clid, channel, dstchannel, lastapp, lastdata, calldate, answerdate, hangupdate, duration, billsec, disposition, amaflag, uniqueid, custom);


in a file named cdr-import.sql, then

cat cdr-import.sql | mysql -u asterisk -p

This assumes the table is already setup, and that you have a password on the mySQL account (you have, haven't you?)

Why to export CSV to MySQL if you can insert the data directly ?

Like this (tested on Ubuntu 8.10, Asterisk 1.4.21.2, asterisk-stat-v2):

a) Mysql > CREATE DATABASE asteriskcdrdb;
b) Mysql > GRANT ALL PRIVILEGES
-> ON asteriskcdrdb.*
-> TO asterisk@localhost;
c) Mysql > use asteriskcdrdb;
d) Mysql > 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 '',
-> userfield varchar(255) NOT NULL default ''
-> );

Asterisk side

a) Copy cdr_addon_mysql.so into /usr/lib/asterisk/modules/
b) Edit /etc/sterisk/cdr_mysql.conf and add the following lines:

[global]
hostname=localhost
dbname=asteriskcdrdb
;password=password
user=asterisk
;port=3306
;sock=/tmp/mysql.sock
;userfield=1

I do not set any password for my database

c) Edit "sudo gedit /etc/asterisk/modules.conf" and add the following line inside the modules section:
Load => cdr_addon_mysql.so

Adding Libraries path:

1. Open the console and type “sudo gedit /etc/profile”
2. Add in the following line at the bottom and save it:
a) export LIBDIR = “/usr/lib/mysql”
b) export LD_Library_Path=”${LIBDIR}”
c) export LD_RUN_PATH=”${LIBDIR}”

3. Edit /etc/ld.so.conf and add in “/usr/lib/mysql”

At the end restart asterisk with "restart now"
Now it should work !

Back to Asterisk billing
I hope this can help others, so this is it.
Use it at your own risk. I have test it on 3 separate systems without any problem.

Take care to edit the following files taking into consideration your own settings.
If you have all the CDR info in the Master.csv too, then delete all the data from the 'cdr' table in MySQL before running the script below in order to prevent duplicate records.

In my example, I have the following config:
CDR database: asteriskcdrdb
CDR table: cdr
CVS file: /var/log/asterisk/cdr-csv/Master.csv

1. Create a file named 'impcdr2sql' with the following content:

  1. !/bin/bash
  2. make a copy of the original Master.csv file to Master.csv.mod
cp -vf /var/log/asterisk/cdr-csv/Master.csv /var/log/asterisk/cdr-csv/Master.csv.mod
  1. format the file to comply with the MySQL data (delete '"' chars when needed)
  2. use a VIM script (nofielddelims.vim) for this purpose
ex /var/log/asterisk/cdr-csv/Master.csv.mod -c ":sourcenofielddelims.vim" -c ":exit"
  1. run the MySQL commands from the cmd.sql file
mysql < cmd.sql

2. Enter the command to make the script executable:

chmod 755 impcdr2sql

3. Create a file named 'nofielddelims.vim' with the following content:

"
" Delete '"' chars at the beginning of the line
"
:%s/^"//
"
" Delete '"' chars at the end of the line
"
:%s/"$//
"
" Delete '"' chars near the ',' char
"
:%s/",/,/g
:%s/,"/,/g
"
" Replace '""' by '"'
"
:%s/""/"/g

4. Create a file named 'cmd.sql' with the following content:

use asteriskcdrdb;
ALTER TABLE `bit_cdr` ADD `tmp1` VARCHAR(30) DEFAULT "x" NOT NULL;
ALTER TABLE `bit_cdr` ADD `tmp2` VARCHAR(30) DEFAULT "y" NOT NULL;
LOAD DATA INFILE '/var/log/asterisk/cdr-csv/Master.csv.mod'
replace INTO TABLE cdr
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(accountcode,src,dst,dcontext,clid,channel,dstchannel,lastapp,lastdata,calldate,tmp1,tmp2,duration,billsec,disposition,amaflags,uniqueid,userfield);
ALTER TABLE `bit_cdr` DROP `tmp1`;
ALTER TABLE `bit_cdr` DROP `tmp2`;

5. Keep all the files in the same directory. All you need to do is to run the script:

./impcdr2sql

as root or as an user with full rights on the asteriskcdrdb database and cdrtable
E... voila!
All your old data from Master.csv is now in the MySQL database in thecorrect format (I hope).

Please feel free to make any improvements you want.
I'm not a Linux expert.

Best regards to you all,
Dan

Another solution that's much simpler and is currently used to update the database each 5 minutes is this:

ard@patty:~$ cat cdr.sql 
create database asterisk;
use asterisk;
create table cdr (
        accountcode varchar (30),
        src varchar(64),
        dst varchar(64),
        dcontext varchar(32),
        clid varchar(32),
        channel varchar(32),
        dstchannel varchar(32),
        lastapp varchar(32),
        lastdata varchar(64),
        calldate timestamp NOT NULL,
        callpickup timestamp ,
        callhangup timestamp ,
        duration int(8) unsigned default NULL,
        billsec int(8) unsigned default NULL,
        status varchar(32),
        userfield varchar(128),
        PRIMARY KEY (clid,channel,calldate)
);
ard@patty:~$ cat read-master-csv 
#!/bin/sh
ssh 192.168.1.10 cat /var/log/asterisk/cdr-csv/Master.csv|
sed 's/,,/,"",/;s/\(^.*$\)/REPLACE INTO cdr VALUES\(\1\);/'|
mysql -u root asterisk

ard@patty:~$ crontab -l
*/5 * * * * /home/ard/read-master-csv
ard@patty:~$ 

Well, you get the idea.
(Of course this is only temporarily until asterisk does it straight away. On the other hand, having text files as originals is never a bad idea.)



http://kaneda.bohater.net/files/asterisk_csv2mysql_converter.sh

  1. !/bin/bash
  2. simple asterisk csv2mysql converter
  3. Before insert - CHECK 2 times everything :]
  4. Works for my configuration on 1.2.18/1.4.2/1.4.13 asterisk
  5. ver 20071115
  6. http://kaneda.bohater.net


if [ -z "$1" ] ; then
echo "Error: Choose csv file"
echo "Example: $0 ./Master.csv"
exit
fi

export IFS='
'

for linia in `cat $1|sed s/'",,"'/'","","'/g` ; do

clid=`echo $linia|awk -F '","' '{print $2}'| sed s/'"'//g`
src=`echo $linia|awk -F '","' '{print $5}'| sed s/'"'//g`
dst=`echo $linia|awk -F '","' '{print $3}'| sed s/'"'//g|awk '{print $1}'`
dcontext=`echo $linia|awk -F '","' '{print $4}'| sed s/'"'//g`
channel=`echo $linia|awk -F '","' '{print $6}'| sed s/'"'//g`
dstchannel=`echo $linia|awk -F '","' '{print $7}'| sed s/'"'//g`
lastapp=`echo $linia|awk -F '","' '{print $8}'| sed s/'"'//g`
lastdata=`echo $linia|awk -F '","' '{print $9}'| sed s/'"'//g`
start=`echo $linia|awk -F '","' '{print $10}'| sed s/'"'//g`
answer=`echo $linia|awk -F '","' '{print $11}'| sed s/'"'//g`
end=`echo $linia|awk -F '","' '{print $12}'| sed s/'"'//g`

duration=`echo $linia|awk -F ',"DOCUMENTATION' '{print $1}'|awk -F "," '{print $(NF-2)}'`
billsec=`echo $linia|awk -F ',"DOCUMENTATION' '{print $1}'|awk -F "," '{print $(NF-1)}'`
disposition=`echo $linia|awk -F ',"DOCUMENTATION' '{print $1}'|awk -F "," '{print $NF}'`

  1. DOCUMENTATION == 3
amaflags="3"
accountcode=""
userfield=""

echo "insert into cdr values ('$start','$clid','$src','$dst','$dcontext','$channel','$dstchannel','$lastapp','$lastdata','$duration','$billsec','$disposition','$amaflags','$accountcode','$userfield');"

done


MYSQL Native Import

Setup SQL Table
create table cdr (
accountcode varchar (30),
src varchar(64),
dst varchar(64),
dcontext varchar(32),
clid varchar(32),
channel varchar(32),
dstchannel varchar(32),
lastapp varchar(32),
lastdata varchar(64),
calldate timestamp NOT NULL,
answerdate timestamp NOT NULL,
hangupdate timestamp NOT NULL,
duration int(8) unsigned default NULL,
billsec int(8) unsigned default NULL,
disposition varchar(32),
amaflag varchar(128),
uniqueid varchar(128),
custom varchar(128),
PRIMARY KEY (clid,channel,calldate,uniqueid)
);



USE asterisk
LOAD DATA LOCAL INFILE '/var/log/asterisk/cdr-csv/Master.csv'
INTO TABLE asterisk1.cdr
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(accountcode, src,dst, dcontext, clid, channel, dstchannel, lastapp, lastdata, calldate, answerdate, hangupdate, duration, billsec, disposition, amaflag, uniqueid, custom);


in a file named cdr-import.sql, then

cat cdr-import.sql | mysql -u asterisk -p

This assumes the table is already setup, and that you have a password on the mySQL account (you have, haven't you?)

Why to export CSV to MySQL if you can insert the data directly ?

Like this (tested on Ubuntu 8.10, Asterisk 1.4.21.2, asterisk-stat-v2):

a) Mysql > CREATE DATABASE asteriskcdrdb;
b) Mysql > GRANT ALL PRIVILEGES
-> ON asteriskcdrdb.*
-> TO asterisk@localhost;
c) Mysql > use asteriskcdrdb;
d) Mysql > 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 '',
-> userfield varchar(255) NOT NULL default ''
-> );

Asterisk side

a) Copy cdr_addon_mysql.so into /usr/lib/asterisk/modules/
b) Edit /etc/sterisk/cdr_mysql.conf and add the following lines:

[global]
hostname=localhost
dbname=asteriskcdrdb
;password=password
user=asterisk
;port=3306
;sock=/tmp/mysql.sock
;userfield=1

I do not set any password for my database

c) Edit "sudo gedit /etc/asterisk/modules.conf" and add the following line inside the modules section:
Load => cdr_addon_mysql.so

Adding Libraries path:

1. Open the console and type “sudo gedit /etc/profile”
2. Add in the following line at the bottom and save it:
a) export LIBDIR = “/usr/lib/mysql”
b) export LD_Library_Path=”${LIBDIR}”
c) export LD_RUN_PATH=”${LIBDIR}”

3. Edit /etc/ld.so.conf and add in “/usr/lib/mysql”

At the end restart asterisk with "restart now"
Now it should work !

Back to Asterisk billing
Created by: flavour, Last modification: Mon 23 of Apr, 2012 (23:22 UTC) by admin
Please update this page with new information, just login and click on the "Edit" or "Discussion" tab. Get a free login here: Register Thanks! - Find us on Google+