Adhoc LDAP information requests

Many times in the life of an Active Directory administrator, you will get various business people or application guys coming to you to pull a lot of information from the directory, or to add on to information they already have. Its great in these instances to have some basic powershell skills to make what looks like a lot of work, turn into a few minute task with some adhoc scripting. In this case, I'll skip the AD commandlets and Quest AD commandlets that many people commonly use, and I'll give you a basic sample of an easy request and how one may go about working on it.

Scenario:

Here is an excel file that has 5000 rows containing Full Name, Logon name (a really helpful people making the request), email address, and a blank column to fill in (Current status: enabled/disabled)

First of all, to make things easy, we want to take the Excel file and convert it to CSV with a save-as. We want to keep column headers in the first row to show what the various attributes will be. And to help with further processing, you may want to take out the spaces and any special characters that powershell may not like as part of an attribute name. Once you have that all together, save your csv file in a location where you want to run the script:

$entries = import-csv .\userlist.csv
$de = [System.DirectoryServices.ActiveDirectory.Domain]::getcurrentdomain().getdirectoryentry()
$ds = new-object directoryservices.directorysearcher($de)
$ds.propertiestoload.add("useraccountcontrol")|out-null

foreach ($entry in $entries) {
 $ds.filter = "(&(objectclass=user)(objectcategory=user)(|(samaccountname=$($entry.samaccountname))(mail=$($entry.email))))"
 $DSsearchresult = $ds.findone()
 if ($DSsearchresult -eq $null) {
  $entry.status = "NotFound"
 } else {
  if ($DSsearchresult.properties.useraccountcontrol[0] -band 2) {
   $entry.status = "Disabled"
  } else {
   $entry.status = "Active"
  }
 }
}

$entries|convertto-csv -notypeinformation |out-file results.csv

To walk through this a bit, the first line is importing the CSV file into an array of objects. Each object will have attributes for all of the defined columns in the CSV file's first row...including any blanks. See why its important to have column headers?

Secondly, we want to set up something to search the directory. Here I am just using .NET classes. The DirectoryServices.DirectorySearcher will do the main work. To keep the results small, we use the propertiestoload method to restrict the results to a single attribute, in this case we want to see if the account is enabled so we use UserAccountControl.

Next, the all important loop. Whenever processing large amounts of data, we will end up in a loop, even if its hidden as a pipeline, basically the end result is the same, though loops may be a bit more controllable and readable. Inside I'm defining the ldap search filter. We want these to be restrictive as possible to minimize results and for faster lookup times. Here I'm doing an OR on samaccountname and email just in case the information given wasn't 100% perfect. This will be likely to find users with a better percentage change of success. As we are referencing attribute properties of $entry, we wrap the whole object in a $() to process what is inside first. Then we continue to do a search for a single result (samaccountname should be unique here, so we get one result). If there is no result, we can put a status into that object stating it is not there. Otherwise we view the useraccountcontrol against 0x2 (disabled flag) to see if its active or not. Notice the [0] here. When working with directoryservices results, the properties are usually collections, so treat it like a single value array in most cases (even if the attribute isn't a multivalued one).

Now that we are done with our loop, our whole intial import into the variable $entries will have their Status attributes populated. So we can just dump that back out into CSV format, bring it back into excel and save it in xslx format to sent back to the requestor.

As requests vary a lot, the complexity can grow and further processing and error handling may be required. This is just a good starting point to see how to turn 5000 lookup's into a one minute script. In other cases, other commandlets may be useful for you, or you could take values to write out a large batch file full of dsmod/dsget type commands. Whatever works best and whatever seems the most efficient for you.