The situation: After the upgrade to v6 one of our techs ran a manual user import. It was misconfigured so that the LDAP UID was linked to samaccountname and username was linked to CN changing all of the usernames from, say, jsmith to John Smith, and breaking the link to the LDAP username, breaking the authentication for the effected users. The corrected import, instead of re-updating the username, created new users with the proper login names. However, this means that things like ticket ownership are broken, since those are linked by the ID field of the User table.

Now, I know just enough SQL to break something, but the script I came up with is:

/* Step 1: Change the old ID (1234) to correct username. */
SET `ID` = '1234'
WHERE `USER_NAME` = 'jsmith'; /* Step 2: Change the new ID (5678) to broken username. */
SET `ID` = '5678'
WHERE `USER_NAME` = 'John Smith';

The only way I know how to run SQL update queries is through Ticket Rules, so my questions are: Will this even work? Is there a better way? Is there a way to formulate a query to do this for all of the affected users (something along the logic lines of "IF John Smith and jsmith exist in USER_NAME, switch ID")? 

Answer Summary:
1 Comment   [ + ] Show Comment


  • If you made backups prior to this you can roll back by doing a restore if did not do a backup I would open a ticket kace support can go in on the backend and fix this easier then you can.
    • I tried that and they referred me to their Professional Remote Services. I would like to avoid a $500+ bill for a simple script.
      • yea I don't blame you, not a SQL expert either, hopefully another Ninja who is will have an idea
Please log in to comment



Is LDAP ID set to ObjectGUID for the import? 

FWIW, here is how I generally recommend mapping the fields (first four required):

LDAP ID = objectGUID
login = samaccountname
name = name
email = mail
location = physicalOfficeDeliveryName
work phone = telephoneNumber


The SQL query & update probably is possible, but I'm not going to be much help there. 


Answered 05/22/2014 by: MacDude
Fourth Degree Brown Belt

  • I forgot to mention (and have subsequently updated my question) that the misconfigured import also linked LDAP ID to samaccountname, thus the duplication of the users once the corrected import was done.
Please log in to comment

Through dangerous experimentation, I have discovered the answer: Yes, it does work but you can only run one update query at a time. So I created separate rules for each step. I still have to do it one user at a time, but it's a solution.

Answered 05/27/2014 by: tdickinson
Orange Senior Belt

Please log in to comment
Answer this question or Comment on this question for clarity