Call Detail Records Modules

Latest revision as of 14:55, 11 January 2014

There are 4 modules for CDR in Yate:

• Cdrbuild
• Cdrcombine
• Register
• Cdrfile

In YATE, if you wish to collect information with which you can then either bill customers or verify the bills you received from other service providers, you will need to configure YATE to record CDRs. CDRs can be collected and output to either files using cdrfile, or inserted into a database using register.

Cdrbuild module generates CDRs per channel not like cdrcombine module that generates CDR per call. The cdrcombine module enables the writing of a single CDR entry per call instead of multiple entries, one for each call leg (one for the incoming leg and one or more for outgoing leg(s)). There is no configuration file for cdrcombine module. In order to use it set combine option to true in cdrfile.conf or set cdr_combined in register module [call.cdr] section.

If you wish to store CDRs to a file, you will need to use cdrfile.conf. If you wish to store CDRs in a database you will need to edit either mysqldb.conf or pgsqldb.conf, for either MySQL or PostgreSQL respectively and then register.conf to specify the queries to use.

[edit] Examples of writing CDRs to databases

[edit] CDRs to PostgreSQL

Once you have configured your database access details in pgsqldb.conf you will need to create a table to hold the CDRs.

The recommend table to store the CDRs is:

CREATE TABLE cdr (
    id serial,
    "time" timestamp without time zone not null,
    billid text not null,
    chan text not null,
    address text not null,
    caller text not null,
    "called" text not null,
    billtime interval not null,
    ringtime interval not null,
    duration interval not null,
    direction text not null,
    status text not null,
    reason text not null,
    ended boolean not null default false,
    billed boolean not null default false,
    billed_on timestamp without time zone
);

This has four extra fields (id, ended, billed and billed_on) than a file-based CDR does, but they give you great flexibility.

• id you can use within the database to assign a canonical sitewide CDR id to a call. If you have a single Yate instance, it may be hard to imagine why you would want this. But anyone running multiple YATEs should have it.
• ended will be used by the queries in register.conf to mark when a CDR is complete.
• billed you can use in your billing system to mark when a particular set of records has been either billed, or exported for billing.
• billed_on is used to indicate what date a record was exported. Let's say you need to re-send CDRs for a particular date range, this can help you. Arguably billed_on makes billed redundant as a not null value is the same as billed = true; in practise I've found that querying against billed is faster than billed_on.

For example a self-join like this select * from cdr where id not in (select id from cdr where billed) and ended will give you every call that is ready to be billed.

[edit] CDRs to MySQL

First you need to configure mysqldb.conf. Then you create a database (or use an existing one) with one table.

The recommended structure is:

CREATE TABLE `cdrs` (

 `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
 `time` INT(10) UNSIGNED DEFAULT NULL,
 `billid` VARCHAR(18) DEFAULT NULL,
 `chan` VARCHAR(20) NOT NULL DEFAULT ,
 `address` VARCHAR(30) DEFAULT NULL,
 `caller` VARCHAR(30) DEFAULT NULL,
 `called` VARCHAR(30) DEFAULT NULL,
 `billtime` INT(10) UNSIGNED DEFAULT NULL,
 `ringtime` INT(10) UNSIGNED DEFAULT NULL,
 `duration` INT(10) UNSIGNED DEFAULT NULL,
 `direction` VARCHAR(8) DEFAULT NULL,
 `status` VARCHAR(11) DEFAULT NULL,
 `reason` VARCHAR(40) DEFAULT NULL,
 `ended` SMALLINT(1) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`),
 KEY `cdr_update` (`chan`, `time`),
 KEY `init` (`ended`)
) ENGINE=MyISAM;

We have two additional keys (init, cdr_update), because in register.conf we are going to search exactly on those 3 fields (chan and time and ended). The above information about the pgsql table fields is applied to this table as well.

While Yate's register.conf is originally created for pgsql you need to edit it, if you want to collect CDRs in the above table:

[call.cdr]

critical=no

initquery=UPDATE cdrs SET ended = 1 WHERE ended IS NULL OR ended = 0

cdr_initialize=INSERT INTO cdrs VALUES(NULL, '${time}', '${billid}', '${chan}', '${address}', '${caller}', '${called}', '${billtime}', '${ringtime}', '${duration}', '${direction}', '${status}', 
 '${reason}', 0)

cdr_update=UPDATE cdrs SET address = '${address}', direction = '${direction}', billid = '${billid}', caller = '${caller}', called = '${called}',
duration = '${duration}', billtime = '${billtime}', ringtime  = '${ringtime}', status = '${status}', reason = '${reason}'
WHERE chan = ${chan}' AND time = CAST(${time} AS UNSIGNED) 

cdr_finalize=UPDATE cdrs SET address = '${address}', direction = '${direction}', billid = '${billid}', caller = '${caller}', called = '${called}', 
duration = '${duration}', billtime = '${billtime}', ringtime = '${ringtime}', status = '${status}', reason = '${reason}', ended = 1 WHERE chan = '${chan}'
AND time = CAST(${time} AS UNSIGNED)

[edit] Examples of writing CDRs to file

[edit] Writing CDRs per channel to file

• Make sure the cdrbuild, and cdrfile modules are loaded.

• In cdrfile.conf set:

[general]
; file: string: Name of the file to write the CDR to
; You should check that this file is log rotated - see /etc/logrotate.d/yate
file=/var/log/yate-cdr.csv

format=${time},"${billid}","${chan}","${address}","${caller}","${called}",${billtime},${ringtime},${duration},"${direction}","${status}","${reason}"

• Example of output in configured yate-cdr.csv

2014-01-11 13:58:27,"1389441448-1","sip/2","192.168.168.156:5060","104","103",23.019,14.596,37.621,"answered","","","",,,,""
2014-01-11 13:58:27,"1389441448-1","sip/1","192.168.168.18:1368","104","103",23.023,14.596,37.635,"answered","","","",,,,""

[edit] Writing CDRs per call to file

• Make sure the cdrcombine, cdrbuild, and cdrfile modules are loaded.

• In cdrfile.conf set:

[general]
; file: string: Name of the file to write the CDR to
; You should check that this file is log rotated - see /etc/logrotate.d/yate
file=/var/log/yate-cdr.csv

; combined: bool: Use combined CDR for all legs of a call
combined=true

; comma-separated (.csv), combined=true
format=${time}, "${billid}", "${chan}", "${address}", "${caller}", "${called}", ${billtime}, ${ringtime}, ${duration}, "${status}", "${reason}", "${out_leg.chan}", "${out_leg.address}", ${out_leg.billtime}, ${out_leg.ringtime}, ${out_leg.duration}, "${out_leg.reason}"

• Example of output in configured yate-cdr.csv

1389440074.360,"1389439944-3","sip/3","192.168.168.18:1368","104","103",107.161,17.640,124.823,"answered","","sip/4","192.168.168.156:5060",107.161,17.640,124.811,""

See also

• CDRcombine Module
• CDRbuild Module