Link to home
Start Free TrialLog in
Avatar of TheCleaner
TheCleanerFlag for United States of America

asked on

Need "script" - modify AD users / insert employee number

I know what I want to do, but I'm not sure the best way to approach this.

I have an export from Peoplesoft with employee info, including first name, last name, and employee number.

I'd like to modify the AD user accounts to include the employee #.

I know the "logic" I'm looking for, ie. If last name = field AND first name = field then modify employee number with the #, I just can't quite get started on what to use for this.


I can get the results from Peoplesoft in a .csv format, which complicates the LDIFDE approach.

Any ideas?
Avatar of ryangorman

The first obstacle to overcome it that you cannot find the *right* user based on Last name, First name alone as multiple users may share these attributes

e.g. cn=Jack Dawes,ou=North,dc=domain,dc=local (Dawes, Jack) and cn=Jack Dawes,ou=South,dc=domain,dc=local (Dawes, Jack) both have non-unique surname/forenames but have unique distinguished name/SAMid

Once this issue is resolved - I'd use a VBscript that either edited objects directly (setinfo) or created a correctly formatted LDIF file. DSmod is good but you'd need a unique DN again.
if you want to work straight out of csv files then use CSVDE

however if its one attribute you may have to get fairly dirty with it....Chris-Dent does a stack of work with scripting and AD import and export tools, if this fails, maybe drop him an email

Its a shame that CSVDE cannot modify existing objects, only create new objects.
depends how far you want to push it...if you export then reimport it can be done - not ideal when you look at the possible ramifications...
Avatar of Netman66
If you have NO duplicates, then this is pretty simple.  If you have a duplicate, then move it to a temporary OU.


If you need me to write the script, let me know.  I will need the source file (sample exactly as  you have it for real), the OU structure and whatever relevant information there is.

Avatar of TheCleaner


Yeah DSMOD USER might work, hadn't even thought of using it.  I'll look into that.

ryangorman, I was actually thinking the easiest unique identifier I have currently is email address.  I was going to key off of that and then import the employee number.  Then employee # would become by unique ID in AD for each account to be modifed later.

I'll take a look at DSMOD first and let everyone know if I need help.  I'll update very soon.
Didn't I mention DSmod?

The following DSquery line outputs each user's DN and their primary SMTP address. You could use this with vlookup in Excel to match DN to your original data. You'd then have a sheet with the unique DNs and the Employee# that you want to inject into whatever atrribute.

dsquery * -filter "(&(objectcategory=user)(mail=*@yourmaildomain.tld))" -limit 10 -attr distinguishedName mail

You can then modify one of the various "Create Users from Excel" [1,2] to read the DN column and change your Employee# attribute using data from the Employee# column.

Obviously replace the objPtr = objParent.Create line with obj.Ptr = GetObject() to modify rather than create.


We have a SAP export job that runs that takes a first name and last name and updates the employee ID field for every unique match it gets. Would you like the code or is it all fixed now?

@ryan - yes, you did mention that.  I just reread you post.  Must have read right over it the first time.

Sounds like although Chris' script doesn't process conflicting accounts, that it is engineered for purpose and would be a good solution.

Okay, I'll need to chop out all the other checks we have in as they won't be applicable. But I'll post it as soon as I've been to the pub (well it is friday) ;)

Yeah that'd be great Chris-Dent.

ryan, thanks for the help as well...I did see that you mentioned DSMOD in your first post when I replied...everyone in this thread knows their stuff, so I appreciate this expert receiving expert advice.

For accuracy... can you let me know the format of your import file?

At the moment I'm working on:


But that can be easily changed. I also need to know if the file has a Header Row; although if it has I can make the field locations dynamic based on that.

I haven't exported the "import file" yet.  We use Strategy 6.5 to run reports from the Peoplesoft databases.  It can be delivered in any format I it may be easier if you tell me what you have and I'll make sure the report conforms to that.
Avatar of Chris Dent
Chris Dent
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial

One more part I forgot... it assumes your Import File has a Header Row. This appears on line 79 and just below. It strips off the Header Row before reading anything else. If it doesn't have one comment out the few lines below. It's commented so it should be quite obvious.


oops... I forgot to correct the lines in the script before posting it. I was testing it against my own domain. Anyway the lines the define the DisplayName will need correcting regardless of how your names are confirgured right now:

