For the records I think I completed my project to perform a reliable CID lookup using Freepbx. For this I created a new table in the mysql already present in the Freepbx server and used the CID Lookup module in freepbx.
I have found many good suggestions on this forum and on the internet and I want to summarise here the steps so that it might be useful for the community.
1 - Using SSH you need to create a new database (eg “addressbook”) with a new table (“callerid” in my case) where you will load your address book names and numbers. Note that the table contains also a key field which auto increments (otherwise I found problems to update it using ODBC connector, see later).
Example of table structure below and you find tons of tutorials on how to do this from the mysql CLI :
mysql> describe callerid;
±-------±--------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±-------±--------±-----±----±--------±---------------+
| id_num | int(11) | NO | PRI | NULL | auto_increment |
| name | text | YES | | NULL | |
| number | text | YES | | NULL | |
±-------±--------±-----±----±--------±---------------+
3 rows in set (0.00 sec)
2 - Still in the SSH you need to create a new mysql user to access the table (I did not want to use root which comes with no password in mysql and it is limited to localhost usage by default). Note that the new user needs to have permission to access from a different LAN IP other than “localhost” and you can use the x.x.x.% as jolly symbol to define a range of IP’s (I need to update the table from another windows machine using VBA and ODBC connector). The mysql user is added only to mysql, no need to create a new user in linux. To connect with ODBC from the LAN don’t forget to comment the “;bind-address=127.0.0.1” into the mysql conf file at /etc/my.cnf so that the machines can listen from all IP’s and not only from localhost (ie should be equal to “bind-address=0.0.0.0”)
3 - If you need to cleanup the CallerID transmitted from the trunk like in my case (I wanted to replace the + sign with 00 or remove the international prefix for same country calls for example) edit /etc/asterisk/extensions_custom.conf to include the following code. Note the context must be called [from-pstn-custom] to use the Freepbx predefined context structure (no need to specify a context into the trunk settings), for the rest feel free to customize the number manipulation the way you like.
[from-pstn-custom]
exten => _X.,1,ExecIf($[ “${CALLERID(num):0:3}” = “+39” ]?Set(CALLERID(num)=${CALLERID(num):3}))
exten => _X.,n,ExecIf($[ “${CALLERID(num):0:1}” = “+” ]?Set(CALLERID(num)=00${CALLERID(num):1}))
exten => _X.,n,Set(CALLERID(name)=${CALLERID(num)})
If your country has a better number plan than mine probably you can optimize the “_X.” pattern in the front, not sure of this though.
4 - In my case I also downloaded the ODBC Mysql driver on my Windows machine to link the mysql address book into MS Access and maintain the records from there. No special settings were required other than matching the 32/64 bit version. My case was a 32bit Office on a 64bit Windows and I had to use the 32bit Mysql ODBC driver.
5 - Back to Freepbx go to Admin – Caller ID Lookup sources and create an entry for your mysql address book created above. No strange settings here if you have created the table and the mysql user right as above. Local host is "localhost’ and user is the mysql user you created above, connection string as per Freepbx suggestion.
6 - Go to inbound routes and modify the route you are using. Put there you DID number, if you leave this empty the route will perform a catch all and I think it will not run the CID lookup script. In my case I have 1 incoming trunk only so I have 1 incoming route only and 1 DID. Then in the CID lookup source specify you are using the CID lookup source you have created to access the mysql table.
As an alternative you can achieve a similar result embedding the code straight into a new customized context that will bypass the freepx modules. In this case the name of the context must be specified into the trunk settings.
[my-custom-context]
exten => _X.,1,ExecIf($[ “${CALLERID(num):0:3}” = “+39” ]?Set(CALLERID(num)=${CALLERID(num):3}))
exten => _X.,n,ExecIf($[ “${CALLERID(num):0:1}” = “+” ]?Set(CALLERID(num)=00${CALLERID(num):1}))
exten => _X.,n,Set(CALLERID(name)=${CALLERID(num)})
; here starts the CID lookup
exten => _X.,n,MYSQL(Connect connid localhost mydbname mysqluser mysqlpasswd)
exten => _X.,n,MYSQL(Query resultid ${connid} SELECT name FROM callerid WHERE number LIKE ${CALLERID(num)})
exten => _X.,n,MYSQL(Fetch fetchid ${resultid} name_out)
exten => _X.,n,ExecIf($[ “${fetchid}” = “1” ]?Set(CALLERID(name)=${name_out}))
exten => _X.,n,MYSQL(Clear ${resultid})
exten => _X.,n,MYSQL(Disconnect ${connid})
; here continues to the std call processing
exten => _X.,n,Goto(from-pstn,${EXTEN},1)
That’s all. Note that I did not invent anything, I just tried to make the best out the the Freepbx modules flexibility and credit to all the suggestions that I received and found on the community forums !
…