9 min read

The world has changed quite a bit in the last 150 years. Over this time, the telephone system has been invented, improved, and automated. Telephone switches no longer refer to people sitting in a large room connecting wires between the appropriate jacks. Flexible and powerful telephone service has moved from a dream to an expectation in large businesses, and for most of us it is a necessity.

Today, telephone systems are the lifeblood of business. They are how we take orders, acquire supplies, and even call for emergency assistance. With the increase in prominence of telephones, the expectations of telephone users have increased proportionally.

Not only have the technological expectations for telephone systems increased dramatically, but consumers are expecting more and more out of the businesses they call. Customers expect to be helped quickly and professionally. They want to know everything in a matter of minutes. Roads do not hold the only rage our society is facing today. As a business we have a variety of questions relating to our telephone system such as:

  • How are our personnel handling angry callers?
  • Are our employees answering the calls in a reasonable amount of time?
  • Do we have any workers using the phone system for personal calls when they should be doing their job?

We will never be able to make sure everybody does what they are supposed to do all of the time. What we will be able to do at the end of this article is perform spot-checks on how we are doing on customer service, and make sure our phone service isn’t being used for unauthorized purposes. Ultimately, it comes down to a matter of trust; however, some people do not know better because they haven’t been fully trained. Most will always act honorably; however, some just cannot and should not be trusted. We will try to find out who is who.

Call Detail Records

When we talk about security, what images come to mind? May be a big, burly guard? Perhaps a bunch of guys in green, carrying machine guns? Do we imagine a person with a metal-detecting wand? Or do we think of thick glass window panes?

All of these are security features. It is just that some are a little more intrusive than others. Each time we increase security, we become a little bit less friendly. We all have to decide how far we are willing (and able) to go.

In the continuum of security, Call Detail Records are the least intrusive. No special usernames or passwords have to be remembered. No fear of big brother breathing down your customers’ and users’ necks need be felt. We are simply doing the same thing telephone companies do—tracking what calls were made, when they were made, how long they lasted, where they came from, and a few other bits of information. This information is then available for us to review at our leisure.

Asterisk gives us a few options on how we track this information. The two major choices are flat-file logging and database logging.

Flat-file CDR logging

By default, Asterisk includes a module called cdr_csv. Right out of the box, Asterisk logs all calls coming in and going out. The information for these calls is placed in a Comma Separated Value (CSV) file. This CSV file is located in var/log/asterisk/cdr-csv. All information is available in Master.csv, and some channels can be configured to send some information to other files as well.

The benefit of using a CSV file is the simplicity. Right after compiling and installing Asterisk, this method will work. No additional configuration is required. Also, no additional network traffic is generated, and no additional services have to be installed on our server.

When using the CSV form of CDR, we will see lists and lists of values. They are not very easy to parse, so here is the format, in the order in which they appear:

  • account code: As determined by the channel (for DAHDI) or the user (for IAX and SIP)
  • source: The source of the call
  • destination: The destination of the call
  • destination context
  • caller ID
  • channel: The channel of the source
  • destination channel: If applicable
  • last application: The last application run on the channel
  • last application argument: The last argument to the last application on the channel
  • start time: The time the call commenced
  • answer time: The time the call was answered
  • end time: The time the call ended
  • duration: The difference between start time and end time
  • billable seconds: The difference between answer time and end time, which must be less than the duration
  • disposition: Either ANSWERED, NO ANSWER, or BUSY
  • amaflags: As set for the channel or user, like account code
  • uniqueid: A unique call identifier
  • userfield: A user field set by the SetCDRUserField command

We see that there are many items of information logged for each and every call. We can compare the billable seconds with our phone bill at the end of the month to make sure they’re close. We can look at the destination and figure out if the calls were authorized. This gives us enough information to answer most questions we may have about a phone call.

While we have enough information to answer questions, finding that answer is not very easy. We would have to scan through the entire file to try to find anything. If we are going to use an accounting package or reporting software, CSV may be exactly what we need. However, if we wish to use it in a more ad hoc sort of way, it is not very readable.

Database CDR logging

