Asterisk CDR & Django integration with ODBC

Tobias already mentioned how Caktus uses Asterisk as our PBX. He also mentioned how we tested various frontends both for managing the asterisk configuration and interacting with asterisk to, for example, check our voicemail. We were inticed by some of the client management solutions that we could plumb up with asterisk. Caktus has a loose administration structure, which allows us to be flexible and not have levels of managers between clients and coders. But, this flexiblility can leave loose ends unchecked when the person in charge of a project is distracted for a day or two. We saw this as an opportunity where Caktus could add a level of group accountability and use some neat technology. We also wanted a tool that would integrate well with our current homegrown DjangoERP/CRM as well as Trac, our prefered tool for project management. So, we decided to write some sweet code.

Asterisk provides Call Detail Records (CDR) information for billing calls. This is used for people reselling their asterisk setup on a per call basis which is not what we're doing, but it automatically records a lot of useful information about calls including who called whom when and how long the call lasted. We decided to tie into this information for our interface. Asterisk provides a CDR ODBC interface. ODBC is a generic interface which sits between applications wanting to use a database and the database server itself. This was useful since a lot of the built in features of asterisk rely on MySQL databases which we don't condone the use of. Instead, we used these instructions to setup our Asterisk ODBC interface using unixODBC in order to connect to our Postgres backend. This setup took a while to get all the pieces in place working together. It will take a lot of fiddling to get this working.

First, add an ODBC driver for your database. This is an example for using Postgres since that's our preference.

/etc/odbc-pgsql.ini:

[PostgreSQL]
Description = PostgreSQL driver for Linux & Win32
Driver = /usr/lib/odbc/psqlodbca.so
Setup = /usr/lib/odbc/libodbcpsqlS.so
FileUsage = 1

Add an ODBC interface for the database of the Django project where you want the CDR data to show up. Fill in the blanks with the correct information for your configuration.

/etc/odbc.ini:

[django_odbc]
Description = PostgreSQL Asterisk
Driver = PostgreSQL
Servername = localhost
UserName = django_db_user
Password = django_db_password
Database = django_db_name
Port = 5432
Option = 3

Tell asterisk how to connect to the unixODBC server to log CDR data.

/etc/asterisk/cdr_odbc.conf:

[global]
dsn=django_odbc
username=django_db_name
password=django_db_password
loguniqueid=yes
dispositionstring=yes
table=cdr
usegmtime=no

Tell asterisk to use the CDR/ODBC configuration we just configured to store CDR data.

/etc/asterisk/modules.conf:

load => cdr_odbc.so

Now, the database must be setup as well. Use one of the schemas provided by the asterisk project for the Cdr table in the database. I used this data to create a model in our Django project and imported it into our models.py file using "python ./manage.py inspectdb".

class Cdr(models.Model):
  acctid = models.TextField(primary_key=True)
  calldate = models.DateTimeField()
  clid = models.CharField(max_length=80)
  src = models.CharField(max_length=80)
  dst = models.CharField(max_length=80)
  dcontext = models.CharField(max_length=80)
  channel = models.CharField(max_length=80)
  dstchannel = models.CharField(max_length=80)
  lastapp = models.CharField(max_length=80)

  lastdata = models.CharField(max_length=80)
  duration = models.IntegerField()
  billsec = models.IntegerField()
  disposition = models.CharField(max_length=45)
  amaflags = models.IntegerField()
  accountcode = models.CharField(max_length=20)
  uniqueid = models.CharField(max_length=32)
  userfield = models.CharField(max_length=255)
  class Meta:
    db_table = u'cdr'


  def __str__(self):
    return "%s -> %s" % ( self.src, self.dst )

Storing real-time call information

Since CDR data is only needed for per call billing information, the CDR information is not stored until all the data comes in, after a call is completed. We wanted to be able to creates notes on a call as it was happening, so we created a new table, Interactions. A row in this table will be populated as soon as a call is made or received and it will provide a place for our notes. In order to do this we used Asterisk's ODBC Functions. These allow you to make any SQL call, inserting or selecting data, from within your dialplan.

Create a simple Interaction table in your django project.

django-project/app/models.py

class Interaction(models.Model):
  project = models.ForeignKey(Project, null=True)
  contacts = models.ManyToManyField(User, related_name='interactions')
  memo = models.TextField(null=True)
  cdr = models.ForeignKey('Cdr', null=True, to_field='uniqueid', editable=False)

Now enable a connection for ODBC functions using the unixODBC settings from before.

/etc/asterisk/res_odbc.conf:

[django_odbc]
enabled => yes
dsn => django_odbc
pre-connect => yes

Tell asterisk to use the func_odbc driver.

/etc/asterisk/modules.conf:

preload => func_odbc.so

Here is the actual SQL statement to be called from within the dialplan. We've added a bit of SQL to update the contact field as well based on who was called or who called us. It's probably too tied to our CRM model to be useful for you, but it's not that difficult to do, if you've made it this far. Here, you should replace app_interaction with the name of your Interaction table. It should not be important for security to escape this value since it's internal to asterisk.

/etc/asterisk/func_odbc.conf:

[LOG_INTERACTION]
dsn=django_odbc
write=INSERT INTO app_interaction (cdr_id,project,contacts,memo) VALUES ('${VAL1}',NULL,NULL,NULL);

A call to this function should be made from your dialplan (extensions.conf). This will create an Interaction and link it to the Cdr row for the call once that is populated using the unique id that asterisk assigns each call.

Incoming

/etc/asterisk/extensions.conf:

exten => _XX.,1,Set(ODBC_LOG_INTERACTION()=${UNIQUEID})
exten => _XX.,2,.....

Outgoing

/etc/asterisk/extensions.conf:

exten => _1NXXNXXXXXX,1,Set(ODBC_LOG_INTERACTION()=${UNIQUEID})
exten => _1NXXNXXXXXX,2,....

Future plans.

Store our voicemail in a database. Also, we've had the CallerID information displayed on our softphones populated by our CRM information, but this is not currently working. It would definitely worth looking at again. It would help ease the context switch of stopping what we're doing and picking up the phone. It also would let us better determine who the call is bound for and let that person pick up reducing the number of folks a client has to talk to and not make us transfer them around.