[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1253
  • Last Modified:

Query AD usernames with script

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?
0
samiam41
Asked:
samiam41
  • 15
  • 6
  • 5
  • +1
1 Solution
 
the_crazedCommented:
0
 
samiam41Author 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?
0
 
the_crazedCommented:
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

0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
samiam41Author Commented:
Ahhh.....  I get you.  Thanks for the explanantion.  Can I dump it to a text file?
0
 
the_crazedCommented:
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

0
 
ahoffmannCommented:
# something like:
ldifde (username=*)
ldifde (user.name=*)
0
 
bsharathCommented:
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...
0
 
samiam41Author 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

0
 
ahoffmannCommented:
> 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
0
 
samiam41Author 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.  : )
0
 
samiam41Author 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

0
 
bsharathCommented:
Did you try the script...It that work?
0
 
samiam41Author Commented:
No, to be honest, i wasn't sure if there was an output file generated and if so, where it was located.
0
 
bsharathCommented:
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
0
 
samiam41Author Commented:
Right on!  Doing it now.
0
 
the_crazedCommented:
yep, that was right, only remove the two lines:

....
....

I only meant those to represent "existing code here"
0
 
the_crazedCommented:
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

0
 
samiam41Author 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

0
 
samiam41Author Commented:
bsharath- stupid question #2.  Where do I specify the domain I want to query?
0
 
ahoffmannCommented:
@bsharath, why did post code to generate an excel sheet? Is that a requirement in the question?
0
 
samiam41Author 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.  
0
 
ahoffmannCommented:
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
0
 
bsharathCommented:
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...

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

I am testing it now!
0
 
samiam41Author 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.
0
 
ahoffmannCommented:
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.
0
 
samiam41Author 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


0
 
samiam41Author Commented:
just to clarify, there is a built-in tool in ADUC called "saved queries" that I used.  
0
 
samiam41Author Commented:
Thanks again for the help and your time.  Take care!
0
 
ahoffmannCommented:
glad to have helped with my simple and short (as usual) suggestions
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

  • 15
  • 6
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now