Hey, Scripting Guy! We’re trying to straighten out our Active Directory, and in order to do so, I’ve been asked to come up with a list of all the groups that have just 1 member or fewer. How can I write a script that will return this information for me?

— DR

Hey, DR. Before we answer your question we thought we’d relate an email we received from a “loyal reader:”

Of course, this is the part of the column where we’re supposed to apologize to anyone who was offended by our comment that “We’re assuming that vegetarians won’t eat poison, although, come to think of it, many vegetarians will eat okra. As near as we can tell, there isn’t a whole heck of a lot of difference between poison and okra.” So are we going to apologize for having offended okra fans around the world? Oh, heavens no; have you ever tasted okra?!? The truth is, the only people in the world who like okra are eggplant lovers, and they like okra only because, if okra didn’t exist, then eggplant would be the world’s worst-tasting vegetable.

Although Brussels sprouts could give eggplant a run for its money.

In other words, we’re unlikely to see eye-to-eye with Loyal Reader when it comes to okra. But we have to admit that LR is spot-on when it comes to liver. Let’s face it: there are certain things mankind was never meant to eat. And liver would have to be near the top of that list, right next to tofu.

Incidentally, we did a quick search of the Internet trying to find out which vegetable truly is the least liked. We didn’t come up with much in the way of scientific (or even pseudo-scientific) studies; however, we did discover that Nebraska youths ages 12-14 rank the following as their least favorite vegetables:

As you can see, okra is nowhere to be found. But, then again, asparagus was chosen as the absolute least favorite vegetable, and everyone knows that asparagus is actually pretty good, especially when stir-fried in sesame oil. And can you really have a list of least favorite vegetables without mentioning cauliflower? Of course not.

At any rate, we appreciate the email, Loyal Reader. And we promise: the next time we’ve got a craving for an East Indian seed pod we’ll give okra another try.

Interestingly enough, Nebraska youths ages 12-14 were also asked to pick their favorite script for determining which Active Directory groups have 1 member or fewer. By a nearly 2-to-1 margin they chose 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, Name FROM 'LDAP://dc=fabrikam,dc=com' WHERE objectCategory='group'" 

Set objRecordSet = objCommand.Execute

objRecordSet.MoveFirst

Do Until objRecordSet.EOF
    Set objGroup = GetObject(objRecordSet.Fields("ADsPath").Value)

    i = 0

    For Each strUser in objGroup.Member
        i = i + 1
        If i > 1 Then
            Exit For
        End If
    Next

    If i <= 1 Then
        Wscript.Echo objRecordSet.Fields("Name").Value & " -- " & i
    End If

    objRecordSet.MoveNext
Loop
As it turns out, determining which groups have 1 or fewer members is about as much fun as eating okra. (Which means it could be all sorts of fun, assuming you like okra.) That’s because Active Directory groups do not have a property that tells you how many members a given group has. Instead, we’re going to have to search Active Directory in order to obtain a list of all our groups; we’ll then need to bind to each group individually, and “manually” count how many members are in each group. If a group has just 1 member or fewer (i.e., 0 members) then we’ll echo back the name of that group and the number of members. It’s a hassle, but it’s still better than trying to choke down fried okra. 
Which the Scripting Guy who writes this column had to do on several occasions.





Note. If you’d like to try fried okra for yourself, here’s a recipe for you: Take one pound of okra and cut diagonally into 1/4 inch slices. Sprinkle with salt and pepper, then dredge in cornmeal. Heat 1/4 inch lard in a heavy skillet, add the okra and fry over moderate heat. When the pieces on the bottom are brown, throw them away and go get a hamburger and fries instead.





We now know the tasks we need to take care of in order to get a list of all the Active Directory groups with no more than one member. To actually carry out those tasks, we begin by – well, we aren’t really going to explain how we begin, other than to say that we need to do a search of Active Directory. That’s too much to explain in our little column here, so, instead, we’ll point you to our two-part Tales From the Script series on searching Active Directory. That will not only tell you everything you need to know to write Active Directory search scripts, but it will also explain what the first 10 or so lines in this particular script are for.
About the only thing that we will go over today is the query we use to return a collection of all the Active Directory groups:
objCommand.CommandText = _
    "SELECT ADsPath, Name FROM 'LDAP://dc=fabrikam,dc=com' WHERE objectCategory='group'"
What we’re doing here is requesting two attributes – ADsPath and Name – from the domain fabrikam.com. Of course, we don’t want to get back this information for all the objects in the domain; we’re interested in only group accounts. Therefore, we tack on the Where clause that limits the returned data to those objects that have an objectCategory equal to group.
After we execute this query, we’ll get back a recordset containing a collection of all the groups in our domain. To determine how many users are in each of these groups we next set up a Do Until loop that runs until the recordset’s EOF (end-of-file) property is True (in other words, the loop continues until we run out of records):
Do Until objRecordSet.EOF
Inside this loop, we grab the value of the ADsPath property for the first group in the recordset, then use this line of code to bind directly to the group in Active Directory:
Set objGroup = GetObject(objRecordSet.Fields("ADsPath").Value)
As we noted earlier, the only way to determine the number of members in a group is to count them. With that in mind, we next set the value of a counter variable named i to 0:
i = 0
Once we’ve done that, we use a For Each loop to loop us through all the members in the group (or, to be a bit more technical, all the items in the group’s Member attribute):
For Each strUser in objGroup.Member
    i = i + 1
    If i > 1 Then
        Exit For
    End If
Next
As you can see, each time we run through the loop we increment the value of i by 1. We then check the see if i is greater than 1. Why? Well, remember, we’re interested in finding only groups that have 1 member or fewer. If i is equal to 2, that means this group does not have 1 member or fewer. At that point we can stop counting; this isn’t a group we care about. With that in mind, we do stop counting, using the Exit For statement to exit our For Each loop.
And what do we do once we’re out of the loop? (Yes, yes we know: the Scripting Guys should be experts on being out of the loop.) Well, if i is less than or equal to 1 we echo back the value of the Name attribute, as well as the value of i (the number of members in the group, which will either be 1 or 0):
If i <= 1 Then
    Wscript.Echo objRecordSet.Fields("Name").Value & " -- " & i
End If
In other words, if the group has 1 member or fewer we echo back the group name. If the group has more than 1 member then we do nothing at all.
From there we call the MoveNext method to move to the next record in the recordset, then pop back to the top of the Do loop and repeat the process with the next group in the collection. By the time we finish we should have a report that looks something like this:
Finance Managers –– 1
Web Site Managers –– 1
People Who Like Okra -- 0

That’s all you have to do, DR.
Before we go we should note, in all fairness, that LR is far from alone: turns out that a lot of people like okra. In fact, Roy Blount, Jr. (a reasonably-famous writer) even wrote a song about the vegetable, a song that includes the following verse:
Okra’s green, 
Goes down with ease. 
Forget cuisine
Say “Okra , please.” You can have strip pokra, 
Give me a nice girl and a dish of okra. 
OK, it’s not necessarily poetry. But, then again, no one has ever written a song about determining whether any Active Directory groups have just 1 member or fewer. 
Which is probably just as well.