VB Script to generate ldap value

Hi,

anyone knows how to create a script that generate a specific ldap attribute to a list of users in an excel file?

for example, I have a a list of users on a single excel file with the following columns:

User ID | Last Name | First Name | Middle Name | Object OU Location

I want to generate the Object OU location column.

Is this possible?

Thanks.
tanskieAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

prashanthdCommented:
Following code can be customized for your requirement

Const ADS_SCOPE_SUBTREE = 2

Set objConnection = CreateObject("ADODB.Connection")
Set objCommand =   CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection

objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE

objCommand.CommandText = _
    "SELECT distinguishedName FROM 'LDAP://dc=fabrikam,dc=com' “ & _
        "WHERE objectCategory='user' " & _
            "AND sAMAccountName='kenmyer'"
Set objRecordSet = objCommand.Execute

objRecordSet.MoveFirst
Do Until objRecordSet.EOF
    strDN = objRecordSet.Fields("distinguishedName").Value
    arrPath = Split(strDN, ",")
    intLength = Len(arrPath(1))
    intNameLength = intLength - 3
    Wscript.Echo Right(arrPath(1), intNameLength)
    objRecordSet.MoveNext
Loop

0
Leon FesterSenior Solutions ArchitectCommented:
My favourite tool for this is csvde with direct output in CSV format.
csvde -f test.csv -r "(&(objectCategory=person)(objectClass=user))" -l "givenname, initials, sn ,displayname, canonicalname"
Add the  -d command to specific a specific OU to target or leave it out to trawl throught the whole AD Database.

e.g.  -d "cn=user,dc=contoso,dc=com"

Another options is DSQUERY, output is in not as excel friendly and will need some conversion afterwards.
dsquery * -filter "(&(objectCategory=person)(objectClass=user))" -attr givenname initials sn displayname canonicalname -limit 0 -uc >users.txt
0
tanskieAuthor Commented:
@prashanthd: how do we convert it to read from an excel list of users?
I have already the list of users which belong to different OUs and i want to generate a separate column on the excel sheet that shows the OU path of each user
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

tanskieAuthor Commented:
or does anyone know how to modify below script to read from a list of users on an excel file and output it to a different column on the same excel sheet?



    WScript.Echo GetOU("shmpam")


' brings back the concatenated OU path for the user
' path is concatenated according to it's hierarchy in AD.
'  This is necessary because the same OU name can exist in different levels in the AD hierarchy
Function GetOU( strUserID )
      Set objConnection = CreateObject("ADODB.Connection")
      Set objCommand =   CreateObject("ADODB.Command")
      objConnection.Provider = "ADsDSOObject"
      objConnection.Open "Active Directory Provider"
      Set objCommand.ActiveConnection = objConnection

      objCommand.Properties("Page Size") = 1000
      objCommand.Properties("Searchscope") = 2

      objCommand.CommandText = "SELECT adsPath FROM 'LDAP://dc=asp,dc=compukid,dc=com' WHERE objectCategory='user' AND sAMAccountName='" & strUserID  & "'"
      Set objRecordSet = objCommand.Execute

      objRecordSet.MoveFirst

      Do Until objRecordSet.EOF
                strPath = objRecordSet.Fields("adsPath").Value
                arrPath = Split(strPath, ",")
                strOU = ""
                for each a in arrPath
                  if left(a,2) = "OU" Then
                        strOU = "\" & right(a,len(a) - 3) & strOU
                  End If
                Next
                objRecordSet.MoveNext
      Loop
      GetOU = strOU

End Function
0
prashanthdCommented:
Try the following..
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open _
    ("C:\Scripts\users.xls")


Const ADS_SCOPE_SUBTREE = 2

Set objConnection = CreateObject("ADODB.Connection")
Set objCommand =   CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection

objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE 

intRow = 2

Do Until objExcel.Cells(intRow,1).Value = ""

struser=trim(objExcel.Cells(intRow, 1).Value)

objCommand.CommandText = _
    "SELECT distinguishedName FROM 'LDAP://dc=fabrikam,dc=com' “ & _
        "WHERE objectCategory='user' " & _
            "AND sAMAccountName='"& struser &"'"
Set objRecordSet = objCommand.Execute

if not objRecordSet.EOF then
    strDN = objRecordSet.Fields("distinguishedName").Value
    arrPath = Split(strDN, ",")
    intLength = Len(arrPath(1))
    intNameLength = intLength - 3
    strou= Right(arrPath(1), intNameLength)
    wscript.echo struser &";"strou
    objSheet.Cells(intRow, 5).Value = strou
end if
	
    intRow = intRow + 1
Loop

objExcel.Quit

Open in new window

0
prashanthdCommented:
Errors fixed, try the following

Set the path of your excel file on line no 3
Set objWorkbook = objExcel.Workbooks.Open("C:\users.xls")
On Error Resume next

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\users.xls")

Const ADS_SCOPE_SUBTREE = 2

Set objConnection = CreateObject("ADODB.Connection")
Set objCommand =   CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection
Set objRootDSE = GetObject("LDAP://RootDSE")
strDomain = objRootDSE.Get("DefaultNamingContext")

objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE 

intRow = 2

Do Until objExcel.Cells(intRow,1).Value = ""
    
    struser=Trim(objExcel.Cells(intRow, 1).Value)
    WScript.Echo struser
    
    objCommand.CommandText = _
    "SELECT distinguishedName FROM 'LDAP://"& strdomain &"'" & _
    "WHERE objectCategory='user' " & _
    "AND sAMAccountName='"& struser &"'"
    Set objRecordSet = objCommand.Execute
    
    If Not objRecordSet.EOF Then
        strDN = objRecordSet.Fields("distinguishedName").Value
        strou= Mid(strdn,InStr(strdn,",")+1)
        WScript.echo struser &";" & strou
        objExcel.Cells(intRow, 5).Value = strou
    Else
        WScript.echo struser &" - user not found"
    End If
    
    intRow = intRow + 1
Loop

objexcel.Save
objExcel.Quit

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tanskieAuthor Commented:
great! thanks a lot
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.