Difference between revisions of "Mod cdr csv"

From FreeSWITCH Wiki
Jump to: navigation, search
(sql script does not match default cdr_csv.xml config)
Line 151: Line 151:
   bleg_uuid varchar(100) DEFAULT NULL,
   bleg_uuid varchar(100) DEFAULT NULL,
   accountcode varchar(10) DEFAULT NULL,
   accountcode varchar(10) DEFAULT NULL,
   read_codec varchar(20) DEFAULT NULL,
   domain_name varchar(100) DEFAULT NULL
  write_codec varchar(20) DEFAULT NULL  

Revision as of 23:11, 20 December 2010


The Basics

This module allows you to log call detail records (CDRs) to a text file using text generation templates. These templates along with the default configuration are stored in the file freeswitch/conf/autoload_configs/cdr_csv.conf.xml. Which template is used for the default is defined by the following line (which uses "example" in the default installation).

 <param name="default-template" value="example"/>

Further down in the cdr_csv.conf.xml file, the "example" template is defined: <template name="example"> ... </template >. Therefore to change what is being logged you may edit the example template or create a new template and update the default-template parameter to that template name.

XML structure

Variables in a template

Variables are given in form ${varname}. For an overview of variables, check Channel Variables. You can specify any channel variable.

Template variables can to API calls as well, like:

<template name="custom_timestamp">"${expr(${end_epoch}-${my_timestamp})}"</template>

Here is a sample CDR of a call that was successful, we'll use this to refer to each variable below.

"xtec","2185738219","919814440333","default",""2185738219" <2185738219>","sofia/in/2185738219@","sofia/out/+919814440333 begin_of_the_skype_highlighting              +919814440333      end_of_the_skype_highlighting begin_of_the_skype_highlighting              +919814440333      end_of_the_skype_highlighting@","bridge","sofia/out/+919814440203@","2008-02-11 07:01:49","2008-02-11 07:02:07","2008-02-11 07:27:13","1524","1506","NORMAL_CLEARING","","c60a1f09-ce61-47d3-a806-82bd2c9a4b6a",""

