Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Querying AD for Department membership for 1500 user script

Posted on 2007-10-05
8
Medium Priority
?
640 Views
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?
0
Comment
Question by:amnhtech
  • 3
  • 3
  • 2
8 Comments
 
LVL 11

Expert Comment

by:bsharath
ID: 20024161
Hi,

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

Const ADS_SCOPE_SUBTREE = 2

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
      objRecordSet.MoveNext
Wend
objConnection.Close

Set objFile = Nothing
Set objFileSystem = Nothing

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

Author Comment

by:amnhtech
ID: 20024201
actually my goal here is to input names from a file and find out their department membership
0
 
LVL 11

Expert Comment

by:bsharath
ID: 20024257
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...
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LVL 26

Accepted Solution

by:
Farhan Kazi earned 2000 total points
ID: 20025313
:: ===============
:: READ THIS FIRST
:: ===============
:: * 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 ***
@ECHO OFF
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
:ShowErr
Echo 'UsersInfo.txt' file does not exist or file is empty!
:EndScript
ENDLOCAL
:: *** SCRIPT END ***
0
 
LVL 2

Author Comment

by:amnhtech
ID: 20025478
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
0
 
LVL 26

Expert Comment

by:Farhan Kazi
ID: 20025517
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 *
0
 
LVL 2

Author Comment

by:amnhtech
ID: 20025524
acctually i figured out i had a problem with cvs file there were a space infront the names. Thank you very much.
0
 
LVL 26

Expert Comment

by:Farhan Kazi
ID: 20025544
Great! Thanks for the points.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

In this tutorial I will focus on how to use WhizBase as a tool for sending ICQ messages to ICQ. Here I will use a new technology in WhizBase, published in WhizBase 5.1 version. In this tutorial I will use 3 files, pager.wbsp for the processing, e…
In this tutorial I will show you how to make a simple HTML bar chart with the usage of WhizBase, If you want more information about WhizBase please read my previous articles at http://www.experts-exchange.com/ARTH_5123186.html (http://www.experts-ex…
The viewer will learn how to dynamically set the form action using jQuery.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
Suggested Courses

569 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