i want a script to modify manager's field in AD via Excel Spreadsheet

jurjess
jurjess used Ask the Experts™
on
Hi,

I have got a list of usernames and their managers in a excel spreadsheet.

Is there any way i can run a script using the excel sreadsheet and then it checkes the samaccount name from excel sheet and then populates the manager's name in AD?

Many Thanks.

Regards,

J
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Can you explain with some example?

Commented:
you could extend your excel spreadsheet to get automatically scripts like:

dsmod user -mgr Manager1 cn=user1,ou=1,dc=domain,dc=com

under Manager1 and user1 you will have the fields that contain the required name. Then you copy all of your lines for example 1000 lines to a bat file and then execute it.

Author

Commented:
I have got a excel sheet with two columns
SAMAccountName          manager
a                                   aa      
b                                   bb
c                                   cc

i want to put a manager name in AD as in the excel sheet.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Commented:
I would do it like this - see the file. You now copy the E column to a bat file and execute it.

In A column you have:

="dsquery -samid " & B2 & " |"

and in D you have:

=" dsmod user -mgr " & C2

dsmod.JPG

Author

Commented:
thats great.

how can i make a batch file and run in AD?

and secondly what will be the formula for Column E?

Many Thanks.

Commented:
you just select the column E then past using "ctrl+V" to notepad. Then save the file as "example.bat" and just click it twice it should run. Make shure that when you save using notepad it will save as .bat not as .bat.txt

formula for E is:

=A&D

Author

Commented:
i have done that but i am getting error msgs as

