Sunday, October 17, 2010

Yealink IP Phone corporate directory integrated into FreePBX




Newer firmware of Yealink IP phones allows you to perform searches within the “Remote Directory” setup screen. Instead of just having a static file, users can search just about any parts of the name field and voila, the results are shown on the display.
Here, we take this functionality and add it to the FreePBX directory meaning, it is truly realtime to the adding and removal of users in FreePBX as it uses the asterisk.users directory in FreePBX MySQL table.
Test on:
1) FreePBX 2.x
2) Debian 5
3) Asterisk 1.6 (versions of Asterisk doesn’t really matter)
NOTE: If the codes appear truncated, simply copy the whole table and paste into a text editor
Features
  • Wildcard searching capability using backend PHP engine
  • Searches directly from FreePBX no need to maintain separate DB/files, thus making it dynamic
  • Some security tips plus Database access is limited with very low privileges for the user that connects to FreePBX / MySQL
  • May work for just about any type of phones that support the search function
Security Notice
Do not allow this file to be exposed in the public domain it can disclose your corporate directory and users/people

- Enforce ACL on this website/page so that only internal users can enumerate
- Try to use a complex name for the php file so that it’s difficult for someone to guess it.
Platforms
Tested on Debian5, FreePBX2.8, Asterisk1.6, MySQL5,Apache2 (PHP5)
Assumptions/notes:
1) You have a running apache server which support PHP5 and does not force SSL.
2) Apache runs as asterisk in group asterisk
3) There's no other service listening on port 80
4) Use freepbx asterisk database with users table found (default)
5) Using IP 10.10.10.1 as example web server
6) Newer Yealink firmware allows searching functionality, get that to use with this function
IMPORTANT: YOU MUST USE NON SSL BASED WEBSITE AS THE PHONES MAY NOT KNOW HOW TO INTERPRET SSL TRANSACTIONS
We will use a very low privileged user for this requests.
Create DB user with very low privileges (change the username and password accordingly to connect to your MySQL box, here, its a local box). This below is give access to the database asterisk, table user and column name.
mysql -h localhost -u root -ppassword  mysql --execute="GRANT SELECT (name) ON asterisk.users TO directory@localhost IDENTIFIED BY ‘p@ssword1';"

mysql -h localhost -u root -ppassword  mysql --execute="GRANT SELECT (extension) ON asterisk.users TO directory@localhost IDENTIFIED BY 'p@ssword1';"

Create PHP to XML transformation file that will retrieve info directly from FreePBX's user table in the database asterisk (created by FreePBX)

