Custom export all users in Active directory

Do you want to export a bunch of users from a local Active Directory to an Excel file?

Perhaps you need to filter out when users last logged on or get a list of phone numbers?

If you have the data everything is possible! 😃

The basic one-liner

For basic day to day operations this would be the way to go:

Get-ADUser -Filter {(employeeID -like "*")} -properties employeeID,Name,enabled | select employeeID,Name,enabled

But you might need something more complicated. In which case I have script that will get a whole bunch of info with a little more customized filters.

---

Custom script

This script is non-destructive because we use the Get-ADUser command. In short what the script is doing is:

• Set the OU location for where in Active Directory we want to search.
• Include a search filter to define which users we are looking for.
• Define which of the many attributes we want to include in the result.
• Run the command on a DC server with Active Directory Web Services running
• Alternatively, choose one of the output options as described below (you will have to enter one of these following the last line).

So what do you need to change here? Well, first of all, you probably have a different searchScope, depending on your AD OU structure. You can also modify searchFilter and the attributes depending on what you need. Lastly, you need to set the name of the DC server and then add one of the output options as described below.

# Export users in Active Directory to text file or CSV

# Drilldown search a specific OU folder
$searchScope = "OU=Users,OU=Contoso,DC=con,DC=local"

# Search with specific filter
$searchFilter = {
    (employeeID -like "*") 
    -And (Enabled -eq $true)
    -And (company -NotLike "*Loremipsumdolorsitamet")
}

# Get specific attributes in the list
$attributes = 
    "employeeID",
    "employeeNumber",
    "whencreated",
    "enabled",
    "userPrincipalName",
    "name",
    "givenName",
    "sn",
    "displayname",
    "title",
    "description",
    "company",
    "department",
    "manager",
    "physicalDeliveryOfficeName",
    "streetAddress",
    "st",
    "postalCode",
    "postOfficeBox",
    "telephoneNumber",
    "mobile",
    "homePhone",
    "ipPhone",
    "pager",
    "mail",
    "mailNickName",
    "wWWHomePage",
    "c",
    "CO",
    "l",
    "distinguishedName",
    "profilePath",
    "targetAddress",
    "sAMAccountName",
    "facsimileTelephoneNumber",
    "info",
    "LastLogonTimeStamp",
    "CanonicalName"

# Run the command
Get-ADUser -Credential con.local\Administrator -server condc01.con.local -SearchBase $searchScope -Filter $searchFilter -properties $attributes | select $attributes

Output options

Grid view

The easiest way to go is just to print the results with GridView. If you don’t know what that means you should just try it, and be amazed 😉 Just add the code to the end of the script.

| Out-GridView

Export CSV

You can also export the results to a CSV file with a timestamp in the file name. Just add the code to the end of the script.

| Export-Csv -Force -Path $env:USERPROFILE\Desktop\$(get-date -f yyyy-MM-dd-HH-mm-ss)-csv_export.CSV -NoTypeInformation -Delimiter ";" -Encoding UTF8

Export to Excel

If you want to export directly to Excel, my personal favorite, you can do this with the following command:

| Export-Excel "$env:USERPROFILE\Desktop\$(get-date -f yyyy-MM-dd-HH-mm-ss)-AD-Export.xlsx" -FreezeTopRow -AutoSize -AutoFilter -BoldTopRow -Show

Just make sure you have already installed the ImportExcel module as I have described earlier here. Basically the command is Install-Module -Name ImportExcel.