Hey, Scripting Guy! I have a script that returns the names of all the users in an Active Directory group. How can I sort those names in alphabetical order?
Hey, JW. Ah, yes, sorting data: the bane of script writers everywhere. Unfortunately, VBScript doesn’t have a built-in sorting mechanism, and neither does ADSI. Consequently, most people believe they have no choice but to live with data sorted by whatever order ADSI or WMI or imposes on them. So is that the case? Are you truly at the mercy of ADSI when it comes to returning group membership in alphabetic order? Hey, you know what they say: where there’s a will, there’s a way.
There are actually a couple different ways you can sort data, and if you’d like to know more we encourage you to tune in to Scripting Week 2, a series of 10 webcasts to be held in January, 2005. (The schedule hasn’t officially been finalized, but details will be available in the Script Center very soon.) Because you’re only dealing with a single item – user names – we’ll show you how to use a simple bubble sort to return these names in alphabetical order. If you need to deal with – and sort – a bunch items (user names, email addresses, profile paths, etc.) you don’t want to use a bubble sort; that would be way too complicated. But for sorting a single list like this, the bubble sort works pretty well, and requires only a few lines of code.
Here’s what our script looks like:
Dim arrNames() intSize = 0 Set objGroup = GetObject("LDAP://CN=Accountants,OU=Finance,DC=fabrikam,DC=com") For Each strUser in objGroup.Member Set objUser = GetObject("LDAP://" & strUser) ReDim Preserve arrNames(intSize) arrNames(intSize) = objUser.CN intSize = intSize + 1 Next For i = (UBound(arrNames) - 1) to 0 Step -1 For j= 0 to i If UCase(arrNames(j)) > UCase(arrNames(j+1)) Then strHolder = arrNames(j+1) arrNames(j+1) = arrNames(j) arrNames(j) = strHolder End If Next Next For Each strName in arrNames Wscript.Echo strName Next
We begin by creating a dynamic array named arrNames (for more information about dynamic arrays, see this portion of the Microsoft Windows 2000 Scripting Guide). Why do we do this? Well, we’ve already determined that ADSI doesn’t have a built-in sorting mechanism; therefore, we can’t sort the data as it comes back. Instead, we have to grab the data, temporarily store it in an array, and then sort that array.
Next we use this line of code to bind to the Accountants group in our hypothetical Active Directory:
Set objGroup = GetObject("LDAP://CN=Accountants,OU=Finance,DC=fabrikam,DC=com")
When we bind to a group, one of the properties we get back is the Member property, which just happens to hold the membership list for the group. If we just wanted to echo back the distinguished name of each group member, and if we didn’t care about sorting the membership list, we could use code like this:
For Each objMember in objGroup.Member Wscript.Echo objMember Next
But that’s not what we want, is it? First of all, we probably don’t want distinguished names like CN=Ken Myer, OU=Finance, DC=fabrikam, DC=com. Instead, we probably want plain old common names (CNs), names like Ken Myer. Consequently, inside our For-Each loop we bind to each user account, the better to retrieve the CN for each user. We use this code to bind to the individual user accounts:
Set objUser = GetObject("LDAP://" & strUser)
Second, we don’t want to echo the user names right now; instead, we want to stash those names in our array and then sort them before we actually display them inscreen. That’s what this code does; it grabs each user’s CN and creates a spot to store that CN within the array arrNames:
ReDim Preserve arrNames(intSize) arrNames(intSize) = objUser.CN intSize = intSize + 1
(Yeah, we know: if you aren’t used to working with arrays, this might look more like gobbledygook than it does scripting code. If you need an explanation, check out the Microsoft Windows 2000 Scripting Guide.)
Eventually, arrNames will contain the CNs for each user in the Accountants group. What we need to do then is sort that array using a bubble sort. Explaining the ins and outs of the bubble sort is more than we can handle in this column; we’ll explain it a bit better during Scripting Week 2. For now, we’ll just say that the bubble sort methodically compares each item in the collection against every other item, slowly by surely determining the alphabetical order. For example, suppose our array looked like this:
Sam Mary Abigail
The bubble sort would begin by comparing Sam and Mary. Because, alphabetically-speaking, Mary comes before Sam, the bubble sort switches their places in the array. The array then looks like this:
Mary Sam Abigail
Next the sort compares Sam and Abigail and, again, swaps their places in the array. Now the array looks like this:
Mary Abigail Sam
We now know that Sam is the very last item in the array. It has to be; we’ve compared Sam with every other item. Therefore, in this simple example, the bubble sort is finished with Sam, and only has to compare Mary with Abigail. The script compares the two, swaps their places, and gives us this final, sorted list:
Abigail Mary Sam
That’s the basic idea anyway and, admittedly, the actual code looks a bit more complicated:
For i = (UBound(arrNames) - 1) to 0 Step -1 For j= 0 to i If UCase(arrNames(j)) > UCase(arrNames(j+1)) Then strHolder = arrNames(j+1) arrNames(j+1) = arrNames(j) arrNames(j) = strHolder End If Next Next
If you take the time to trace what’s happening, though, you’ll see that the code follows the process we described above; it’s not as bad as it looks. The one tricky thing to note here is that we must convert each name to uppercase (using the UCase function) before making our comparisons. That’s because VBScript makes comparisons using the ASCII values of individual letters. In ASCII, uppercase letters always come before lowercase letters; thus Zachary would come before anne. Converting everything to uppercase ensures that ANNE comes before ZACHARY.
Finally, we use another For-Each loop to cycle through the array arrNames and echo the user names in alphabetical order.
As we noted the bubble sort is not the only way to sort data, and it has its limitations: it’s best used when you have only a single field (like user name) to worry about, and can be slow if you have hundreds of items in your list. If the bubble sort won’t handle your sorting needs, you can either wait for Scripting Week 2, or take a look at the brief discussion of Disconnected Recordsets found in the Scripting Guide.