Wednesday, January 19, 2011

Export Domain Users to CSV with PowerShell

If you would like to create a list of all domain users in excel with a powershell script you are in luck. Quest software has created some pretty awesome tools to manage Microsoft Active Directory. Here's how to do it:

1. You'll need to install the Quest ActiveRoles management shell v. 1.4 or later, located here:

2. Create a PowerShell file with the following script. Basically, just copy this text into a text file and rename the extension to .ps1.

#This script requires Quest ActiveRoles management shell v.1.4 or later and Powershell to be installed.
#The script may be run on any machine that is bound to the domain and actively connected. You do not have to be a domain administrator.
#Two files will be created. One CSV with results and one TXT log file.

$strTimestamp =  [string](Get-Date -format "yyyy-MM-dd_hh-mm")
start-transcript DomainScriptLog-$strTimestamp.txt #starting log file to verify that the script did not have any errors.

Get-Date -format s

# $strFilePath = ($Home + "\My Documents\") 
#This is the path of the logged in user. Use this if you want to save to My Documents (add below into csv-export and out-file commands, otherwise it will save where the script is located.

$strFileName = ("DomainUsers_" + $strTimestamp + ".csv")
Write "Writing File to $strFileName. This may take some time..."

#set sizelimit to 0 for full list
#you can set the various attributes that you would like as columns here
Get-QADUser -Sizelimit '0' -ShowProgress -ProgressThreshold 0 | select-object Name, SAMAccountName, givenName, sn, title, manager, employeeID, employeeNumber, employeeType, defaultGroup, postalAddress, City, postalCode, PasswordNeverExpires, AccountIsDisabled, Description | export-csv ($strFileName) -notype

#this will put a timestamp in the file if it finished correctly
"Execution Completed successfully starting $strTimestamp ending: " + [string](Get-Date -format "yyyy-MM-dd_hh-mm") | Out-File ($strFileName) -append
Write "Execution Complete"
Get-Date -format s

3. Open the Quest ActiveRoles Management Shell for Active Directory program that you installed in step 1 and find the ps1 file that you created. If you run it in normal a normal PowerShell it won't work. Your computer should be logged in to a domain account and have an active connection. The process may take a while, but this script includes a progress bar as well as telling you which account it's working on.

PowerShell script with progress bar

4. You might get an error saying:

"File ....ps1 cannot be loaded. The file ....ps1 is not digitally signed. The script will not execute on the system. Please see "get-help about_signing" for more details.."

You can either figure out how to digitally sign the script by following these long instructions:

Or you can just turn off the check because you know what it's doing:
Set-ExecutionPolicy Unrestricted

If you want to query specific groups such as admins, there's a similar tutorial here:

1 comment:

  1. It took me a while to find the attribute for getting the creation date of user accounts. I thought it was "CreatedOn" but you'll want to use "whenCreated".