Script to modify Job Title and Description attribute in AD from Excel

Premkumar Yogeswaran
Premkumar Yogeswaran used Ask the Experts™
on
Hi,
Need t script to modify multiple users Title and Description in AD from a Excel.

Excel Format:
Name                                     Title
Last Name,First Name           Sales Manger
Mark,Neon Gagne                  Sr. Analyst

Format of the excel is given above.

Need a script to find the name (Name Column From Excel) and update the title from the excel to Title & Description in Active Directory
Also that script should produce the error log. In case of any error occured.

Thanking you in advance
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Chris DentPowerShell Developer
Top Expert 2010

Commented:

Always a bit of a problem matching on criteria like that, first name and last name are not unique.

Does the script have to account for the possibility that it might encounter more than one match for a name?

Chris
Premkumar YogeswaranSr. Analyst - System Administrator

Author

Commented:
Hi Chris,
Thanks for the reply..

If it matces more than one name it should log a error in a file. And it should not update the user account in AD.

Thanks,
Prem
Chris DentPowerShell Developer
Top Expert 2010

Commented:

One more then... how big is AD and how many users are in the sheet?

Just need to know if a single query for everything is more efficient than queries for each entry in the sheet.

Chris
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Hey,
here is the script you need....the complicated thing is to search for the users according to their surnames, givennames, because you have to do a rather complicated ADO search for the regarding LDAP objects in your AD. We need a filter to search for the surname and givenname for that.

You can see how to handle the Excel data in this script, and you can see how to search for AD users.... Please note that there is NO runtime error handling in case of having no accesss permissions are similar things.... but it should be a good example to show you the way... :)

The follogin website contains all the information you need:

LDAP Searches:
http://www.selfadsi.org/search.htm

LDAP Attributes for AD users (to see what attribute are "surname", "givenname", "title"...
http://www.selfadsi.org/user-attributes-w2k3.htm

Setting the value "title":
http://www.selfadsi.org/write.htm

Ok, here is the script:




'___________________ [set you variables  here !! ]
'LDAP domain path. Example: domain name "yourdomain,com" is this:
baseStr = "DC=yourdomain,DC=com"
'Path to the Excel File
excelFile = "c:\test\test.xlsx"

'___________________ [open excel worksheet]
Set xlo = CreateObject("Excel.Application")
xlo.Visible = True

Set xlw = xlo.Workbooks.Open(excelFile)
Set xls = xlw.Sheets(1)

'___________________ [read the names and search the regarding users in the AD]
i = 1
name = xls.Cells(i,1)
title = xls.Cells(i,2)

While (Len(name) > 0)

      If (Len(title) > 0) AndAlso (InStr(name, ",") > 0) Then            'do it only for complete lines
            surName = Split(name, ",")(0)                              'build the LDAP filter for the user search
            givenName = Split(name, ",")(1)
            ldapFilter = "(&(sn=" & surName & ")(givenName=" & givenName & "))"
            
            WScript.Echo "___________________________"
            WScript.Echo "Searching user account for " & name
            
            Set ado = CreateObject("ADODB.Connection")     'create new ADO connection
            ado.Provider = "ADSDSOObject"                  'use the ADSI interface
            ado.Properties("Encrypt Password") = True
            ado.Open "ADS-Search"                          'use any name for the connection
      
            Set adoCmd = CreateObject("ADODB.Command")     'create new ADO command
            adoCmd.ActiveConnection = ado                  'assignment to an existing ADO connection
            adoCmd.CommandText = "<LDAP://" & baseStr & ">;" & ldapFilter & ";ADsPath;subtree"
      
            Set objectList = adoCmd.Execute                         'perform search
            

            If (Not objectList.EOF) then
                  userDN = objectList.Fields("ADsPath")
                  WScript.Echo "Found: " & userDN
                  WScript.Echo "Setting Title attribute..."
                  
                        Set user = GetObject(userDN)              'connect to the user which was found
                  user.title = title
                  user.SetInfo

                        WScript.Echo "Done"
            End If
      End If

      'get the next name in the excel sheet
      i = i + 1
      name = xls.Cells(i,1)
      title = xls.Cells(i,2)
Wend

'___________________ [close excel]
xlw.Close
xlo.Quit
Set xlo=Nothing

Premkumar YogeswaranSr. Analyst - System Administrator

Author

Commented:
550 Users title and description need to be updated.

AD Consist of nearly 7000 Users...!

-Prem
Chris DentPowerShell Developer
Top Expert 2010

Commented:

Okay, I have some code that'll happily deal with this kind of update on that size directory. Let me just prod it into shape.

Chris
Sorry i forgot the error handling for double finding of "surname, givenname":
Just insert somthing like this

.....if (objectList.RecordCount > 1) then.....

AND PLEASE: Run this script with "cscript.exe  THISSCRIPT.VBS" in a CMD.EXE command shell! Other wise you will get a lot of popup message box dialogs... :)

