Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 918
  • Last Modified:

Vb script to update AD Users tab from excel

Hi Experts,

I'm looking for a vbs that will allow me to change many fields in active directory base on a Excel or csv file.
I will need to change the description & title & department & manager ( if possible )


So far i'm doing this process manually  twice a year and its very long.

The excel looks like the following

A                            B                       C                                 D
Full Name               Title                   Department                Manager


My users are in different OU, and if the user is not found i would like to continu the update process for other users

I google a lot saw similar examples but i d'ont really get it, does anything can give me help ?

Its very appreciate !
thanks you
0
brular01
Asked:
brular01
  • 7
  • 6
  • 2
  • +1
1 Solution
 
ColosseoCommented:
Can your excel file not also include the users user id?

I can write some vbs to do it but having the users user id would help a lot.
0
 
bluntTonyCommented:
Hi there,

Below is a simple example of a script to do this. As colosseo says, you need a link to the object in AD. The most user firendly link will be the user login name (i.e. the 'username' bit from DOMAIN\username). If you can replace 'Full Name' with this, then the below will do it.

Change strCSV to the path to your CSV, and strDomain to the NetBIOS name of your domain (i.e. the 'DOMAIN' in DOMAIN\username).

Save the sheet as a CSV (the header order must be LOGIN,TITLE,DEPARTMENT,MANAGER) and run the script against it. As always test this before running out on production.

Give me a shout if you need this modifying.

Tony

Const ForReading = 1

strCSV = "C:\mycsv.csv"
strDomain = "DOMAIN"

Set objFso = CreateObject("Scripting.FileSystemObject")
Set objCSV = objFSO.OpenTextFile(strCSV,ForReading)


'USE THIS NEXT LINE IF THE CSV INCLUDES HEADERS!!!
objCsv.SkipLine 

