Hey, Scripting Guy! How Can Retrieve a List of All My Computer Accounts, Grouped By OU?
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?
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:
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.