From FreeSWITCH Wiki

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@89.212.283.238:5060","sofia/out/+919814440333@222.199.89.27","bridge","sofia/out/+919814440203@222.199.89.27","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","89.202.183.238"

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>

Some significant variables

accountcode

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.

amaflags

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

answer_stamp

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

billsec

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).

bleg_uuid

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

bleg_uuidaccountcode

The accountcode of the bleg_uuid in the call.

bridge_channel

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

call_clientcode

caller_id

caller_id_name

See Channel_Variables#caller_id_name

caller_id_number

See Channel_Variables#caller_id_number

channel_name

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

context

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

destination_number

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

duration

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

end_stamp

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

hangup_cause

See Hangup_causes

last_app

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

last_arg

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

read_codec

See Channel_Variables#read_codec

sip_gateway_name

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

sip_rtp_rxstat

sip_user_agent

sofia_record_file

start_stamp

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

userfield

A field to set your own custom values. For example

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

uuid

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

write_codec

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

 /usr/local/freeswitch/conf/autoload_configs/cdr_csv.conf.xml

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.

 CREATE TABLE cdr (
 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,
 read_codec varchar(20) DEFAULT NULL,
 write_codec varchar(20) DEFAULT NULL 
 );

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 logfile 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) {
    chop($line);
    my $stm     = "LOAD DATA LOW_PRIORITY LOCAL INFILE '$line' INTO TABLE cdr FIELDS ENCLOSED BY '\"' TERMINATED BY ','";
    my $ul      = $dbh->prepare($stm) or die "$0: Couldn't prepare statement $stm: " . $dbh->errstr;;
    $ul->execute();
    $ul->finish;
    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) {
       chop($line);
       #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;

Mod cdr csv