* You are viewing the archive for the ‘Initial Configuration of Asterisk’ Category

Asterisk Insecure

This setting specifies how to handle connections with peers. By default Asterisk will authenticate all connections (this is the same as insecure=no). The parameters invite and port were added in v1.2.x  (and are to be used when you trust the IP of the caller), yes and very were removed in v1.6.x.

Note: yes/very are deprecated in 1.8

res_odbc.conf and cdr_odbc.conf sample files for MySQL/MariaDB

On a previous opportunity we configured our Asterisk installation to connect with MariaDB (or MySQL) database through ODBC, using unixODBC. Well, this is a sample of the res_odbc.conf and cdr_odbc.conf files that would work just out of the box with that configuration:

 
This is the /etc/asterisk/res_odbc.conf file, where we configure our DSNs as available resources for Asterisk.

Remember
The default context name expected by Asterisk is ‘asterisk‘, but it could have been called whatever you want as long as you remember that name and configure it correctly in the rest of configuration files that use ARA.
Note that we didn’t indicate the username or password for the database as that information was configured in the DSN’s definition’s file (/etc/asterisk/odbc.ini).

[ENV]
; Define your environmental variables here.
[asterisk]
enabled => yes
dsn => asterisk-dsn
pre-connect => yes

 

This is the /etc/asterisk/cdr_odbc.conf file. Here we configure the Asterisk DSN resource that we will use to store our CDR data.

[global]
dsn=asterisk
loguniqueid=yes
dispositionstring=yes
table=cdr       ; "cdr" is default table name
hrtime=yes     ; Enables microsecond accuracy
                     ;  with the billsec and duration fields

 
Note: the Asterisk DSN
Remember that when we use ‘dsn=asterisk‘ setting here, we are talking bout the context name configured previously in res_odbc.conf. (again, not the DSN in /etc/odbc.ini). ‘table=cdr‘ indicates the name of your CDR table.

The previous configuration files should be a good start point for any quick configuration that you plan to do.

If there’s anything I could make any clearer, just let me know.

Asterisk unixODBC configuration files for MySQL and MariaDB

1.0 Asterisk + unixODBC

Having almost all of our Asterisk configuration based on our preferred Database Management System is one of the greatest advantages that we have at the moment to deploy a VoIP solution. Now, having the possibility of building and integrated and unified communication solution in a non-intrusive way for client’s company, while at the same time assuring scalability and flexibility, that’s a mayor thing. That’s precisely what we have at the moment of using Asterisk+unixODBC.

unixODBC “allows the user or the system administrator to easily configure an application to use any ODBC compliant data source. This is perhaps the single biggest advantage of coding an application to the ODBC API and to purchase these applications. Dyamic binding allows the end-user to pick a data source, ie an SQL Server, and use it for all data applications without having to worry about recompiling the application.”

“The unixODBC Project goals are to develop and promote unixODBC to be the definitive standard for ODBC on non MS Windows platforms. This is to include GUI support for both KDE and GNOME.

ODBC is an open specification for providing application developers with a predictable API with which to access Data Sources. Data Sources include SQL Servers and any Data Source with an ODBC Driver.” (unixODBC website)

In this article I’m making the following assumptions:

  • You are using CentOS
  • You are using MariaDB or MySQL (All MySQL connectors -PHP, Perl, Python, Java, MyODBC, Ruby, MySQL C connector etc- work unchanged with MariaDB)

 

2.0 Asterisk ODBC Installation

Follow this document in order to install Asterisk.

You’ll also need to install the Asterisk ODBC package and (optionally) the Asterisk Voicemail ODBC package (if you plan to store your voicemail using ODBC):

$ yum install asterisk16-odbc asterisk16-voicemail-odbcstorage

 

3.0 Install the MySQL-connector

$ yum install mysql-connector-odbc

 

4.0 Configuring the System File for unixODBC: odbcinst.ini

The system file odbcinst.ini contains information about ODBC drivers available to all users. You could have multiple drivers configured, so if you want to use MariaDB, PostgreSQL and ORACLE at the same time, here is where you would be configuring the drivers.

Create the following /etc/odbcinst.ini file

[MySQL]
Description=MySQL ODBC 5.1 Driver
Driver=/usr/lib/libmyodbc5.so
SETUP=/usr/lib/myodbc3S.so
UsageCount=1

 
In the previous example, the ‘[MySQL]‘ part, is an identifier for this driver (you could call it whatever you want, just remember the name later). The ‘driver‘ setting indicates where is located the MySQL Connector driver file. If you are using CentOS 64bit (or other distro), this might have a different location, you might want to try finding it using:

