Thursday, March 24, 2011

Cisco SIP IP Phone corporate directory integrated into FreePBX


Back sometime we wrote a guide on how to dynamically read and display searchable directory information straight out of a Yealink phone here.
Currently, we have a customer that migrated from Cisco Callmanager to Asterisk! (YAY) and a little bit of research with my colleague Wan Azwin, we managed to use the same concept on Yealink with Cisco phones. Most of the phones work except for 7912 which we are still figuring out how to even set the directory information. We must say that Cisco product ranges have so many variations in their formats it’s just a nightmare to figure them out sometimes. Also, if you need how-tos for to convert Cisco phones, try this link.
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) Apache 2 with PHP support
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.
Assumptions/notes:
1) You have a running apache server which support PHP5 and does not force SSL on virtual directories
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) Using SIP based firmware on Cisco
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. Change the passwords accordingly ..first one for connecting to your DB and the second for the new user called “directory” that you are allowing access to the table users and extensions respectively in the database asterisk (freepbx defaults).
At the command prompt run:
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';"
mysql -h localhost -u root -ppassword mysql --execute="flush privileges;"
Basically, now you need to create the menu files and the search file. You will now create:
1) menu.xml
2) menu2.xml
3) search.php
Assuming your webroot is in /var/www/
mkdir /var/www/ciscodir
nano menu.xml
Paste the following into menu.xml
<CiscoIPPhoneMenu>
  <Prompt>Astiostechtech Sdn Bhd</Prompt>
  <MenuItem>
    <Name>Search for a person..</Name>
    <URL>http://10.10.10.1/ciscodir/menu2.xml</URL>
  </MenuItem>
</CiscoIPPhoneMenu>
nano menu2.xml
Paste the following into menu2.xml
<CiscoIPPhoneInput>
  <Title>Astiostechtech Sdn Bhd</Title>
  <Prompt>Enter The Search Criteria</Prompt>
  <URL>http://10.10.10.1/ciscodir/search.php</URL>
  <InputItem>
    <DisplayName>Enter Name</DisplayName>
    <QueryStringParam>sn</QueryStringParam>
    <InputFlags>U</InputFlags>
  </InputItem>
</CiscoIPPhoneInput>
nano search.php
Paste the following into search.php. BE SURE TO CHANGE THE
1) $URL
2) Username and Password in $mysql_conn which you created in the mysql command line above
<?
header ("content-type: text/xml");
// Created by Astiostech
// 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/ciscodir/';
// Choose how many results to return if search term produces a lot of output
$per_page = '100';
// 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["sn"];
$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("<CiscoIPPhoneDirectory>\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("</CiscoIPPhoneDirectory>\n");
   } else {
$result = mysql_query("SELECT name,extension,extension
                           FROM users
                           WHERE users.name LIKE '%$NAME%'
                           ORDER BY name ", $mysql_conn);
    print("\n");
    print("<CiscoIPPhoneDirectory>\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("</CiscoIPPhoneDirectory>\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("<CiscoIPPhoneDirectory>\n");
   print("<Title>Astiostechtech Directory</Title>\n");
   print("<Prompt>Astiostechtech Directory</Prompt>\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("</CiscoIPPhoneDirectory>\n");
}

?>
Save and close. Give proper permissions to the file
chown asterisk:asterisk /var/www/ciscodir/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/ciscodir/search.php?sn=san
Also, do ensure your webserver interprets .xml files as text by modifying the mime types. In apache, this is done by editing the /etc/mime.types file. Add an entry
text/xml                                   xml
Finally, edit your SEP<MACADDRESS>.cnf.xml, look for directory info and set it to menu.xml, this is an example for Cisco 7941
<directoryURL>http://10.10.10.1/ciscodir/menu.xml</directoryURL>
Restart the phone.
If you need help, please contact us sanjay@astiostech.com
Good night!
Post a Comment