Configuring CEL in Asterisk

At the present time, Asterisk is gaining more and more popularity; it is used not only by organizations, but also by ordinary people and for its users there is a question of charging calls. For example, in a relatively simple system where accuracy is unstable, you can do with a standard CDR (Call Detail Record) module, which has three events: “Start”, “Answer”, “End” of a call and keep records of time as the difference between the end of a call and the answer to the call, but what if we have a call with a non-zero probability can be forwarded or put on hold? For this purpose, there is a CEL (Channel Event Logging) module in Asterisk and under the cat I will describe its concept and configuration example.

So, the main object in Asterisk is the "Channel", it is based on the connection between the two communication ports. It makes sense to have an event system that records important events, each event joins a channel, for example, ANSWER or HANGUP. CEL generates events that can be collected in one place and subsequently used to calculate statistics.

Key events at CEL:

Event	Description
CHAN_START	channel creation time
CHAN_END	channel completion time
ANSWER	response time
HANGUP	time when the subscriber "hung up"
CONF_ENTER	channel enablement time
CONF_EXIT	time to remove the channel from the conference
CONF_START	time of entry of the first party to the conference
CONF_END	the last call from the conference
APP_START	application launch time
APP_END	application completion time
PARK_START	call start time
PARK_END	call pause end time
BRIDGE_START	bridge launch time
BRIDGE_END	bridge completion time
BRIDGE_UPDATE	channel change ()
3WAY_START	generated when a conference with 3 participants is started, usually used for conditional call forwarding
3WAY_END
BLINDTRANSFER	this event occurs when unconditional call forwarding
ATTENDEDTRANSFER	occurs when conditional call forwarding
FORWARD	an event occurs when the channel is redirected
HOOKFLASH	event created when the DAHDI interface hangs
USER_DEFINED	depends on dialplan and has a username

After reviewing the CEL concept, you can begin to configure it. The goal is to record all events in the MySql database, all work with the database will be done using ODBC.
Before configuring ODBC in Asterisk, you need to install the necessary packages in the system:
apt-get install unixODBC unixODBC-dev libmyodbc

(if you are using a different system than Debian, you should use another package manager)
The configuration for the MySQL ODBC driver is performed in the /etc/odbcinst.ini file
Configuration example:
[MySQL]
Description = ODBC for MySQL
Driver = /usr/lib/odbc/libmyodbc.so
Setup = /usr/lib/odbc/libodbcmyS.so
FileUsage = 1

If there are no specified directories for the specified paths, then they can be located in another directory, you can easily find them, just run the following commands:
:~# updatedb
:~# locate libmyodbc.so
:~# locate libodbcmyS.so
The last two commands will show the location of the libraries on the screen, and we will jump to the appropriate path, for example: /usr/lib/i386-linux-gnu/odbc/libmyodbc.so
')
The next step is to configure the /etc/odbc.ini file, which is used to create an identifier that Asterisk will use to refer to this configuration, if in the future you decide to change the database, you should reconfigure this file.
Here is an example configuration:
[asterisk-connector]
Description = MySQL connection to 'asterisk' database
Driver = MySQL
Database = asterisk
Server = localhost
UserName = user
Password = 123456
Port = 3306

Now we will configure Asterisk to work with the database through ODBC, for this purpose the file /etc/asterisk/res_odbc.conf is used .
An example of the configuration of this file:
[asterisk]
enabled => yes
dsn => asterisk-connector
username => asterisk
password => 123456
pooling => no
pre-connect => yes
The dsn option indicates the connection that is configured in /etc/odbc.ini, and the pre-connect option tells Asterisk to raise the connection to the database when the res_odbc.so module is loaded

Important note, Asterisk must be built with ODBC support!
For verification, you can run the odbc show command from the CLI.

And now the most important thing, we proceed to the configuration of CEL. Open the configuration file. Open the config /etc/asterisk/cel.conf and make the following changes to it:
[general]
enable=yes
apps=all
events=all
dateformat = %F %T

apps - this option indicates which applications should be monitored.
events - using this option, we specify which events (from the table above) should be entered into the database.
Next, you need to edit the /etc/asterisk/cel_custom.conf file in it, uncommenting the [mappings] section.

And the last file to edit /etc/asterisk/cel_odbc.conf will make the following changes to it:
[first]
connection=asterisk
table=cel
loguniqueid=yes
The connection option sets the name of the connector from the res_odbc.conf file, and the table option specifies the name of the database table to save the data.

The final step is to create a database and a table.
:~# mysql –uroot –p
mysql> CREATE DATABASE asterisk;
mysql> use asterisk;
mysql> CREATE TABLE IF NOT EXISTS `cel` (
`id` int(11) NOT NULL auto_increment,
`eventtype` varchar(30) NOT NULL,
`eventtime` datetime NOT NULL,
`cid_name` varchar(80) NOT NULL,
`cid_num` varchar(80) NOT NULL,
`cid_ani` varchar(80) NOT NULL,
`cid_rdnis` varchar(80) NOT NULL,
`cid_dnid` varchar(80) NOT NULL,
`exten` varchar(80) NOT NULL,
`context` varchar(80) NOT NULL,
`channame` varchar(80) NOT NULL,
`src` varchar(80) NOT NULL,
`dst` varchar(80) NOT NULL,
`channel` varchar(80) NOT NULL,
`dstchannel` varchar(80) NOT NULL,
`appname` varchar(80) NOT NULL,
`appdata` varchar(80) NOT NULL,
`amaflags` int(11) NOT NULL,
`accountcode` varchar(20) NOT NULL,
`uniqueid` varchar(32) NOT NULL,
`linkedid` varchar(32) NOT NULL,
`peer` varchar(80) NOT NULL,
`userdeftype` varchar(255) NOT NULL,
`eventextra` varchar(255) NOT NULL,
`userfield` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
KEY `uniqueid_index` (`uniqueid`),
KEY `linkedid_index` (`linkedid`)
);

Restart Asterisk.

Verification of work is carried out using commands from the Asterisk CLI:
cel show status
odbc show

At this configuration is completed, it remains to make a test call and look at the contents of the table.