$ locate libmyodbc

 
… or you can use whatever method you feel comfortable with (e.g. find command)

The ‘setup‘ and ‘usercount‘ parts can be ignored, as they are used when you create the file using a GUI (which is not our case right now).

  • For extensive documentation about how to correctly configure unixODBC drivers file and Data Source Name file, visit the exceptional documentation provided by Nick Gorham at unixODBC website.
  • Information about configuring MySQL ODBC Connector and underlying details can be found on MySQL ODBC Connector documentation’s page.

 

5.0 Configuring the DSN (Data Source Name) File for unixODBC: odbc.ini

The DSN file contains information about DSN’s available to all users. These “System DSN’s” are useful for application such as web servers that may not be running as a real user and so will not have a home directory to contain a .odbc.ini file.

Create the following /etc/odbc.ini file:

[asterisk-dsn]
Description=Asterisk Data Source Name
Driver=MySQL
SERVER=localhost
UID=astdbuser
PWD=MYSQL_USER_PASSWD
DATABASE=asteriskdb
PORT=3306
; SOCKET=/var/lib/mysql/mysql.sock

 
In this example ‘[asterisk-dsn]‘ is the DSN identifier, the ‘Driver‘ part indicates what driver to use from your odbcinst.ini file (section 4.0 of this guide). ‘Server‘ indicates where to connect using the driver belonging to this section (in this example, ‘localhost’). ‘UID‘ and ‘PWD‘ are the username and password of a user that has proper privileges on the database defined by ‘DATABASE‘. You might want to indicate a ‘SOCKET’, instead of a ‘PORT’ (only for local connections: localhost). This is the socket where MariaDB/MySQL listen to, in my system:

[root@asteriskfaqs etc]# netstat -n -l | grep -i mysql
unix  2      [ ACC ]     STREAM     LISTENING     14847  /var/lib/mysql/mysql.sock

 

6.0 Testing your unixODBC configuration

If everything went OK, then this command should help you test your configuration:

[root@asteriskfaqs etc]# echo ‘select now()’ | isql asterisk-dsn
+—————————————+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+—————————————+
SQL> +——————–+
| now()              |
+——————–+
| 2011-06-30 17:57:01|
+——————–+
SQLRowCount returns 1
1 rows fetched

 

7.0 Avoiding deadly pitfalls

  • Get sure that the database configured in the DSN file exists! (you could get weird errors if it doesn’t, but none of the meaningful)
  • Get user that the username in the DSN file is valid (it exists) and has privileges over the indicated database
  • Get sure to provide the correct file name in the odbcinst.ini file, when configuring the driver

 
 

I hope this post has been useful to you. I’ll try to keep it updated and keeping improving it over the time. If there’s something you don’t understand, just drop me a line.

MySQL and MariaDB voicemessages Schema

If you are using MySQL or MariaDB, and are planning to use ODBC storage for Voicemail, this table schema for voicemessages table could be useful.

The storage engine has explicitly been defined as ‘MyISAM’. Recent versions of MySQL use InnoDB by default, you can also use XtraDB (drop-in replacement for InnoDB in MariaDB).

