3 Replies Latest reply on Feb 19, 2010 2:25 AM by mitchella

    AD Import not Importing where TPS_Name already exists in Database.

    Apprentice

      Hi All,

       

      I'm hoping someone will be able to offer some wisdom on this as it's been vexing me for some time. When the implementation of Servicedesk was originally configured the decision was taken to have the primary key for the TPS_Users to be the TPS_Name (User name when imported from AD). Works fine, imports without issue. When users are disabled in AD Landesk Servicedesk is configured to pick that up and soft-deletes the user from the database. This also works okay and does what's needed.

       

      The problem I have is that when users are disabled within the AD after a certain period as part of general house-keeping, the disabled users will be deleted from Active Directory and the username can be recycled, so for example: John Smith (user name: smithj) leaves the company, the account is disabled in AD and the end user account is soft-deleted in Servicedesk. 6 Months later SmithJ gets deleted from AD and Jane Smith joins the company. She gets assigned SmithJ as a user name and Servicedesk won't import her because there is already a SmithJ in the Database. This prevents her using the Service Portal and Tickets cannot be logged in her name.

       

      We tried creating a procedure that would rename the user-names for Soft-Deleted users in the database to {DateDeleted}-{Username} which ran every night on any users that were soft-deleted that day. The problem is that when that script runs it renames the username and when the Import of Disabled users runs, until the accounts have been deleted from AD, it reimports all the disabled accounts again, then soft-deletes them so for example: John Smith gets soft-deleted, the account gets renamed to {date}-SmithJ and every night following until the account is actually deleted from AD his account is reimported by the Disabled Import and renamed with that days date.

       

      This unfortunately causes potentially hundreds of extra entries into the database per week for disabled accounts and just isn't going to work. In addition some accounts are disabled temporarily for security purposes and then re-enabled which also causes issues as the re-enabled accounts don't automatically re-enable in Servicedesk. .

       

      I have included a copy of the SQL we created below:

       

      USE [LDSD]
      GO
      SET ANSI_NULLS ON
      GO
      SET QUOTED_IDENTIFIER ON
      GO
      CREATE PROCEDURE [dbo].[sp_RenameSoftDeletedUsers] AS
      BEGIN
        UPDATE tps_user
          SET tps_name =
            CONVERT (VARCHAR(10), GETDATE (), 105) + tps_name
          WHERE
            tps_deleted = 1 AND
            tps_name NOT LIKE
              '[0123][0123456789]-[01][0123456789]-20[01][0123456789]%'
      END
      Any help in getting this working would be great, as I can't keep going without being able to recycle these user names, the more time passes, the more users will be missing from the database.
      Many Thanks in advance,
      Anthony Mitchell
        • 1. Re: AD Import not Importing where TPS_Name already exists in Database.
          elizabethcombrink Employee

          Anthony, some thoughts from me.  But I don't use the ITBM AD import, so I don't know how useful they will be

           

           

          The AD import should be able to reset the is_deleted flag for you on each import - get support to investigate this.

           

          I would attempt to import the actual GUID value from AD as the Primary key, rather than the name.  There are many reasons why the name may get changed (ie married, to differentiate it from somebody with the same name etc)  If you use the GUID from AD as the unique identifier, the AD import should auto update the name.  And for each new AD record created - regardless if somebody has had the same login as before, a new user record will be created in ITBM.

           

          Regarding your Stored Proc - On the AD import, can you set a timestamp on the tps_user record that is evaluated when the stored proc runs to determine when last AD updated that record.  If AD has updated in the last 24 hours, then don't rename it yet because its not a dead record until its actually been deleted in AD.

          • 2. Re: AD Import not Importing where TPS_Name already exists in Database.
            LegoGuy SupportEmployee

            I would suggest that you update your script and only rename the user when the tps_last_update column is older than 6 months.  That column would be updated when they were soft deleted but shouldn't get updated after that.  So six months after they are soft deleted you could then go in and rename them.

            1 of 1 people found this helpful
            • 3. Re: AD Import not Importing where TPS_Name already exists in Database.
              Apprentice

              Thanks Elizabeth,

               

              If I had the option I could use the Employee ID for the Primary Key as that is unique and never recycled but I don't know what the effect would be of changing the primary key attribute from the import.

               

              If anyone has any information on what that would do? I'm assuming it wouldn't be wise to suddenly switch the primary key, but I don't know what the effects would be in this instance.

               

              Many Thanks,