Hey, Scripting Guy! How can I return a list of all my Active Directory groups, along with the name of the user responsible for each group?

— PL

Hey, PL. You know, we realize that most of you are thinking, “Wow, this has been a busy month for you Scripting Guys. You guys really deserve to take some time off.” Well, as much as we’d like to – what’s that? No one was thinking that we deserve to take some time off? No one at all? Mom? Dad? Anyone?

Hmmm ….

Well, that’s just as well, because the Scripting Guys can’t afford to take any time off right now anyway. After all, the 2008 Winter Scripting Games are just around the corner (February 15^th through March 3^rd), and we have a lot to do in order to get ready for Scripting Games III. (Like, say, come up with some events for people to participate in.) As you might expect, our plan is to make the 2008 Games even bigger – and even better – than the 2007 version. To that end, we’ve added a whole new division to the Games; this year you can compete (as either a beginning scripter and/or an advanced scripter) in Perl as well as VBScript and Windows PowerShell. (That’s right: Perl.) On top of that, we’ll also have some bonus events designed for those show-offs (you know who you are) who somehow manage to complete all the events in a single day. (Sad but true: even though we were the ones who designed the events in the first place even the Scripting Guys can’t complete all the events in a single day.) And if you’re a member of a user group, well, stay tuned; there’s a good chance we’ll have a special little “Games within the Games” for user groups.

What’s that? Prizes? Of course there will be prizes. (Assuming we’re able to come up with something, that is.) There will be prizes, and there will be Certificates of Excellence awarded to anyone who receives a score of 60 or better in any one division (e.g., Advanced Perl). Needless to say, it should be a great time for everyone.

Well, except maybe for the two Scripting Guys, who have to test and score all those many entries. But for everyone else, it should be a great time.

Make that it will be a great time.

Ah, good question: how are you supposed to remember that the Scripting Games begin February 15th? Well, it’s possible that we’ll mention the fact once or twice before then. Alternatively, Microsoft Outlook users can run this script and set a reminder for Thursday, February 14, 2008:

Const olAppointmentItem = 1
Set objOutlook = CreateObject(“Outlook.Application”)
Set objAppointment = objOutlook.CreateItem(olAppointmentItem)
objAppointment.Start = #2/15/2008 8:00 AM#
objAppointment.AllDayEvent = True
objAppointment.Subject = “2008 Winter Scripting Games”
objAppointment.Body = “The 2008 Winter Scripting Games begin at 8:00 AM Pacific Standard Time. ” & _
    “Go to http://www.microsoft.com/scriptcenter/funzone/games.mspx to begin play. The Games end ” & _
        “on Monday, March 3, 2008.”
objAppointment.Location = “TechNet Script Center”
objAppointment.ReminderMinutesBeforeStart = 1440
objAppointment.ReminderSet = True

objAppointment.Save

And that’s how you return a list of all your Active Directory groups, along with the name of the user responsible for each of those groups. Thank you, and we’ll see you all tomorrow.

Oops; sorry. We got a little ahead of ourselves there, didn’t we? This is how you return a list of all your Active Directory groups, along with the name of the user responsible for each of those groups:

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, managedBy FROM ‘LDAP://DC=fabrikam,DC=com’ WHERE objectCategory=’group'”
Set objRecordSet = objCommand.Execute
objRecordSet.MoveFirst
Do Until objRecordSet.EOF
    Wscript.Echo objRecordSet.Fields(“Name”).Value 
    Wscript.Echo objRecordSet.Fields(“managedBy”).Value
    Wscript.Echo
    objRecordSet.MoveNext
Loop

That’s a little more like it.

As you can see, this is an Active Directory search script. And because this is an Active Directory search script, that can only mean one thing: we aren’t going to discuss the script in much detail today. Why not? Well, for one thing, the Scripting Guys are kind of lazy (and deserving of some time off, even if no one else thinks so). More important, however, is the fact that a detailed discussion of Active Directory search scripts goes way beyond what we can do in a single Hey, Scripting Guy! column. But don’t despair; after all, we have a two-part Tales from the Script series that tells you everything you need to know about searching Active Directory.

And then some.

Instead of discussing Active Directory searching in detail, we’ll take just a moment to look at two parts of our script: the SQL query used to retrieve group information, and the Do Until loop used to display that information. Our SQL query looks like this:

objCommand.CommandText = _
    “SELECT Name, managedBy FROM ‘LDAP://DC=fabrikam,DC=com’ WHERE objectCategory=’group'”

As you can see, we’re searching the entire fabrikam.com domain, asking the script to return the values for two attributes: Name and managedBy (the attribute that keeps track of the group owner). This query also includes a Where clause that restricts data to all those objects that have an objectCategory equal to group. What does all that add up to? That adds up to a SQL query that returns the name and owner of all the groups in fabrikam.com.

Which is just exactly what we wanted our SQL query to return.