If we wish to read our CDR logs, it is most easily accomplished when the records are sortable. The easiest way to do this is to store our CDR records in a database.

Even in this, Asterisk gives us choices. Included with Asterisk is support for PostgreSQL databases. In order to be able to install this, we must first have the postgresql-devel package installed on our system. If you have to install this package, you’ll need to reinstall Asterisk. The automake system will automatically detect that we have the capability to use PostgreSQL and compile that module for us.

Aside from the development packages we have installed, we will also need a PostgreSQL server somewhere in our network. It can be the same machine as the Asterisk server, but it doesn’t necessarily need to be. In fact, it probably makes sense to have only one such database server on our network, and we don’t want to tie up too much of our PBX’s resources with database maintenance and storage.

There is a script in /usr/src/asterisk/contrib/scripts/ called postgres_cdr.sql, which creates the correct table structure for us. This script should be run from the database server.

If we get an error message while rebuilding that says something like “cannot find-lz”, then we need to install zlib-devel.

Now that we have set up our database and installed the CDR module, we must configure Asterisk to use the correct database. In order to do this, we need to edit /etc/asterisk/cdr_pgsql.conf. All of the configuration variables are in the global section. Our file should look like the following:

[global]
hostname=dbserver.mydomain.tld
port=5432
dbname=asterisk
password=supersecret
user=asteriskuser

Once we have these variables set, the next time we restart Asterisk, all CDR records will be logged in the database.

If PostgreSQL is not our database of choice, we can use MySQL. This is not a part of the normal distribution of Asterisk. But as we have already installed asterisk-addons, we should already have the ability to use MySQL for CDR logging.

Before we compile, we need to make sure that we have mysql-devel installed. First, we need to decide which version we’re going to use. Because of some license quibbles, MySQL version 4.0 and later is not in the automatic package distribution chain. Instead, if we do need to download it, we will have to get it directly from www.mysql.com. However, the older version (3.x) will work with Asterisk and hence, you may wish to take a look at the differences between what version 3 offered and what later versions give us.

Other than the development package mentioned, we will also need a MySQL server somewhere in our network. Just as with PostgreSQL, we can choose to have it on the same server as Asterisk, but for the same reasons, we probably shouldn’t.

Next, on the database server, we need to create the database with a user and a table for the CDR data. We do this by running the following code:

# mysqladmin create database asteriskcdrdb 
# mysql
mysql> GRANT ALL PRIVILEGES
   -> ON asteriskcdrdb.*
   -> TO asteriskcdruser
   -> IDENTIFIED BY 'changethis2yourpassword';
mysql> USE asteriskcdrdb;
mysql> CREATE TABLE cdr (
   -> uniqueid varchar(32) NOT NULL default '',
   -> userfield varchar(255) NOT NULL default '',
   -> accountcode varchar(20) NOT NULL default '',
   -> src varchar(80) NOT NULL default '',
   -> dst varchar(80) NOT NULL default '',
   -> dcontext varchar(80) NOT NULL default '',
   -> clid varchar(80) NOT NULL default '',
   -> channel varchar(80) NOT NULL default '',
   -> dstchannel varchar(80) NOT NULL default '',
   -> lastapp varchar(80) NOT NULL default '',
   -> lastdata varchar(80) NOT NULL default '',
   -> calldate datetime NOT NULL default '0000-00-00 00:00:00',
   -> duration int(11) NOT NULL default '0',
   -> billsec int(11) NOT NULL default '0',
   -> disposition varchar(45) NOT NULL default '',
   -> amaflags int(11) NOT NULL default '0'
-> );

That’s all there is to it! We only have to do this once, so it’s really not so bad. Next, we have to modify the /etc/asterisk/cdr_mysql.conf file to correctly reflect our choices.

[global]
hostname=ourdbserver.ourdomain.tld
dbname=asteriskcdrdb
password=changethis2yourpassword
user=asteriskcdruser
port=3306
userfield=1

The next time we restart Asterisk, our CDR information will be stored in the MySQL database. What does that give us? We now have the ability to use a number of very powerful tools to search our CDR records to find trends and patterns.

Packt

LEAVE A REPLY

Please enter your comment!
Please enter your name here