November 12, 2014 · linux asterisk voip sql

Smart calls routing in Asterisk (using mysql or postgresql via odbc)

Good day, dear all!

I think it's very useful when some specific calls going to some specific peer (i.e. manager/support team department).
For example, when your friend Alice calls to your bungalow-office, only your phone (pc/laptop) should ring.
That's not so hard with asterisk and you favorite DB (I'll try with mysql).
So, all we need is:

Odbc (Open Database Connectivity) is something like high-level api for relational db's. Maybe it's little bit old, but it's just works (c)

Besides, there are Asterisk mysql-addon for direct mysql connection (with limited functionality) but odbc is much powerful and flexible.

Ok. After odbc stuff is installed it's time to configure connection parameters:

Odbc part:

/etc/odbc.ini stored DB's credentials and connection options:
(Note: you may specify credentials and options in res_odbc.conf, especially if you have multiple databases and want to keep all in one place)

Driver       = MySQL  
Description  = MySQL ODBC 3.51 Driver DSN  
Server       = 127.1  
Port         = 3306  
User         = justin  
Password     = nobodyknowsmypassword  
Database     = bieber  
Option       = 3  
Socket       =  

must be libraries location (depends of distro)

Description     = MySQL driver  
Driver          =  
Setup           =  
CPTimeout       =  
CPReuse         =

Check connection to database:

[root@some:~] isql bieber

if you see this

| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |

than all is fine (try to make some requests to DB too), time to configure connection from Asterisk.

Asterisk part:

Set database(s) for Asterisk odbc resources:



is the database specified in /etc/odbc.ini


is recommended for reduce requests time

also, connection credentials can be specified in this file, instead /etc/odbc.ini

Now, it's time to set functions that we will later call from dialplan:

readsql = SELECT `users` FROM `all` WHERE `TEL` LIKE REPLACE('${ARG1}', '+1 '') LIMIT 1


is optional, but it's good for human-friendly marking. Using this prefix our function will look like check_MANAGER_STATE instead ODBC_MANAGER_STATE


is the resource from res_odbc.conf



is one of two operation modes (another are write)

Now time for play with dialplan:

exten => 106,1,Set(Check_manager=${check_MANAGER_STATE(${CALLERID(num)})})  
exten => 106,n,Set(DialTarget=${IF($[${LEN(${Check_manager})} != 0]?Sip/${Check_manager}:${DIALALL})})  
exten => 106,n,Dial(${DialTarget})  
exten => 106,n,Playback(${NBAV})  
exten => 106,n,Hangup()  

Suppose, our number is 106 (Alice number is +10XXXXXXXXXXXXXX), before AppDial will run, Asterisk function make request to table all and search for CID in column TEL (LIKE REPLACE removes +1 code if exist).

| users  | TEL                                

And if there are user from users column with corresponding CID from TEL column, user saved in Check_manager variable and then call going to this user.
We have also check that user was found:

106,n,Set(DialTarget=${IF($[${LEN(${Check_manager})} != 0]?Sip/${Check_manager}:${DIALALL})})  

If no user was found, ${LEN(${Check_manager})} (length of variable) returns zero and DialTarget takes DIALALL value (call to all available peers, for example).

Sure, this is extra basic example. The are a lot of space for ideas about Asterisk interactivity and much more.

  • LinkedIn
  • Tumblr
  • Reddit
  • Google+
  • Pinterest
  • Pocket
Comments powered by Disqus