VoIP Mailing List Archives

[faisalmaqsoodi at yaho...]

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

[faisalmaqsoodi at yaho...]

if (typeof YAHOO == "undefined") { var YAHOO = {}; } YAHOO.Shortcuts = YAHOO.Shortcuts || {}; YAHOO.Shortcuts.hasSensitiveText = false; YAHOO.Shortcuts.sensitivityType = []; YAHOO.Shortcuts.doUlt = false; YAHOO.Shortcuts.location = "us"; YAHOO.Shortcuts.document_id = 0; YAHOO.Shortcuts.document_type = ""; YAHOO.Shortcuts.document_title = "Maintaining call detail record"; YAHOO.Shortcuts.document_publish_date = ""; YAHOO.Shortcuts.document_author = "faisalmaqsoodi@yahoo.com"; YAHOO.Shortcuts.document_url = ""; YAHOO.Shortcuts.document_tags = ""; YAHOO.Shortcuts.document_language = "english"; YAHOO.Shortcuts.annotationSet = { "lw_1229161419_0": { "text": "caller id", "extended": 0, "startchar": 187, "endchar": 195, "start": 187, "end": 195, "extendedFrom": "", "predictedCategory": "", "predictionProbability": "0", "weight": 0.2054, "relScore": 5.33095, "type": ["shortcuts:/concept"], "category": ["CONCEPT"], "wikiId": "Caller_ID", "relatedWikiIds": [], "relatedEntities": [], "showOnClick": [], "context": "fs with mysql in order to maintain calls record like caller id and time n date of call etc. I ve worked", "metaData": { "visible": "true" } }, "lw_1229161419_1": { "text": "http://wiki.freeswitch.org/wiki/Mod_cdr_csv,", "extended": 0, "startchar": 425, "endchar": 468, "start": 425, "end": 468, "extendedFrom": "", "predictedCategory": "", "predictionProbability": "0", "weight": 1, "relScore": 0, "type": ["shortcuts:/us/instance/identifier/URL"], "category": ["IDENTIFIER"], "wikiId": "", "relatedWikiIds": [], "relatedEntities": [], "showOnClick": [], "context": "", "metaData": { "visible": "true" } } }; YAHOO.Shortcuts.headerID = "c0b92bae50300eda0a2889f03525f330"; 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

[msc at freeswitch.org]

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@yahoo.com (faisalmaqsoodi@yahoo.com)> wrote:

[faisalmaqsoodi at yaho...]

if (typeof YAHOO == "undefined") { var YAHOO = {}; } YAHOO.Shortcuts = YAHOO.Shortcuts || {}; YAHOO.Shortcuts.hasSensitiveText = false; YAHOO.Shortcuts.sensitivityType = []; YAHOO.Shortcuts.doUlt = false; YAHOO.Shortcuts.location = "us"; YAHOO.Shortcuts.document_id = 0; YAHOO.Shortcuts.document_type = ""; YAHOO.Shortcuts.document_title = "[Freeswitch-users] Maintaining call detail record"; YAHOO.Shortcuts.document_publish_date = ""; YAHOO.Shortcuts.document_author = "faisalmaqsoodi@yahoo.com"; YAHOO.Shortcuts.document_url = ""; YAHOO.Shortcuts.document_tags = ""; YAHOO.Shortcuts.document_language = "english"; YAHOO.Shortcuts.annotationSet = { "lw_1229319648_0": { "text": "caller id", "extended": 0, "startchar": 188, "endchar": 196, "start": 188, "end": 196, "extendedFrom": "", "predictedCategory": "", "predictionProbability": "0", "weight": 0.2054, "relScore": 5.33095, "type": ["shortcuts:/concept"], "category": ["CONCEPT"], "wikiId": "Caller_ID", "relatedWikiIds": [], "relatedEntities": [], "showOnClick": [], "context": "fs with mysql in order to maintain calls record like caller id and time n date of call etc. I ve worked", "metaData": { "visible": "true" } }, "lw_1229319648_1": { "text": "http://wiki.freeswitch.org/wiki/Mod_cdr_csv,", "extended": 0, "startchar": 426, "endchar": 469, "start": 426, "end": 469, "extendedFrom": "", "predictedCategory": "", "predictionProbability": "0", "weight": 1, "relScore": 0, "type": ["shortcuts:/us/instance/identifier/URL"], "category": ["IDENTIFIER"], "wikiId": "", "relatedWikiIds": [], "relatedEntities": [], "showOnClick": [], "context": "", "metaData": { "visible": "true" } } }; YAHOO.Shortcuts.headerID = "284c8f98b4fb0aebc968053934caa66b"; 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