We help IT Professionals succeed at work.

Query AD usernames with script

Medium Priority
1,265 Views
Last Modified: 2010-04-21
Hey Experts!  I would like to run a query on my AD network to retrieve the username for all my users.  Since we merged a couple of years ago, some users have user.name and others have username.  I would like to dump a list of the user accounts to aid the helpdesk.  We did this when we merged but I don't have the script any longer.

Can this be done with a batch file?
Comment
Watch Question

Author

Commented:
To be honest, I am all servers and networks.  I am just now getting into scripting and what I do know is batch files.  Can you help with the vbs script you posted a link to?
yep, just create a file called getusers.vbs
and paste in the snippet that follows, inserting your own domain dn.

finally, run it at a command prompt like this:

cscript getusers.vbs

this will write out all the users to your window
' This VBScript code finds all user accounts in a domain.
 
' ---------------------------------------------------------------
' Provided as a web-only addition for the book:
'     "Active Directory Cookbook" by Robbie Allen
' ISBN: 0-596-00466-4
' ---------------------------------------------------------------
 
' ------ SCRIPT CONFIGURATION ------
strDomainDN = "<DomainDN>"    ' e.g. dc=rallencorp,dc=com
' ------ END CONFIGURATION ---------
 
strBase   =  "<LDAP://" & strDomainDN & ">;"
' To search the whole forest using the global catalog, uncomment the following line:
' strBase   =  "<GC://" & strDomainDN & ">;"
 
strFilter = "(&(objectclass=user)(objectcategory=person));" 
strAttrs  = "name,sAMAccountName;"
strScope  = "subtree"
 
set objConn = CreateObject("ADODB.Connection")
objConn.Provider = "ADsDSOObject"
objConn.Open "Active Directory Provider"
set objRS = objConn.Execute(strBase & strFilter & strAttrs & strScope)
objRS.MoveFirst
while Not objRS.EOF
    
    Wscript.Echo objRS.Fields("name").Value & " "& objRS.Fields("sAMAccountName").Value
    objRS.MoveNext
wend

Open in new window

Author

Commented:
Ahhh.....  I get you.  Thanks for the explanantion.  Can I dump it to a text file?
yep,  the filesystemobject in vbs can do this

'add to top
const ForAppending = 8,   ForReading = 1,   ForWriting = 2
Set objFSO = CreateObject("Scripting.FileSystemObject") 
Set objTextFile = objFSO.OpenTextFile _
("users.txt", ForWriting, True)
....
....
'amend existing code
while Not objRS.EOF
    objTextFile.WriteLine(objRS.Fields("name").Value & " "& objRS.Fields("sAMAccountName").Value)
    objRS.MoveNext
wend
objTextFile.Close

Open in new window

CERTIFIED EXPERT

Commented:
# something like:
ldifde (username=*)
ldifde (user.name=*)

Commented:
Use this vbs file which i got from an expert in EE>..
Save as .vbs

Dim ObjWb
Dim ObjExcel
Dim x, zz

Set objRoot = GetObject("LDAP://RootDSE")
strDNC = objRoot.Get("DefaultNamingContext")
Set objDomain = GetObject("LDAP://" & strDNC) '' Bind to the top of the Domain using LDAP using ROotDSE

Call ExcelSetup("Sheet1") '' Sub to make Excel Document

x = 1
Call enummembers(objDomain)


