[Freeswitch-users] Maintaining call detail record

[Michael Collins]

Faisal,

A few things to keep in mind:

In cdr_csv.conf.xml you need to specify the correct template. There are
several templates specified. I don't know much about the perl script
mentioned there but I don't know if that is how I would approach the
situation personally. One of the templates is called "sql" and it creates
SQL statement for each cdr. You could literally pipe the contents of the cdr
file into MySQL and it will load the data into your table. However you will
need to handle the log file rotation. Some people use cron to send a HUP
signal to the freeswitch process which then rotates the log and Master.csv
files. I recommend you look at the cdr-csv directory before and after a
rotation so that you can see exactly what happens.

Here's a brief checklist for you to help you get going:
1 select the "sql" template in cdr_csv.conf.xml
 <param name="default-template" value="sql"/>

2 create a MySQL database for your cdr data

3 create a table called "cdr" (or rename the table used in the "sql"
template)
 the table needs to have all the fields laid out the way the template lays
them out
 this page can be used as a reference to get you started, but note the these
fields are NOT laid out the same way the sql template lays them out
 http://wiki.freeswitch.org/wiki/Mod_cdr#MySQL_Schema

4 decide how frequently you want to rotate log files and then set up a cron
job that sends the HUP signal:
 kill -hup `cat /usr/local/freeswitch/log/freeswitch.pid`
 After the kill -hup is sent your /usr/local/freeswitch/log/cdr-csv will
look something like this:
 -rw------- 1 root root        0 Dec 13 15:24 Master.csv
 -rw------- 1 root root  1473657 Dec 12 22:13 Master.csv.2008-12-13-15-24-16

  The file Master.csv.YYYY-MM-DD-hh-mm-ss now has the most recent CDR's.

5 run the most recent file through mysql. it is essentially just a text file
with a bunch of INSERT INTO statements
  mysql -u user -p password < Master.csv.YYYY-MM-DD-hh-mm-ss
  rm -f Master.csv.YYYY-MM-DD-hh-mm-ss

steps 4 and 5 could all be in the cron job which just has a script do all
the work. if you need assistance with setting up scripts and doing cron jobs
then i recommend that you manually do the steps one at a time and see
exactly what is happening and then learn how to do the shell script + cron
job.

Good luck!
-MC

P.S. - if anyone already has done all of this and is willing to share
his/her experiences please contact me off list as I would like to talk about
getting a wiki page set up that is dedicated to this sort of thing.

On Sat, Dec 13, 2008 at 1:44 AM, Faisal Maqsoodi <faisalmaqsoodi at yahoo.com>
wrote:
> How can i interface fs with mysql in order to maintain calls record like
> caller id and time n date of call etc. I ve worked on xml cdr but it
> contains too much info, more than i need and in a format which is not
easily
> understandable. I also tried perl coding mentioned on the link at the
bottom
> of the page
> http://wiki.freeswitch.org/wiki/Mod_cdr_csv, but so many error msgs r
> displayed during its execution. Is there any easy method for that. Plz
help
> me.
>

> faisal
>
> _______________________________________________
> Freeswitch-users mailing list
> Freeswitch-users at lists.freeswitch.org
> http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
> UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
> http://www.freeswitch.org
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.freeswitch.org/pipermail/freeswitch-users/attachments/20081213/1001a1d1/attachment-0002.html