Line 111: strDisplayName =  arrLine(0) & " " & arrLine(1)
Line 176: strDisplayName =  strGivenName & " " & strSurname

OK may be a few days before I can really comment on this, but I'll try to get updates asap.

No problem, whenever really :)

Yell if it doesn't do quite what you want, it can always be altered.


Oh I didn't notice earlier... you have the e-mail address for the user in there too? I'll re-add the section of code the matches based on that when I get a moment. Display Name is the last possible resort for us, Username and Email address come above that.

Yeah...ideal would be:

1.  Input file is read
2.  Looks up match in AD based on email address
3.  Modifies that user to add the employee number to the employee number field in AD
4.  Logs the results of each line item for review

Thanks again.

Sorry for the wait... bit of a busy weekend.

I believe this does everything you have above.

If you don't want to use the Matching based on display name there's a Constant at the top you can set to False to stop it doing it. It will only match by displayname if it can't match by e-mail.

Option Explicit

Const IMPORT_FILE = "Test.csv"

Dim objFileSystem, objLogFile, objTemp, objByDisplayName, objByEmail, objUsersToProcess
Dim strTimeStamp, strLogFile
Dim arrTemp
Dim booReportMode

' Subroutines

Sub UsageText
      Dim strMessage

      strMessage = "Usage:" & VbCrLf & VbCrLf
      strMessage = strMessage & "cscript " & WScript.ScriptName & " [-report | -run]" & VbCrLf & VbCrLf
      strMessage = strMessage & VbTab & "-report - Reports changes" & VbCrLf
      strMessage = strMessage & VbTab & "-run - Reports and Commits changes" & VbCrLf
      WScript.Echo strMessage
End Sub

Sub SortArgv
      ' Sort the Command Line Arguments

      Dim objArgv
      Dim strMode

      Set objArgv = WScript.Arguments
      If objArgv.Count < 1 Then
      End If
      strMode = objArgv(0)
      If LCase(strMode) = "-report" Then
            booReportMode = True
      ElseIf LCase(strMode) = "-run" Then
            booReportMode = False
      End If
      Set objArgv = Nothing      
End Sub

