Skip to main content

mod_cdr_pg_csv

About

This module logs call detail records (CDRs) directly to a PostgreSQL database, using the schema defined in the config file (freeswitch/conf/autoload_configs/cdr_pg_csv.conf.xml).

If a record insert fails for whatever reason, the record will be locally spooled to disk, in either CSV or SQL format (user-configurable).

Click here to expand Table of Contents

Installing

Debian:

To compile/install the module on Debian the PostgreSQL-Server-Devs are needed.

On Squeeze/Wheezy/Jessie use PostgresSQL packages to install. You can use:

http://www.postgresql.org/download/linux/debian/

PostgresSQL maintains Debian packages for Postgres for (currently) Debian Squeeze (6.x), Debian Wheezy (7.x) and Debian Jessie (8.x). Their packages will likely be more modern and up to date than what's in the Debian packages.

Read the link above and carefully add the packages to your system.

Or

aptitude install libpq-dev libpq5

For compiling the module from Freeswitch uncomment the module in modules.conf and add the module to freeswitch/conf/autoload_configs/modules.conf.xml for autoload:

conf/autoload_configs/modules.conf.xml

<load module="mod_cdr_pg_csv"/>

CentOS 6.x:

rpm -ivh http://yum.postgresql.org/9.1/redhat/rhel-6-i386/pgdg-centos91-9.1-4.noarch.rpm
yum -y install postgresql91-devel postgresql91-server postgresql91-odbc postgresql91-plperl

Link your pg_config like this: ln -s /usr/pgsql-9.1/bin/pg_config /usr/bin

For CentOS to use PG 9.X instead of 8.3 (Standard w/git pull version) Series:

Modify Makefile for mod_cdr_pg_csv to look like this:

UNAME := $(shell uname -s)
ifeq ($(UNAME),SunOS)
ISA64 := $(shell isainfo -n)
LOCAL_CFLAGS=-I/usr/pgsql-9.1/include
ifneq (,$(findstring m64,$(CFLAGS)))
LOCAL_LDFLAGS=-L/usr/pgsql-9.1/lib/$(ISA64) -R/usr/pgsql-9.1/lib/$(ISA64) -lpq -static
else
LOCAL_LDFLAGS=-L/usr/pgsql-9.1/lib -R/usr/pgsql-9.1/lib -lpq -static
endif
else
LOCAL_CFLAGS=-I/usr/pgsql-9.1/include
LOCAL_LDFLAGS=-L/usr/pgsql-9.1/lib -lpq -static
endif
include ../../../../build/modmake.rules

Database Setup

Edit conf/autoload_configs/cdr_pg_csv.conf.xml

<param name="db-info" value="host=localhost dbname=DBname user=DBuser password=DBpass connect_timeout=10" />

make sure postgresql is set as md5 login in the pg_hba.conf.

host     all      all    127.0.0.1/32 md5 
local all all md5

Schema Definition

The schema section of the config tells the module which channel variable to grab from the call's session, and how to handle them. The SQL INSERT query will be constructed using parameters defined in the schema section, and fields will be in the order they are listed in the config file.

  • All fields are treated as strings (eg. they will have single quotes around them in the SQL query), unless specified otherwise.
  • If a channel variable resolves to an empty string, it will be cast to a SQL null, unless specified otherwise.
    • The reason for this is to be able to use more efficient, native PostgreSQL column types, such as timestamp (optionally with timezone), inet for storing IPv4/IPv6 addresses, and uuid for natively storing a UUID as a 128 bit binary value. PostgreSQL will not automatically cast empty strings for such fields, and therefore the values need to be handled by the module.

Defining a Field

The absolute minimum required to define a field is the channel variable name. Fields that do not specify a var attribute will be ignored. The following example will attempt to get a value for the channel variable_caller_id_number_, and will treat it as a string.

 <field var="caller_id_number"/>

To use a column name that is different to the channel variable name, simply override it in the field definition:

<field var="local_ip_v4" column="server_address"/>

Sample CDR table schema

create table cdr (
id serial primary key,
local_ip_v4 inet not null,
caller_id_name varchar,
caller_id_number varchar,
destination_number varchar not null,
context varchar not null,
start_stamp timestamp with time zone not null,
answer_stamp timestamp with time zone,
end_stamp timestamp with time zone not null,
duration int not null,
billsec int not null,
hangup_cause varchar not null,
uuid uuid not null,
bleg_uuid uuid,
accountcode varchar,
read_codec varchar,
write_codec varchar,
sip_hangup_disposition varchar,
ani varchar
);