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:
- Asterisk
- [you favorite] *nix distro
- unixodbc
- libmyodbc - for mysql
- odbc-postgresql - for postgresql
- brain
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:
In
/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)
[asterisk_server_db]
Driver = MySQL
Description = MySQL ODBC 3.51 Driver DSN
Server = 127.1
Port = 3306
User = justin
Password = nobodyknowsmypassword
Database = bieber
Option = 3
Socket =
In
/etc/odbcinst.ini
must be libraries location (depends of distro)
[MySQL]
Description = MySQL driver
Driver = libmyodbc.so
Setup = libodbcmyS.so
CPTimeout =
CPReuse =
Check connection to database:
[root@some:~] isql bieber
if you see this
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL>
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:
/etc/asterisk/res_odbc.conf
[asterisk_db]
enabled=>yes
dsn=>asterisk_server_db
pooling=>no
pre-connect=>yes
dsn
is the database specified in /etc/odbc.ini
pre-connect
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:
/etc/asterisk/func_odbc.conf
[MANAGER_STATE]
prefix=check
dsn=asterisk_db
readsql = SELECT `users` FROM `all` WHERE `TEL` LIKE REPLACE('${ARG1}', '+1 '') LIMIT 1
prefix
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
dsn
is the resource from res_odbc.conf
and
readsql
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
+--------------------------------------------------------------+
| 106 | 0XXXXXXXXXXXXXX
+--------------------------------------------------------------+
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.