Sub WriteLog(strMessage, booLogOnly)
      ' For writing Log Entries
      Dim strScriptHost
      Dim booShowCommandLine

      strScriptHost = WScript.FullName
      strScriptHost = Right(strScriptHost, Len(strScriptHost) - InStrRev(strScriptHost, "\"))

      booShowCommandLine = True
      If UCase(strScriptHost) = "WSCRIPT.EXE" Then
            booShowCommandLine = False
      End If
      If booLogOnly = True Then
            booShowCommandLine = False
      End If

      objLogFile.WriteLine "[" & Now() & "] " & strMessage
      If booShowCommandLine = True Then
            wscript.echo "[" & Now() & "] " & strMessage
      End If
End Sub

Sub ReadFile(strInputFile)
      ' Responsible for Reading the Input File and loading it into the Script Dictionary objTemp

      Dim objInputFile
      Dim strLine
      Dim arrLine, arrMatchingDNs(), arrTemp

      Set objInputFile = objFileSystem.OpenTextFile(strInputFile, 1, False, 0)
      ' Assumes there is a Title row and discards it
      If Not objInputFile.AtEndOfStream Then
      End If

      ' Reads the data from the file into the dictionary
      Do While Not objInputFile.AtEndOfStream
            strLine = objInputFile.ReadLine

            If strLine <> "" Then
                  If Not objTemp.Exists(strLine) Then
                        objTemp.Add strLine, Array(0, arrMatchingDNs)
                  End If
            End If
End Sub

Sub SortData
      ' Creates a new Dictionary based on the Display Name; Duplicates in the Import are discarded here.

      Dim objTempDisplayName
      Dim strLine, strEmail, strDisplayName
      Dim arrLine
      Dim i

      Set objTempDisplayName = CreateObject("Scripting.Dictionary")

      For Each strLine in objTemp
            arrLine = Split(strLine, ",")

            If UBound(arrLine) >= 3 Then
                  strEmail = Replace(LCase(arrLine(2)), """", "")
                  If strEmail <> "" Then
                        If Not objByEmail.Exists(strEmail) Then
                              objByEmail.Add strEmail, strLine
                              WriteLog "Duplicate Email in Data Set:", True
                              WriteLog strEmail & ": " & strLine, True
                              WriteLog strEmail & ": " & objByEmail(strEmail), True
                        End If
                  End If
            End If

            strDisplayName =  arrLine(1) & " " & arrLine(0)
            If objTempDisplayName.Exists(strDisplayName) Then
                  If strLine <> objTempDisplayName(strDisplayName)(0) Then
                        i = objTempDisplayName(strDisplayName)(1) + 1
                        objTempDisplayName.Remove strDisplayName
                        objTempDisplayName.Add strDisplayName, Array(strLine, i)
                        i = 1
                        objTempDisplayName.Add strDisplayName, Array(strLine, i)
                  End If
                  i = 1
                  objTempDisplayName.Add strDisplayName, Array(strLine, i)
            End If
      For Each strDisplayName in objTempDisplayName
            If objTempDisplayName(strDisplayName)(1) = 1 Then
                  strLine = objTempDisplayName(strDisplayName)(0)
                  objByDisplayName.Add strDisplayName, strLine
            End If
      Set objTempDisplayName = Nothing
End Sub

Sub GetADData
      ' Attempt to match each unique DisplayName from the Import to a DisplayName in Active Directory

      Const ADS_SCOPE_SUBTREE = 2

      Dim objConnection, objCommand, objRootDSE, objRecordSet
      Dim strGivenName, strSurname, strADSPath, strDisplayName, strLine, strAddress
      Dim arrMatchingDNs, arrAddresses
      Dim i
      Set objConnection = CreateObject("ADODB.Connection")
      objConnection.Provider = "ADsDSOObject"
      objConnection.Open "Active Directory Provider"

      Set objCommand = CreateObject("ADODB.Command")
      objCommand.ActiveConnection = objConnection

      objCommand.Properties("Page Size") = 1000
      objCommand.Properties("Timeout") = 600
      objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
      objCommand.Properties("Cache Results") = False

      Set objRootDSE = GetObject("LDAP://RootDSE")
      objCommand.CommandText = "SELECT aDSPath, givenName, sn, proxyAddresses " &_
            " FROM 'LDAP://" & objRootDSE.Get("defaultNamingContext") & "' WHERE objectClass='user'"
      Set objRootDSE = Nothing

      Set objRecordSet = objCommand.Execute

      While Not objRecordSet.EOF

            On Error Resume Next
            strGivenName = "" : strGivenName = objRecordSet.Fields("givenName")
            strSurname = "" : strSurname = objRecordSet.Fields("sN")
            strADSPath = objRecordSet.Fields("aDSPath")
            arrAddresses = objRecordSet.Fields("proxyAddresses")
            On Error Goto 0

            If IsArray(arrAddresses) Then
                  For Each strAddress in arrAddresses
                        strAddress = LCase(strAddress)
                        If InStr(strAddress, "smtp:") Then
                              strAddress = Replace(strAddress, "smtp:", "")
                              If objByEmail.Exists(strAddress) Then
                                    If objTemp.Exists(objByEmail(strAddress)) Then
                                          If booReportMode = True Then
                                                WriteLog "Matched By Email: " & strADSPath, True
                                                WriteLog objByEmail(strAddress), True
                                          End If
                                          objUsersToProcess.Add objByEmail(strAddress), strADSPath
                                          objTemp.Remove objByEmail(strAddress)
                                    End If
                              End If
                        End If
            End If

            ' Match Display Name

            If MATCH_BY_DISPLAY_NAME = True Then

                  strDisplayName = strSurname & " " & strGivenName
                  If strDisplayName <> " " And strDisplayName <> "" Then
                        If objByDisplayName.Exists(strDisplayName) Then
                              If objTemp.Exists(objByDisplayName(strDisplayName)) Then
                                    strLine = objByDisplayName(strDisplayName)
                                    i = objTemp(objByDisplayName(strDisplayName))(0) + 1
                                    arrMatchingDNs = objTemp(objByDisplayName(strDisplayName))(1)
                                    ReDim Preserve arrMatchingDNs(i - 1)
                                    arrMatchingDNs(i - 1) = strADSPath
                                    objTemp.Add strLine, Array(i, arrMatchingDNs)
                              End If
                        End If
                  End If
            End If
      ' Protection against multiple catches when matching by display name only - Any DisplayName matched to more than
      ' one user is discarded and logged here.
      For Each strLine in objTemp
            If objTemp(strLine)(0) = 1 Then
                  If Not objUsersToProcess.Exists(strLine) Then
                        If booReportMode = True Then
                              WriteLog "Matched By Displayname: " & objTemp(strLine)(1)(0), True
                              WriteLog strLine, True
                        End If
                        objUsersToProcess.Add strLine, objTemp(strLine)(1)(0)
                        objTemp.Remove strLine
                  End If
            End If
End Sub

Sub WriteFailedMatches
      Dim objOutputFile
      Dim strLine

      If Not objFileSystem.FolderExists("FailedMatches") Then
      End If
      Set objOutputFile = objFileSystem.OpenTextFile("FailedMatches\FailedMatches" & strTimeStamp & ".csv", 2, True, 0)

      For Each strLine in objTemp
            objOutputFile.WriteLine strLine
      Set objOutputFile = Nothing
End Sub

Sub UpdateUsers
      Dim objUser
      Dim strLine, strADSPath
      Dim arrUserData

      WriteLog "Updating User Accounts", False

      For Each strLine in objUsersToProcess
            strADSPath = objUsersToProcess(strLine)
            Set objUser = GetObject(strADSPath)
            arrUserData = Split(strLine, ",")
            CheckUpdate objUser, "employeeID", arruserData(UBound(arrUserData))
            Set objUser = Nothing
End Sub

Sub CheckUpdate(objUser, strAttribute, strValue)
      Dim strCurrentValue

      On Error Resume Next
      strCurrentValue = "" : strCurrentValue = objUser.Get(strAttribute)
      If LCase(strCurrentValue) <> LCase(strValue) And strValue <> "" Then
            WriteLog "Updating " & strAttribute & " Attribute: " &_
                  objUser.Get("displayName") & ": Old: " & strCurrentValue &_
                  " New: " & strValue, True
            If booReportMode = False Then
                  objUser.Put strAttribute, strValue
            End If
      End If
      On Error Goto 0
End Sub

' Main Code

Set objFileSystem = CreateObject("Scripting.FileSystemObject")

strTimeStamp = CStr(Now())
strTimeStamp = Replace(strTimeStamp, "/", "")
strTimeStamp = Replace(strTimeStamp, " ", "")
strTimeStamp = Replace(strTimeStamp, ":", "")
If Not objFileSystem.FolderExists("Logs") Then
End If
arrTemp = Split(WScript.ScriptName, ".")
strLogFile = "Logs\" & arrTemp(0) & strTimeStamp & ".log"
Set objLogFile = objFileSystem.OpenTextFile(strLogFile, 2, True, 0)


WriteLog "Script Started", False
If booReportMode = True Then
      WriteLog "** Running in Report Mode **", False
End If

Set objTemp = CreateObject("Scripting.Dictionary")


Set objByEmail = CreateObject("Scripting.Dictionary")
Set objByDisplayName = CreateObject("Scripting.Dictionary")


Set objUsersToProcess = CreateObject("Scripting.Dictionary")

If objTemp.Count > 0 Then
End If

WriteLog "Successful User Matches: " & objUsersToProcess.Count, True
WriteLog "Failed User Matches: " & objTemp.Count, True

If objTemp.Count > 0 Then
End If

Set objByEmail = Nothing
Set objByDisplayName = Nothing
Set objTemp = Nothing

If objUsersToProcess.Count > 0 Then
      WriteLog "** No Users To Process **", False
End If

Set objUsersToProcess = Nothing

WriteLog "Script Completed", False

Set objLogFile = Nothing
Set objFileSystem = Nothing

It does help if I tell you the things I changed that would directly effect the operation of the script...

It expects the input format to be:


Chris-Dent - gotta thank you for this script. I ran across this thread today while trying to find a way to do this exact same thing; almost 1000 user accounts needing modification for Employee Number. This saved me days of trying to remeber how to write (or piece together) a script to do the same thing.

Glad it was useful :)


I've been so swamped lately I haven't had a chance to work on this.  I'm sure it will work well, but give me a bit of time still before I close this


Absolutely, no rush  :)