Sub enumMembers(objDomain)
      On Error Resume Next
      Dim Secondary(20) '' Variable to store the Array of 2ndary email alias''s

    For Each objMember In objDomain '' go through the collection
          
            If ObjMember.Class = "user" Then '' if not User object, move on.
            x = x +1 '' counter used to increment the cells in Excel
            
        objwb.Cells(x, 1).Value = objMember.Class
        '' I set AD properties to variables so if needed you could do Null checks or add if/then''s to this code
        '' this was done so the script could be modified easier.
            SamAccountName = ObjMember.samAccountName
            Cn = ObjMember.CN
            FirstName = objMember.GivenName
            LastName = objMember.sn
            initials = objMember.initials
            Descrip = objMember.description
            Office = objMember.physicalDeliveryOfficeName
            Telephone = objMember.telephonenumber
            Email = objMember.mail
            WebPage = objMember.wwwHomePage
            Addr1 = objMember.streetAddress
            City = objMember.l
            State = objMember.st
            ZipCode = objMember.postalCode
            Title = ObjMember.Title
            Department = objMember.Department
            Company = objMember.Company
            Manager = ObjMember.Manager
            Profile = objMember.profilePath
            LoginScript = objMember.scriptpath
            HomeDirectory = ObjMember.HomeDirectory
            HomeDrive = ObjMember.homeDrive
            AdsPath = Objmember.Adspath
             LastLogin = objMember.LastLogin
            
            zz = 1 '' Counter for array of 2ndary email addresses
            For each email in ObjMember.proxyAddresses
                   If Left (email,5) = "SMTP:" Then
                  Primary = Mid (email,6) '' if SMTP is all caps, then it''s the Primary
                   ElseIf Left (email,5) = "smtp:" Then
            Secondary(zz) = Mid (email,6) '' load the list of 2ndary SMTP emails into Array.
            zz = zz + 1
                   End If
            Next

            '' Write the values to Excel, using the X counter to increment the rows.
             
            objwb.Cells(x, 2).Value = SamAccountName
            objwb.Cells(x, 3).Value = CN
            objwb.Cells(x, 4).Value = FirstName
            objwb.Cells(x, 5).Value = LastName
            objwb.Cells(x, 6).Value = Initials
            objwb.Cells(x, 7).Value = Descrip
            objwb.Cells(x, 8).Value = Office
            objwb.Cells(x, 9).Value = Telephone
            objwb.Cells(x, 10).Value = Email
            objwb.Cells(x, 11).Value = WebPage
            objwb.Cells(x, 12).Value = Addr1
            objwb.Cells(x, 13).Value = City
            objwb.Cells(x, 14).Value = State
            objwb.Cells(x, 15).Value = ZipCode
            objwb.Cells(x, 16).Value = Title
            objwb.Cells(x, 17).Value = Department
            objwb.Cells(x, 18).Value = Company
            objwb.Cells(x, 19).Value = Manager
            objwb.Cells(x, 20).Value = Profile
            objwb.Cells(x, 21).Value = LoginScript
            objwb.Cells(x, 22).Value = HomeDirectory
            objwb.Cells(x, 23).Value = HomeDrive
            objwb.Cells(x, 24).Value = Adspath
            objwb.Cells(x, 25).Value = LastLogin
            objwb.Cells(x,26).Value = Primary
            
            '' Write out the Array for the 2ndary email addresses.
            For ll = 1 To 20
                  objwb.Cells(x,26+ll).Value = Secondary(ll)
            Next

            '' Blank out Variables in case the next object doesn''t have a value for the property
            SamAccountName = "-"
            Cn = "-"
            FirstName = "-"
            LastName = "-"
            initials = "-"
            Descrip = "-"
            Office = "-"
            Telephone = "-"
            Email = "-"
            WebPage = "-"
            Addr1 = "-"
            City = "-"
            State = "-"
            ZipCode = "-"
            Title = "-"
            Department = "-"
            Company = "-"
            Manager = "-"
            Profile = "-"
            LoginScript = "-"
            HomeDirectory = "-"
            HomeDrive = "-"
            Primary = "-"
            For ll = 1 To 20
                  Secondary(ll) = ""
            Next
        End If
       
        '' If the AD enumeration runs into an OU object, call the Sub again to itinerate
       
        If objMember.Class = "organizationalUnit" or OBjMember.Class = "container" Then
            enumMembers (objMember)
        End If
    Next
End Sub

Sub ExcelSetup(shtName) '' This sub creates an Excel worksheet and adds Column heads to the 1st row
      Set objExcel = CreateObject("Excel.Application")
    Set objwb = objExcel.Workbooks.Add
    Set objwb = objExcel.ActiveWorkbook.Worksheets(shtName)
      Objwb.Name = "Active Directory Users" '' name the sheet
    objwb.Activate
      objExcel.Visible = True
      objwb.Cells(1, 2).Value = "SamAccountName"
    objwb.Cells(1, 3).Value = "CN"
    objwb.Cells(1, 4).Value = "FirstName"
      objwb.Cells(1, 5).Value = "LastName"
      objwb.Cells(1, 6).Value = "Initials"
      objwb.Cells(1, 7).Value = "Descrip"
      objwb.Cells(1, 8).Value = "Office"
      objwb.Cells(1, 9).Value = "Telephone"
      objwb.Cells(1, 10).Value = "Email"
      objwb.Cells(1, 11).Value = "WebPage"
      objwb.Cells(1, 12).Value = "Addr1"
      objwb.Cells(1, 13).Value = "City"
      objwb.Cells(1, 14).Value = "State"
      objwb.Cells(1, 15).Value = "ZipCode"
      objwb.Cells(1, 16).Value = "Title"
      objwb.Cells(1, 17).Value = "Department"
      objwb.Cells(1, 18).Value = "Company"
      objwb.Cells(1, 19).Value = "Manager"
      objwb.Cells(1, 20).Value = "Profile"
      objwb.Cells(1, 21).Value = "LoginScript"
      objwb.Cells(1, 22).Value = "HomeDirectory"
      objwb.Cells(1, 23).Value = "HomeDrive"
      objwb.Cells(1, 24).Value = "Adspath"
      objwb.Cells(1, 25).Value = "LastLogin"
      objwb.Cells(1, 26).Value = "Primary SMTP"