When we execute this query we get back a recordset containing all the requested information. Once we have that information, we set up a Do Until loop that runs until the recordset’s EOF (end-of-file) property is True. Inside that loop, we use these two lines of code to echo back the group name and the group owner:

Wscript.Echo objRecordSet.Fields(“Name”).Value 
Wscript.Echo objRecordSet.Fields(“managedBy”).Value

After that, we use the command Wscript.Echo to insert a blank line in our output, then use the MoveNext method to move to the next record in the recordset. (Don’t forget to call the MoveNext method. If you do forget, then your script will simply echo back the value of the first record in the recordset forever and ever.) When we’re all done, we should have output similar to this:

Finance Department Employees
CN=Ken Myer,OU=Finance,DC=fabrikam,DC=com

Finance Department Managers
CN=Jonathan Haas,OU=Finance,DC=fabrikam,DC=com
Human Resources Employees
CN=Pilar Ackerman,OU=HR,DC=fabrikam,DC=com

And that’s how you return a list of all your Active Directory groups, along with the name of the user responsible for each of those groups. Thank you, and we’ll see you all tomorrow.

Oh, right; good point. As you can see, our sample script worked just fine: it returned the name of each group as well as the owner of each group. The only problem is that the managedBy attribute stores group owners by their distinguished name; that means we get back names similar to this:

CN=Ken Myer,OU=Finance,DC=fabrikam,DC=com

That’s OK, but in many cases we’d rather have owner names displayed like this:

Ken Myer

And that’s a problem. That’s a display name, and the managedBy attribute doesn’t store display names.

So does that mean we’re out of luck? Hey, the Scripting Guys have been out of luck for years. (After all, if we were even the least bit lucky we wouldn’t even be Scripting Guys in the first place.) Fortunately, though, we don’t need luck to care of this problem; all we need to do is modify our Do Until loop so that it looks like this:

Do Until objRecordSet.EOF
    Wscript.Echo objRecordSet.Fields(“Name”).Value 
    strUserDN = objRecordSet.Fields(“managedBy”).Value
    Set objUser = GetObject(“LDAP://” & strUserDN)
    Wscript.Echo objUser.displayName
    Wscript.Echo
    objRecordSet.MoveNext
Loop

What’s different with this loop? Well, to begin with, nothing: in the first line we echo back the value of the group’s Name attribute, just like we did in our original script. In line 2, however, things begin to change. This time around we don’t echo the value of the managedBy attribute; instead, we store that value in a variable named strUserDN:

strUserDN = objRecordSet.Fields(“managedBy”).Value

What’s the point of that? No point, really; we’re just killing a little time before lunch.

No, hey, just kidding: there’s always a point to anything that the Scripting Guys do. (Whether or not there’s a good point to anything that the Scripting Guys do is another story.) We’re grabbing the value of the owner’s distinguished name because we can then pass that value to the GetObject function and, in turn, bind directly to the owner’s user account in Active Directory:

Set objUser = GetObject(“LDAP://” & strUserDN)

And what’s the point of that? Well, once we’re connected to the user account we can echo back any of the attribute values for that account. If we bind to the group account we can only echo back the distinguished name of the group owner; that’s because (via the managedBy attribute) that’s the only name available to us. By binding to the owner’s individual user account we can echo back any attribute value for that account, including the displayName:

Wscript.Echo objUser.displayName

And why do we want to echo back the value of the displayName attribute? Because then we get output that looks like this:

Finance Department Employees
Ken Myer

Finance Department Managers
Jonathan Haas
Human Resources Employees
Pilar Ackerman

Pretty slick, huh?

And sure, you can easily modify the original script so that it returns the names of all the groups that are managed by a specified user. All you have to do is include the distinguished name of that user in your Where clause, like so:

objCommand.CommandText = _
    “SELECT Name FROM ‘LDAP://dc=fabrikam,dc=com’ WHERE objectCategory=group’ ” & _
        “AND managedBy=’CN=Ken Myer,OU=Finance,dc=fabrikam,dc=com'”

That query returns a list of groups managed by Ken Myer.

That should do it, PL. Remember, the Winter Scripting Games are just a couple months of way (February 15, 2008 through March 3, 2008). Keep checking the Games home page between now and then; among other things, we’ll be posting weekly tips to help you prepare for the Games. And if you got a perfect score in last year’s Games, be sure to send us information for our Profiles in Perfection feature; just email a brief bio and a picture (or two) to scripter@microsoft.com (in English, if possible).

Oh, and keep this in mind: Tiger Woods did not enter the 2007 Scripting Games, and look what happened to him. Let that be a lesson to you all.

What’s that? He earned how much money?!? Wow; that is a lot of money, isn’t it? Well, enter the Scripting Games anyway; money isn’t everything you know.

Besides, we bet Tiger Woods doesn’t have a Dr. Scripto Bobblehead doll. And we wouldn’t sell him one, either, not at any price. Great Scripting Games prizes like Dr. Scripto bobblehead dolls can’t be bought; they have to be earned.