FOP2 Phonebook as CLID Lookup Source in FreePBX

FOP2 has a nifty feature: when you receive a call, it searches the callerid with records in your phonebook, and if found, it shows you the contact name in the call notification window. This article describes how to set the same information but on the callerid name itself, so the contact name will be shown also in your phone display.

Background

FOP2 includes a phonebook. It is not an essential feature, but a handy addon that lets you store name, company, picture and phone numbers of your contacts, mark records as public/private, etc.

It is used for click to dial (or realtime search to dial when using the fop2 dial box), and also for populating information into the incoming call notification popup that you get when your are logged into FOP2 and your phone is ringing: if the callerid number is found in the phonebook, it will retrieve the contact name, company and picture to make the popup more meaningful.

The drawback is that the notification popup is displayed only if you are logged into FOP2.

Fortunately, FreePBX has a nice feature that can be used to set the callerid name for inbound trunk calls inside the Asterisk dialplan, so if we have a match, the name will be displayed in the phone display, and it will also be stored on the CDR records. And best of all, you do not need to be logged into FOP2 to have that information appended in the call.

Requisites

First of all, you must have a FreePBX based Asterisk installation, FOP2 installed and the visual phonebook configured and working.

Since FOP 2.21, the visual phonebook table will be created automatically in a FreePBX based system, using the same database where FreePBX stores its configuration tables (normally named 'asterisk').

In previous FOP2 versions you must create a database, the table, and have the proper MySQL credentials in order to use the phonebook. Instructions on how to setup the phonebook database for FOP 2.20 are found here. You must know the MySQL credentials in order to set this up. Without this information, you will have issues.

The information needed in order to set this is the mysql host, database name, user and password.
How to find out the correct mysql data

FOP 2.20 or prior:

If your phonebook is working, you have all that information already setup in the file /usr/local/fop2/fop2.cfg, so open the file up and take note on those values, as you will need them for setting up the FreePBX lookup source. The host is usually "localhost", and the suggested database name is "fop2". The user and password can be read in your fop2.cfg file, stored in the $DBUSER and $DBPASS variables.

MySQL Host: localhost MySQL database name: fop2 MySQL User: -value from $DBUSER- MySQL Password: -value from $DBPASS-

FOP 2.21 or up:

Since FOP 2.21 , the database is the same one that FreePBX uses, as setup in /etc/amportal.conf. It is generally named "asterisk". The mysql user and password are also set in /etc/amportal.conf, saved as AMPDBUSER and AMPDBPASS, you can find all those values with the command:

#>grep ^AMPDB /etc/amportal.conf

Setting up the Callerid Lookup Source

Log into your FreePBX web admin, and select CallerID Lookup Source from the Setup menu.

Add a source with name "Fop2", Source Type "MySQL" and optionally check the "Cache results" checkbox.

Then, in the MySQL section complete the Host, Database, Username and Password with the data you collected before, and in the Query section put:

SELECT CONCAT(firstname,' ',lastname) AS name FROM visual_phonebook WHERE phone1 LIKE CONCAT('%',SUBSTRING('[NUMBER]',-8)) OR phone2 LIKE CONCAT('%',SUBSTRING('[NUMBER]',-8)) ORDER BY LENGTH(CONCAT(phone1,phone2)) LIMIT 1

This query will try to match the last 8 digits of a phone number in the phonebook with the incoming callerid. If you want to match on fewer digits, or more, change the 8 to the number you want. Finally save it by clicking on the "Submit Changes" button.

Enabling the Callerid Lookup Source

The last step is to enable the Callerid Lookup Source in your inbound routes. Go to "Inbound Routes" in FreePBX, edit one of your inbound routes, and select the newly created source in the "CID Loockup Source" section.

That's it! Next time you get a call in that route, a lookup will be performed and if a match is found, the callerid name will be set to the name as set in FOP2 visual phonebook.

Enjoy!