C:\>dsquery -samid Adam   | dsmod user -mgr Jurjess
dsquery failed:The parameter is incorrect.:Incorrect object type specified.
type dsquery /? for help.dsmod failed:Value for `Target object for this command'
 has incorrect format.
type dsmod /? for help.

Commented:
sorry i've missed the "user". it should be:

="dsquery user -samid " & B2 & " |"

Author

Commented:
Now i am getting this

C:\>dsquery user -samid Adam   | dsmod user -mgr Jurjess
dsmod failed:Value for `Target object for this command' has incorrect format.
type dsmod /? for help.dsquery failed:Value for `startnode' has incorrect format
.

Commented:
Sorry i'm writing this script from memory - i will be able to check them later. For now i see that the parameter for -mgr has to be in Distinguish Name Format. So if you change Jurjess for:

CN=Jurjess,OU=Employes,DC=domain,dc=com

the script will work. I will try to figure out how to change it.

Author

Commented:
that's fine

i have got 500 users so does taht mean i have to get the full dn for each user and type it in excel sheet?

Commented:
for the above full DN is required "only" for managers not for users. Will try to fix it.
bluntTonyHead of ICT
Top Expert 2009

Commented:
Hi there,

This VBscript will do the job for you.

Remove the headers and save the XLS as a CSV file, so you end up with a file looking like

user1,manager1
user2,manager2

..etc...

(where user1 is the user you want to modify, and manager1 is the manager you want to add. Both of these are sAMAccountName)

Save the below code as a VBS file, change strDomain to your domain name and change strCSV to the path of your CSV file.

Run the vbs and it should update the users.
On Error Resume Next
Const ForReading = 1
 
strDomain = "NEPTUNE"
strCSV = "C:\users.csv"
 
Set objFSo = CreateObject("scripting.FileSystemObject")
Set objTxt = objFso.OpenTextFile(strCSV, ForReading)
 
Do
	arrData = Split(objTxt.ReadLine,",")
	strSamAccountName = arrData(0)
	strManager = arrData(1)
	
	Set objUser = GetObject("LDAP://" & getDNFromNTName(strDomain & "\" & strSamAccountName))
	objUser.Put "manager", getDNFromNTName(strDomain & "\" & strManager)
	objUser.SetInfo
		
Loop Until objTxt.AtEndOfStream
 
Function getDNFromNTName(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
 getDNFromNTName = Replace(objNT.Get(ADS_NAME_TYPE_1779),"/","\/")
 
End Function

Open in new window

Author

Commented:
Thanks for the code BluntTony.

I have run the script as you guided...

but its says script complete and not doing any changes

any suggestions?
bluntTonyHead of ICT
Top Expert 2009

Commented:
Remove the very first line, and tell me what error you get.
Thanks.

Author

Commented:
please see the attached file
Const ForReading = 1
 
strDomain = "DC=abc,DC=com"
strCSV = "C:\Users\jashua\Desktop\man.csv"
 
Set objFSo = CreateObject("scripting.FileSystemObject")
Set objTxt = objFso.OpenTextFile(strCSV, ForReading)
 
Do
        arrData = Split(objTxt.ReadLine,",")
        strSamAccountName = arrData(0)
        strManager = arrData(1)
        
        Set objUser = GetObject("LDAP://" & getDNFromNTName(strDomain & "\" & strSamAccountName))
        objUser.Put "manager", getDNFromNTName(strDomain & "\" & strManager)
        objUser.SetInfo
                
Loop Until objTxt.AtEndOfStream
 
Function getDNFromNTName(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
 getDNFromNTName = Replace(objNT.Get(ADS_NAME_TYPE_1779),"/","\/")
 
End Function

Open in new window

error.jpg

Author

Commented:
i have managed to resolve the above problem

now i am getting the following error message


error2.jpg
bluntTonyHead of ICT
Top Expert 2009

Commented:
This means that a name you are providing is not found in AD
strDomain in the script should be the NetBIOS name of your domain, i.e. the DOMAIN bit in DOMAIN\username (not the DNS name).  The names you are passing in should be sAMAccountName, i.e. the username bit in DOMAIN\username.
Have you changed strDomain in your script to match the name of your domain?

Author

Commented:
I have checked the name and its all fine...exactly the same

and also i have changed the strDomain which is fine as welll
bluntTonyHead of ICT
Top Expert 2009

Commented:
ARe you using sAMAccountName for the manager, ie. jurjess as opposed to CN=jurjess,OU=etc...etc?
With this script you can use sAMAccountNAme for both. Can you give an example line from your CSV?

Author

Commented:
I am just using like this as csv file

Adam H,Jurjess
bluntTonyHead of ICT
Top Expert 2009

Commented:
And both Adam H and jurjess are the pre-200 login names as shown in the 'Account' tab for the two users in ADUC?

Are you running this script as an admin?

Author

Commented:
that is true.

i am Domain admin and also enterprise admin of my domain

still  getting the same error message :(
Ciprian LozonschiOps Team Office 365/Messaging

Commented:
Hello, use next script, should do the work you want
Format of excel file is the one from picture, both fileds (sAMAccountName, manager) are sAMAccountName of the user, this can be changed

'On Error Resume Next
 
strExcelSheet = "C:\Users\Administrator\Desktop\managers.xlsx"
Set objExcel = CreateObject("Excel.Application")
Set objSpread = objExcel.Workbooks.Open(strExcelSheet)
 
intRow = 2 'Row 1 often contains headings
Do Until objExcel.Cells(intRow,1).Value = ""
	strsAMAccountName = objExcel.Cells(intRow,1).Value
	'WScript.Echo "strsAMAccountName: " & strsAMAccountName
	strmanager = objExcel.Cells(intRow,2).Value
	'WScript.Echo "strmanager: " & strmanager
 
	Set objRSsAMAccountName = CreateObject("adodb.recordset")
	Connstring = "Provider=ADsDSOObject"
	strSQLsAMAccountName = "SELECT distinguishedName,sAMAccountName,manager FROM 'LDAP://dc=labdomain,dc=com' WHERE objectCategory='user' AND sAMAccountName='" & strsAMAccountName & "'"
	objRSsAMAccountName.Open strSQLsAMAccountName, Connstring
	If Not objRSsAMAccountName.EOF And Not objRSsAMAccountName.BOF Then
		fullPathsAMAccountName = objRSsAMAccountName("distinguishedName")
		WScript.Echo "dn sAMAccountName: " & objRSsAMAccountName("distinguishedName")
	Else
		WScript.Echo "dn sAMAccountName ERROR"
	End If
	
	Set objRSmanager = CreateObject("adodb.recordset")
	Connstring = "Provider=ADsDSOObject"
	strSQLmanager = "SELECT distinguishedName,sAMAccountName,manager FROM 'LDAP://dc=labdomain,dc=com' WHERE objectCategory='user' AND sAMAccountName='" & strmanager & "'"
	objRSmanager.Open strSQLmanager, Connstring
	If Not objRSmanager.EOF And Not objRSmanager.BOF Then
		fullPathmanager = objRSmanager("distinguishedName")
		WScript.Echo "dn manager: " & objRSmanager("distinguishedName")
	Else
		WScript.Echo "dn manager ERROR"
	End If
 
	Set objUser = GetObject("LDAP://" & fullPathsAMAccountName)
	'WScript.Echo "SAM Account Name: " & objUser.sAMAccountName
	objUser.Put "manager", fullPathmanager
	objUser.SetInfo
 
	objRSsAMAccountName.Close
	Set objRSsAMAccountName = Nothing
	objRSmanager.Close
	Set objRSmanager = Nothing
 
	intRow = intRow + 1
	WScript.Echo ""
Loop
 
objExcel.Quit
WScript.Quit

Open in new window

Excel.jpg
bluntTonyHead of ICT
Top Expert 2009

Commented:
To be honest I'm not sure why the NameTranslate is not working. If the names are correct and you're an admin then it should work (you're running it from an XP/2003 machine right?)
You can use ADO instead of NameTranslate, as below. You don't need to worry about the domain name in this one, just change the file path to you CSV file (line 2)
Tony

Const ForReading = 1
strCSV = "C:\users.csv"
 
Set objFSo = CreateObject("scripting.FileSystemObject")
Set objTxt = objFso.OpenTextFile(strCSV, ForReading)
Set objConn = CreateObject("ADODB.Connection")
Set objComm =   CreateObject("ADODB.Command")
Set oRootDSE = GetObject("LDAP://RootDSE")
 
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"
 
Do
	arrData = Split(objTxt.ReadLine,",")
	strSamAccountName = arrData(0)
	strManager = arrData(1)
	
	Set objUser = GetObject("LDAP://" & getDNFromNTName(strSamAccountName))
	WScript.Echo "Updating User : " & strSamAccountName & " with manager : " & strManager
	objUser.Put "manager", getDNFromNTName(strManager)
	objUser.SetInfo
		
Loop Until objTxt.AtEndOfStream
 
Set oRootDSE = Nothing
Set objConn = Nothing
Set objComm = Nothing
Set objUser = Nothing
 
'End of script
 
Function getDNFromNTName(strNTName)
strFilter = "(&(objectclass=user)(objectCategory=person)(sAMAccountName=" & strNTName &"));" 
objComm.CommandText = strBase & strFilter & strAttrs & strScope
Set objRS = objComm.Execute
If objRs.RecordCount = 1 Then 
	objRS.MoveFirst
	getDNFromNTName = Replace(objRS.Fields("distinguishedName").Value,"/","\/")
End If
End Function

Open in new window

Author

Commented:
that's nice.

do i have to change the samaccountname to firstinitial.surname?

because in my AD usernames are firstname surname

Author

Commented:
Hi BluntTony,

I have run this script and its runs fine but it runs only for first 21 users in the list ...

i thought it mught be a disabled user the next one so i removed that users but it still stops after 21 users and gives me unspecified error on line 25.

Any Suggestions?
Ciprian LozonschiOps Team Office 365/Messaging
Commented:
Hi jurjess,
You can put on manager column, the names that are fit to your needs, this was just one example, but also the query maybe needs to be changed.
Post here how you have the excel (just 2,3 rows and the header) and will see
Head of ICT
Top Expert 2009
Commented:
Have you checked that 22nd line is correct?
Any spaces/carriage returns? I've changed the script to remove any spaces, but you may have to check for returns.
Tony

Const ForReading = 1
strCSV = "C:\users.csv"
 
Set objFSo = CreateObject("scripting.FileSystemObject")
Set objTxt = objFso.OpenTextFile(strCSV, ForReading)
Set objConn = CreateObject("ADODB.Connection")
Set objComm =   CreateObject("ADODB.Command")
Set oRootDSE = GetObject("LDAP://RootDSE")
 
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"
 
Do
	arrData = Split(objTxt.ReadLine,",")
	strSamAccountName = Trim(arrData(0))
	strManager = Trim(arrData(1))
	
	Set objUser = GetObject("LDAP://" & getDNFromNTName(strSamAccountName))
	WScript.Echo "Updating User : " & strSamAccountName & " with manager : " & strManager
	objUser.Put "manager", getDNFromNTName(strManager)
	objUser.SetInfo
		
Loop Until objTxt.AtEndOfStream
 
Set oRootDSE = Nothing
Set objConn = Nothing
Set objComm = Nothing
Set objUser = Nothing
 
'End of script
 
Function getDNFromNTName(strNTName)
strFilter = "(&(objectclass=user)(objectCategory=person)(sAMAccountName=" & strNTName &"));" 
objComm.CommandText = strBase & strFilter & strAttrs & strScope
Set objRS = objComm.Execute
If objRs.RecordCount = 1 Then 
	objRS.MoveFirst
	getDNFromNTName = Replace(objRS.Fields("distinguishedName").Value,"/","\/")
End If
End Function

Open in new window

Author

Commented:
really helpful

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial