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
Comments