Windows

PowerShell script for getting Active Directory information

Scott Lowe shares a PowerShell script he wrote to extract a number of fields from Active Directory and write the extracted information into a CSV file.

For a work project, I needed to compare Active Directory actual information to what was present in our ERP system, as well as match that with information about the user's Exchange 2003 mailbox.

I wrote a "down and dirty" PowerShell script to extract a number of fields from Active Directory and write the extracted information into a CSV file. My overall plan was to compare the three data sets -- the Active Directory information, the Exchange mailbox information, and the ERP information -- using Excel, while making sure there was information in all three data sets that would link the data sets to each other.

Here is more information about the project, followed by the PowerShell script I wrote.

Project details

Our reasons for this project:

  • The organization has 16,000 Exchange mailboxes, and we wanted to ensure that only users who should have mailboxes do.
  • We also wanted to ensure that Active Directory accounts for departed employees are inactive and are marked for removal.

These were the project challenges:

  • In a separate report, I had to use WMI to gather Exchange mailbox information since Exchange 2003 doesn't include PowerShell.
  • The organization has more than 600,000 user accounts in Active Directory, most of which are valid; only about 20,000 of these accounts are employees, while the rest are customers. However, in some cases, the customers were also temporary employees, so there was a need to search the entire Active Directory database for potential employee accounts.

A look at the PowerShell script

Notes: This PowerShell script was intended for one-time use and that creates a very different development environment, at least to me. I was going for immediate functionality rather than elegance (I am not a programmer), which is why I consider this a "down and dirty" PowerShell script.

I'll take a line-by-line (or, in some cases, a section-by-section) look at what this PowerShell script does and explain my thinking.

# Start of script

I needed to clear the screen before script execution to make sure there was no clutter that would confuse me when I looked at display results.

Cls

I added a processing loop to break down the Active Directory information into usable chunks. Prior to adding this loop, my script crashed because the machine on which I was running it ran out of memory trying to handle more than 600,000 records at once. Each item in the "targetou" section is an Active Directory organizational unit. Immediately below, you will see a line that outputs to the screen that OU is currently being processed. By displaying information at run-time, I know exactly where I am in a process.

