Making CDR-Stats 2.0.9+ work with Asterisk+PostgreSQL CDR

2,159 views

Skip to first unread message

Iain Chesworth

unread,

Oct 6, 2013, 8:48:30 AM10/6/13

to cdr-...@googlegroups.com

Hey,

I've just been fighting with CDR-Stats 2.0.x to get it work with Asterisk using PostgreSQL for the CDR records. The rest of this post describes - in some short hand - everything you need to do to get Asterisk putting it's CDR data into a PostgreSQL database, connecting that to CDR-Stats, and have CDR-Stats import that data and do it's thing.

Contextual Stuff....for those reading on.

asterisk is a virtualised host running Debian 7.1, Asterisk, and PostgreSQL for CDR
cdr-stats is a virtualised host running Ubuntu 12.04.3 LTS server, CDR-Stats
Asterisk is version 11.4 and is built from the stock source tarball.
CDR-Stats is version 2.0.9 and is built from whatever the installation script does
$ means a standard user bash prompt
# means a root prompt (or a comment, which is waaaaay more likely).

Assumptions:

Your standard user is a member of the sudoers group (i.e. and you have sudo installed).

Part 0 - Installing CDR-Stats

Handy Hint: The install script completely blows up on Debian Wheezy. It has many deps on Ubuntu....which it does tell you. Don't try and make it play nicely, just give up and use the Ubuntu 12.04.3 server ISO.

Handy Hint: If you are installing for Asterisk+PostgreSQL, do NOT use the standard Asterisk installer script provided by CDR-Stats. It assumes you have a mysql database installed, tries to change it, and then fails and dies. If you want to use Asterisk+PostgreSQL, install using

wget –no-check-certificate https://raw.github.com/Star2Billing/cdr-stats/master/install/install-cdr-stats.sh -O install-cdr-stats.sh

bash install-cdr-stats.sh

and select the FreeSwitch option (option 1) when asked. This is *IMPORTANT*!

Handy Hint: When configuring AMI integration, it appears that the CDR-Stats AMI user only needs "read = call,cdr" and nothing else. This is not mentioned anywhere in the documentation so I thought I'd put it here. Why open things up more than they need to be.

Part 1 – Configuring Asterisk To Record CDR Data To PostgreSQL

Stage 1.1: Installing the database and configuring cdr_pgsql.so

By default, building Asterisk from source on a stock Debian installation doesn't actually allow you to build the cdr_pgsql.so module. To do so, you must install the libpg-dev library. This library replaces the original postgresql-dev library on pre-wheezy distributions. Since, we can use apt-get to do the dirty work, just tag all the various deps together.

$ sudo apt-get install postgresql postgresql-contrib postgresql-client libpg-dev

Now, go and build the cdr_pgsql.so module and install it into the modules folder. There is no need to run 'make install' as only the single executable is required.

$ cd /usr/src/asterisk-11.4.0/
$ ./configure
$ make menuselect

- configure for cdr_pgsql.so

$ make

$ cp cdr/cdr_pgsql.so /usr/lib/asterisk/modules/cdr_pgsql.so

Stage 1.2: Creating the PostgreSQL database (with users and tables)

Okay, the time has come to configure the database with the tables and users (and stuff) necessary for Asterisk to dump CDR data. The reputed /contrib/scripts database file mentioned on the Internet seems to have disappeared from the stock tarball. The following SQL should adequately replace whatever its contents is.

Handy Hint: Asterisk 12.x onwards drastically changes how the CDR works. I would suspect that neither this nor CDR-Stats will work with Asterisk 12 (at least until patches are submitted for CDR-Stats).

$ sudo su - postgres
$ psql

postgres=# CREATE USER asterisk WITH PASSWORD 'sooopersecretpassword';

CREATE ROLE

postgres=# CREATE DATABASE asterisk OWNER asterisk;

CREATE DATABASE

postgres=# \q

$ psql -d asterisk

asterisk=# CREATE TABLE cdr (