this particular CDR was made with the following template (note, it doesn't have bleg_uuid)

<template name="legacy" >"${accountcode}","${caller_id_number}","${destination_number}","${context}", ${caller_id}","${channel_name}","${bridge_channel}","${last_app}","${last_arg}","${start_stamp}", "${answer_stamp}","${end_stamp}","${duration}","${billsec}","${hangup_cause}","${amaflags}", "${uuid}","${userfield}"</template>

Generating SQL from template

Some strings may contain characters that need escaping to generate a valid SQL statement. These include characters such as ', " and many non-printable characters.

The API call sql_escape can be used to escape these variables to generate valid SQL, for example:

<template name="sql">INSERT INTO cdr (caller_id, ...) VALUES ('${sql_escape(${caller_id_name})}', ...);</template>

Some significant variables


An arbitrary value given at whim. Useful for billing and segregating calls by account. This is the accountcode of the aleg. In the sample above its xtec.

NOTE: if the value of the accountcode variable matches the name of a template then that template will be used. This is valuable for having a specific template be used on a per-call basis.


Automatic Message Accounting, an archaic system that "flags" CDR for post-processing. Commonly used flags are billing, default, documentation, omit.


Timestamp when the call was answered (eg, SIP 200 OK is received), in ISO 8601 format (YYYY-MM-DD hh:mm:ss)


The answered or billing span of the calls in seconds, i.e. "answer_stamp - end_stamp". Should only be > 0 in calls where HANGUP_CAUSE == NORMAL_CLEARING (16).


The uuid of the bleg, i.e. the called party or terminating end.


The accountcode of the bleg_uuid in the call.


The channel name of the bleg that the aleg was bridged to. e.g. sofia/out/+919814440333@




See Channel_Variables#caller_id_name


See Channel_Variables#caller_id_number


This is the channel_name of the aleg originating end. In this case its sofia/in/2185738219@


The context where the call first entered the dialplan, in this case default.


This is the number (rather, extension) specified by the aleg. In the example above its 919814440333. The dialplan can perform any mutation on this number before its actually dialed out (in the case above a + is added).


The entire duration of the call, end to end in seconds, i.e. "start_stamp - end_stamp". Duration should always be >= billsec.


Timestamp when the call was hung up, in ISO 8601 format (YYYY-MM-DD hh:mm:ss)


See Hangup Causes


The last application run by the call, in the case of our example, its bridge.


The arguments passed to the last_app, i.e. sofia/out/+919814440203@


See Channel_Variables#read_codec


See How does FreeSWITCH CDR determine which gateway was used in failover?





Timestamp when the call was started, in ISO 8601 format (YYYY-MM-DD hh:mm:ss)


A field to set your own custom values. For example

 <action application="set" data="userfield=${network_addr}"/>


A unique identifier for the call. A UUID is a block of 16 bytes ref. This is the uuid of the aleg of the call, i.e. the calling party or origination end. According to ref UUIDs are formatted as: 00112233-4455-6677-8899-AABBCCDDEEFF


See Channel_Variables#write_codec

Example SQL Script to create MySQL cdr table for default "example" cdrs

To confirm that the mod_cdr_csv is set for the "sql" template, check the file


The default is the example template. If that is indeed the case, the script below is just what you will need to create a the cdr table.

 caller_id_name varchar(30) DEFAULT NULL,
 caller_id_number varchar(30) DEFAULT NULL,
 destination_number varchar(30) DEFAULT NULL,
 context varchar(20) DEFAULT NULL,
 start_stamp datetime DEFAULT NULL,
 answer_stamp datetime DEFAULT NULL,
 end_stamp datetime DEFAULT NULL,
 duration int(11) DEFAULT NULL,
 billsec int(11) DEFAULT NULL,
 hangup_cause varchar(50) DEFAULT NULL,
 uuid varchar(100) DEFAULT NULL,
 bleg_uuid varchar(100) DEFAULT NULL,
 accountcode varchar(10) DEFAULT NULL,
 domain_name varchar(100) DEFAULT NULL

For PostgreSQL

 caller_id_name character varying(30),
 caller_id_number character varying(30),
 destination_number character varying(30),
 context character varying(20),
 start_stamp timestamp without time zone,
 answer_stamp timestamp without time zone,
 end_stamp timestamp without time zone,
 duration integer,
 billsec integer,
 hangup_cause character varying(50),
 uuid uuid,
 bleg_uuid uuid,
 accountcode character varying(10),
 read_codec character varying(20),
 write_codec character varying(20)

Example Perl Script for CDR into MySQL

Someone with a little knowledge of Perl could probably fix up all the system() and other hacks and lack of error checking...

Ensure the following line is uncommented in cdr_csv.conf.xml

<param name=”rotate-on-hup” value=”true”/>

What this does is allow the Master.csv and other CDR files to be rotated via the same method as used by the mod_logfile method. The script below uses one of these methods to rotate the log file and load the file into a database. (The naming format for the rotated files is from (e.g.) Master.csv to Master.csv.yyyy-mm-dd-hh-mm-ss)

Create the script using the code below, and run through cron every minute if you like...

#!/usr/bin/perl -w

# Convergence FreeSWITCH(tm) Tools Version 7.0
# (c) MMII Convergence. All rights reserved.
# <info@convergence.pk> http://www.convergence.pk

# This program is free software, distributed under the terms of
# the GNU General Public License.http://www.gnu.org/licenses.html

use strict;
use DBI();
use File::Copy;

# this commands HUPS fs, she creates new cdr.csv files, so we can load the old ones up
my @cc  = ("killall", "-HUP", "freeswitch");
system(@cc) == 0 or die "$0: system @cc failed: $?";

my $dbh = DBI->connect("DBI:mysql:database=freeswitch;host=localhost","fsdbuser","fsdbpass") or die "$0: Couldn't connect to database: " . DBI->errstr;

# this is the standard location of the cdr-csv
my @LS  = `ls -1t /usr/local/freeswitch/log/cdr-csv/Master.csv.*`;
foreach my $line (@LS) {
    my $ul      = $dbh->prepare($stm) or die "$0: Couldn't prepare statement $stm: " . $dbh->errstr;;
    system("cat $line >> /usr/local/freeswitch/log/cdr-csv/FULL_Master.csv"); # we do this to maintain a single FULL file if needed
    unlink $line;

# one silly thing is that each accountcode has its own cdr.csv as well, either handle those here, by loading them into their own tables, or rm them
my @BS  = ("xtec","megaphone","mafcom","xeivacom");
foreach my $code (@BS) {
   @LS = `ls -1t /usr/local/freeswitch/log/cdr-csv/$code.csv.*`;
   foreach my $line (@LS) {
       #unlink($line);  #to delete
       move($line, "/usr/local/freeswitch/log/cdr-csv/trash/$code/"); # or move into a separate dir for later procession
exit 0;