Wednesday, January 12, 2011

Export Domain Admins to CSV with Powershell

It would be nice to be able to run a PowerShell script to query certain domain groups in Microsoft Active Directory, and write them to .CSV files for easy review in excel. Here's one way 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 text file with .ps1 extension, and the following text. You can review the comments in the script to see what each part does, or just google the commands you don't know. Quest has some documentation last time I checked.



#the AD groups that will be queried. These are groups that are considered domain admins
$aGroups = @("Administrators", "Domain Admins", "Server Operators", "Enterprise Admins", "Account Operators", "Backup Operators", "Group Policy Creator Owners", "Schema Admins",  "Domain Controllers")

#where the results are saved. you may want to change this
$strOutputPath = "C:\Users\username\Desktop\"

#creates a time-stamp for the file name
$strTimestamp = [string](Get-Date).Year + "-" + [string](Get-Date).Month + "-" + [string](Get-Date).Day + "_" +  [string](Get-Date).Hour + "-" + [string](Get-Date).Minute

#the query happens for the groups provided above, and will record the details listed here and export it to a csv file for each with the timestamp
foreach ($group in $aGroups){
Get-QADGroupMember $group -Indirect -Type 'user'| select-object Name, sAMAccountName, title, manager, employeeID, employeeNumber, employeeType, associatedDomain, ObjectClass, defaultGroup, postalAddress, City, postalCode, PasswordNeverExpires, AccountIsDisabled, Description  | export-csv ($strOutputPath + "_" + $strTimestamp + "_" + $group + ".csv") -notype
}



3. Run the powershell script on a domain machine that is currently on the network. You will have to open the Quest ActiveRoles Management Shell that you installed in step one. You should get a CSV file for each group specified. You shouldn't have to be a domain admin to run the script successfully, but your user and computer should be on the domain.

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

No comments:

Post a Comment