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: http://www.quest.com/powershell/activeroles-server.aspx

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. http://www.quest.com/powershell/activeroles-server.aspx
#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
stop-transcript



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:
http://www.hanselman.com/blog/SigningPowerShellScripts.aspx

Or you can just turn off the check because you know what it's doing:
Set-ExecutionPolicy Unrestricted
http://technet.microsoft.com/en-us/library/dd347628.aspx



If you want to query specific groups such as admins, there's a similar tutorial here:
http://www.yanzzee.com/2011/01/use-powershell-to-create-domain-admin.html

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".

    ReplyDelete