Solved

VBScript to create a list of all users in AD and in what OU

Posted on 2006-11-15
10
1,051 Views
Last Modified: 2012-05-05
Hi!

I'm looking for a VBScript that export a list of all user accounts in an AD to a texfile. The list should include "User Logon Name", "First name", Last name" and in what OU it resides.
0
Comment
Question by:Stefan_Soderquist
  • 5
  • 4
10 Comments
 
LVL 67

Expert Comment

by:sirbounty
ID: 17946374
This should be what you're after...

'UserReport.vbs

Dim objFSO, strFileName, RptFile

Set objFSO = CreateObject("Scripting.FileSystemObject")
strFileName="C:\Output.txt"
Set RptFile = objFSO.CreateTextFile(strFileName)

Dim objCon, objCmd, objRoot, strDomain
Dim strFilter, strQuery, objRS
On Error Resume Next

Set objCon = CreateObject("ADODB.Connection")
Set objCmd = CreateObject("ADODB.Command")

objCon.Provider = "ADsDSOOBject"
objCon.Open "Active Directory Provider"

Set objCmd.ActiveConnection = objCon
Set objRoot = GetObject("LDAP://RootDSE")

'Get domain
strDomain = objRoot.Get("defaultNamingContext")
strBase = "<LDAP://" & strDomain & ">"
RptFile.WriteLine "Users in " & Replace(Replace(strDomain, "DC=",""), ",",".")
RptFile.WriteLine "============================="
RptFile.WriteLine

'Define the filter elements
strFilter = "(&(objectCategory=person)(objectClass=user))"

'List all attributes you will require
strAttributes = "distinguishedName,sAMAccountName,givenName,sn,userPrincipalName"

'compose query
strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"
With objCmd
  .CommandText = strQuery
  .Properties("Page Size") = 99999
  .Properties("Timeout") = 300
  .Properties("Cache Results") = False
Set objRS = .Execute
End With

With objRS
  .MoveFirst

  Do Until .EOF
     strData="DN: " & .Fields("distinguishedName") & vbCrLf & "GN: " & .Fields("givenName") & vbCrLf & "SN: " & .Fields("sn") & vbCrLf & _
                  "SAM: " & .Fields("sAMAccountName") & vbCrLf & "UPN: " & .Fields("userPrincipalName")
     RptFile.WriteLine strData
     RptFile.WriteLine
'Remove the next two lines (and this one) to display full report)
     x=x+1
     If x>=10 then wscript.quit
    .MoveNext
  Loop
  RptFile.Close
End With

' Clean up.
objCon.Close
Set objCon = Nothing
Set objCmd = Nothing
Set objRoot = Nothing
Set objRS = Nothing
Set RptFile = Nothing
Set objFSO = Nothing
0
 

Author Comment

by:Stefan_Soderquist
ID: 17946533
Hi Sirbounty!

I think i wasn't clear about the output and formatting on the textfile.
I need a textfile that list all users (one line per user with logon name, first name and last name) and is grouped by OU so I can later import it to Excel to make a report that shows the user accounts grouped by OU.

Let me show you what I mean;

OU1

Login name1, First name, Last name
Login name2, First name, Last name


OU2

Login name3, First name, Last name
Login name4, First name, Last name

and so on
0
 
LVL 67

Expert Comment

by:sirbounty
ID: 17947494
Hmm - that complicates things then.
I can see two immediate methods for this, one doable since I have some model code, the other maybe/maybe not - would work in theory and take some testing.

The first would appear in the format:

[OU Name]
User Login, gn, sn

Would that suffice?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Stefan_Soderquist
ID: 17949622
The first method would be perfect!
0
 
LVL 67

Expert Comment

by:sirbounty
ID: 17949646
Haha - too late, I considered it a challenge to enumerate all the OUs...just about done.  Will post something shortly...
0
 
LVL 67

Expert Comment

by:sirbounty
ID: 17949720
One last question...do you want to know what's being done, while it's being done, or would you rather just stare at an empty screen until it's complete?

Iow - we can have it state:
Processing...please wait (until it's done)

or I can iterate each OU as it's being queried along the way, so that you know the current status (I don't know how large your AD is - mine this would be a benefit with over 40k users!)
0
 

Author Comment

by:Stefan_Soderquist
ID: 17949812
If it isn't to much job for you, include the progress bar to indicate which OU the script is processing. Otherwise it's just OK with a simple progress bar
0
 
LVL 67

Accepted Solution

by:
sirbounty earned 500 total points
ID: 17949984
Here ya go...

Dim objFSO, strFileName, RptFile

Set objFSO = CreateObject("Scripting.FileSystemObject")
strFileName="C:\Output.csv"
Set RptFile = objFSO.CreateTextFile(strFileName)

Dim objRoot, objDomain
Set objRoot = GetObject("LDAP://RootDSE")
Set objDomain = GetObject("LDAP://" & objRoot.Get("defaultNamingContext"))

EnumOUs(objDomain.ADsPath)
RptFile.Close
Set RptFile=Nothing
Set objUsers=Nothing
Set objOUs=Nothing
Set objDomain=Nothing
Set objRoot=Nothing
Set objFSO=Nothing
wscript.quit

Sub EnumOUs(adspath)
    Dim objOUs, OU
    Set objOUs = GetObject(adspath)
    objOUs.Filter = Array("OrganizationalUnit")
    Call EnumUsers(objOUs.ADsPath)
    For Each OU In objOUs
        wscript.echo "Processing " & Replace(OU.Name, "OU=", "")
        RptFile.WriteLine Replace(Ucase(OU.Name), "OU=", "")
        Call EnumOUs(OU.ADsPath)
    Next
End Sub

Sub EnumUsers(adspath)
    Dim objUsers, User
    Set objUsers = GetObject(adspath)
    objUsers.Filter = Array("User")
    RptFile.WriteLine
    For Each User In objUsers
        If User.sn <> "" and User.givenName <> "" Then
            RptFile.WriteLine User.sAMAccountName & ", " & User.givenName & ", " & User.sn
        End If
    Next
    RptFile.WriteLine
End Sub
0
 

Author Comment

by:Stefan_Soderquist
ID: 17959049
The script works like I first wanted. Later I realised when I executed the script, I needed a path added to the OU because i have multiple OU with the same name and I needed a way to differentiate them. I found out that if I changed one line in the Sub EnumOUs section;

RptFile.WriteLine Replace(Ucase(OU.Name), "OU=", "")

to

RptFile.WriteLine Replace(Ucase(OU.distinguishedName), "OU=", "")

it solved my last problem

Many thanks to sirbounty for the fast response and a speedy solution
0
 
LVL 23

Expert Comment

by:gecko_au2003
ID: 17959069
handy info as well, thanks :)
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

Question has a verified solution.

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

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

763 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