Avatar of TheCleaner
TheCleaner
Flag 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.

LDIFDE?
vbscript?

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

Any ideas?
Windows Server 2003

Avatar of undefined
Last Comment
Chris Dent

8/22/2022 - Mon
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.
Jay_Jay70

if you want to work straight out of csv files then use CSVDE

http://www.computerperformance.co.uk/Logon/Logon_CSVDE.htm

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

James
ryangorman

Its a shame that CSVDE cannot modify existing objects, only create new objects.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Jay_Jay70

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...
Netman66

If you have NO duplicates, then this is pretty simple.  If you have a duplicate, then move it to a temporary OU.

Use DSMOD USER.

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.



TheCleaner

ASKER
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ryangorman

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.

[1] http://www.computerperformance.co.uk/vbscript/vbscript_user_spreadsheet.htm
[2] http://www.msexchange.org/articles/Creating-Exchange-Users-Excel.html
Chris Dent


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?

Chris
Netman66

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

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ryangorman

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


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) ;)

Chris
TheCleaner

ASKER
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Chris Dent


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

At the moment I'm working on:

FirstName,LastName,EmployeeID

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.

Chris
TheCleaner

ASKER
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 wish...so it may be easier if you tell me what you have and I'll make sure the report conforms to that.
ASKER CERTIFIED SOLUTION
Chris Dent

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Chris Dent


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.

Chris
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Chris Dent


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

Chris
TheCleaner

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


No problem, whenever really :)

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

Chris
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Chris Dent


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.

Chris
TheCleaner

ASKER
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.
Chris Dent


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"
Const MATCH_BY_DISPLAY_NAME = True

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
      WScript.Quit
End Sub

Sub SortArgv
      ' Sort the Command Line Arguments

      Dim objArgv
      Dim strMode

      Set objArgv = WScript.Arguments
      If objArgv.Count < 1 Then
            UsageText()
      End If
      strMode = objArgv(0)
      If LCase(strMode) = "-report" Then
            booReportMode = True
      ElseIf LCase(strMode) = "-run" Then
            booReportMode = False
      Else
            UsageText()
      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
            objInputFile.ReadLine
      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
      Loop
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
                        Else
                              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)
                  Else
                        i = 1
                        objTempDisplayName.Add strDisplayName, Array(strLine, i)
                  End If
            Else
                  i = 1
                  objTempDisplayName.Add strDisplayName, Array(strLine, i)
            End If
      Next
      
      For Each strDisplayName in objTempDisplayName
            If objTempDisplayName(strDisplayName)(1) = 1 Then
                  strLine = objTempDisplayName(strDisplayName)(0)
                  objByDisplayName.Add strDisplayName, strLine
            End If
      Next
      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
                  Next
            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.Remove(strLine)
                                    objTemp.Add strLine, Array(i, arrMatchingDNs)
                              End If
                        End If
                  End If
            End If
            objRecordSet.MoveNext
      WEnd
      
      ' 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
      Next
End Sub

Sub WriteFailedMatches
      Dim objOutputFile
      Dim strLine

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

      For Each strLine in objTemp
            objOutputFile.WriteLine strLine
      Next
      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
      Next
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
                  objUser.SetInfo
            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
      objFileSystem.CreateFolder("Logs")
End If
arrTemp = Split(WScript.ScriptName, ".")
strLogFile = "Logs\" & arrTemp(0) & strTimeStamp & ".log"
Set objLogFile = objFileSystem.OpenTextFile(strLogFile, 2, True, 0)

SortArgv

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

Set objTemp = CreateObject("Scripting.Dictionary")

ReadFile IMPORT_FILE

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

SortData

Set objUsersToProcess = CreateObject("Scripting.Dictionary")

If objTemp.Count > 0 Then
      GetADData
End If

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

If objTemp.Count > 0 Then
      WriteFailedMatches
End If

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

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


Set objUsersToProcess = Nothing

WriteLog "Script Completed", False

Set objLogFile = Nothing
Set objFileSystem = Nothing
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Chris Dent


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:

GivenName,Surname,EmailAddress,EmployeeID

Chris
knightworld

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.
Chris Dent


Glad it was useful :)

Chris
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
TheCleaner

ASKER
Chris,

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 out...cool?

Thanks!
Chris Dent


Absolutely, no rush  :)

Chris