Asterisk CDR csv conversion mysql

Berita Kehilangan

Telah hilang seorang anak, dengan ciri-ciri sebagai berikut:

Nama : Ujang David
Alamat : Bandung
Usia : 5 tahun

Hilang hari minggu jam 13.00 WIB, memakai baju partai, celana pendek, kulit putih, rambut cepak dan pakai sendal kegedean. Bagi yang menemukan ciri-ciri diatas harap menghubungi ibunya karena sendalnya mau dipakai...

History

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:

#!/bin/bash
# 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
# format the file to comply with the MySQL data (delete
'"' chars when needed)
# use a VIM script (nofielddelims.vim) for this purpose
ex /var/log/asterisk/cdr-csv/Master.csv.mod -c ":
sourcenofielddelims.vim" -c ":exit"
# run the MySQL commands from the cmd.sql file
mysql <>

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 `cdr` ADD `tmp1` VARCHAR(30) DEFAULT "x" NOT NULL;
ALTER TABLE `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 `cdr` DROP `tmp1`;
ALTER TABLE `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.)