Mod cdr csv

From FreeSWITCH Wiki
Jump to: navigation, search

Contents

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.


Default configuration

conf/autoload_configs/cdr_csv.conf.xml

<configuration name="cdr_csv.conf" description="CDR CSV Format">
  <settings>
    <!-- 'cdr-csv' will always be appended to log-base -->
    <!--<param name="log-base" value="/var/log"/>-->
    <param name="default-template" value="example"/>
    <!-- This is like the info app but after the call is hung up -->
    <!--<param name="debug" value="true"/>-->
    <param name="rotate-on-hup" value="true"/>
    <!-- may be a b or ab -->
    <param name="legs" value="a"/>
  </settings>
  <templates>
    <template name="sql">INSERT INTO cdr VALUES ("${caller_id_name}","${caller_id_number}","${destination_number}","${context}","${start_stamp}","${answer_stamp}","${end_stamp}","${duration}","${billsec}","${hangup_cause}","${uuid}","${bleg_uuid}", "${accountcode}");</template>
    <template name="example">"${caller_id_name}","${caller_id_number}","${destination_number}","${context}","${start_stamp}","${answer_stamp}","${end_stamp}","${duration}","${billsec}","${hangup_cause}","${uuid}","${bleg_uuid}","${accountcode}","${read_codec}","${write_codec}"</template>
    <template name="snom">"${caller_id_name}","${caller_id_number}","${destination_number}","${context}","${start_stamp}","${answer_stamp}","${end_stamp}","${duration}","${billsec}","${hangup_cause}","${uuid}","${bleg_uuid}", "${accountcode}","${read_codec}","${write_codec}","${sip_user_agent}","${call_clientcode}","${sip_rtp_rxstat}","${sip_rtp_txstat}","${sofia_record_file}"</template>
    <template name="linksys">"${caller_id_name}","${caller_id_number}","${destination_number}","${context}","${start_stamp}","${answer_stamp}","${end_stamp}","${duration}","${billsec}","${hangup_cause}","${uuid}","${bleg_uuid}","${accountcode}","${read_codec}","${write_codec}","${sip_user_agent}","${sip_p_rtp_stat}"</template>
    <template name="asterisk">"${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>
  </templates>
</configuration>

Useful things to know:

  • You can change the "legs" value "a" for a-leg, "b" for b-leg and "ab" for both legs.
  • The template can be changed to include useful information such as "${remote_media_ip}" and "${network_addr}"
  • The template can be changed to include any custom variables you have set in the dialplan of the corresponding leg, with something like {customer_name=whitehouse}sofia/gateway/foo/$1. Then ${customer_name} can be used in the CDR template.

XML Structure

The following params can be set under the settings tag in your cdr_csv.conf.xml file:


debug

When set to true, this prints out all channel data in your log when creating a new CDR record is generated.

  • Possible values: 'true' or 'false'
  • Default value: 'false'


default-template

The name of a template as defined in the 'templates' section of your cdr_csv.conf.xml

  • Default value: 'default'


legs

Choose whether the module will save cdr's for the a or b leg, or both of them.

  • Possible values: 'a', 'b', 'ab'
  • Default value: 'a'


log-base

The directory where the csv files will be written to.

  • Default value: root-of-your-freeswitch-installation/log/cdr-csv


master-file-only

When enabled, all cdr's will be written to a single file instead of to multiple files split up per accountcode.

  • Possible values: "true', 'false'
  • Default value: 'false'


rotate-on-hup

When enabled, after sending a HUP signal to FreeSWITCH, the logfile will be rotated to a format like this:

Master.csv.YYYY-mm-dd-HH-MM-SS

API

To rotate CDRs (This method will rotate only CSV CDRs without HUPing the entire FS)

cdr_csv rotate

Or, from fs_cli

fs_cli -x 'cdr_csv rotate'

Variables in a Template

Variables are given in the 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 begin_of_the_skype_highlighting              +919814440333      end_of_the_skype_highlighting begin_of_the_skype_highlighting              +919814440333      end_of_the_skype_highlighting@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>

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

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_epoch

UNIX epoch value when the call was answered, eg. 1294060468 for Mon, 03 Jan 2011 13:14:28 GMT.

answer_stamp

Timestamp when the call was answered (eg, SIP 200 OK is received), in ISO 8601 format (YYYY-MM-DD hh:mm:ss), in the local timezone (not UTC). If the call is not answered, this will be an empty string.

billsec

The answered or billing span of the calls in seconds, i.e. "end_stamp - answer_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. e.g. 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 perform 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_epoch

UNIX epoch value when the call ended, eg. 1294060468 for Mon, 03 Jan 2011 13:14:28 GMT.

end_stamp

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

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_epoch

UNIX epoch value when the call was started, eg. 1294060468 for Mon, 03 Jan 2011 13:14:28 GMT.

start_stamp

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

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,
   domain_name varchar(100) DEFAULT NULL
 );

For PostgreSQL

 CREATE TABLE cdr (
   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) {
    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;