SECURITY TIP: Use an odd name only you would know. Common names can be guessed and then information enumerated.
nano /var/www/directory/search.php
Paste into file, read the file headers and change where appropriate.
<?
// with credit to JOYCE CR, s.r.o. http://www.joyce.cz/produkt-soubory/searching_remote_phonebook_manual.pdf
// Make sure you configure the allowable settings only
// This script directly integrates with FreePBX and picksup the asterisk.users table
// Should work for both device-user mode or extensions mode
// Works by searching from anywhere of the person's name
// feedback to sanjay@astiostech.com
// Change here to match the webaddress absolute path
$URL = 'http://10.10.10.1/directory/';
// Choose how many results to return if search term produces a lot of output
$per_page = '10';
// Change here to match your own passwords
$mysql_conn = mysql_connect('localhost', 'directory', 'p@ssword1');
// Dont change anything from here unless you know what you are doing
mysql_select_db('asterisk', $mysql_conn );
$NAME=$_GET["name"];
$FROM=$_GET["FROM"];
$TO=$_GET["TO"];
if ( ($FROM=='') and ($TO=='') )
{
   //check to see how many
   $result= mysql_query("SELECT count(users.name) as total
                         FROM users
                         WHERE users.name LIKE '%$NAME%' ", $mysql_conn);
   $howmany = mysql_fetch_row($result);
   if ($howmany[0] > $per_page)
   {
    $start = 0;
    $index = 0;
    $total = $howmany[0];
    $remain = $per_page;
    print("\n");
    print("<YealinkIPPhoneDirectory>\n"); 
    while ($start < ($total + 1))
    {
      $limitstart = 'LIMIT '.$start.','.$per_page;
      $result = mysql_query("SELECT name,extension
                             FROM users
                             WHERE name LIKE '%$NAME%' ORDER BY name $limitstart", $mysql_conn);
      $row = mysql_fetch_row($result);
      $from = $row[0];
      if (($total - $start) < $per_page) { $remain = $total - $start; }
      for ($i = 1; $i < $remain; ++$i) { $row = mysql_fetch_row($result); }
      $to = $row[0];
      print("<SoftKeyItem>\n");
      print("\t<Name>");
      print($index);         
      print("</Name>\n");
      print("\t<URL>");
      print($URL."search.php?FROM=".$from."&TO=".$to);
      print("</URL>\n");
      print("</SoftKeyItem>\n");
      $start = $start + $per_page;
      $index = $index+1;
    }
    print("</YealinkIPPhoneDirectory>\n");
   } else {
    $result = mysql_query("SELECT name,extension,extension
                           FROM users
                           WHERE users.name LIKE '%$NAME%'
                           ORDER BY name ", $mysql_conn);
    print("\n");
    print("<YealinkIPPhoneDirectory>\n"); 
    while($row = mysql_fetch_row($result))
    {
      print("<DirectoryEntry>\n");
      print("\t<Name>");
      print($row[0]."- ".$row[1] );
      print("</Name>\n");
      print("\t<Telephone>");
      print($row[2]);
      print("</Telephone>\n");
      print("</DirectoryEntry>\n");
    }
    print("</YealinkIPPhoneDirectory>\n");
   }
} else {
  $result = mysql_query("SELECT name,extension,extension
                         FROM users
                         WHERE name>='$FROM' AND name<='$TO'
                         ORDER BY name", $mysql_conn);
   print("\n");
   print("<YealinkIPPhoneDirectory>\n"); 
   while($row = mysql_fetch_row($result))
   {
     print("<DirectoryEntry>\n");
     print("\t<Name>");
     print($row[0]."- ".$row[1] );
     print("</Name>\n");
     print("\t<Telephone>");
     print($row[2]);
     print("</Telephone>\n");
     print("</DirectoryEntry>\n");
   }
   print("</YealinkIPPhoneDirectory>\n");
}
?>
Save and close. Give proper permissions to the file
chown asterisk:asterisk /var/www/directory/search.php
Try on browser, assuming a user Sanjay is being searched for, you can also use ja, nj, as long as it's within the correct sequence
http://10.10.10.1/directory/search.php?name=sa
An output should look something like this:
Sanjay WS- 1000 1000
Now, on your Yealink:
Go to yealink admin page | Contacts | Remote Phone Book
http://10.10.10.1/directory/search.php?name=#SEARCH
Name this entry like “Corporate Directory” and what not.
Now, save, hit Dir, scrolls to the newly created “Corporate Directory” and a search function should show up. IF you search, enter the relevent text or if you don’t it will enumerate all list of directory entries limited by the number of pages specified in the php script above.
Do test it out and let us know.

12 comments:

J Wright said...

This really works!!! I just set this up on an Elastix 2.3 version with Yealink T38 phones. The only changes is the following path is correct for directory:

nano /var/www/directory/html/search.php

good job on this!!

J

Ross Ryding said...

Hello - must have done sone simple wrong - when i try to run it i get

$per_page) { $start = 0; $index = 0; $total = $howmany[0]; $remain = $per_page; print("\n"); print("\n"); while ($start < ($total + 1)) { $limitstart = 'LIMIT '.$start.','.$per_page; $result = mysql_query("SELECT name,extension FROM users WHERE name LIKE '%$NAME%' ORDER BY name $limitstart", $mysql_conn); $row = mysql_fetch_row($result); $from = $row[0]; if (($total - $start) < $per_page) { $remain = $total - $start; } for ($i = 1; $i < $remain; ++$i) { $row = mysql_fetch_row($result); } $to = $row[0]; print("\n"); print("\t"); print($index); print("\n"); print("\t"); print($URL."search.php?FROM=".$from."&TO=".$to); print("\n"); print("\n"); $start = $start + $per_page; $index = $index+1; } print("\n"); } else { $result = mysql_query("SELECT name,extension,extension FROM users WHERE users.name LIKE '%$NAME%' ORDER BY name ", $mysql_conn); print("\n"); print("\n"); while($row = mysql_fetch_row($result)) { print("\n"); print("\t"); print($row[0]."- ".$row[1] ); print("\n"); print("\t"); print($row[2]); print("\n"); print("\n"); } print("\n"); } } else { $result = mysql_query("SELECT name,extension,extension FROM users WHERE name>='$FROM' AND name<='$TO' ORDER BY name", $mysql_conn); print("\n"); print("\n"); while($row = mysql_fetch_row($result)) { print("\n"); print("\t"); print($row[0]."- ".$row[1] ); print("\n"); print("\t"); print($row[2]); print("\n"); print("\n"); } print("\n"); } ?>

but i test mysql connection and queries work fine.

thanks ross

Ross Ryding said...

having a little bit of an issue, seems to be access the database but is not formated as you mentioned and seem to be getting afew errors.

Below is what i see what i put it in browser

Notice: Undefined index: FROM in /ha0/var/www/html/directory/search.php on line 10 Notice: Undefined index: TO in /ha0/var/www/html/directory/search.php on line 11 0 http://192.168.250.30/directory/search.php?FROM=Accounting Fax&TO=Beverely 1 http://192.168.250.30/directory/search.php?FROM=Bill &TO=CC House Phone 2 http://192.168.250.30/directory/search.php?FROM=CC House Phone 1&TO=Dale 3 http://192.168.250.30/directory/search.php?FROM=Dan &TO=Fire Alarm #2 4 http://192.168.250.30/directory/search.php?FROM=Front Door&TO=IT Conf Room 5 http://192.168.250.30/directory/search.php?FROM=IT House Phone&TO=Lead Desk 1 6

thanks ross

JG said...

The beginning of the code is missing the php tag. It has <? at the beginning of the code. It should be <?php

If not it won't be handled as php code. I also had this problem until someone pointed it out.

Also, when I enter the phonebook and don't search anything it doesn't show me any entries. It sounds like it should from what I read in the blog post.

Michael_pt said...

I'm having the same problem, empty search, gives back nothing instead of everything.
Any idea why?

michael_pt said...

I'm experiencing the same thing, empty search field won't return anything. Any clue on this?

michael_pt said...

I'm having the same problem, empty search field not showing anything. Any clue why?

Michael Marques said...

I'm having the same problem, empty search field not showing anything. Any clue why?

Michael Marques said...

I'm having the same problem, empty search field not showing anything. Any clue why?

Sanjay Willie said...

Very likely the php is broken, can u run it independently on a browser? Does it throw any (XML) data back?

Also turn on PHP debugging and see...

Does it work when searching?

michael_pt said...

yes it works ok on a browser using
I get the full list using
http://10.10.10.1/directory/search.php?name=#SEARCH.

But on the phone an empty one until I put some letter in the search field.

Anonymous said...

I'm having the same problem as michael_pt, but only on T41P, it works as expected on T46G.