How Can I Return a List of All My Computers Except Those in a Specified OU?

ScriptingGuy1

Hey, Scripting Guy! I need to get back a list of all the computers in my domain, except for the computers which are in our Test Lab OU. How do I do that?

— AM

Hey, AM. This seems to be one of the hot new trends in the IT world: in the past two weeks we’ve gotten three similar emails, all involving administrators who want to exclude a particular OU when listing all the computers in their domain. In this case, it appears you’re not interested in computers housed in your test lab; in the other two cases, we had an admin who wanted to exclude developer machines, and an admin who didn’t want domain controllers showing up in the list. Regardless, the basic idea is the same: you want a list of all your computers except for those one specific OU.

If you’re an avid reader of Hey, Scripting Guy! (and who isn’t?) then you know that 9 times out of 10 our answer to a question is, “Search Active Directory.” That’s going to be our answer today as well, although we must confess that we’re not 100% satisfied with that answer (you’ll see why in a moment). At first glance, you might think this would be an easy search to carry out in Active Directory; after all, you’d have to do nothing more than include a WHERE clause that tells the script to skip any computers found in the Test Lab OU. In other words:

objCommand.CommandText = _
    “SELECT Name FROM ‘LDAP://dc=fabrikam,dc=com’ WHERE OU <> ‘TestLab OU’ AND ” _
        & “objectCategory=’computer'”

This is a great solution, except for one problem: Active Directory computer accounts don’t have a property named OU; that means you can’t use the OU in a WHERE clause. So much for Plan A.

Of course, you might then think, “Hey, what about the Distinguished Name (DN)?” After all, the DN for a computer (e.g., CN=atl-ws-01,OU=Test Lab OU,DC=fabrikam,DC=com) contains the name of the OU where the computer account resides. Couldn’t we just do a wildcard search on the Distinguished Name, something similar to this:

objCommand.CommandText = _
    “SELECT Name FROM ‘LDAP://dc=fabrikam,dc=com’ WHERE distinguishedName <> ”  _
        ” ‘*OU=Finance’ AND objectCategory=’computer'”

Alas, this won’t work, either. As it turns out, the DN is a “constructed” attribute. The DN isn’t actually stored in Active Directory, it’s constructed on-the-fly any time you request it. Thus you can’t do a wildcard search on the distinguishedName attribute. And don’t bother asking about the ADsPath attribute, which also contains the OU name; you can’t do a wildcard search on the ADsPath, either.

In fact, as far as we know, the only way to do this is to conduct a search that returns a list of all your computers. When you get that list back, you’re probably going to do something with it (for now, we’ll assume you just want to echo the computer names to the screen). Before anything is done to a computer in the list, however, our script will check the DN to see if the target string (for example, OU=Test Lab OU) can be found. If it is, the script will simple skip that computer and go on to the machine in the list. If the target string isn’t found, then the script will echo the computer name.

Maybe this will make more sense if you see the actual 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 Name, distinguishedName FROM ‘LDAP://dc=fabrikam,dc=com’ ” _
        & “WHERE objectCategory=’computer'”  
Set objRecordSet = objCommand.Execute
objRecordSet.MoveFirst
Do Until objRecordSet.EOF
    intTestOU = InStr(objRecordSet.Fields(“distinguishedName”).Value, “OU=Test Lab OU”)
    If intTestOU = 0 Then
        Wscript.Echo objRecordSet.Fields(“Name”).Value
    End If
    objRecordSet.MoveNext
Loop

The lines of code we care about are these:

intTestOU = InStr(objRecordSet.Fields(“distinguishedName”).Value, “OU=Test Lab OU”)
If intTestOU = 0 Then
    Wscript.Echo objRecordSet.Fields(“Name”).Value
End If

What we’re doing here is using the InStr function to see if the string OU=TestLab OU can be found anywhere within the computer’s Distinguished Name. If the string is found, then intTestOU gets set to the character position where the string begins. For example, in the DN CN=atl-ws-01,OU=Test Lab OU,DC=fabrikam,DC=com, intTestOu would be set to 14, because our target phrase begins at the 14^th letter in the string.

But what if intTestOU equals zero? That can only mean one thing: the target phrase wasn’t found. In turn, that must mean this particular computer is not in the Test Lab OU, so we go ahead and echo the computer name. Thus we end up echoing the names of all the computers except those in the Test Lab OU.

Like we said, this approach does the job, although it lacks a certain elegance. But, hey, style isn’t everything, right?

Alternatively, you might consider storing OU names in an unused computer account attribute; for example, you could record the OU name in the description attribute. That way, you could search on that attribute, using a query similar to this:

objCommand.CommandText = _
    “SELECT Name FROM ‘LDAP://dc=fabrikam,dc=com’ WHERE objectCategory=’computer’ ” & _
        “AND description<>’Test Lab OU'”

This works great; you’ll just have to remember to change the value of the description attribute should you ever move the computer to a new OU

ScriptingGuy1

Follow