LDAP: how to do server-side sorting and why it's a bad idea

  • Article

Active Directory is an object repository, in many ways similar to a database. And like any database, it can deliver its output sorted in any way you like. However, this server-side sorting is rarely done. In fact, it is so rare that it's pretty hard to find out how to do it. The usual and recommended way of obtaining a sorted set is to have the client do it. Simple example:

[powershell]
Get-ADUser -filter * | Sort-Object name
[/powershell]

We probably all know that part. But to have AD do the sorting is not so simple. One reason is that the instruction to the server to sort the result set is not part of the LDAP query itself; it is an LDAP control, which is a way to tweak the query behavior. The full list of LDAP controls is here. The Powershell commandlets do not expose LDAP controls natively. They implement just some of them, and hide the details from you.

The deleted objects control (1.2.840.113556.1.4.417) is a good example. Its purpose is to show deleted objects that are hidden by default. The control is used under the covers by PowerShell, and is exposed as a simple switch called "-IncludeDeletedObjects":

[powershell]
Get-ADObject -IncludeDeletedObjects -Filter { objectclass -eq "user" }
[/powershell]

But you guessed it: the sort control (1.2.840.113556.1.4.473) is not implemented anywhere in our AD powershell commandlets. But we can still do it by going down a layer and start using ADSI. Even this layer hides the gory details, but it has enough entry points to make it work.

Here is a working example of a server-side sort. And again, this is not a best practice, this is just to show that it can be done. We look for all users, but this time we let the server sort it:

[powershell]
$strfilter = "(&(objectclass=user)(objectcategory=person))"
$objDomain = New-Object System.DirectoryServices.DirectoryEntry
$objSearcher = New-Object System.DirectoryServices.DirectorySearcher
$objSearcher.SearchRoot = $objDomain
$objSearcher.PageSize = 1000
$objSearcher.Filter = $strFilter
$objSearcher.Sort.PropertyName = "name"
$objSearcher.Sort.Direction = [System.DirectoryServices.SortDirection]::Ascending

$colProplist = ,"name","whenchanged"
foreach ($i in $colPropList)
{
[void]$objSearcher.PropertiesToLoad.Add($i)
}

$objSearcher.FindAll() | ForEach-Object {
[PScustomObject] @{
name = $_.properties.name[0]
whenchanged = $_.properties.whenchanged[0]
}
}
[/powershell]

Let's not discuss all the details that don't really matter, but focus on the important part: the Sort property of the $objSearcher object. The Sort property is also an object, and has two relevant properties: the attribute name ("name"), and the sort direction (Ascending). Note that there no sorting after the search here, any sorting comes straight out of AD. This is what it looks like in a small lab:

sorted-by-name

Convincing, isn't it? If we change the sort attribute to "whenChanged", we get:
sorted-by-whenchanged

Which is an even nicer example. I wonder who messed with the Guest account, though...

So what's the harm then? Why is server-side sorting not a common occurrence? Simple: sorting is an expensive operation. Just one query is not going to hurt, but thousands of them could seriously load the server -- for no real purpose when client-side sorting works just as well. We even have an article saying so (thx Glenn):

Avoid sorting if the result set does not need to be sorted:
Sorting consumes a great deal of processor time on the server. Avoid sorting the result set whenever possible. This is especially true for a large result set because the server cannot return any objects until a completed result set is constructed.

Now consider a result set of a million objects ...