Hey, Scripting Guy! How can I use wildcards to get a list of all the groups whose name starts with G-S-Group?

— JD

Hey, JD. It’s the day before Thanksgiving, and one of the Scripting Guys is headed out of town as soon as he finishes his Hey, Scripting Guy! column. And what a coincidence: he happened to pick a question about searching Active Directory, the kind of column he could write in his sleep (and probably has).

The reason Active Directory search columns are so easy to write is the fact that we cheat: we typically don’t explain the details behind such searches. Active Directory searches are actually pretty easy; after all, most of the code is boilerplate that doesn’t need to change from script-to-script. However, there is a considerable amount of boilerplate code, and that requires explanation that goes beyond the scope of this little column. Therefore, we typically refer people to our two-part Tales from the Script series Dude, Where’s My Printer? if they need more information about writing scripts to search Active Directory.

But we won’t be totally lazy; at least we’ll show you the code and explain how the query works. Here’s a script that returns all the groups whose Name starts with G-S-Group:

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://DC=fabrikam,DC=com' WHERE objectCategory='group' " & _
        "AND Name='G-S-Group*'"

Set objRecordSet = objCommand.Execute

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

The part we’re interested in is the SQL query that returns just those groups that meet our criteria (names starting with G-S-Group):

"SELECT Name FROM 'LDAP://DC=fabrikam,DC=com' WHERE objectCategory='group' " & _
        "AND Name='G-S-Group*'"

As you can see, we need to specify two things in the Where clause: 1) the objectCategory must be equal to group (that way, we make sure we get back only groups and not users, OUs, printers, or anything else found in Active Directory); and 2) the Name attribute must begin with the letters G-S-Group. To specify the latter, we set the search value of Name to the desired string – G-S-Group – followed by an asterisk (*), the asterisk being short for “anything.” What we’re saying here is pretty straightforward: “Show me all the groups whose name starts with G-S-Group; I don’t care what – if anything – comes after that.”

You can’t get much more straightforward than that.

If we wanted to search for groups whose Name ended in G-S-Group we’d put the asterisk before the string:

Name='*G-S-Group'

That’s read as, “Show me all the groups whose name ends with G-S-Group; I don’t care what – if anything – comes before that.” Or we could search for groups that have the string G-S-Group somewhere (anywhere) in their name. To do that, we put asterisks before and after the string:

Name='*G-S-Group*'

All part of the fun – and the utility – of using scripts to search Active Directory.

As for us, there’s a turkey out there somewhere with our name on it. See you all again on Monday.