How Can I Get a List of All the Groups Whose Name Starts with G-S-Group?
Hey, Scripting Guy! How can I use wildcards to get a list of all the groups whose name starts with G-S-Group?
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.
Note. You want to know how hard it is to be a Scripting Guy? Well, let’s see. This particular Scripting Guy writes a column that simply refers people to previously-written material. And then he celebrates Thanksgiving by making his mom do all the cooking. A very tough life indeed.
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:
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:
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.