1. Kayako Download customers: we will continue to develop and support Kayako Download beyond July 2017, alongside the new Kayako for existing customers.

    Find out more.

  2. The forum you are viewing relates to Kayako Classic. If you signed up or upgraded to the new Kayako (after the 4th July 2016), the information in this thread may not apply to you. You can visit the forums for the new Kayako here.

Active Directory User Sync Script

Discussion in 'Apps and modifications' started by Dylan Lindgren, Aug 26, 2009.

  1. Dylan Lindgren

    Dylan Lindgren Established Member

    Active Directory User Sync Script [FIXED]

    Hey all,

    This is my Active Directory user synchronisation script. Thanks to Landspeed for creating the original, as this script is loosely based on his import script. We wanted something we could setup as a scheduled task which would synchronise all users within a group. Forgive me if everything sounds a bit rushed... its quite a complex script to explain and I don't have a huge amount of time to explain it, so I thought i'd post up a quick rundown and then answer any questions anyone has.

    Some features of this script...
    • Full sync between an AD Group and SupportSuite.
    • Imports Custom Fields.
    • Reports changes for troubleshooting purposes.
    Basically the process that the script follows is:
    1. Import users that exist in the AD group but do not exist in SupportSuite
    2. Update users that both exist in SupportSuite and exist in the AD Group
    3. Mark users that no longer exist in the AD Group as a non-current employee.
    To get this working for you, you will need to...
    • Provide it with AD login details (lines 33-36)
    • Enter the filter which will point it to the correct group to sync with (line 37)
    • Enter the details for your supportsuite database (lines 45 - 49)
    You will also need to customise the $attrs variable (line 41), the $adusers[x]['other'] array with the values you want to pull from active directory. You will also need to add/remove to the batch of sql queries on lines 140-151 and 155 -160 with the custom fields in your environment. Lastly, you will need to add/remove to the SQL code on lines 167-174 with the custom fields in your environment.

    Lets say for instance the group you want to sync with supportsuite is called "SupportSuite users", is inside an OU called "ABC Groups" and is part of the abc.com.au domain.

    The filter you will want to use is...

    "(&(objectCategory=user)(memberOf=CN=SupportSuite Users,OU=ABC Groups,DC=abc,DC=com,DC=au))";
    • '&' is an AND, meaning that both the following requirements need to be met
    • 'objectCategory=user' means that the object must be a user
    • 'memberOf=CN=SupportSuite Users,OU=ABC Groups,DC=abc,DC=com,DC=au' means that the user must be part of the SupportSuite Users Group.

    I can answer any questions you may have on getting this working in your environment.

    Kind regards,


    Attached Files:

  2. KrisW

    KrisW Established Member

    Thanks for sharing.
  3. webby

    webby New Member

    Will this work for synching staff also?
  4. Dylan Lindgren

    Dylan Lindgren Established Member

    No, as it is this will only sync users. With a few modifications it wouldn't be too hard to use this script to sync staff members however.

  5. Dylan Lindgren

    Dylan Lindgren Established Member

    I should also probably add that I have a scheduled task that will run daily to poll this page (an exact copy of the cron jobs in supportsuite, but hitting this script instead). This is why I call it a Sync script rather than an Import script, as it checks whether the user exists before it imports it. It won't just keep importing duplicate users if you run it multiple times.
  6. jcossota

    jcossota Established Member

    When I run this script, if a user already exists it gives the message "Duplicate entry 'email@address.com' for key 'email'" and imports another record in the database, causing it to show up twice. Even though it's importing all the fields correctly, under Manage Users it only displays the Full Name of the duplicate. E-mail address and User ID are blank (however they are not blank in the database).

    Does this make sense? If I need to post my modification I can, but all I did was put in my own server variables for AD and mySQL etc.

    I don't know why the script is importing duplicates when it's supposed to check and skip if it already exists

    EDIT: deleting this duplicate object from Manage Users does not work, I have to manually delete it from the swusers database.
  7. Dylan Lindgren

    Dylan Lindgren Established Member


    When the script checks if the user exists - eg jsmith, all it does is check if there is a user in the database with the loginapi_userid field set to 'jsmith'. if there isn't one in there, it will import the user. What is probably happening is that there is already a normal, non-loginshare user in the database with the same email address as one of the ones you are trying to import.

    I think this could be considered a bug in the script and I should probably change this so it not only checks if the username exists, but also the email however as we were it was not a problem I cam across. For the time being I would recommend you start using this script from a completely clean database, if not then at least an database empty of all its users.

    I will try and get this fixed and post it up in the next few days.

    Kind regards,

  8. jcossota

    jcossota Established Member


    The database I'm running the script on isn't production yet, so there aren't a lot of usernames and email addresses that have been imported. I've tried importing just some dummy accounts, accounts that I know do not exist in the database.

    I have made sure I deleted all other users (besides my own) from the swusers table and all other email addresses from the swuseremails also, but still get the duplicate error when running the script again.

    I will wait for your modifications to see if that resolves my issue.
  9. Dylan Lindgren

    Dylan Lindgren Established Member

    Hey mate,

    I've figured out what the problem is. I think your login details for the MySQL connection are wrong. There is'nt alot of error reporting in this script and so it wasn't giving you an exact error. I tested the script in my development environment and I was getting the same error as you (duplicate email). After some troubleshooting I changed to the root password and it all started working.

    Ensure your mysql login details are correct and get back to me whether it worked or not.

    As for the bug I noticed while researching this issue, please apply the patch below.

    Change these lines (starting line 103)...
    // find out which users we need to import, and which users we need to update
    foreach ($adusers as $username => $values):
     if (isset(
    $toupdate[$username] = $values;
    $toimport[$username] = $values;
    To these lines...
    // find out which users we need to import, and which users we need to update
    foreach ($adusers as $username => $values):
     if (isset(
    $toupdate[$username] = $values;
      if (!
    in_array($values['email'], $ssusers)):
    $toimport[$username] = $values;
    Kind regards,

  10. jcossota

    jcossota Established Member


    Thanks for the update. Unfortunately I am experiencing the same problem as before, after having made the script change and confirmed that the mySQL username and password I am using is correct (it also happens to be the same database username and password I use for Kayako, so I had reset to make sure and Kayako stopped working until I updated it's own config.php with the updated credentials).

    Here's my script modification, with the AD and mySQL account info changed of course:

    | Current Version: 1.0.1
    | Created by: Dylan Lindgren (dylan.lindgren@gmail.com, 0421 211 820)
    | Creation Date: 19/08/2009
    | Last Modified: 20/08/2009
    | Script History
    | VERSION 1.0
    | VERSION 1.0.1
    |         Bug fix:    ISSUE - apostrophe's in names and emails were being escaped, rendering emails and names invalid
    |                    CAUSE - was using real_escape_string on names and emails, removed this and changed the names and emails
    |                            to be passed to the SQL queries by doublequotes.

        The below section contains all the configuration settings that 
        will enable this script to connect to the various databases. These are:
         - ACTIVE DIRECTORY: Connect using the LDAP functions in PHP
         - MYSQL: Connect using the MySQLi functions in PHP */

    // AD
    $host "ipaddress";
    $user "domain\samaccountname";
    $pswd "password";
    $dn "OU=Test,OU=Users,OU=Global,DC=domain,DC=dot,DC=com";
    $filter "(&(objectCategory=user)(memberOf=CN=SupportSuite Users,OU=Test,OU=Users,OU=Global,DC=domain,DC=dot,DC=com))";
    $ad ldap_connect($host) or die( "Could not connect to LDAP server!" );
    ldap_set_option($adLDAP_OPT_PROTOCOL_VERSION3) or die ("Could not set ldap protocol");        
    $bd ldap_bind($ad$user$pswd) or die ("Could not bind to LDAP server");
    $attrs = array("sn""givenname" ,"mail","telephonenumber","mailnickname","physicaldeliveryofficename",

    // MYSQL
    $myhostname "localhost";
    $myusername "databaseusername";
    $mypassword "databasepassword";
    $mydatabase "databasename";
    $myprefix "sw";
    $mysqli = new mysqli($myhostname,$myusername,$mypassword,$mydatabase);

    /* ------- END OF CONFIGURATION SETTINGS ------- */

        This section will initalize the variables that will be needed commonly throughout the script */
    $unixtimestamp time();
    $ssusers = array();
    $adusers = array();
    $toimport = array();
    $toupdate = array();
    $noaction = array();
    $inserted = array();
    $updated = array();
    $nochange = array();
    /* ------- END VARIABLE INITIALIZATION ------- */

    /* ------- START LOGIC ------- */

    // Populate the SupportSuite users array
    $ssquery "SELECT swusers.userid, fullname, phone, loginapi_userid, email FROM supportsuite.swusers LEFT JOIN 
                    supportsuite.swuseremails ON swusers.userid = swuseremails.userid WHERE loginapi_userid != '0'"
    if (
    $result $mysqli->query($ssquery)):
        while (
    $row $result->fetch_object()):
    $ssusers[$row->loginapi_userid] = array('username' => $row->loginapi_userid,
    'fullname' => $row->fullname
    'phone' => $row->phone
    'email' => $row->email,
    'other' => array ('userid' => $row->userid));

    // Populate the Active Directory users array
    if ($search ldap_search($ad$dn$filter$attrs)):
    $adlookup ldap_get_entries($ad$search);
        if (
    $adlookup["count"] > 0):
            for (
    $i=0$i<$adlookup["count"]; $i++):
    $adusers[$adlookup[$i]["mailnickname"][0]] = array(    'username' => $mysqli->real_escape_string($adlookup[$i]["mailnickname"][0]),
    // 'fullname' => $adlookup[$i]["givenname"][0] . " " . $adlookup[$i]["sn"][0],
    'fullname' => $adlookup[$i]["sn"][0] . ", " $adlookup[$i]["givenname"][0],
    'phone' => $mysqli->real_escape_string($adlookup[$i]["telephonenumber"][0]), 
    'email' => $adlookup[$i]["mail"][0],
    'other' => array (    'guid' => $mysqli->real_escape_string($adlookup[$i]["objectguid"][0]),
    'location' => $mysqli->real_escape_string($adlookup[$i]["physicaldeliveryofficename"][0]),
    'initials' => $mysqli->real_escape_string($adlookup[$i]["initials"][0]),
    'department' => $mysqli->real_escape_string($adlookup[$i]["department"][0]),
    'title' => $mysqli->real_escape_string($adlookup[$i]["description"][0])));
    "There were no results pulled from the AD query. Nothing has been imported.";

    // find out which users we need to import, and which users we need to update
    foreach ($adusers as $username => $values):
     if (isset(
    $toupdate[$username] = $values;
      if (!
    in_array($values['email'], $ssusers)):
    $toimport[$username] = $values;

    // Loop through the SupportSuite users array to see which ones no longer exist in AD
    foreach ($ssusers as $user => $array):
        if ((!isset(
    $toupdate[$user])) && (!isset($toimport[$user]))):
    $noaction[] = $user;

    /* ------- END LOGIC ------- */

    /* ------- START DATABASE CHANGES ------- */

    // loginapi_moduleid needs to be the module id of the authentication scheme we use (107 for Active Directory).
    // if this is incorrect your users will not be able to authenticate

    // build the user insert queries...
    $insertsql "";
    $toimport as $user => $array):
    $inserted[] = $user;
    $insertsql "INSERT INTO `swusers` (`userid`, `usergroupid`, `fullname`, `phone`, 
                        `userpassword`, `userpasswordtxt`, `dateline`, `lastvisit`, `lastactivity`, 
                        `enabled`, `loginapi_moduleid`, `loginapi_userid`, `languageid`, `timezoneoffset`, 
                        `enabledst`, `useremailcount`, `allowemail`, `slaplanid`, `slaexpiry`, `ismanager`) 
                        VALUES (NULL, '2', " 
    '"' .$array['fullname']. '"' ", '" $array['phone'] . "', '', '', '" $unixtimestamp "', '0',
                        '0', '1', '107', '"
    .$array['username']."', '0', '0', '0', '0', '0', '0', '0', '0')";
    // we need the user id of the new user, so we have to run the main insert query now...
    if(!$mysqli->query($insertsql)){ echo $mysqli->error; }
    $userid $mysqli->insert_id;
    $sql_email "INSERT INTO `swuseremails` (`useremailid`, `userid`, `email`, `isprimary`) VALUES (NULL, '"$userid "'" ', "'.$array['email'].'"' ", '1')";
    $sql_guid "INSERT INTO `swcustomfieldvalues` (`customfieldvalueid`, `customfieldid`, `typeid`,
                      `fieldvalue`, `isserialized`) VALUES (NULL, '1', '" 
    $userid "', '" $array['other']['guid'] . "', '0')";
    $sql_location "INSERT INTO `swcustomfieldvalues` (`customfieldvalueid`, `customfieldid`, `typeid`,
                      `fieldvalue`, `isserialized`) VALUES (NULL, '2', '" 
    $userid "', '" $array['other']['location'] . "', '0')";
    $sql_initials "INSERT INTO `swcustomfieldvalues` (`customfieldvalueid`, `customfieldid`, `typeid`,
                      `fieldvalue`, `isserialized`) VALUES (NULL, '3', '" 
    $userid "', '" $array['other']['initials'] . "', '0')";
    $sql_department "INSERT INTO `swcustomfieldvalues` (`customfieldvalueid`, `customfieldid`, `typeid`,
                      `fieldvalue`, `isserialized`) VALUES (NULL, '4', '" 
    $userid "', '" $array['other']['department'] . "', '0')";
    $sql_position "INSERT INTO `swcustomfieldvalues` (`customfieldvalueid`, `customfieldid`, `typeid`,
                      `fieldvalue`, `isserialized`) VALUES (NULL, '5', '" 
    $userid "', '" $array['other']['title'] . "', '0')";
    $sql_employment "INSERT INTO `swcustomfieldvalues` (`customfieldvalueid`, `customfieldid`, `typeid`,
                      `fieldvalue`, `isserialized`) VALUES (NULL, '6', '" 
    $userid "', 'Yes', '0')";
    $sql_customlinks "INSERT INTO `swcustomfieldlinks` (`customfieldlinkid`, `linktype`, `typeid`, `customfieldgroupid`) VALUES (NULL, '1', '" $userid "', '2')";
    // and we can run the rest later...
    if(!$mysqli->query($sql_email)){ echo $mysqli->error; }
    $mysqli->query($sql_guid)){ echo $mysqli->error; }
    $mysqli->query($sql_location)){ echo $mysqli->error; }
    $mysqli->query($sql_initials)){ echo $mysqli->error; }
    $mysqli->query($sql_department)){ echo $mysqli->error; }
    $mysqli->query($sql_position)){ echo $mysqli->error; }
    $mysqli->query($sql_employment)){ echo $mysqli->error; }
    $mysqli->query($sql_customlinks)){ echo $mysqli->error; }

    // build the user update query
    foreach($toupdate as $user => $array):
    $updated[] = $user;
    $updatesql "UPDATE `swusers` LEFT JOIN `swcustomfieldvalues` as guid ON swusers.userid = guid.typeid
                        LEFT JOIN `swcustomfieldvalues` as location ON swusers.userid = location.typeid
                        LEFT JOIN `swcustomfieldvalues` as initials ON swusers.userid = initials.typeid
                        LEFT JOIN `swcustomfieldvalues` as department ON swusers.userid = department.typeid
                        LEFT JOIN `swcustomfieldvalues` as position ON swusers.userid = position.typeid 
                        LEFT JOIN `swcustomfieldvalues` as employment ON swusers.userid = employment.typeid 
                        SET guid.fieldvalue='" 
    $array['other']['guid'] . "', location.fieldvalue='" $array['other']['location'] . "', initials.fieldvalue='" $array['other']['initials'] . "', department.fieldvalue='" $array['other']['department'] . "', position.fieldvalue='" $array['other']['title'] . "' , employment.fieldvalue='Yes' 
                        WHERE guid.customfieldid='1' AND location.customfieldid='2' AND initials.customfieldid='3' AND department.customfieldid='4' AND position.customfieldid='5' AND employment.customfieldid='6' AND swusers.loginapi_userid='
    // and run it...
    if(!$mysqli->query($updatesql)){ echo $mysqli->error; }

    // build the inactive user query...
    foreach($noaction as $user):
    $nochange[] = $user;
    $removesql "UPDATE `swusers` LEFT JOIN `swcustomfieldvalues` as employment ON swusers.userid = employment.typeid 
                        SET employment.fieldvalue='No' 
                        WHERE employment.customfieldid='6' AND swusers.loginapi_userid='
    // and run it...
    if(!$mysqli->query($removesql)){ echo $mysqli->error; }

    /* ------- END DATABASE CHANGES ------- */

    /* ------- START REPORTING ------- */
    echo '<style type="text/css">
    em {
        font-weight: bold;
    tr {
        text-align: left;
        vertical-align: top;
    th {
        text-align: left;
    "<h2>SupportSuite AD user import script</h2>";
    "<li>There are <em>" count($adusers) . "</em> users in the <em>AD security group</em>.</li>";
    "<li>There are <em>" count($ssusers) . "</em> AD users currently in <em>SupportSuite</em>.</li>";
    "<li><em>" count($inserted) . "</em> were <em>imported</em>.</li>";
    "<li><em>" count($toupdate) . "</em> were <em>updated</em>.</li>";
    "<li><em>" count($noaction) . "</em> exist in SupportSuite but do not exist in AD.</li>";
    '<table width="100%">
    <th>Imported</th><th>Updated</th><th>No Longer Exist</th>
    foreach (
    $inserted as $key):
    $key '<br />';
    foreach (
    $updated as $key):
    $key '<br />';
    foreach (
    $nochange as $key):
    $key '<br />';
    /* ------- END REPORTING ------- */

    /* ------- START TIDY UP ------- */
    /* ------- END TIDY UP ------- */
    I think the mySQL configuration has been correct as the script has always successfully written to the swusers table the first time perfectly, it's when the account already exists that it creates the duplicate with a userid of 0 and no email address.

    Strangly the script mod didn't affect this behavior. :(
  11. jcossota

    jcossota Established Member

    I just don't think I can use this script as I created a fresh database and fresh install of Kayako in hopes that I could get the AD import working. Started over with the script and just entered the account info for AD and for MySQL and I still get the 'duplicate entry' error which generates a duplicate user object in the swusers table.

    I wish Kayako had better AD support because I can't use it without it. Appreciate your efforts though.
  12. Dylan Lindgren

    Dylan Lindgren Established Member

    Hey guys,

    I'm really sorry that it's taken me so long to get this resolved. I've been really busy at work and haven't even had a chance to think about this. Finally, I know what the problem is. I was specifically referencing a database in the query.

    This line below...

    $ssquery "SELECT swusers.userid, fullname, phone, loginapi_userid, email FROM supportsuite.swusers LEFT JOIN
    supportsuite.swuseremails ON swusers.userid = swuseremails.userid WHERE loginapi_userid = '0'"
    Should have been this...

    $ssquery "SELECT swusers.userid, fullname, phone, loginapi_userid, email FROM swusers LEFT JOIN 
    swuseremails ON swusers.userid = swuseremails.userid WHERE loginapi_userid = '0'"
    Please find attached the now working sync script. I have also updated the original post.



    Attached Files:

  13. CharityCase

    CharityCase New Member

    This sounds like a fix for the exact issue I am having which is that users that submit tickets via email before logging in for the first time can't login to Support Center.

    This may sound like a stupid question but how do I run the script on the server? We'll be using this strictly in-house (I'm one of two tech support agents in a small charity) and have our test server running on IIS 7 on a Windows 2008 R2 server. I've tried running with php -f in a command window but that doesn't seem to work.

    I'm not familiar with PHP and didn't realize how much tweaking I would have to do to get it to function in our AD environment. Any help you can provide is greatly appreciated.

  14. landspeed

    landspeed New Member

    Cool! I haven't touched the code for a good year or so. It keeps chugging along.

    I will give your code a try on my new box that I plan to stand up soon. Keep up the good work.
  15. Dylan Lindgren

    Dylan Lindgren Established Member

    Sorry it's taken me so long to respond guys, I haven't really been on these forums for a month or so cause of the holiday break.

    Yes CharityCase this script should fix your issue. It's just a normal PHP script, so say your SupportSuite installation is at http://www.myhelpdesk.com and the Import_AD_Users.php script is in the root directory of your web server, you would run it manually by going into internet explorer and going to http://www.myhelpdesk.com/Import_AD_Users.php. Obviously we'd want to automate it, so you can setup a Scheduled Task to run it every day for instance. You should already have some Scheduled Tasks setup as required by SupportSuite (details on how to do this is in the installation guide). Basically you just copy one of the Scheduled Tasks you already have setup, and change it to run the Import_AD_Users.php script. The RUN field in the dialog should look something like below

    C:\WINDOWS\wget.exe -q -O NUL [URL]http://localhost/custom/Import_AD_Users.php[/URL]
    I have mine set to run at 6pm every day. See the attached screenshot also. It's probably a good idea to place the script in a place that is not accessible to the general public, as its quite an intense script and if someone was to hit it multiple times in quick succession it could overload the server.

    Hope this helps!

    Let me know how you get on landspeed, and if it does what you need it to do.


    Attached Files:

  16. nmni

    nmni New Member

    I'm having trouble getting users from the domain, can anyone help with the connection string I should be using?

    this is an internal installation, the domain is magni.local, the users are all in an OU container called MAGNI Users, and I want everyone added to Kayako

    I'm using the below:

    $dn = "DC=magni,DC=local";
    $filter = "(&(objectCategory=user)(memberOf=CN=Domain Users,OU=MAGNI Users,DC=magni,DC=local))";

    When I run the script I get
    SupportSuite AD user import script

    • There are 0 users in the AD security group.
    • There are 0 AD users currently in SupportSuite.
    • 0 were imported.
    • 0 were updated.
    • 0 exist in SupportSuite but do not exist in AD.
  17. Dylan Lindgren

    Dylan Lindgren Established Member

    Hi nmni,

    If the user accounts are within an OU containter and you want ALL users in this OU to be added to supportsuite you would use the connection string (assuming your MAGNI Users OU is in the root of the domain):

    $dn = "OU=MAGNI Users,DC=magni,DC=local";
    $filter = "(objectCategory=user)";

    my connection string is

    $dn = "OU=HDY44 User Accounts,DC=hdy44,DC=com,DC=au";
    $filter = "(&(objectCategory=user)(memberOf=CN=SupportSuite Users,OU=HDY Groups,DC=hdy44,DC=com,DC=au))";

    Basically the way it works for mine is that it is adding all users that are inside the HDY44 User Accounts OU, that are also a member of the SupportSuite Users group. The & Sign indicates that the object must satisfy both the following filter rules to be included in the result.

    Hope this helps.
  18. Dylan Lindgren

    Dylan Lindgren Established Member

    By the way- I didn't really know much about active directory selectors/CN before writing this script, and I found the ADSI edit plugin for the Microsoft Management Console really helped me get my head around it all. There is more info about the ADSI edit at this site here Adsiedit Overview: Active Directory

    So has anyone got this successfully running on their SupportSuite install yet? Its been running on my install perfectly since I originally posted this up.

  19. Ookii

    Ookii New Member


    This is fantastic, it's odd Kayako didn't make one, so I'm glad you did.

    Is it possible to change the script so it's not looking at the mailnickname and looks at the samaccountname instead? In our environment half a dozen users have a different samaccountname than their mailnickname. Anyway, ldap authenticates against samaccountname anyway right?

    I'm going to plug away at it, simply changing 'mailnickname' to 'samaccountname' didn't really do the trick.
  20. smrshl

    smrshl Member

    Has anyone had any luck doing this? I would like to do the same thing.

Share This Page