Novell Home

Reporting on LDAP Information with MySQL and Linux

Novell Cool Solutions: Feature
By Daniel Bray

Digg This - Slashdot This

Posted: 20 Dec 2006


I need to quickly generate a report on GroupWise or other LDAP information. For example, I might need to answer questions such as "How many users are in this Post Office?" Or, "How many users have a homespace on that server?"


Convert parts of your eDirectory information into a MySQL database. Once the information is in the DB, you can run reports, scripts ... virtually anything you want with this data. I have even used this data to create some NCF files that I've used to migrate GWise archives from various homespace servers to one central server. Your options are limitless with what you can do, once you have this information in MySQL.

I use this in two ways:

1. Via a nightly cron job that runs with just a single parameter for searching the whole tree.

2. Every now and then, to make sure a single user has updated information in the DB.

Environment: SUSE Linux Enterprise Server or OES Linux server running a LAMP configuration


Here is the script:

//basedn argument is only requirement, search whole tree or specific container
$basedn = $argv[1];
if($basedn == "")
echo "\n\nYou need to specify a basedn\n";
echo "example: ldap2mysql.php ou=Corp,o=HF\n\n";

echo "\n\nOPTIONAL:  you can also specify an exact UID to filter\n";
echo "example: ldap2mysql.php ou=Corp,o=HF DA104983\n\n";

//optional user ID to search for, used to update single user as opposed to whole db
$searchID = $argv[2];
$searchID = "";

// MySQL connection
$selectedtable = "users";
if(! $mysqlconnect = mysql_connect("localhost", "mysqladmin", "") )
{die ("Can't MySQL connect\n\n");}
$mysql_select_db = mysql_select_db($selecteddatabase, $mysqlconnect);
//if not trying to update a single user then empty the table, to allow for all new 
//LDAP information to be imported
if($searchID == "") 
$mysqlTRUNCATE_query = "TRUNCATE TABLE `$selectedtable`";
$mysqlTRUNCATE_results = mysql_query($mysqlTRUNCATE_query)
or die("The following MySQL Query:\n 
$mysql_query \n
has failed with the following error code:\n" 
. mysql_error());

//LDAP connection
$host = "";
if(! $ldap_connect = ldap_connect($host))
{die ("Can't LDAP connect to $host\n\n");}

//first make an LDAP call that will create an array of cn's, this saves on memory usage
//or if just one cn was specified, filter just on that
if($searchID == "")
$filter = "(&(objectClass=user)(objectClass=person)(cn=*))";
$filter = "(&(objectClass=user)(objectClass=person)(cn=$searchID))";
$cnonly = array("cn");//we just want the cn for now
if(! $ldap_usersearch = ldap_search($ldap_connect, $basedn, $filter, $cnonly))
{die ("Can't LDAP search to $basedn\n\n");}

//next try to get the list of LDAP users
if(! $ldap_get_users = ldap_get_entries($ldap_connect, $ldap_usersearch))
{die ("Can't LDAP get list of users\n\n");}

