Export all users in ActiveDirectory » cyberfella ltd, since 2012.

If you’re tasked with generating a list / creating a spreadsheet of all user accounts in AD but are worried you might miss out an OU when manually going through and exporting the list using the Active Directory Users and Computers MMC Snap-in, then use Powershell to generate a list instead, safe in the knowledge it’ll find everything.

If you’re really keen you can subsequently use GNUWin32 to give you neat command line tools usually only available to a bash command prompt on a Linux/UNIX OS to chop columns out of the exported csv file using cut, awk, sort and uniq. Or just use Excel to achieve it. More on GNUWin32 here.

Open a Powershell and type the following to export all users in the directory to a csv file…

Import-module activedirectory

get-aduser -filter * | Export-Csv c:\myusers.csv

Since the OU Path’s are themselves comma separated, it throws the keys in the csv out of alignment, making it challenging to extract the columns to the right of it that contains the samAccountName “Logon Name”. To get over this hurdle, go back to PowerShell and be more specific about the exact key (or Label) you want, e.g. if you just want a list of Logon Names for all users in AD, then this command works…

get-aduser -filter * | select-object @{Label = “Logon Name”;Expression ={$_.saMAccountName}} | Export-Csv D:\ADUsers\ADUsers.LogonNames.csv

Some other useful Labels you may want to use are shown below for your convenience (including a neat If statement for extracting Disabled Accounts).

@{Label = “First Name”;Expression = {$_.GivenName}}
@{Label = “Last Name”;Expression = {$_.Surname}}
@{Label = “Display Name”;Expression = {$_.DisplayName}}
@{Label = “Logon Name”;Expression = {$_.sAMAccountName}}
@{Label = “Full address”;Expression = {$_.StreetAddress}}
@{Label = “City”;Expression = {$_.City}}
@{Label = “State”;Expression = {$_.st}}
@{Label = “Post Code”;Expression = {$_.PostalCode}}
@{Label = “Country/Region”;Expression = {if (($_.Country -eq ‘GB’) ) {‘United Kingdom’} Else {”}}}
@{Label = “Job Title”;Expression = {$_.Title}}
@{Label = “Company”;Expression = {$_.Company}}
@{Label = “Description”;Expression = {$_.Description}}
@{Label = “Department”;Expression = {$_.Department}}
@{Label = “Office”;Expression = {$_.OfficeName}}
@{Label = “Phone”;Expression = {$_.telephoneNumber}}
@{Label = “Email”;Expression = {$_.Mail}}
@{Label = “Manager”;Expression = {%{(Get-AdUser $_.Manager -server $ADServer -Properties DisplayName).DisplayName}}}
@{Label = “Account Status”;Expression = {if (($_.Enabled -eq ‘TRUE’) ) {‘Enabled’} Else {‘Disabled’}}}
@{Label = “Last LogOn Date”;Expression = {$_.lastlogondate}}

You can combine the Labels above in a single command with a comma in the select-object section, for example to extract all logon names and whether or not the account is disabled…

get-aduser -filter * | select-object @{Label = “Logon Name”;Expression ={$_.saMAccountName}},@{Label = “Account Status”;Expression = {if (($_.Enabled -eq ‘TRUE’) ) {‘Enabled’} Else {‘Disabled’}}} | Export-Csv D:\ADUsers\ADUsers.LogonNames.csv

I had some trouble with the LastLogon Label, so have included the working example used to obtain this information below.

get-aduser -filter * -properties * | select-object @{Label = “LogonName”;Expression = {$_.saMAccountName}},@{Label = “LastLogonDate”;Expression = {$_.LastLogonDate}}| Export-Csv D:\ADUsers\ADUsers.LastLogon.csv

Did you like this?
Tip cyberfella with Cryptocurrency