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:

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 , , , , , , , . Bookmark the permalink.

6 Responses to FreePBX MySQL Caller Lookup Source

  1. Andy says:

    Hi Danny,

    I have just tested your Phonebook on my elastix Server with 3 Cisco Phones and it works fine.Excellent.

    I have another directory on MYSQL, where I have stored all my Contacts. On The Cisco Phones I’m able to lookup all the Numbers and browse related Names.
    Now my question: How can I enable the Calleridlookup in Elastixs to lookup the Names related to the Numbers in that Dbase?

    Your Help would be very much appreiciated.

    Thanks a lot

    Andy.

    Details:
    $sql_data = array(
    database_server => ‘XXX.XXX.XXX.XX’,
    database => ‘xml_directory’,
    database_user => ‘test’,
    database_pawd => ‘test’
    );
    if(is_numeric($caller_num))
    {
    // das SELECT zusammenbauen
    $query = “select * from directory_entries, directory_numbers “;
    $query .= “where directory_entries.entry_id = directory_numbers.entry_id “;
    $query .= “and number = ‘$caller_num'”;
    // zur Datenbank verbinden
    connect_db($sql_data);

    // query an die Datenbank
    $result = safe_query($query);

    if (mysql_num_rows($result) == 0)
    {

        • Danny says:

          I’m not sure I understand your question. You have a database (assuming you didn’t mean Dbase) and you’re running Elastix.

          If Elastix is the same as FreePBX you should be able to configure it via the web GUI by adding a CallerID Lookup Source and follow the post or am I missing something?

  2. Amaury says:

    Hi Danny,

    I have a question for you. I have pretty much the same configuration on my Asterisk (1.8) & FreePBX (2.11) but I have 1 problem. The local phones I have still use their built-in phonebook to display the names (or just displays the number when no local entry is present). I know CIDlookup works fine because my CDR history shows the correct name/number association that I’ve stored in the database.

    I’m assuming Asterisk is supposed to send the CID name information to the SIP phone when it rings but either it doesn’t or the phone ignores it.
    The only particularity I have is that I use normal phones connected to an ATA.

    Any help you can provide would be appreciated.

    Thanks,

    Amaury

    • Amaury says:

      Ok, nevermind.

      I should have thought of this sooner. I just tested with a SIP client on the computer and it does display the correct name. I guess my ATA is ignoring the CID.

      Regards,

      Amaury

Leave a Reply