[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

LDAP Query Help VBScript

Posted on 2005-04-26
14
Medium Priority
?
2,797 Views
Last Modified: 2013-12-03
Hello,

I am pretty new to VBScripting and I want to play around a little bit.  I hope you guys can help me...

1) I want a script which checks an entire OU (EMEA, Main, Spain) and in the given OU it checks each users and outputs the username, full name and tel. number into a CSV file IF the phone number starts with +34.  So far I could do it on a user level however this is not what I want. Also this is a full name and not the userID.  The LDAP query I came up with is: "LDAP://cn=Doe\, John,ou=Spain,OU=Main,OU=EMEA,DC=sub,DC=domain,DC=com"  This does work for that specific user.

2) I want the same script, however then accepting a userID - so for example: cscript findtel.vbs "jdoe1" it should then search in my AD (40.000+ accounts) in all OU's and then again display the output (thus not to file).

So I am looking for 2 scripts on how I can manage this.  I know I am asking for a pre-cooked script, however my intention is to study that on how it works, so comments are appreciated.  Best answer is worth 500 points.

Thanks for your assistance
0
Comment
Question by:DaGo21
  • 8
  • 6
14 Comments
 
LVL 71

Accepted Solution

by:
Chris Dent earned 2000 total points
ID: 13864998
Hi,

I'll post the second script in a little while, but you can have this one to be going on with.

This vbscript returns what you're looking for and outputs it to a CSV file:

Const OUTPUTFILE = "C:\Temp\Report.csv"
Const OVERWRITE = "True"

' Setup the FileSystem Object and create a file to write to

Set objFileSystem = CreateObject("Scripting.FileSystemObject")
Set objFile = objFileSystem.CreateTextFile(OUTPUTFILE, OVERWRITE, false)

' Allows us to grab the domain name for AD

Set objRootDSE = GetObject("LDAP://rootDSE")

' Define and connect to the OU

strOU = "ou=Spain,ou=Main,ou=EMEA" & "," & objRootDSE.Get("defaultNamingContext")

Set objOU = GetObject("LDAP://" & strOU)

' Filter the object so it only contains users

objOU.Filter = Array("user")

On Error Resume Next
For Each objUser in objOU
    strTelephoneNumber = ""
    strTelephoneNumber = objUser.Get("telephoneNumber")
    If Left(strTelephoneNumber, 3) = "+34" Then
        strFirstName = objUser.Get("givenName")
        strInitials = objUser.Get("initials")
        strSurname = objUser.Get("sn")
        strFullName = strFirstName & " " & strInitials & " " & strSurname
        strUserName = objUser.Get("sAMAccountName")
        strOutputLine = strUserName & "," &_
            strFullName & "," & strTelephoneNumber
        objFile.WriteLine strOutputLine
    End If
Next

HTH

Chris
0
 
LVL 71

Expert Comment

by:Chris Dent
ID: 13865032
Just a quick note on the above...

If the Telephone Number in an account is not set then LDAP returns a null value which generates a script stop error. For this reason the line "On Error Resume Next" is included before sorting through the user accounts. While this isn't a particularly good method of error handling on its own it does enough to handle this request. The next version will have better error control because it needs to do much more.

This is also why strTelephoneNumber is set to "" before attempting to get it from the user account again. Since a null value is returned any data held inside the strTelephoneNumber variant is not overwritten and the If statement below it continues to return true even if a telephone number is not defined for the particular user account.

Hope that makes sense.
0
 
LVL 4

Author Comment

by:DaGo21
ID: 13865154
Hi Chris,

Wow, the first script works exactly what I had in mind.  Hope you can work out the second script also.  Thanks for your prompt response!

Cheers
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 4

Author Comment

by:DaGo21
ID: 13865214
Just an other comment...

In some case I get the following results

a) First M. Last (what does the M. do there)
b) Firtst ' Last (the apostrophe)
c) First T Last (the letter T, however no dot - T can me L, J, D or EJM)

And some are just as they had to be... Strange...?
0
 
LVL 71

Expert Comment

by:Chris Dent
ID: 13865253

Okay, here's the second script. It's hopefully quite clear what this one does, but a few notes just in case...

The Option Explicit command means that all variables used must be declared (Dim). This is useful for keeping a script relatively tidy.

The script connects to the root of the domain and recurses through all the child OUs - checking the Users container along the way. Since we're searching for a specific user it currently returns the user properties regardless of the status of the telephone number - this can be changed by adding the "If Left(strTelephoneNumber, 3) = "+34" Then" line in again (remember that End If must also be included). The main reason for this is to ensure the script returns something useful.

The script expects command line input at the moment, but I have added the code to make it take the input from an InputBox instead - the input box is neater although I haven't written any hints of checks on the input.

Finally, the method used to get out of the script isn't particularly neat, and could be rewritten if required.

' A small script to find a user account in AD, Username matching