So the new version:

'___________________ [set you variables  here !! ]
'LDAP domain path. Example: domain name "yourdomain,com" is this:
baseStr = "DC=yourdomain,DC=com"
'Path to the Excel File
excelFile = "c:\test\test.xlsx"

'___________________ [open excel worksheet]
Set xlo = CreateObject("Excel.Application")
xlo.Visible = True

Set xlw = xlo.Workbooks.Open(excelFile)
Set xls = xlw.Sheets(1)

'___________________ [read the names and search the regarding users in the AD]
i = 1
name = xls.Cells(i,1)
title = xls.Cells(i,2)

While (Len(name) > 0)

      If (Len(title) > 0) AndAlso (InStr(name, ",") > 0) Then            'do it only for complete lines
            surName = Split(name, ",")(0)                              'build the LDAP filter for the user search
            givenName = Split(name, ",")(1)
            ldapFilter = "(&(sn=" & surName & ")(givenName=" & givenName & "))"
           
            WScript.Echo "___________________________"
            WScript.Echo "Searching user account for " & name
           
            Set ado = CreateObject("ADODB.Connection")     'create new ADO connection
            ado.Provider = "ADSDSOObject"                  'use the ADSI interface
            ado.Properties("Encrypt Password") = True
            ado.Open "ADS-Search"                          'use any name for the connection
     
            Set adoCmd = CreateObject("ADODB.Command")     'create new ADO command
            adoCmd.ActiveConnection = ado                  'assignment to an existing ADO connection
            adoCmd.CommandText = "<LDAP://" & baseStr & ">;" & ldapFilter & ";ADsPath;subtree"
     
            Set objectList = adoCmd.Execute                         'perform search
           
            if (objectList.RecordCount = 1) then
                  userDN = objectList.Fields("ADsPath")
                  WScript.Echo "Found: " & userDN
                  WScript.Echo "Setting Title attribute..."
                 
                  Set user = GetObject(userDN)              'connect to the user which was found
                  user.title = title
                  user.SetInfo

                   WScript.Echo "Done"
            ElseIf (objectList.RecordCount > 1) then     'double found
                   WScript.Echo "Double found!!"
                   While (Not objectList.EOF)
                            WScript.Echo objectList.Fields("ADsPath")
                            objectList.MoveNext
                   WEnd
            Else 'no user found
                   WScript.Echo "Not found!!"
            End If
      End If

      'get the next name in the excel sheet
      i = i + 1
      name = xls.Cells(i,1)
      title = xls.Cells(i,2)
Wend

'___________________ [close excel]
xlw.Close
xlo.Quit
Set xlo=Nothing


Chris DentPowerShell Developer
Top Expert 2010

Commented:

Or you can use that one ;)

Chris
Sorry Chris, i didn't realize that you were working on that also, i wrote the script without looking at the forum posts... ;)
Chris DentPowerShell Developer
Top Expert 2010

Commented:

PFoeckeler, I see you just joined us, welcome to EE :)

Anyway, it's no bother, I'm quite happy as long as whatever is posted works (and your code is rather neat and tidy) :)

I'll help again in the unlikely event that you get stuck or it suffers from feature creep.

All the best,

Chris
Premkumar YogeswaranSr. Analyst - System Administrator

Author

Commented:
Hi Friends,
Thanks for your support. I will check for the script and let you know...!

PFoeckeler - Welcome to E-E.
Commented:
Here is a good tool & its free.
http://www.wisesoft.co.uk/
Premkumar YogeswaranSr. Analyst - System Administrator

Author

Commented:
Hi All,
Bulk AD User is tool worked great suggested by Awinish...!

Good Awinish...!

I would also award PFoeckeler for his effort.

Thank you all...!
Premkumar YogeswaranSr. Analyst - System Administrator

Author

Commented:
Tool Works great

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