End Sub

MsgBox "Done" '' show that script is complete


You get all the users and the details...

Author

Commented:
bsharath- Thanks for the post.  From a beginner's stand point, dang that looks confusing.  Does it specify somewhere in there how to dump the results to a text file??

ahoffmann- I'm not sure what you mean in your post.  Can you explain further?

the_crazed- I am testing your suggestion now.  I have no idea how to combine the two parts.  Please verify I have the code correct:
' This VBScript code finds all user accounts in a domain.
 
' ---------------------------------------------------------------
' Provided as a web-only addition for the book:
'     "Active Directory Cookbook" by Robbie Allen
' ISBN: 0-596-00466-4
' ---------------------------------------------------------------
 
' ------ SCRIPT CONFIGURATION ------
strDomainDN = "<DomainDN>"    ' e.g. dc=rallencorp,dc=com
' ------ END CONFIGURATION ---------
 
strBase   =  "<LDAP://" & strDomainDN & ">;"
' To search the whole forest using the global catalog, uncomment the following line:
' strBase   =  "<GC://" & strDomainDN & ">;"
 
'add to top
const ForAppending = 8,   ForReading = 1,   ForWriting = 2
Set objFSO = CreateObject("Scripting.FileSystemObject") 
Set objTextFile = objFSO.OpenTextFile _
("users.txt", ForWriting, True)
....
....
'amend existing code
while Not objRS.EOF
    objTextFile.WriteLine(objRS.Fields("name").Value & " "& objRS.Fields("sAMAccountName").Value)
    objRS.MoveNext
wend
objTextFile.Close
 
strFilter = "(&(objectclass=user)(objectcategory=person));" 
strAttrs  = "name,sAMAccountName;"
strScope  = "subtree"
 
set objConn = CreateObject("ADODB.Connection")
objConn.Provider = "ADsDSOObject"
objConn.Open "Active Directory Provider"
set objRS = objConn.Execute(strBase & strFilter & strAttrs & strScope)
objRS.MoveFirst
while Not objRS.EOF
    
    Wscript.Echo objRS.Fields("name").Value & " "& objRS.Fields("sAMAccountName").Value
    objRS.MoveNext
wend

Open in new window

CERTIFIED EXPERT

Commented:
> I'm not sure what you mean in your post.  Can you explain further?
hmm, in your question you say:
> .. run a query on my AD network

assuming that you mean a windoze Active Directory by "AD network" I'd simply use windoze'
own command line tools to query it, ldifde.exe is such a tool

Author

Commented:
Man I asked a noob question.  Trying to do too much at once and mis-read that.  Thanks for the explanation and minimizing the sarcasim.  : )

Author

Commented:
Ok, I searched google and found more about ldifde.  Where do I get the exe from?  Would you provide the command I would use?  This is what I found:


ldifde -f C:\ldif\ExportUsers.ldf s SERVERNAME -d "OU=YourOUname,dc=YourDomainName,dc=com" -p subtree -r "(objectClass=User)" -l "cn,givenName,Title,SamAccountName"

Open in new window

Commented:
Did you try the script...It that work?

Author

Commented:
No, to be honest, i wasn't sure if there was an output file generated and if so, where it was located.

Commented:
Just save the data into a vbs file and run the file.It opens a excel file with all data recovered from the ADS on the screen

Author

Commented:
Right on!  Doing it now.
yep, that was right, only remove the two lines:

....
....

