Home > Development, Infra > Migrating AD Manager information into Infra

Migrating AD Manager information into Infra

July 27th, 2009 Kurt Wolf Leave a comment Go to comments

Infra has been a great ITIL toolset for my department.  The only downfall I have run into is some of the simpler integrations or features come at a steep price point.  This integration cannot be that far out of the box for Infra but yet costs thousands of dollars.  Instead I have been spending some time understanding the Infra database and have come up with the following VBS to move the manager information stored in AD into the Infra database.  We have this script to run each night and it keeps everything in order.

   1:  ' Root DN of the Users
   2:  LDAPRootDN = "LDAP://OU=HCL Users,DC=hcl,DC=internal"
   3:   
   4:  ' Infra database DSN
   5:  InfraDSN = "Driver={SQL Server};Server=data01.hcl.internal;Database=infraEnterprise;Trusted_Connection=TRUE"
   6:   
   7:  set objConnection = CreateObject("ADODB.Connection")
   8:  objConnection.ConnectionString = InfraDSN
   9:  objConnection.open
  10:   
  11:  ' Start the process
  12:  ProcessOU(LDAPRootDN)
  13:   
  14:  function ProcessOU ( DN )
  15:      set CNUsers = GetObject(DN)
  16:      for each User in CNUsers
  17:          select case User.class
  18:              case "user"
  19:                  ' Migrate manager information if a manager is defined
  20:                  if ( User.manager <> "" ) then
  21:                      MigrationCount = MigrationCount + 1
  22:                      MigrateManager User.distinguishedName, User.manager
  23:                  else
  24:                      ' No manager information on file
  25:                  end if
  26:              case "organizationalUnit", "container"
  27:                  ' This is an OU, recurse into
  28:                  ProcessOU( User.ADsPath )
  29:          end select
  30:      next
  31:  end function
  32:   
  33:  function MigrateManager ( StaffDN, ManagerDN )
  34:      set Staff = GetObject("LDAP://" + StaffDN)
  35:      set Manager = GetObject("LDAP://" + ManagerDN)
  36:      strQuery = "exec sp_MigrateManager '" + Staff.GUID + "', '" + Manager.GUID + "'"
  37:      set rs = objConnection.execute(strQuery)
  38:  end function

 

The following is the supporting TSQL for the sp_MigrateManager procedure.

   1:  USE infraEnterprise
   2:  GO
   3:   
   4:  IF EXISTS ( (SELECT NAME FROM sysobjects WHERE NAME = 'sp_MigrateManager' AND TYPE = 'P'))
   5:  BEGIN
   6:      DROP PROCEDURE sp_MigrateManager
   7:  END
   8:  GO
   9:   
  10:  CREATE PROCEDURE sp_MigrateManager 
  11:      @StaffGUID NVARCHAR(300),
  12:      @ManagerGUID NVARCHAR(300)
  13:  AS
  14:  BEGIN
  15:   
  16:      DECLARE @ManagerRef INT
  17:      
  18:      SELECT @ManagerRef = COALESCE(REF, 0) FROM AR_USER_ATTRIBUTES WHERE ldap_object_id = @ManagerGUID
  19:      
  20:      UPDATE AR_USER_ATTRIBUTES
  21:      SET
  22:          MANAGER_REF = @ManagerRef
  23:      WHERE
  24:          ldap_object_id = @StaffGUID
  25:      
  26:  END
  27:  GO
Categories: Development, Infra Tags:
  1. No comments yet.
  1. No trackbacks yet.