Collecting Billing Information

If you are using YATE, you may wish to collect information with which you can then either bill customers for or verify the bills you receive from other service providers.

You will need to configure YATE to record CDRs.

CDRs can be collected and output to either files, or inserted into a database.

To enable the collection of CDRs, edit the configuration file cdrbuild.conf.

Specify the resolution you want (usec is recommended) and if you wish to override any fields.

Initially, you should not need to override any. Wait until the need arises.

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 Postgres respectively and then register.conf to specify the queries to use.

CDRs to files

CDRs to Postgres

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 use 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 fourextra 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.

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)