Hey, Scripting Guy! How Can Retrieve a List of All My Computer Accounts, Grouped By OU?

Hey, Scripting Guy! How Can Retrieve a List of All My Computer Accounts, Grouped By OU?

  • Comments 7
  • Likes
Hey, Scripting Guy! Question

Hey, Scripting Guy! How can I get a list of all the computers in my domain, grouping those computers by the OU in which the computer account resides?

-- TA

SpacerHey, Scripting Guy! AnswerScript Center

Hey, TA. Well, today is Monday, and the Scripting Guy who writes this column is actually glad it’s Monday. Not that he’s all that excited about coming back to work, mind you; it’s just that last week was such an absolutely miserable week that he’s been waiting for a chance to start all over again and see if this week goes any better.

So how bad was it last week? Let’s put it this way: on Friday, the day that kicked off the second week of Spring, it snowed all day – hard. Some of that snow even stuck, which might not be all that surprising considering the fact that the temperature at the time was a whole 34 degrees Fahrenheit. The truth is, unless you live at the North Pole or the top of Mt. Everest you don’t expect to see blizzard-like conditions on March 28th. Nor do you expect the temperature at 2:00 PM to be 34 degrees Fahrenheit.

Note. For the record, the average high in Seattle on March 28th is 56 degrees Fahrenheit. In 1994 the thermometer peaked at – sigh – 70 degrees on March 28th.

So was Friday the worst day in Seattle history? Well, with the possible exception of the day the Mariners traded Randy Johnson – who went on to win the next four Cy Young Awards as the best pitcher in the National League – yes, definitely. Granted, back in February, 1916 21.5 inches of snow fell on Seattle in a single 24-hour period. However, a) that was in early February, when you might expect a little snow; and, b) people were a lot tougher back in 1916 than they are today. Would we modern-day Seattleites survive if 21.5 inches of snow fell in a 24-hour period? Are you kidding? Half of us would probably die if 21.5 inches of snow fell during a 24-year period!

Anyway, this is a new day and a new week, and hopefully things will go a little better this week than they did last week. With that in mind, let’s see what we can do about writing a script that can retrieve a list of all the computers in a domain, grouping those computers by organizational unit.

Interestingly enough, this is a trickier problem than you might expect it to be. At first glance you might think, “Well, that should be easy enough: I just get back a list of all the computers and the OUs they belong to, then sort that list by OU.” That’s actually a pretty good idea, except for one thing: there is no Active Directory property that tells you which OU an object resides in. (No really; take a peek at this column for information on the roundabout method you need to use to determine the OU an object belongs to.)

But that’s OK. The logical way to attack this problem is to retrieve a list of all the computers and then determine which OU they belong to. In this case, however, the logical way doesn’t do us any good. Therefore, we need to do things the illogical (i.e., the Scripting Guys) way; we’ll get back a list of all the OUs, and then determine which computer accounts can be found in each OU.

In other words, we’re going to use the following script:

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 ADsPath FROM 'LDAP://dc=fabrikam,dc=com' WHERE " & _
        "objectCategory='organizationalUnit'"  

Set objRecordSet = objCommand.Execute

objRecordSet.MoveFirst

Do Until objRecordSet.EOF
    Set objOU = GetObject(objRecordSet.Fields("ADsPath").Value)
    Wscript.Echo objOU.distinguishedName

    objOU.Filter = Array("Computer")
    
    For Each objItem in objOU
        Wscript.Echo "  " & objItem.CN
    Next

    Wscript.Echo
    Wscript.Echo
    objRecordSet.MoveNext
Loop

As you can see, the first half of this script involves a search of Active Directory. Per our usual policy, we won’t discuss Active Directory searching in any great detail today; you can find more information about that in our two-part Tales From the Script series Dude, Where’s My Printer? (You’ll find Part 1 of that series right here, and Part 2 of the series over here.) We will, however, take just a second to look at the query required to conduct our search:

objCommand.CommandText = _
    "SELECT ADsPath FROM 'LDAP://dc=fabrikam,dc=com' WHERE " & _
        "objectCategory='organizationalUnit'"

Actually there’s nothing especially fancy about this query; we’re simply asking the script to search the fabrikam.com domain and return the ADsPath attribute for each object that has an objectCategory equal to organizationalUnit. And yes, you’re way ahead of us: anything with an objectCategory equal to organizationalUnit is going to be, well, an organizational unit.

After we execute the query we’ll get back a recordset consisting of all the OUs in that domain; what we have to do now is try to figure out which computers reside in each of those OUs. To do that, we set up a Do Until loop that runs until we’ve looped through each and every OU. (Or, more technically, until the recordset’s EOF – end-of-file – property is True.)

In order to retrieve a list of all the computers that belong to a given OU we need to first bind to that OU; that’s something we can do by using the ADsPath attribute, the very same attribute we requested when configuring our search query:

Set objOU = GetObject(objRecordSet.Fields("ADsPath").Value)

After we make the connection we echo back the value of the OU’s distinguishedName attribute; that’s going to be something similar to this:

OU=Administration,DC=fabrikam,DC=com

By default connecting to an OU brings back a collection of all the objects contained in that OU; that means we can echo back a list of those objects simply by setting up a For Each loop similar to this one:

For Each objItem in objOU
    Wscript.Echo "  " & objItem.CN
Next

However, we don’t want to do that just yet. Why not? Because that would list all the objects in the OU, and we aren’t interested in all the objects; we’re interested in only the computer accounts. Therefore, before we do anything else we set a Filter that restricts the returned data to computer objects:

objOU.Filter = Array("Computer")

Now we can execute our For Each loop, indenting two spaces (for aesthetic purposes) and then echoing back the value of each computer’s CN attribute. That’s going to result in output similar to this:

OU=Administration,DC=fabrikam,DC=com
  atl-ws-001
  atl-ws-002
  atl-ws-003
  atl-ws-004

Which is exactly the kind of output we wanted.

After we’ve dispensed with the first OU in the recordset we use a pair of Wscript.Echo statements to insert a couple of blank lines in our output, then call the MoveNext method to move on to the next record in the recordset. From there we simply repeat the process with the next OU.

Like we said, this approach might seem a little backwards. In the end, though, it works: we wind up with a list of each OU in the domain as well as a list of all the computer accounts residing in each of those OUs.

In case you’re wondering, it wasn’t just the weather that bedeviled the Scripting Guy who writes this column last week. Friday afternoon he went to the bathroom, and when he came out he, well, discovered that he couldn’t come out; that’s because there was a crew working on the lights or something and they had parked several big dollies full of equipment right in front of the bathroom door. For a brief moment it looked like the Scripting Guy who writes this column might have to spend the rest of the day in the bathroom.

Which, considering the way the rest of the week went, might not have been all that bad.

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • How do you output this to a text file?

  • Alternatively, you could say "If object-type = computer, then list in the report."

    I'm not sure of the syntax, but I know it is possible, even without the array thingy.

    more food for thought.

    tnjman.

  • HI,

      This is  Great , Can i know  how  to take this output  to a  TEXT / CSV ?. Also can  we run this script in a workstation without domain admin rights ?

  • This works great!!  One problem I am having is when I use this to connect to our DMZ domain and am getting a "Read-only property value not set".  

    I have been playing around with a property setting, but can't seem to get it.....

    objConnection.Properties("Read-only") = ADS_READONLY_SERVER

  • Once again, fantastic. Exactly what I needed, when I needed it.

  • Please inject in the command on how to export the output to a .txt, .csv, or.xls file. I have too many objects and pop-up window is not applicable. Appreciate your inputs please. Thanks in advance.

  • thanks