CREATE TABLE `voicemessages` (
  `msgnum` INT(11) UNSIGNED DEFAULT NULL,
  `dir` VARCHAR(80) COLLATE utf8_unicode_ci DEFAULT NULL,
  `context` VARCHAR(80) COLLATE utf8_unicode_ci DEFAULT NULL,
  `macrocontext` VARCHAR(80) COLLATE utf8_unicode_ci DEFAULT NULL,
  `callerid` VARCHAR(40) COLLATE utf8_unicode_ci DEFAULT NULL,
  `origtime` VARCHAR(40) COLLATE utf8_unicode_ci DEFAULT NULL,
  `duration` VARCHAR(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `flag` VARCHAR(8) COLLATE utf8_unicode_ci DEFAULT NULL,
  `mailboxuser` VARCHAR(80) COLLATE utf8_unicode_ci DEFAULT NULL,
  `mailboxcontext` VARCHAR(80) COLLATE utf8_unicode_ci DEFAULT NULL,
  `recording` LONGBLOB,
  KEY `msgnum` (`msgnum`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT=‘Voicemail Messages’;
 

MySQL/MariaDB CDR Schema

If you are using MySQL or MariaDB, this table schema for CDR table could be useful:
 

CREATE TABLE `cdr` (                                                                                                                        
  `calldate` DATETIME NOT NULL DEFAULT ‘0000-00-00 00:00:00′,
  `clid` VARCHAR(80) COLLATE utf8_unicode_ci NOT NULL,
  `src` VARCHAR(80) COLLATE utf8_unicode_ci NOT NULL,
  `dst` VARCHAR(80) COLLATE utf8_unicode_ci NOT NULL,
  `dcontext` VARCHAR(80) COLLATE utf8_unicode_ci NOT NULL,
  `channel` VARCHAR(80) COLLATE utf8_unicode_ci NOT NULL,
  `dstchannel` VARCHAR(80) COLLATE utf8_unicode_ci NOT NULL,
  `lastapp` VARCHAR(80) COLLATE utf8_unicode_ci NOT NULL,
  `lastdata` VARCHAR(80) COLLATE utf8_unicode_ci NOT NULL,
  `duration` INT(11) NOT NULL DEFAULT ‘0’,
  `billsec` INT(11) NOT NULL DEFAULT ‘0’,
  `disposition` VARCHAR(45) COLLATE utf8_unicode_ci NOT NULL,
  `amaflags` INT(11) NOT NULL DEFAULT ‘0’,
  `accountcode` VARCHAR(20) COLLATE utf8_unicode_ci NOT NULL,
  `uniqueid` VARCHAR(32) COLLATE utf8_unicode_ci NOT NULL,
  `userfield` VARCHAR(255) COLLATE utf8_unicode_ci NOT NULL,
  KEY `uniqueid` (`uniqueid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 

 
Notes: In this case, the default engine for the database is MyISAM, you can also use InnoDB or XtraDB (drop-in replacement for InnoDB in MariaDB).

MySQL CEL Table Schema

From the Asterisk Wiki, we have this description of CEL:
CEL, or Channel Event Logging, has been written with the hopes that it will help solve some of the problems that were difficult to address in CDR records. Some difficulties in CDR generation are the fact that the CDR record stores three events: the “Start” time, the “Answer” time, and the “End” time. Billing time is usually the difference between “Answer” and “End”, and total call duration was the difference in time from “Start” to “End”. The trouble with this direct and simple approach is the fact that calls can be transferred, put on hold, conferenced, forwarded, etc. In general, those doing billing applications in Asterisk find they have to do all sorts of very creative things to overcome the shortcomings of CDR records, often supplementing the CDR records with AGI scripts and manager event filters.

If you are using MySQL or MariaDB, this is the table schema for CEL ODBC backed. Please note the following:

1. Attributes like default charset and collate are set to UTF8, which supports lots of different characters.
2. Storage engine has explicitly been defined as ‘InnoDB‘.

CREATE TABLE `cel` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `eventtype` VARCHAR(30) COLLATE utf8_unicode_ci NOT NULL,
  `eventtime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
   ON UPDATE CURRENT_TIMESTAMP,
  `userdeftype` VARCHAR(255) COLLATE utf8_unicode_ci NOT NULL,
  `cid_name` VARCHAR(80) COLLATE utf8_unicode_ci NOT NULL,
  `cid_num` VARCHAR(80) COLLATE utf8_unicode_ci NOT NULL,
  `cid_ani` VARCHAR(80) COLLATE utf8_unicode_ci NOT NULL,
  `cid_rdnis` VARCHAR(80) COLLATE utf8_unicode_ci NOT NULL,
  `cid_dnid` VARCHAR(80) COLLATE utf8_unicode_ci NOT NULL,
  `exten` VARCHAR(80) COLLATE utf8_unicode_ci NOT NULL,
  `context` VARCHAR(80) COLLATE utf8_unicode_ci NOT NULL,
  `channame` VARCHAR(80) COLLATE utf8_unicode_ci NOT NULL,
  `appname` VARCHAR(80) COLLATE utf8_unicode_ci NOT NULL,
  `appdata` VARCHAR(80) COLLATE utf8_unicode_ci NOT NULL,
  `amaflags` INT(11) NOT NULL,
  `accountcode` VARCHAR(20) COLLATE utf8_unicode_ci NOT NULL,
  `peeraccount` VARCHAR(20) COLLATE utf8_unicode_ci NOT NULL,
  `uniqueid` VARCHAR(150) COLLATE utf8_unicode_ci NOT NULL,
  `linkedid` VARCHAR(150) COLLATE utf8_unicode_ci NOT NULL,
  `userfield` VARCHAR(255) COLLATE utf8_unicode_ci NOT NULL,
  `peer` VARCHAR(80) COLLATE utf8_unicode_ci NOT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

 

Optimize Your Tables
If you are planning to extensively use the ARA, then you should seriously consider optimizing your tables if you expect your system to be scalable.
There are lots of resources online about database optimization, take some time to read about the topic.

Note: this article was originally posted by me at the Wiki, but never made it into public, so I’m posting it here, where I plan to keep it updated.