• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 484
  • Last Modified:

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.
0
tanskie
Asked:
tanskie
  • 3
  • 3
1 Solution
 
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
tanskieAuthor Commented:
great! thanks a lot
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now