CIDLookup in FreePbx on SOGO
As SOGo is a very nice collaboration suite for the office, like I already showed in one of my earlier blog entries, why not also usa a free phone box like the Asterisk-based FreePBX project?
For good interaction with your SOGo-Installation, you can try to make your PBX lookup the incoming phone numbers in the SOGo address book. This article shows you how to do this.
First off, you need to install the CIDLookup FreePBX plugin into it.
Next, look up the MySQL-tables of SOGo that contain the address data of interest. As you may have multiple users, you may want to search in all address books or just specific address books, you need to configure this in a first step to have base data available. We do this my creating a MySQL view in the database that can easily be modified lateron according to your needs.
So logon to your sogo database and check the tables in there:
mysql -u sogo -p sogo
show tables;
You may notice the tables named in the style: sogo[username][uid]_quick
Some of these tables are for calendar data, but others are for address book data. Just select them to check if they are address book data tables, which exhibit a column called c_telephonenumber.
After you have collected all the tables with interesting address data in it, create the view `abooks` that join all of these into one, for example:
CREATE VIEW abooks AS SELECT * FROM sogouname0012c6fb387_quick UNION SELECT * FROM sogooffice0020231de93_quick;
Now we need to sanitize the phne numbers in there by removing unneccessary chracters from it and put them in the new view `phonenos`
CREATE VIEW phonenos AS SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(`c_telephonenumber` USING ascii),' ',''),'(',''),')',''),'-',''),'?','') phone, c_cn FROM abooks WHERE `c_telephonenumber`<>'';
Now that we sanitized it a bit, we finally need to make the numbers available in the correct format used by the PBX. In my example, the phone numer is always in international format without leading + character. This is then put in view `phoneno_f`. The reason why we don’t combine it with phonenos view in one query is that Views in MySQL don’t support subselects.
CREATE OR REPLACE VIEW phoneno_f AS SELECT CASE WHEN LEFT(phone,1)='+' THEN SUBSTR(phone,2) WHEN LEFT(phone,2)='00' THEN SUBSTR(phone,3) WHEN LEFT(phone,1)='0' THEN CONCAT( '43',SUBSTR(phone,2)) ELSE phone END phone, c_cn FROM phonenos;
Now we have a view containing all names and their respecive phone numbers in a unified format that can then be used to select in the CIDlookup module in FreePBX.
So now go to “Admin/Called ID lookup sources” in FreePBX GUI, Add a CIDLookup Source and set:
Source description: SOGo
Source type: MySQL
Host: localhost
Database: sogo
Query: SELECT c_cn FROM phoneno_f WHERE phone='[NUMBER]’
Username: sogo
Pass: sogopasswd
That’s it!
System programmer