I only meant those to represent "existing code here"
sorry, should be
' This VBScript code finds all user accounts in a domain.
 
' ---------------------------------------------------------------
' Provided as a web-only addition for the book:
'     "Active Directory Cookbook" by Robbie Allen
' ISBN: 0-596-00466-4
' ---------------------------------------------------------------
 
' ------ SCRIPT CONFIGURATION ------
strDomainDN = "<DomainDN>"    ' e.g. dc=rallencorp,dc=com
' ------ END CONFIGURATION ---------
 
strBase   =  "<LDAP://" & strDomainDN & ">;"
' To search the whole forest using the global catalog, uncomment the following line:
' strBase   =  "<GC://" & strDomainDN & ">;"
 
'add to top
const ForAppending = 8,   ForReading = 1,   ForWriting = 2
Set objFSO = CreateObject("Scripting.FileSystemObject") 
Set objTextFile = objFSO.OpenTextFile _
("users.txt", ForWriting, True)
 
 
strFilter = "(&(objectclass=user)(objectcategory=person));" 
strAttrs  = "name,sAMAccountName;"
strScope  = "subtree"
 
set objConn = CreateObject("ADODB.Connection")
objConn.Provider = "ADsDSOObject"
objConn.Open "Active Directory Provider"
set objRS = objConn.Execute(strBase & strFilter & strAttrs & strScope)
objRS.MoveFirst
while Not objRS.EOF
    objTextFile.WriteLine(objRS.Fields("name").Value & " "& objRS.Fields("sAMAccountName").Value)
    objRS.MoveNext
wend
objTextFile.Close

Open in new window

Author

Commented:
the_crazed- I get an error when I run this (any ideas?):
Line: 31
Char: 1
Error: Table does not exist
Code: 80040E37
Source: Provider

Open in new window

Author

Commented:
bsharath- stupid question #2.  Where do I specify the domain I want to query?
CERTIFIED EXPERT

Commented:
@bsharath, why did post code to generate an excel sheet? Is that a requirement in the question?

Author

Commented:
ahoffmann-  Do you know where i can get the exe file you mentiond?  As for the question to bsharath, I needed the results dumped into text format.  Not sure why excel was used.  
CERTIFIED EXPERT

Commented:
ldifde.exe is part of the server tools, not sure if the former reskit contains it too.
may be following contains it (otherwise you need to check your server CDs):
http://download.microsoft.com/download/3/e/4/3e438f5e-24ef-4637-abd1-981341d349c7/WindowsServer2003-KB892777-SupportTools-x86-ENU.exe

Commented:
Hi,

This script is not done by me and as you know i have no knowledge of scripting..
I just wanted to share what i had...
I though that it would not be difficult deleting the colums and coping the data to a txt file.If thats what the asker wanted...

Author

Commented:
After searching numerous sites, it's located in =>  %systemroot%\system32

I am testing it now!

Author

Commented:
ahoffman, have you used this before?  I wanted to see if you had any examples before I begin a query on the 30+ domains in the state's forest.
CERTIFIED EXPERT
Commented:
yes, but currently have no acces to windoze
it's very similar to the ages old ldapsearch available on reliable systems, so you should be able to query you AD for records with the attributes you're interested in (see my simple, but not perfect and incomplete example in http:#20766088 ).
You need to add the options for the AD server, the database, the base, username and password. Sorry cant help there, but a simple
   ldifde.exe /?
or
   ldifde.exe -h
should give the proper information.

If you get the desired results, then post some parts here and we can try to feed it to another tool to strip it down to what you need in the end.

Author

Commented:
ahoffmann- thanks for hanging in there.  to my surprise, there is a built-in tool that allows you to export any data you could ever want.  after trying to figure out the syntax for that command, I found an article on "queries" in AD.  in any event, I was able to get it working and I have the data I need.

I am awarding all the points to ahoffmann.  the other scripts that were offered up required some "tweaking" and I am not a scripter and who ever posted the scripts didn't help 'tweak" them.  I appreciate the posts but I needed a little more help.  

Thanks again everyone.  Take care.

-Aaron


Author

Commented:
just to clarify, there is a built-in tool in ADUC called "saved queries" that I used.  

Author

Commented:
Thanks again for the help and your time.  Take care!
CERTIFIED EXPERT

Commented:
glad to have helped with my simple and short (as usual) suggestions

Explore More ContentExplore courses, solutions, and other research materials related to this topic.