Option Explicit

' Global Variable Declaration

Dim objRootDSE, objDomainRoot, objArg, objItem
Dim strSearch

'
' Subroutines
'

Sub ProcessUsers(objUsers)

' This routine takes an OU from OURecurse and checks the users to
' see if we find what we're looking for

    Dim objUser
    Dim strUserName, strTelephoneNumber, strFirstName, strInitials
    Dim strSurname, strFullName, strOutputLine

    objUsers.Filter = Array("user")

    For Each objUser in objUsers

        strUserName = objUser.Get("sAMAccountName")
        strUserName = LCase(strUserName)

        If strUserName = strSearch Then
            On Error Resume Next
            strTelephoneNumber = ""
            strTelephoneNumber = objUser.Get("telephoneNumber")
            strFirstName = objUser.Get("givenName")
            strInitials = objUser.Get("initials")
            strSurname = objUser.Get("sn")
            strFullName = strFirstName & " " & strInitials & " " & strSurname
            strOutputLine = strUserName & "," &_
                strFullName & "," & strTelephoneNumber
            wscript.echo strOutputLine

            ' We've found the user and reported it... so may as well
            ' quit the script

            wscript.Quit
        End If
    Next
End Sub


Sub OURecurse(objFirst)

' This OU is responsible for going through the AD Structure

    Dim objOrgUnit, objItem

    Set objOrgUnit = GetObject(objFirst.ADSPath)
    For Each objItem in objOrgUnit
        If (objItem.Class = "organizationalUnit") Then
            ProcessUsers objItem
            OURecurse objItem
        End If
    Next
End Sub


'
' Main Code
'

' Connect to the Root of the Domain

Set objRootDSE = GetObject("LDAP://rootDSE")
Set objDomainRoot = GetObject("LDAP://" & objRootDSE.Get("defaultNamingContext"))

' Read in the UserName we want to look for

For Each objArg in WScript.Arguments
    strSearch = objArg
Next

' If you prefer to read this in from a text box then remove the lines above and use this instead:

' strSearch = InputBox("Enter the Username to search for:")

' Convert the search string to lowercase, all comparisons are case sensitive

strSearch = LCase(strSearch)

' Objects like the Users OU is actually a container so it must be caught with the container check
' Other objects we're interested in are real OUs
' Not recursing through container objects (too messy, User Objects are containers)

For Each objItem in objDomainRoot
     If (objItem.Class = "container") Then
          ProcessUsers objItem
     End If
     If (objItem.Class = "organizationalUnit") Then
          ProcessUsers objItem
          OURecurse objItem
     End If
Next
0
 
LVL 71

Expert Comment

by:Chris Dent
ID: 13865271

> a) First M. Last (what does the M. do there)
> b) Firtst ' Last (the apostrophe)
> c) First T Last (the letter T, however no dot - T can me L, J, D or EJM)

Those are the initials defined in the account, it just reads them straight in, I should have cleared that one out as well actually since it probably suffers the same problem as a telephone number that isn't set.

Changing the user account section of the script like this should get rid of that:

        strFirstName = objUser.Get("givenName")
        strSurname = objUser.Get("sn")
        strFullName = strFirstName & " " & strSurname
        strUserName = objUser.Get("sAMAccountName")
        strOutputLine = strUserName & "," &_
            strFullName & "," & strTelephoneNumber

Chris
0
 
LVL 4

Author Comment

by:DaGo21
ID: 13865569
Hi Chris,

Thanks once more.   I just replaced your orignal code part with the one in your last post, and now the output is a 0Kb file.

Secondly your input script does do something - however should I put a user in with or without quotes?  I used your input box also (without them) but it seems to take awful long, is that correct?  Actually we have several 100 OU's and about 100.000 object - might explain it perhaps?

Cheers
0
 
LVL 71

Expert Comment

by:Chris Dent
ID: 13865657

Since it has to search through each OU, list the users and check each user to see if it matches the search parameter it may well take quite a while to run - it may be possible to write a faster method using the WinNT interface instead of LDAP. I'll check it and post something back - if it works it'll probably be really short.

The search string you should provide is just the username without quotes.

Original Code should be this, it was probably just missing the objFile.WriteLine command:

Const OUTPUTFILE = "C:\Temp\Report.csv"
Const OVERWRITE = "True"

' Setup the FileSystem Object and create a file to write to

Set objFileSystem = CreateObject("Scripting.FileSystemObject")
Set objFile = objFileSystem.CreateTextFile(OUTPUTFILE, OVERWRITE, false)

' Allows us to grab the domain name for AD

Set objRootDSE = GetObject("LDAP://rootDSE")

' Define and connect to the OU

strOU = "ou=Spain,ou=Main,ou=EMEA" & "," & objRootDSE.Get("defaultNamingContext")

Set objOU = GetObject("LDAP://" & strOU)