foreach ($targetou in 'A','B','C','D','E','F','G','GUESTACCOUNTS','H','I','J','K','L','CONTRACTOR','M','N','O','P','Q','R','S','T',','U','V','W','X','Y','Z')

{

"Processing information for OU $targetou"

The $targetou variable above is the lowest point in the Active Directory hierarchy at which I worked. The $domainrootpath variable builds the full LDAP string to the OU against which the script was to run for each iteration.

$DomainRootPath='LDAP://OU='+$targetou+',OU=ORGUSER,DC=contoso,DC=com'

The next several lines create and populate an Active Directory searcher object in PowerShell.

$adsearch = New-Object DirectoryServices.DirectoryAdsearch([adsi]$DomainRootPath)

I limited the kinds of objects that would be returned. The line below limits results to user objects.

$adsearch.filter = "(objectclass=user)"

The PropertiesToLoad items below were necessary for the reporting task I had ahead of me. These lines modify the behavior of the Active Directory search by forcing it to return only what is specified rather than returning everything. Because of the size of the data set, I needed to limit the returned data to only what was essential.

$adsearch.PropertiesToLoad.AddRange(@("name"))

$adsearch.PropertiesToLoad.AddRange(@("lastLogon"))

$adsearch.PropertiesToLoad.AddRange(@("givenName"))

$adsearch.PropertiesToLoad.AddRange(@("SN"))

$adsearch.PropertiesToLoad.AddRange(@("DisplayName"))

$adsearch.PropertiesToLoad.AddRange(@("extensionAttribute1"))

$adsearch.PropertiesToLoad.AddRange(@("extensionAttribute2"))

$adsearch.PropertiesToLoad.AddRange(@("comment"))

$adsearch.PropertiesToLoad.AddRange(@("title"))

$adsearch.PropertiesToLoad.AddRange(@("mail"))

$adsearch.PropertiesToLoad.AddRange(@("userAccountControl"))

$adsearch.Container

This line executes the search based on the parameters specified above. For each iteration of the foreach loop, Active Directory will search the organizational unit for that loop and return all of the attributes specified above for each user account. The results of the execution will be stored in the variable named users. Unfortunately, as it exists, the information from this array can't be simply written to a CSV file since that CSV file would contain only the Active Directory object name and an entry called "System.DirectoryServices.ResultPropertyCollection." I needed to expand out and capture the individual Active Directory elements, which I do later in the script.

$users = $adsearch.findall()

As the script was running, I wanted to know how many objects were returned from each loop iteration, so I added the line below to show how many user accounts were being handled.

$users.Count

I initialized an array variable into which I'd write the individual Active Directory elements we wanted to capture.

$report = @()

I started another loop that executes for each Active Directory account for which we wanted to capture information.

foreach ($objResult in $users)
{

I needed to create a variable that houses the properties for an individual record. (There are other ways to do this, but I like to break things down to make them more readable.)

$objItem = $objResult.Properties

I created a new temporary object into which to write the various Active Directory attributes for this single record being processed in this processing iteration (remember, this is repeated for each record returned from Active Directory).

$temp = New-Object PSObject

For each individual Active Directory property that was returned from the Active Directory searcher, I added a named property to the temp variable for this loop iteration. Basically, this breaks out the single Active Directory record for a user into its individual components, such as name, title, email address, and so forth. (Case-sensitivity matters in this section.)

$temp | Add-Member NoteProperty name $($objitem.name)

$temp | Add-Member NoteProperty title $($objitem.title)

$temp | Add-Member NoteProperty mail $($objitem.mail)

$temp | Add-Member NoteProperty displayname $($objitem.displayname)

$temp | Add-Member NoteProperty extensionAttribute1 $($objitem.extensionattribute1)

$temp | Add-Member NoteProperty extensionAttribute2 $($objitem.extensionattribute2)

$temp | Add-Member NoteProperty givenname $($objitem.givenname)

$temp | Add-Member NoteProperty sn $($objitem.sn)

$temp | Add-Member NoteProperty useraccountcontrol $($objitem.useraccountcontrol)

I added the results of this individual record to the primary array into which we're capturing the full results from the search for later export to CSV.

$report += $temp
}

This line creates the name of the file that will be written. I created a new file for each organizational unit processed.

$csvfile="AD-"+$targetou+".csv"

The line writes the entire file to disk and then notifies the user that processing for this OU has completed.

$report | export-csv -notypeinformation $csvfile

"Wrote file for $targetou"

}

Summary

For my purposes, this PowerShell script captured exactly the information that I needed, and I was able to complete my comparison task. If you know of a more elegant way to get this information, please post it in the discussion.

More PowerShell tips and tricks

Keep up with Scott Lowe's posts on TechRepublic

About

Since 1994, Scott Lowe has been providing technology solutions to a variety of organizations. After spending 10 years in multiple CIO roles, Scott is now an independent consultant, blogger, author, owner of The 1610 Group, and a Senior IT Executive w...

8 comments
agrawalpiyush
agrawalpiyush

Its better to use a third party software which can provide us the interface to interact with windows active directory. Running a script is always a cumbersome process. Plus the the output cannot be filtered. Tools like Lepide ADMR provides us with functionality so that the interaction with windows active directory is easy. Plus the reports generated can be saved in different formats.

Tim77
Tim77

I do exports from Active Directory to CSV files all the time using the CSVDE tool found in the \Windows\System32 folder. One line can accomplish the same thing as the Powershell script using any ldap query and specifying any fields for output. -s is the domain controller, -r is the ldap query, -f is the output file, -l is the fields to output.Here's an example: csvde -s "domaincontrollername" -r "(&(objectcategory=person)(objectClass=user)(UserAccountControl=512)(msExchHomeServerName=*))" -f c:\temp\EXCHUSERS.CSV -l "samAccountName,name,mail,mailNickname,company,streetaddress,l,st,postalcode,telephoneNumber,description,department,co,description" -m

wizard57m-cnet
wizard57m-cnet

Why are you posting to old threads? That can earn a spam notification

CharlieSpencer
CharlieSpencer

Lepide ADMR is $150 for one license, $399 for three, etc. PowerShell is free, and can be used for more than just querying AD

Scott Lowe
Scott Lowe

But, your way is easier and, therefore, much less fun and less impressive. Kidding, of course. Nice job! Scott

AnsuGisalas
AnsuGisalas

The one who didn't do that thing with the thing for those people :D And if I recall correctly, neither did the other guy? Anyway, confusion abounds, which is the most important thing :^0

Editor's Picks