Install and configure CEL
Before configuring ODBC in Asterisk, you must install the necessary packages on the system:
apt-get install unixODBC unixODBC-dev libmyodbc
If you have Ubuntu and there is no connector in the repositories, you need to download the current version from the site https://dev.mysql.com/downloads/connector/odbc/ and install it:
$ tar xvf ./mysql-connector-odbc*.tar.gz
$ cp ./lib/libmyodbc5a.so /usr/lib/x86_64-linux-gnu/odbc/libmyodbc5a.so
The configuration for the MySQL ODBC driver is done in the /etc/odbcinst.ini file Configuration Example:
[MySQL]
Description = ODBC for MySQ
Driver = /usr/lib/odbc/libmyodbc.so
Setup = /usr/lib/odbc/libodbcmyS.so
FileUsage = 1
If there are no specified directories on the specified paths, then they can be in another directory, they can be easily found, 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, we will write the corresponding 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 you decide to change the database in the future, 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, use the /etc/asterisk/res_odbc.conf file.
Example 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! To verify, 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 /etc/asterisk/cel.conf config 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 - with this option we indicate which events (from the table above) should be entered in the database.
Next, you need to edit the /etc/asterisk/cel_custom.conf file in it, uncomment the [mappings] section.
And the last file to be edited /etc/asterisk/cel_odbc.conf, we will make the following changes to it:
[first]
connection=asterisk
table=cel
loguniqueid=yes
The connection option specifies the name of the connector from the res_odbc.conf file, and the table option specifies the name of the database table for saving data.
The final step in the configuration is to create a database and table.
$ mysql –uroot –p
mysql> CREATE DATABASE asterisk;
mysql> use asterisk;
mysql> CREATE TABLE IF NOT EXISTS `cel` (
`id` int(11) auto_increment,
`eventtype` varchar(30),
`eventtime` datetime,
`cid_name` varchar(80) DEFAULT '',
`cid_num` varchar(80) DEFAULT '',
`cid_ani` varchar(80) DEFAULT '',
`cid_rdnis` varchar(80) DEFAULT '',
`cid_dnid` varchar(80) DEFAULT '',
`exten` varchar(80) DEFAULT '',
`context` varchar(80) DEFAULT '',
`channame` varchar(80) NULL DEFAULT NULL,
`src` varchar(80) NULL DEFAULT NULL,
`dst` varchar(80) NULL DEFAULT NULL,
`channel` varchar(80),
`dstchannel` varchar(80) NULL DEFAULT NULL,
`appname` varchar(80) NULL DEFAULT NULL,
`appdata` varchar(80) NULL DEFAULT NULL,
`amaflags` int(11) NULL DEFAULT NULL,
`accountcode` varchar(20) NULL DEFAULT NULL,
`uniqueid` varchar(32),
`linkedid` varchar(32),
`peer` varchar(80) NULL DEFAULT NULL,
`userdeftype` varchar(255) NULL DEFAULT NULL,
`eventextra` varchar(255) NULL DEFAULT NULL,
`userfield` varchar(255) NULL DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `uniqueid_index` (`uniqueid`),
KEY `linkedid_index` (`linkedid`)
);
Restart Asterisk.
# service asterisk restart
Verification of work is carried out using commands from the CLI Asterisk:
> cel show status
> odbc show
Last modified 3yr ago