Querying AD for Department membership for 1500 user script

Posted on 2007-10-05
Last Modified: 2012-05-05
I have a file with names / First , Last / and I need to get everybodys department from AD. What the easiest way to automate this?
Question by:amnhtech
    LVL 11

    Expert Comment


    Save this file as vbs and run
    You will get all users from the ad with department.

    I got this from one of the posts in EE.

    Option Explicit


    Dim objConnection, objCommand, objRecordSet, objRootDSE, objFileSystem, objFile, objUser
    Dim strDisplayName, strDepartment, strUserName

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

    Set objCommand = CreateObject("ADODB.Command")
    objCommand.ActiveConnection = objConnection

    Set objRootDSE = GetObject("LDAP://RootDSE")
    objCommand.CommandText = "SELECT aDSPath, displayName, sAMAccountName, department FROM " &_
          "'LDAP://" & objRootDSE.Get("defaultNamingContext") & "' WHERE objectClass='user'"

    objCommand.Properties("Page Size") = 1000
    objCommand.Properties("Timeout") = 600
    objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
    objCommand.Properties("Cache Results") = False

    Set objRecordSet = objCommand.Execute
    Set objRootDSE = Nothing

    Set objFileSystem = CreateObject("Scripting.FileSystemObject")
    Set objFile = objFileSystem.OpenTextFile("UsersAndDepartments.txt", 2, True, 0)

    While Not objRecordSet.EOF
          Set objUser = GetObject(objRecordSet.Fields("aDSPath"))
          If objUser.Class = "user" Then
                strDisplayName = ""
                If Not IsNull(objRecordSet.Fields("displayName")) Then
                      strDisplayName = objRecordSet.Fields("displayName")
                End If
                strDepartment = ""
                If Not IsNull(objRecordSet.Fields("department")) Then
                      strDepartment = objRecordSet.Fields("department")
                End If
                strUserName = objRecordSet.Fields("sAMAccountName")
                objFile.WriteLine strDisplayName & VbTab & strUserName & VbTab & strDepartment
          End If

    Set objFile = Nothing
    Set objFileSystem = Nothing

    Set objRecordSet = Nothing
    Set objCommand = Nothing
    Set objConnection = Nothing
    LVL 2

    Author Comment

    actually my goal here is to input names from a file and find out their department membership
    LVL 11

    Expert Comment

    Sorry i dont know of a way as such.You can still get all data put in excel sort the data and match with your list...

    May be some other expert may help with this post...
    LVL 26

    Accepted Solution

    :: ===============
    :: ===============
    :: * This script require "UsersInfo.txt" file on from where it will pick user first name and last name.
    :: * 'UsersInfo.txt' should be in comma separated file.
    ::      Like:  
    ::             FirstName,LastName
    ::             Farhan,Kazi
    :: * Successful run will generate "UserDeptInfo.txt" file
    :: * Copy and paste following script in notepad and save it with any name having .cmd extension.
    :: *** SCRIPT START ***
    SETLOCAL EnableDelayedExpansion

    IF NOT EXIST UsersInfo.txt Goto ShowErr
    FOR %%R IN (UsersInfo.txt) Do IF %%~zR EQU 0 Goto ShowErr
    IF EXIST UserDeptInfo.txt DEL /F /Q UserDeptInfo.txt

    FOR /F "Tokens=1-2 delims=," %%a IN ('TYPE UsersInfo.txt') DO (
          ECHO Querying: %%a %%b
          DSQuery * -Filter "(&(samAccountType=805306368)(givenName=%%a)(sn=%%b))" |FIND /I "CN=" >NUL
          IF NOT ERRORLEVEL 1 (
                SET FN= &SET LN= &SET DT=
                SET /A Counter=1
                FOR /F "tokens=2 delims=:" %%u IN ('DSQuery * -Filter "(&(samAccountType=805306368)(givenName="%%a")(sn="%%b"))" -Attr givenName sn department -L') DO (
                      IF "!Counter!"=="1" SET FN=%%u
                      IF "!Counter!"=="2" SET LN=%%u
                      IF "!Counter!"=="3" SET DT=%%u
                      SET /A Counter+=1)
                ECHO !FN:~1! !LN:~1! ^(!DT:~1!^) >>UserDeptInfo.txt
          )ELSE (ECHO ** ERROR ** %%a %%b: user not found.))

    ECHO. &ECHO Script complete check 'UserDeptInfo.txt' file. &ECHO.
    Goto EndScript
    Echo 'UsersInfo.txt' file does not exist or file is empty!
    :: *** SCRIPT END ***
    LVL 2

    Author Comment

    Thank you very much for the script it looks like there is a small problem taht i can't figure out. Here is the errors i am getting

    Querying:  John Smith

    dsquery failed:No superior reference has been configured for the directory servi
    ce. The directory service is therefore unable to issue referrals to objects outs
    ide this forest.

    Please advise
    LVL 26

    Expert Comment

    On what OS you are running this script?

    Can please run following commands (one by one) and post the results?

    Click Start -> Run -> Cmd.exe

    DSQuery * -Filter "(&(samAccountType=805306368)(givenName=John)(sn=Smith))"

    DSQuery * DomainRoot -Filter "(&(samAccountType=805306368)(givenName=John)(sn=Smith))"

    DSQuery * ForestRoot -Filter "(&(samAccountType=805306368)(givenName=John)(sn=Smith))"

    DSQuery user -name John*

    DSQuery user DomainRoot -name *

    DSQuery user ForestRoot -name *
    LVL 2

    Author Comment

    acctually i figured out i had a problem with cvs file there were a space infront the names. Thank you very much.
    LVL 26

    Expert Comment

    Great! Thanks for the points.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Ever wondered how to display how many visitors you have online. In this tutorial I will show you an easy but effective way to display the number of online visitors in WhizBase. In this article I assume you have read my previous articles and know …
    This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
    The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
    This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

    761 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

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now