asterisk(# calldate timestamp with time zone NOT NULL default now(),

asterisk(# clid varchar(80) NOT NULL default '',

asterisk(# src varchar(80) NOT NULL default '',

asterisk(# dst varchar(80) NOT NULL default '',

asterisk(# dcontext varchar(80) NOT NULL default '',

asterisk(# channel varchar(80) NOT NULL default '',

asterisk(# dstchannel varchar(80) NOT NULL default '',

asterisk(# lastapp varchar(80) NOT NULL default '',

asterisk(# lastdata varchar(80) NOT NULL default '',

asterisk(# duration bigint NOT NULL default '0',

asterisk(# billsec bigint NOT NULL default '0',

asterisk(# disposition varchar(45) NOT NULL default '',

asterisk(# amaflags bigint NOT NULL default '0',

asterisk(# accountcode varchar(20) NOT NULL default '',

asterisk(# uniqueid varchar(32) NOT NULL default '',

asterisk(# userfield varchar(255) NOT NULL default ''

asterisk(# );

CREATE TABLE

asterisk=# GRANT INSERT ON cdr TO asterisk;

GRANT

asterisk=# \q

As can be seen in the final SQL statement, the 'asterisk' user must be given the ability to insert records into the 'cdr' table....this should be self-explanatory.

Stage 1.3: Configuring Asterisk To Record CDR To PostgreSQL

Now, configuring Asterisk is really simple. It automatically loads the cdr_pgsql.conf file (if the module cdr_pgsql.so is present). All you have to do is remove the comments from the cdr_pgsql.conf file as below.

Change /etc/asterisk/cdr_pgsql.conf

[global]

hostname=localhost

port=5432

dbname=asterisk

table=cdr ; SQL table where CDRs will be inserted

user=asterisk

password=sooopersecretpassword

encoding=LATIN9 ; Encoding of logged characters in Asterisk

;timezone=UTC ; Uncomment if you want datetime fields in UTC/GMT

Asterisk will not yet recognise the new module (which was copied in above). Restart Asterisk to trigger the new configuration *and* load the new module. If you just reload the configuration, nothing special will happen as Asterisk won't realise the cdr_pgsql.so module is there.

$ sudo /etc/init.d/asterisk restart

$ sudo asterisk -vvvvr

asterisk*CLI> help cdr

cdr show pgsql status Show connection status of the PostgreSQL CDR driver (cdr_pgsql)

cdr show status Display the CDR status

Part 2 – Configuring PostgreSQL So That CDR-Stats Can Use It

Stage 2.1: Allow External Hosts To Access The Database

By default, PostgreSQL only binds and listens to localhost. This must be updated so that the database listens on the actual network adapter IP address. There are two things that must be done to achieve network connectivity from a remote host. The first is configuring the trust so that PostgreSQL accepts passwords and so forth, the second is binding to a non-localhost address.

Part 1:

$ sudo nano -w /etc/postgres/9.1/main/pg_hba.conf

Append:

# Permit local network hosts to query the database (replace 192.168.1.0/24 with your required range)

host all all 192.168.1.0/24 trust

Part 2:

$ sudo nano -w /etc/postgres/9.1/postgresql.conf

Replace the listen address with:

listen_addresses='*'

Stage 2.2: Sort Out The CDR-Stats Database User

CDR-Stats needs to have a database user. You could use the standard Asterisk one...but its easier to manage permissions if they are separate. Also, it means that CDR-Stats doesn't automatically have access to everything in the database (if you use it for more than just the CDR data). There is *NO* mention anywhere on the CDR-Stats website of the permissions required for the database users. Reading the Python, it appears that the user only needs SELECT on table 'cdr' and update to 'import_cdr' and 'acctid'.

$ sudo su - postgres

$ psql -d asterisk

asterisk=# CREATE USER cdr_stats WITH PASSWORD 'sooopersecretpassword';

CREATE ROLE

asterisk=# GRANT SELECT,UPDATE ON cdr to cdr_stats;

GRANT

postgres=# \q

Handy Hint: CDR-Stats attempts to alter the database schema at runtime to add extra columns, indexes, and other stuff if they don't ready exist. Unfortunately, the Python assumes that the database in question supports MySQL syntax and this will fail on PostgreSQL. While it may appear that just a simple ALTER TABLE...OWNER change on the database may suffice will enable the Python to work, it really doesn’t. More manual magic is needed. Yaaaay!

Stage 2.3: Bugs Bugs Bugs (aka the joy of CDR-Stats integration with PostgreSQL)

Add all the extra columns, indexes, and so forth that CDR-Stats wants to have (and will do it's runtime-damnedest to make happen).

$ sudo su - postgres

$ psql -d asterisk

asterisk=# ALTER TABLE cdr ADD import_cdr SMALLINT NOT NULL DEFAULT '0';

ALTER TABLE

asterisk=# CREATE SEQUENCE acctid_seq;

CREATE SEQUENCE

asterisk=# ALTER TABLE cdr ADD acctid BIGINT NOT NULL default nextval('acctid_seq');

ALTER TABLE

asterisk=# ALTER SEQUENCE acctid_seq OWNED BY cdr.acctid;

ALTER SEQUENCE

asterisk=# GRANT USAGE, SELECT ON SEQUENCE acctid_seq TO asterisk;

GRANT

Oh – this has not sorted out the index on cdr(import_cdr) which the original SQL called for. That’s not necessarily an issue (perhaps a performance one for large installations). It also doesn’t set up any foreign keys that the original SQL may require (although I cannot see any in the Python, the installer script does some SQL strangeness in the default Asterisk-MySQL parts).

Stage 2.3.x...: What To Do If You've Forgotten To GRANT Asterisk Extra Privileges

If you’ve forgotten to sort out the GRANT USAGE,SELECT then Asterisk will fail to write to the CDR database with an error message similar to:

[Oct 6 22:17:25] ERROR[12054][C-00000001] cdr_pgsql.c: HARD ERROR! Attempted reconnection failed. DROPPING CALL RECORD!

[Oct 6 22:17:25] ERROR[12054][C-00000001] cdr_pgsql.c: Reason: ERROR: permission denied for sequence acctid_seq

Once the permissions are fixed, do the following to reset the CDR-Stats database:

$ sudo /etc/init.d/cdr-stats-celeryd stop

$ sudo mongo

> use cdr-stats;

switched to db cdr-stats

> db.monthly_analytic.remove({});

> db.daily_analytic.remove({});

> db.aggregate_world_report.remove({});

> db.aggregate_result_cdr_view.remove({});

> db.aggregate_hourly_country_report.remove({});

> db.cdr_common.remove({});

bye

$ sudo su - postgres

$ psql -d asterisk

asterisk=# UPDATE cdr SET import_cdr=0 WHERE import_cdr=1;

UPDATE 1

$ sudo /etc/init.d/cdr-stats-celeryd start

Stage 2.4: Finally, Set Up CDR-Stats (If You’ve Not Already)

Configure the /usr/share/cdr_stats/settings_local.py:

LOCAL_SWITCH_IP = 'asterisk.271-bluff-road.home'

CDR_BACKEND = {

'asterisk.271-bluff-road.home': { # was MYSQL_IMPORT_CDR_HOST

'db_engine': 'pgsql', # mysql, pgsql, mongo

'cdr_type': 'asterisk', # asterisk or freeswitch

'db_name': 'asterisk', # was 'MYSQL_IMPORT_CDR_DBNAME',

'table_name': 'cdr', # was 'MYSQL_IMPORT_CDR_TABLENAME',

'host': 'asterisk.271-bluff-road.home', # was 'MYSQL_IMPORT_CDR_HOST',

'port': 5432, # 3306 mysql, 5432 pgsql

'user': 'cdr_stats', # was 'MYSQL_IMPORT_CDR_USER',

'password': 'sooopersecretpassword', # was 'MYSQL_IMPORT_CDR_PASSWORD',

Handy Hint: The name use above (in the line that originally contains MYSQL_IMPORT_CDR_HOST) *must* match the LOCAL_SWITCH_IP. It’s not a plain-text field it would seem at first glance. This doesn't appear to be mentioned in the documentation and seems to be the cause of much confusing both on the group and on the wider Internet.

Hopefully, this will enable some of you with questions about the PostgreSQL CDR support to answer what you need to know. The basic support for Asterisk+PostgreSQL CDR in CDR-Stats 2.0 is in there, it's just rough enough to make it non-trivial to install, configure, or manage.