Do Until objCsv.AtEndOfStream
	arrData = Split(objCsv.ReadLine,",")
	'Bind to user
	WScript.Echo arrData(0)
	Set objUser = GetObject("LDAP://" & getUserByNTName(strDomain & "\" & arrData(0)))
	
	'Set attributes
	objUser.Put "title", arrData(1)
	objUser.Put "department", arrData(2)
	objUser.Put "manager", getUserByNTName(strDomain & "\" & arrData(3))
	objUser.SetInfo
Loop



Function getUserByNTName(strNTName)
 Const ADS_NAME_INITTYPE_GC = 3
 Const ADS_NAME_TYPE_NT4 = 3
 Const ADS_NAME_TYPE_1779 = 1
 Set objNT = CreateObject("NameTranslate")
 objNT.Init ADS_NAME_INITTYPE_GC, ""
 objNT.Set ADS_NAME_TYPE_NT4, strNTName
 getUserByNTName = Replace(objNT.Get(ADS_NAME_TYPE_1779),"/","\/")
End Function

Open in new window

0
 
bluntTonyCommented:
Oh, this is VBScript by the way. Save it as VBS file and run from the command line via cscript, e.g.

cscript updateUsers.vbs
0
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
Premkumar YogeswaranAnalyst II - System AdministratorCommented:
Hi BluntTony,
Please can you give a script to change the EMP ID with Title, Department and Manger as per the same process above.

Thanking you in advance
0
 
bluntTonyCommented:
Hi premglitz,

Is this request for you personally? I see no mention of employee ID in the original question?

If you want to add attributes to change, add an extra column to the end (so in your case they will be LOGIN,TITLE,DEPARTMENT,MANAGER,EMPLOYEE ID), then this data will be the next element in the array 'arrData', so in this case 'arrData(4)'. So in the section where I am writing the values (commented 'Set Attributes'), add the line...

objUser.Put "employeeID", arrData(4)

This should do it. I'd rather not go into too much detail as this might confuse matters for the asker of the original question.

Tony
0
 
Premkumar YogeswaranAnalyst II - System AdministratorCommented:
Thanks BluntTony
0
 
brular01Author Commented:
Wow you guys are fast, I will try it today and let you know.

thanks
0
 
brular01Author Commented:
Hello,

I try on my dev domain with domain admin rights and i'm always getting

34,2 <null> Name Translation: Could not find the name or insufficient right to see name.

I change the strDomain  to my netbios domain name
In the CSV under A i enter my domain user

thanks
0
 
bluntTonyCommented:
It needs to be the user login name (sAMAccountName), i.e. the 'username' bit in 'DOMAIN\username'. Check the 'Account' tab for the user in ADUC. In the CSV, just enter the 'username' bit.

This error usually means it cannot find the name given.
0
 
brular01Author Commented:
Good I figure out,  

I need to put a valid AD login for the manager, is there a way to only put the full name ?

Another thing, even if the manager user id exists the script will put the name of the user that we are updating

Finnaly, if the used does not exist, I would like to have the script continu to update the others rows listed in the csv.

Let me know, and thanks a lot for your help !!!
0
 
bluntTonyCommented:
Hi there,

I need to put a valid AD login for the manager, is there a way to only put the full name ?

-- Yes, but Fullname isn't unique so isn't 100%. I'll post a script shortly that utilises fullname instead of login.

Another thing, even if the manager user id exists the script will put the name of the user that we are updating

-- The manager column should have the login of the manager in question. What do you want to enter in this column?

Finnaly, if the used does not exist, I would like to have the script continu to update the others rows listed in the csv.

-- Adding 'On Error Resume Next' at the top of the script will do this, but let me know about the other two questions and I'll post a new script.

Tony
0
 
brular01Author Commented:
For Manager i would like to use the Full Name if possible ( if not possible no problem i can deal with that )

thanks
0
 
bluntTonyCommented:
Hi there,

The script will now accept full names for both the user and the manager. See how this goes in testing. The CSV order is now:

User(Full Name),Title,Department,Manager(Full Name)

Tony

Const ForReading = 1

strCSV = "C:\mycsv.csv"

'Objects
Set oRootDSE = GetObject("LDAP://RootDSE")
Set objConn = CreateObject("ADODB.Connection")
Set objComm =   CreateObject("ADODB.Command")
Set objFso = CreateObject("Scripting.FileSystemObject")
Set objCSV = objFSO.OpenTextFile(strCSV,ForReading)

'ADODB Parameters
objConn.Provider = "ADsDSOObject"
objConn.Open "Active Directory Provider"
Set objComm.ActiveConnection = objConn
objComm.Properties("Page Size") = 1000
strBase   =  "<LDAP://" & oRootDSE.get("defaultNamingContext") & ">;"
strAttrs  = "distinguishedName;"
strScope  = "subtree"

'USE THIS NEXT LINE IF THE CSV INCLUDES HEADERS!!!
objCsv.SkipLine 

Do Until objCsv.AtEndOfStream
        arrData = Split(objCsv.ReadLine,",")
        'Bind to user
        strDN = getDNFromFullName(arrData(0))
        If strDN <> "" Then
	        Set objUser = GetObject("LDAP://" & strDN)
	        'Set attributes
	        objUser.Put "title", arrData(1)
	        objUser.Put "department", arrData(2)
	        strManagerDN = getDNFromFullName(arrData(3))
	        If strManagerDN <> "" Then objUser.Put "manager", strManagerDN
	        objUser.SetInfo
        End If
Loop


Set oRootDSE = Nothing
Set objConn = Nothing
Set objComm = Nothing

'END!! ##########


Function getDNFromFullName(strFullName)
 strFilter = "(&(objectclass=user)(objectCategory=person)(name=" & strFullName & "));" 
 objComm.CommandText = strBase & strFilter & strAttrs & strScope
 Set objRS = objComm.Execute
 If objRs.RecordCount = 1 Then getDNFromFullName = objRs.Fields(0).Value Else getDNFromFullName = ""
End Function

Open in new window

0
 
brular01Author Commented:
Awesome its pefect, 1 last thing and I leave you alone.

If there a way to output the error in a txt file when it occur, but continu the script to run.

thanks you !!! thanks you !!!
0
 
bluntTonyCommented:
Hi there,

The below script will now write to an error log file 'strLog' when either the fullname of the user is not recognised, or the fullname of the manager for a user is not recognised.

Hope this helps!

Tony

Const ForReading = 1
Const ForWriting = 2

strCSV = "C:\mycsv.csv"
strLog = "C:\errorlog.log"

'Objects
Set oRootDSE = GetObject("LDAP://RootDSE")
Set objConn = CreateObject("ADODB.Connection")
Set objComm =   CreateObject("ADODB.Command")
Set objFso = CreateObject("Scripting.FileSystemObject")
objFso.CreateTextFile strLog, True
Set objLog = objFso.OpenTextFile(strLog,ForWriting)
Set objCSV = objFSO.OpenTextFile(strCSV,ForReading)

'ADODB Parameters
objConn.Provider = "ADsDSOObject"
objConn.Open "Active Directory Provider"
Set objComm.ActiveConnection = objConn
objComm.Properties("Page Size") = 1000
strBase   =  "<LDAP://" & oRootDSE.get("defaultNamingContext") & ">;"
strAttrs  = "distinguishedName;"
strScope  = "subtree"

'USE THIS NEXT LINE IF THE CSV INCLUDES HEADERS!!!
objCsv.SkipLine 

Do Until objCsv.AtEndOfStream
        arrData = Split(objCsv.ReadLine,",")
        'Bind to user
        strDN = getDNFromFullName(arrData(0))
        If strDN <> "" Then
	        Set objUser = GetObject("LDAP://" & strDN)
	        'Set attributes
	        objUser.Put "title", arrData(1)
	        objUser.Put "department", arrData(2)
	        strManagerDN = getDNFromFullName(arrData(3))
	        If strManagerDN <> "" Then 
	        	objUser.Put "manager", strManagerDN
	        Else
	        	objLog.WriteLine "Error >> Could not add mananger field to user " & arrData(0) _
	        		& ", the manager '" & arrData(3) & "' could not be found in the directory"
	        End If
	        objUser.SetInfo
	    Else
	    	objLog.WriteLine "Error >> The user '" & arrData(0) & "' could not be found in the directory"	        
        End If
Loop

objLog.Close
objCsv.Close

Set oRootDSE = Nothing
Set objConn = Nothing
Set objComm = Nothing

'END!! ##########


Function getDNFromFullName(strFullName)
 strFilter = "(&(objectclass=user)(objectCategory=person)(name=" & strFullName & "));" 
 objComm.CommandText = strBase & strFilter & strAttrs & strScope
 Set objRS = objComm.Execute
 If objRs.RecordCount = 1 Then getDNFromFullName = objRs.Fields(0).Value Else getDNFromFullName = ""
End Function

Open in new window

0
 
brular01Author Commented:
Tony you are the best thanks you !!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 7
  • 6
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now