Cisco SIP IP Phone corporate directory integrated into FreePBX

admin

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 [email protected]
// 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 [email protected]
Good night!

17 Comments

  1. Can you tell me which Cisco SIP firmware you used here. Can you post your default SEP.cnf.xml file here.

    Thanks,
    Shyju

  3. Hi Sanjay!
    Big respect for this article!!! I am the new user of the Asterisk, but the corporate phone directory was necessary to me. I to myself broke a brain, but couldn't make it. You helped me well.

    Thanks!

    Best regards,
    Edgar.

  4. Any luck getting a company directory loaded on the 7921G over sccp? Just migrated and we got one working on the 7941 using SIP but no luck on the 7921G using sccp.

  5. Just went live with asterisk. We migrated from Call Manager 5.1. We have two sites and each site has a SIP and a SCCP server. The SIP server supports Cisco 7941 and 7961 devices as well as some Aastra phones. The SCCP server was installed to support our large 7921G wireless infrastructure, about 100 devices. The 7921G's work great but our user's lost the company directory and they need it. Any luck getting a directory to work on the 7921G?

  7. God bless you sir!
    managed to quite easily tweak this to use the old sugarcrm DB and it works like a charm.
    You saved me from duplicating work and keeping powered up the old callmanager 4.X servers.
    Happy days : P

  8. God bless you sir!
    Managed to quite easily change this to work with our sugarcrm DB, and it works like a charm.
    Saved me duplicating work and keep the old callmanager 4.x server powered up.
    Happy days : P

  10. First of all Thank you to wrought this blog , it’s really help us .

    I want to know can we sync this directory with phone call logs?

    While we are show the phone log from placed or received call log we can’t see name of that caller or called party name.

    Please tell me if we can do this.

    Thanks in advance

  11. That has nothing to do with directory, perhaps this is what you are looking for provided the Cisco's support it…(it should) but yea…try this, in FreePBX there's a check box called "Display CallerID on Calling Phone" in the Adv settings in FBX2.10. It can also be set at extensions level. Go check out connectline info on Asterisk https://wiki.asterisk.org/wiki/display/AST/New+in+1.8

    Also look out the settings in FreePBX

  12. This is great, thanks for your help! I'm struggling a little bit though. I have installed IncrediblePi (uses PIAF and Nerd Vittle's Asteridex). I would like to keep everything on Asteridex as it makes things easy for me to add/delete contacts. I tried modifying your script to use asteridex.user1 instead of asterisk.users table but asteridex uses 'out' instead of 'extension' for numbers. I get a syntax error when I try to grant permission.

    What would I need to tweak to your instructions so it pulls through contacts from Asteridex instead of Asterisk?

    Thanks again you're a star.

  14. Yes but I have done it from MyPHPAdmin server now so all fixed thanks.

    There's a modified version of your script here:

    http://www.pbxinaflash.com/community/index.php?threads/cisco-directory-from-asteridex.15105/

    This uses your script to pull from Asteridex instead of asterisk.users so now I have all my Asteridex numbers on there. Adding a section to print '9' before the number gives me a straight outside line too.

    You've put together some really good code here, thanks very much.

Comments are closed.