How Can I Get a List of All the Users in an OU and Its Sub-OUs?

How Can I Get a List of All the Users in an OU and Its Sub-OUs?

  • Comments 5
  • Likes
Hey, Scripting Guy! Question

Hey, Scripting Guy! How can I get a list of all the users in an OU and its sub-OUs?

-- MN

SpacerHey, Scripting Guy! AnswerScript Center

Hey, MN. Thanks to either faulty wiring or a neighbor’s fireworks (the investigation was inconclusive), one of the Scripting Guys had the delightful experience of watching his garage burn down this summer. Because the Scripting Cars were never parked in the garage, this Scripting Guy decided to rebuild the thing as a family room. That’s fine, and, in general, the contractors did a pretty good job. Of course, as a garage, the room originally had a pair of electrical outlets in the ceiling in order to plug in garage door openers. Upon rebuilding the garage as a family room, the contractor left those two electrical outlets exposed, in order to plug in … well, whatever kind of device you normally plug into a ceiling. (Best of all, neither outlet is near a wall. Instead, both are out towards the middle of the room, the very place you want to see electrical cords hanging.)

On the one hand, this Scripting Guy can’t help but look at those two electrical outlets and think, “How could you miss something so obvious?” On the other hand, that same Scripting Guy can look back over 15 months of Hey, Scripting Guy! columns and notice that we’ve never answered this particular question. Good heavens, how could we miss something that obvious?!? Realizing that we’ve never answered this often-asked questions makes it a bit harder to yell at the contractor for not covering over those electrical outlets.

Barring more faulty wiring (or another poorly-aimed firework), our Scripting Guy is stuck with a pair of electrical outlets in his family room ceiling forever and ever. But at least readers of this column no longer have to wonder how to get a list of all the users in an OU and its sub-OUs:

On Error Resume Next

Const ADS_SCOPE_SUBTREE = 2

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

objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE 

objCommand.CommandText = _
    "SELECT Name FROM 'LDAP://ou=finance,dc=fabrikam,dc=com' WHERE objectCategory='user'"  
Set objRecordSet = objCommand.Execute

objRecordSet.MoveFirst
Do Until objRecordSet.EOF
    Wscript.Echo objRecordSet.Fields("Name").Value
    objRecordSet.MoveNext
Loop

As you can see, this is an Active Directory search script. We like using Active Directory search scripts when answering these questions, for two reasons. One, these search scripts are extremely powerful, yet relatively simple to write. And two, they’re just complicated enough that we can’t explain all the ins and outs in this column; instead, if you’re unfamiliar with scripts for searching Active Directory we always encourage you to read our two-part Tales from the Script series Dude, Where’s My Printer?

Note. Why is that a good thing? Well, to be honest, we’re lazy, and this way we don’t have to write very much. But if anyone asks, it’s a good thing because the two-part series does a much better job of discussing Active Directory search scripts than we could possibly do in a daily column. Which is actually true.

The whole secret to searching just an OU and its sub-OUs is picking a starting point. Typically when searching Active Directory you start the search in the root and work your way down through the directory tree:

objCommand.CommandText = _
    "SELECT Name FROM 'LDAP://dc=fabrikam,dc=com' WHERE objectCategory='user'"

Note as well that we also specify the objectCategory as being equal to user. That ensures that we get back only user accounts and not computer accounts, group accounts, or any other objects found in Active Directory.

But what if we want to search only the Finance OU and its sub-OUs? In that case, we simply start the search in the Finance OU:

objCommand.CommandText = _
    "SELECT Name FROM 'LDAP://ou=finance,dc=fabrikam,dc=com' WHERE objectCategory='user'"

It’s that simple. Suppose the Finance OU has a sub-OU named Accounting and we wanted to search just the Accounting OU and its sub-OUs? Okey-doke:

objCommand.CommandText = _
    "SELECT Name FROM 'LDAP://ou=accounting,ou=finance,dc=fabrikam,dc=com' " & _
        "WHERE objectCategory='user'"

Incidentally, the reason we search the OU and its sub-OUs is because we defined a constant named ADS_SCOPE_SUBTREE and set the value to 2. We then used that constant when defining the search scope:

objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE

When the Searchscope property is set to 2, a search will be conducted in a container and all its sub-containers. That’s how we can search all of Active Directory: we simply start in the root, and with Searchscope set to 2 we’ll search not only the root but all its sub-containers (and all their sub-containers). That, of course, will amount to the entire domain. If we wanted to search just an OU (ignoring its sub-OUs) then we would set Searchscope to 1.

Well, that was easy, wasn’t it? Now if we could just figure out what to do with those electrical outlets in the ceiling all our problems would be solved.

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • as a noob,

    i don't know how to run your script, please help.

  • In return for your helpful article, i'll make a suggestion to help with the ceiling socket dilema.

    Install a wireless access point in the ceiling, then box it in. Great if you dont have a POE switch, get it out of the way and you will recieve better signal with it being on the ceiling.

    Ta daa! now everyones happy.

  • @RYO this is a VBScript. YOu will need to copy the code, save it with a .VBS file extension, and then run it from the command prompt like this: cscript yourscriptname.vbs

  • I am very new to scripting. I dont fully understand what all of what the script does. I am trying to do something very similar to that. I want to generate a list of all the users in an OU. In that OU there are 4 Sub-OUs that represent 4 state AR,FL,CA,DC. How do i generate a report that gives me a list of all these users formatting it by city and sends the report by email? Please any help is appreciated

  • thanks