//now, we should have a list of cn's to search through one at a time to get the specified attributes
for ($users = 0; $users < $ldap_get_users['count']; $users++)
$searchcn = addslashes($ldap_get_use
$filter = "(&(objectClass=user)(objectClass=person)(cn=$searchcn))";
$listofattribs = array("dn","mail","cn","ngwpostoffice","givenname","sn","homedirectory","ngwfileid","telephonenumber","groupmembership");
if(! $ldap_search = @ldap_search($ldap_connect, $basedn, $filter, $listofattribs))
{die ("Can't LDAP search to $basedn\n\n");}
if(! $ldap_get_entries = @ldap_get_entries($ldap_connect, $ldap_search))
{die ("Can't LDAP get entries\n\n");}

//finally, we have just one user we and are going to look up all the attributes we want below
for ($item = 0; $item < $ldap_get_entries['count']; $item++)
$userdn = addslashes($ldap_get_entries[$item]["dn"]);
$email = addslashes($ldap_get_entries[$item]["mail"][0]);
$usercn = addslashes($ldap_get_entries[$item]["cn"][0]);
$ngwpostoffice = addslashes($ldap_get_entries[$item]["ngwpostoffice"][0]);
$firstname = addslashes($ldap_get_entries[$item]["givenname"][0]);
$lastname = addslashes($ldap_get_entries[$item]["sn"][0]);
$homedirectory = addslashes($ldap_get_entries[$item]["homedirectory"][0]);
$ngwfileid = addslashes($ldap_get_entries[$item]["ngwfileid"][0]);
$phone = addslashes($ldap_get_entries[$item]["telephonenumber"][0]);

$ldap_first_entry = @ldap_first_entry($ldap_connect, $ldap_search);
$groupmembership = @ldap_get_values($ldap_connect, $ldap_first_entry, "groupmembership");
for ($i=0; $i < $groupmembership["count"]; $i++)
$groups .=  $groupmembership[$i]  . "\n";

//I am only going to check for users that have a valid email address
if( $testemail = stristr($email, "") )
//format the data into how we want it to appear in the db
//alter the format on the user's context
$context = $userdn;
$context = strstr ( $context, "," );
$context = preg_replace('/,/', '.', $context);
$context = preg_replace('/ou=/', '', $context);
$context = preg_replace('/o=/', '', $context);

//alter the format on the user's groups
$ngwpostoffice = preg_replace('/cn=/', '', $ngwpostoffice);
$ngwpostoffice = preg_replace('/,[^*]+$/', '', $ngwpostoffice);

//alter the format on the user's post office
$groups = preg_replace('/,/', '.', $groups);
$groups = preg_replace('/cn=/', '', $groups);
$groups = preg_replace('/ou=/', '', $groups);
$groups = preg_replace('/o=/', '', $groups);

//alter the format on the user's GWise userdb file and archive folder
$userdb = "user" . $ngwfileid . ".db";
$archivefolder = "of" . $ngwfileid . "arc";

//alter the format on the user's homespace, mainly converting into UNC format
if ($homedirectory == "")
$homedirectory = "NO HOME SPACE";
$archive = "\\\\\\\\hfgwarchive1\\\\vol2\\\\Archives\\\\$archivefolder";
$server = $homedirectory;
$volume = $homedirectory;
$testhomedir = $homedirectory;
if($testhomedir = stristr($server,"SERVERNAME"))
$server = preg_replace("/.*SERVERNAME/", 'SERVERNAME', $server);

$volume = preg_replace("/.*VOLUME/", 'VOLUME', $volume);
$volume = preg_replace("/,[^*]+$/", '',$volume);
$volume = $server . $volume;
$homedirectory = "\\\\\\\\$server\\\\$volume\\\\users\\\\$usercn";
$archive = "$homedirectory\\\\grpwise\\\\$archivefolder";

//finally time to update the MySQL db with all the formatted information obtained
$mysqlsearch = "SELECT * FROM `$selectedtable` WHERE `dn` LIKE '$userdn'";
$mysqlsearch_results = mysql_query($mysqlsearch);
$mysqlsearch_numrows = mysql_num_rows($mysqlsearch_results);
//if the user doesn't already exist INSERT, otherwise UPDATE
if($mysqlsearch_numrows == 0)
$mysql_query = "INSERT INTO `$selectedtable` 
( `id`,
`email` ,
( NULL , 
$mysql_result = mysql_query($mysql_query)
or die("The following MySQL Query:\n $mysql_query \nhas failed with the following error code:\n" 
. mysql_error());
$mysql_query = "UPDATE `$selectedtable` SET 
`po` = '$ngwpostoffice', 
`uid`= '$usercn',
`context`= '$context', 
`firstname`= '$firstname',
`lastname`= '$lastname',
`email` = '$email',
`homedirectory`= '$homedirectory', 
`archive`= '$archive',
`fid`= '$ngwfileid',
`phone`= '$phone',
`groups` = '-',
`modified` = NOW()
WHERE `dn`= '$userdn';";
$mysql_result = mysql_query($mysql_query) 
or die("The following MySQL Query:\n $mysql_query \nhas failed with the following error code:\n" 
. mysql_error());

Novell Cool Solutions (corporate web communities) are produced by WebWise Solutions.

© 2014 Novell