FreePBX MySQL Caller Lookup Source

Overview

One of the reasons I switched to MySQL instead of using the built in phonebook was because the numbers weren’t matching the ones in the internal phonebook. The built in phonebook only allows numbers so international numbers with + or numbers with hyphens cannot be matched to phonebook entries. Using a user defined database and query will solve these problems.

Setup MySQL

We need a schema and a table to store the phonebook and I’d suggest a new user with at least SELECT privileges. The script below creates a schema called freepbx and a table called phonebook:

—
— Create schema freepbx
—

CREATE DATABASE IF NOT EXISTS freepbx;
USE freepbx;

—
— Definition of table `freepbx`.`phonebook`
—

DROP TABLE IF EXISTS `freepbx`.`phonebook`;
CREATE TABLE `freepbx`.`phonebook` (
`name` varchar(50) NOT NULL,
`number` varchar(20) NOT NULL,
`phone` varchar(10) DEFAULT NULL,
PRIMARY KEY (`name`,`number`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

phonebook.zip

The table contains 3 fields:

• name – The name of the number
• number – The matching phone number
• phone – The type of phone number E.g mob

Add Contacts

Run the following query to insert new contacts in the phonebook:
insert freepbx.phonebook (name, number, phone) values ('Danny','123456','mob');
Replace Danny, 123456. mob with your own values. The name and number (the first 2 items in the values) are required and the phone (‘mob in the example) is optional. When omitting the phone field run the following query instead:
insert freepbx.phonebook (name, number) values ('Danny','123456');
The name and number combined must be unique otherwise it won’t create an entry in the database. See below for how it will be displayed on the phone / reports.

The Siemens S68H which comes with the Siemens Gigaset 685IP will display all the caller ID if it fits on the display but the text will “just go off the screen” if it does not, only display the first part of the name. The number is always displayed below the caller ID where possible.

Setup FreePBX

Log into FreePBX and go to Tools Setup (tab) > Inbound Call Control > CallerID Lookup Sources Click the Add CID Lookup Source button to add a new phonebook directory. Select MySQL from the Source type This will change the require informations on the page. Enter the following information:

Source Description: MySQL
Source type: MySQL
Cache results: yes [tick]
Host: [server address]
Database: freepbx
Query: SELECT CONCAT(name, IF(phone IS NOT NULL,CONCAT(' (', phone, ')'), '')) as cid FROM phonebook WHERE number like '[NUMBER]'

Limited phone field to 1 character and capitalised it. E.g ‘home’ would become Danny (H)
Query: SELECT CONCAT(name, IF(phone IS NOT NULL,CONCAT(' (', UPPER(SUBSTR(phone,1,1)), ')'), '')) as cid FROM phonebook WHERE number like '[NUMBER]'

Username: [username]
Password: [password]

Source description can be any name and it will be used a reference in other settings. The Host will typically be localhost unless the MySQL database is installed on a different machine.

The query will show Danny (mob) if the phone field is populated. The brackets are automatically inserted. If the phone field is empty (NULL in the database) it will just show the name Danny

Press the Submit Changes button to save the new caller ID look up.

Go to Setup (tab) > Inbound Call Control > Inbound Routes and select an inbound route which will use the new phonebook. Under the CID Lookup Source section select the phonebook called MySQL (or what ever it was named in the Source description) in the Source dropdown.

Press the Submit button to save the change and repeat for all other inbound routes which will use the MySQL phonebook.

Apply the changes for all the settings to take hold.

Summary

Having MySQL as a source makes it a lot more flexible and customizable. Also it can be linked to an existing directory. More technical knowledge is required to manage the contacts but that comes with the flexibility.

Setup MySQL CallerID Lookup Source on FreePBX

About Danny

I.T software professional always studying and applying the knowledge gained and one way of doing this is to blog. Danny also has participates in a part time project called Energy@Home [http://code.google.com/p/energyathome/] for monitoring energy usage on a premise. Dedicated to I.T since studying pure Information Technology since the age of 16, Danny Tsang working in the field that he has aimed for since leaving school. View all posts by Danny → This entry was posted in Linux, PBX and tagged caller id, FreePBX, Gigaset, MyISAM, mysql, siemens, siemens gigaset, sql. Bookmark the permalink.

6 Responses to FreePBX MySQL Caller Lookup Source

This site uses Akismet to reduce spam. Learn how your comment data is processed.