' Filter the object so it only contains users

objOU.Filter = Array("user")

On Error Resume Next
For Each objUser in objOU
    strTelephoneNumber = ""
    strTelephoneNumber = objUser.Get("telephoneNumber")
    If Left(strTelephoneNumber, 3) = "+34" Then
        strFirstName = objUser.Get("givenName")
        strSurname = objUser.Get("sn")
        strFullName = strFirstName & " " & strSurname
        strUserName = objUser.Get("sAMAccountName")
        strOutputLine = strUserName & "," &_
            strFullName & "," & strTelephoneNumber
        objFile.WriteLine strOutputLine
    End If
Next
0
 
LVL 4

Author Comment

by:DaGo21
ID: 13865881
Your new script works wonderful!  I am curious to your other of course.  Seem to be quite a huge learning curve for me.  The way you wrote your script is not the way I intended to do it myself.  

My script was like:

   Set objUser = GetObject _
     "LDAP://cn=Doe\, John,ou=Spain,OU=Main,OU=EMEA,DC=sub,DC=domain,DC=com"

      Script.Echo "Home Phone: " & objUser.homePhone

     WScript.Echo "Other Home Phone:"
     For Each strValue in objUser.otherHomePhone
        WScript.Echo strValue
     Next

0
 
LVL 71

Expert Comment

by:Chris Dent
ID: 13866364

There's always more than one way to do it. It just depends on how you want to access the information (or more importantly exactly what you want to access).

The really clear advantage of the LDAP interface is that it allows access to considerably more information than the WinNT interface. So while it's possible to check a user exists using the WinNT interface its much more difficult (basically I have no idea how) to request information like the Telephone number.

This is how the WinNT interface could check if a user account exists by searching for it:

' Using the WinNT Service Interface to search for a user account

' strDomain is the NetBIOS name for your domain, we need this one to access the domain
' through the WinNT service interface.

strDomain = "Your-Domain"
Set objDomain = GetObject("WinNT://" & strDomain)

strSearch = InputBox("Enter the Username to search for:")
strSearch = LCase(strSearch)

' booExists indicates whether we found the account or not

booExists = "FALSE"

' Filter out all non-user objects

objDomain.Filter = Array("user")
For Each objUser In objDomain
      If booExists = "FALSE" Then
            strUserName = objUser.Name
            If LCase(strUserName) = strSearch Then
                  strFullName = objUser.FullName
                  strOutputLine = strUserName & "," & strFullName
                  wscript.echo strOutputLine
                  booExists = "TRUE"
            End If
      End If
Next
If booExists = "FALSE" Then
      wscript.echo "Couldn't find user"
End If


Or you can just attempt to connect to the account and see if it's there (catching the error):


' Using the WinNT Service Interface to connect to a user account

strDomain = "Your-Domain"
strSearch = InputBox("Enter the Username to search for:")
strSearch = LCase(strSearch)

On Error Resume Next
Set objUser = GetObject("WinNT://" & strDomain  & "/" & strSearch & ",user")
If Err.Number <> 0 Then
       wscript.echo "Failed to connect to " & strSearch
Else
      strUserName = objUser.Name
      strFullName = objUser.FullName
      strOutputLine = strUserName & "," & strFullName
      wscript.echo strOutputLine
End If


Neither of these methods returns the information you're interested in though, so back to the LDAP interface.

The main difference between the LDAP search you're using and the scripted ones is the objects we're pulling out. Mine pulls the Telephone field from the main page (General Properties) rather than the contents of the Telephones page.

As an example, this could retrieve every value from the Telephone properties page:

strHomePhone = objUser.Get("homePhone")
strPager = objUser.Get("pager")
strMobile = objUser.Get("mobile")
strFax = objUser.Get("facsimileTelephoneNumber")
strIPPhone = objUser.Get("ipPhone")
strNotes = objUser.Get("info")

Chris
0
 
LVL 4

Author Comment

by:DaGo21
ID: 13866861
Great!  Thanks for all the information.  You well deserved the points.
0
 
LVL 71

Expert Comment

by:Chris Dent
ID: 13866913

Glad I could help :)

Chris
0
 
LVL 4

Author Comment

by:DaGo21
ID: 13867210
I will now see if I can check a user with the WinNT (if user does exist and where) and then run the LDAP query.
Well will play tomorrow :)

Cheers
0
 
LVL 71

Expert Comment

by:Chris Dent
ID: 13867293

Unfortunately the ADSPath attribute is relative to the interface you connect with...

So objUser.ADSPath through the WinNT interface is "WinNT://Domain/username". Through the LDAP interface ADSPath returns the full LDAP path including whichever OU or Container the user is in.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to programmatically preset the "Pages per Sheet" option that's available with most printer drivers.   This setting lets you do "n-Up" printing, where two, four, or more pages are printed on each sheet of paper. If your …
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question