Setting up Channel Event Logging (CEL) on Asterisk 1.8 or up with MySQL and using FreePBX 2.11’s new CDR module to list em’

Hi guys, firstly wishing everyone a Merry Christmas…This would probably be my last posting here on technical matters for 2012. Lets usher in 2013 with some cool new FreePBX 2.11 CEL support. In this guide, I will try to help you setup CEL which is one of the best things that happened in Asterisk 1.8 and up when it comes to audit/security. If you are using the official FreePBX-3.211.63 version, it will already come with CEL support, so you just need to switch it on or off like explained below. Those don’t have or not using the official distro, hope this guide helps.
With FreePBX 2.11 (in beta now), you can easily view CEL events straight from FreePBX’s CDR module (Reports). Here’s the how-to. Do improvise for whatever OS flavor you are running :). Lots of good advice taken off this link here from FreePBX dev team. Assuming the following environments/conditions are already running and working for you already 1) Debian 6 2) Asterisk 1.8 or higher 3) FreePBX 2.11 (NOTE only 2.11 in beta now supports CEL. You must use FreePBX CDR module 2.11 or higher. Best thing is to make sure you get the modules in FBX updated to latest online repo. We will be using the following backend 1) MySQL DB 2) ODBC to connect to MySQL
IMPORTANT to note that the CDR with CEL support is in its infancy stage. Be sure to update it as frequently as available online. I do have some “annoyance” with this interface such as when you pull down the LINKIDs, you get to see all the events but you can’t seem to click on another LINKID, you have to reload the page or do your searches again. Yes, a little annoying but I am sure the FBX team is aware of this and will fix it when they do. This same behavior was noticed on the Distro too… 😀
Firstly, we will need all the required ODBC related files for Asterisk to compile res_odbc and CEL with ODBC support.

# apt-get update && apt-get install unixODBC-dev libmyodbc
Go into your Asterisk source directory, and make sure you recompile, like this
# make clean && ./configure && make menuconfig (if you browse around you will see that the CEL ODBC is selected under Channel Event Logging)
Quit menuselect, then do # make && make install
Lets populate our new tables and structure inside asteriskcdrdb (if don’t already exist, it could exist after compiling CEL CDR via Asterisk installation, you might want to check and skip some steps in item 5). Change values like “pass” accordingly to your MySQL root password. NOTE: I had to make the font small down here so that it first in one line, remember, these are one liners.
# cd /usr/src
# wget -O celcdr.sql http://pastebin.com/download.php?i=XNuN2DZL
# mysql -u root -ppass asteriskcdrdb < ./celcdr.sql
# mysql -h localhost -u root -ppass mysql –execute=”GRANT ALL PRIVILEGES ON asteriskcdrdb.* TO celadmin@localhost IDENTIFIED BY ‘pass’;”
# mysql -h localhost -u root -ppass mysql –execute=”FLUSH PRIVILEGES”
Create an ODBC connection to your local MySQL (modify accordingly your username and pass needed to connect to Asterisk and if the IP is your NIC ip, change to that or if the MySQL is remote, change accordingly. )
# nano /etc/odbc.ini
[mysql-odbc]
Description           = ODBC to Mysql
Driver                = MySQL
Database              = asteriskcdrdb
Server                = 127.0.0.1
UserName              = celadmin
Password              = pass
Socket                = /var/run/mysqld/mysql.sock
option                = 3

###Take note on the socket path for other OSes##

# nano /etc/odbcinst.ini

[MySQL]
Description = ODBC for MySQL
Driver = /usr/lib/odbc/libmyodbc.so
Setup = /usr/lib/odbc/libodbcmyS.so
FileUsage = 1

Now, lets edit Asterisk configurations to use CEL in MySQL, edit the /etc/asterisk/cel.conf file, search and modify the following values
NOTE: In our case, I am recording all events from all apps. Format your date according to your fancy, here I set it up to show like 23-12-2012 22:12:48 or don’t set it to leave it to Asterisk default.
# nano /etc/asterisk/cel.conf
enable=yes
apps=all
events=ALL
dateformat = %d-%m-%Y %T

Edit the res file now, add these at the bottom of these files.
# nano /etc/asterisk/res_odbc.conf

[asterisk]
enabled => yes
dsn => mysql-odbc
username => celadmin
password => pass
pooling => no
limit => 1
pre-connect => yes

# nano /etc/asterisk/cel_odbc.conf

[cel]
connection=asterisk
table=cel
username=celadmin
password=pass
loguniqueid=yes

Then restart amportal (# amportal kill && sleep 2 && amportal start)
To check, go into Asterisk CLI and type “cel show status”, you should see a bunch of stuff thrown out. Basically telling you that CEL is running and logging.
To further check, make a call, if you use a tool like adminer or phpmyadmin or simply from MySQL CLI, you should start to see lines captured in CEL raw like here after making some test calls.
If you see above, great so far, otherwise, just go into asterisk –rv and see if there’s any errors thrown when making calls with regards to res_odbc…its very likely permission or passwords, so fix them and restart asterisk again.
When I enabled the CEL on Asterisk, FreePBX seem to have automatically detected it and set the value of CEL to yes, so really nothing much to do then…woohoo!
That’s just about it. Now, be minded that the original CDR table still remains inside asteriskcdrdb CDR table and FBX is now reading off CEL table. Here’s how it looks with an expanded CEL event. Click on the IDs in “System” column items to expand the call’s events.
NOTE, for my install, I had to click on Search again when I wanted to select another call to list. Meaning, its not like its pushing the list below, it “opens” up just that call info/event that you clicked. Nothing below.
If you don’t want CEL, and want to use back good ole flat CDR, simply reverse the setting in item 13 above to set it to FALSE
Be sure to watch that disk space, for a single call, there could be 10s of lines hah…

1 Comment