I needed to import a list of all Active Directory user accounts into a table in SQL Server for a recent project. This project also gave me a perfect opportunity to learn a little bit of powershell. Below chronicles the script that I built. I’m going to skip over a lot of the powershell basics information, as that is available from other sources. For this project, I needed to populate a table with these fields from Active Directory: Display Name, NT username, email address, and office phone.
I used the powershell Get-ADUser cmdlet to get the information out of Active Directory
Before doing anything else, you need to open a powershell command window (Start–>Run–>powershell.exe) and import the Powershell ActiveDirectory module:

PS C:\> Import-Module activedirectory

After importing the module, you can learn more about the Get-ADUser cmdlet by using some of these commands

Get-Help Get-ADUser
Get-Help Get-ADUser -examples
Get-Help Get-ADUser -detailed

Examples are great, but I learn better by seeing real results, so lets run a quick query to see what information we get.

Get-ADUser -filter * -ResultSetSize 1
#Note, I included “-ResultSetSize 1” so that I was not overwhelming the domain controllers while testing.

Awesome, I can now see user accounts from Active Directory! The output that I got showed me some of the information that I needed, but I am still missing some pieces (primarily email address and phone number). The “-Properties” option will let you pick additional fields to include in the output. I got a little stuck here briefly, because the Get-ADUser cmdlet names for the properties do not all match the Active Directory field names. To figure out what the appropriate field names were, I ran this:

Get-ADUser -filter * -ResultSetSize 1 -Properties *

Cool, now I can put the fields together to get a shortened list of only what I am looking for:

Get-ADUser -filter * -ResultSetSize 1 -Properties EmailAddress,OfficePhone
# Note this will return additional fields (DistinguishedName,Enabled,ObjectClass, SID,…)

I got a little bit stuck here too, because I was getting too much information. When I got to the point of exporting this data to a CSV file and importing it into SQL Server (coming later), I got hung up because some of the fields did not always have information for my organization. The solution came by using a pipe (SHIFT + \ key) and the Select-Object cmdlet. This let me filter for only the specific columns that I wanted out of Active Directory.

Get-ADUser -filter * -ResultSetSize 1 -Properties EmailAddress,OfficePhone | Select-Object EmailAddress,OfficePhone,DisplayName,SamAccountName

I now see only the 4 columns that I care about. On a larger scale test, I realized that I was returning accounts that I did not want to see (like disabled accounts, Administrative accounts, etc.) I used the –Filter option to include some search criteria here.
Filtering in powershell is a little different than what I am used to. For example, “=” is “-eq” in powershell and “not equal to” or “<>” is “-notlike” in powershell. You can also combine multiple filters by including the entire set in curly brackets { }, individual parameters in parenthesis (), and using the “-and” operator. The Asterisk is the wildcard variable.
For example:

-Filter {(Name -notlike "*(Administrator)") -and (Name -notlike "Matt*") -and (Enabled -eq "True") }
# I also threw in there where Name is not like Matt*

Now that I have only the fields that I want, and I filtered out the users that I don’t want to see, I can start working on importing it into SQL Server. I could have used powershell to insert the records directly into SQL, but I was concerned about latency issues and spamming the domain controllers into a denial-of-service attack. I was working with more than 50,000 Active Directory accounts. I definitely did not want to hold up the domain controllers if there was an issue with the SQL server during the process. Because of this, I decided to export the data as a CSV comma delimited file and then use SSIS to import the data.
Exporting the data to a csv file uses another pipe (SHIFT + \ key) and the export-csv cmdlet. Make sure to put in your appropriate file path to export to

#Make sure you put your file path between the < >
 | export-csv -path \\\\ADUsersExported.csv -NoTypeInformation -Encoding "UTF8"

Putting everything together.

Make sure to put in your appropriate file path to export to.
I also took out the “-ResultSetSize” option so that all records were returned.

#Make sure you put your file path between the < >
Get-ADUser -Filter {(Name -notlike "*(Administrator)")  -and (Enabled -eq "True") }  -Properties SamAccountName,DisplayName,EmailAddress,OfficePhone | Select-Object EmailAddress,OfficePhone,DisplayName,SamAccountName | export-csv -path \\\\ADUsersExported.csv -NoTypeInformation -Encoding "UTF8"

Once the data was exported to a CSV comma delimited file, I am using SSIS to import it into SQL server. The powershell script and SSIS package are both scheduled to run daily overnight when things should be slower on the servers.