PowerShell AD Report: Export All Users with Details to CSV

The compliance audit is due Friday and the auditors want a spreadsheet of every Active Directory user with their name, email, department, last logon date, account status, and group memberships. With the right PowerShell export Active Directory users script, that report runs in two minutes and exports a clean CSV that opens directly in Excel. This post builds the complete report incrementally — property selection, filtering active vs disabled users, adding last logon data, including group membership, and exporting with proper formatting.

Define Properties to Collect

Start by defining the properties you need. Not all AD user properties are returned by default — any attribute beyond the default set must be listed in -Properties:

Import-Module ActiveDirectory

$properties = @(
    'DisplayName',
    'SamAccountName',
    'EmailAddress',
    'Department',
    'Title',
    'Manager',
    'Enabled',
    'LastLogonDate',
    'PasswordLastSet',
    'PasswordNeverExpires',
    'LockedOut',
    'WhenCreated',
    'DistinguishedName'
)

$users = Get-ADUser -Filter * -Properties $properties
Write-Host "Total user objects found: $($users.Count)"

Filter Active and Disabled Users

Split the data to report active and disabled users separately, or add an AccountStatus calculated column to the combined report:

# Combined report with status column
$users = Get-ADUser -Filter * -Properties $properties |
    Select-Object DisplayName, SamAccountName, EmailAddress, Department,
        Enabled,
        @{N='AccountStatus'; E={ if ($_.Enabled) { 'Active' } else { 'Disabled' } }},
        LastLogonDate, PasswordLastSet, PasswordNeverExpires, WhenCreated,
        @{N='OU'; E={ ($_.DistinguishedName -split ',',2)[1] }}

$activeCount   = ($users | Where-Object Enabled -eq $true).Count
$disabledCount = ($users | Where-Object Enabled -eq $false).Count
Write-Host "Active: $activeCount | Disabled: $disabledCount"

Add Last Logon Date

LastLogonDate is the replicated last-logon attribute. For a compliance report, this is usually sufficient. If precise last-logon data is required, query all domain controllers (see the AD computers post for that pattern):

$neverLogged = $users | Where-Object { $_.LastLogonDate -eq $null }
Write-Host "Accounts that never logged in: $($neverLogged.Count)"

# Flag accounts with no activity in 90 days
$cutoff = (Get-Date).AddDays(-90)
$inactive = $users | Where-Object {
    $_.Enabled -eq $true -and
    ($_.LastLogonDate -eq $null -or $_.LastLogonDate -lt $cutoff)
}
Write-Host "Active but inactive 90+ days: $($inactive.Count)"

Include Group Membership

Group membership requires a separate Get-ADPrincipalGroupMembership call per user. For large domains, this is slow — consider limiting it to security groups or privileged groups only:

$reportWithGroups = $users | ForEach-Object {
    $groups = try {
        (Get-ADPrincipalGroupMembership $_.SamAccountName -ErrorAction Stop |
            Where-Object GroupCategory -eq Security |
            Select-Object -ExpandProperty Name) -join '; '
    } catch { "Error retrieving groups" }

    $_ | Select-Object *,
        @{N='SecurityGroups'; E={ $groups }}
}

Format and Clean Output

Clean up null values, format date columns consistently, and resolve the manager’s DN to a display name before export:

$cleanReport = $users | ForEach-Object {
    $managerName = if ($_.Manager) {
        try { (Get-ADUser -Identity $_.Manager -ErrorAction Stop).DisplayName }
        catch { $_.Manager }
    } else { "" }

    [PSCustomObject]@{
        DisplayName          = $_.DisplayName
        Username             = $_.SamAccountName
        Email                = $_.EmailAddress ?? ""
        Department           = $_.Department ?? ""
        Manager              = $managerName
        AccountStatus        = if ($_.Enabled) { "Active" } else { "Disabled" }
        LastLogon            = if ($_.LastLogonDate) { $_.LastLogonDate.ToString('yyyy-MM-dd') } else { "Never" }
        PasswordLastSet      = if ($_.PasswordLastSet) { $_.PasswordLastSet.ToString('yyyy-MM-dd') } else { "Never" }
        PasswordNeverExpires = $_.PasswordNeverExpires
        AccountCreated       = $_.WhenCreated.ToString('yyyy-MM-dd')
        OU                   = ($_.DistinguishedName -split ',',2)[1]
    }
}

Export to CSV with -NoTypeInformation

Write the final report to CSV. Always use -NoTypeInformation to suppress the #TYPE header line that Excel does not handle gracefully:

$reportPath = "C:\Reports\AD-UserReport_$(Get-Date -Format 'yyyyMMdd').csv"

$cleanReport | Sort-Object DisplayName |
    Export-Csv -Path $reportPath -NoTypeInformation -Encoding UTF8

Write-Host "Report exported: $reportPath"
Write-Host "Rows: $( (Import-Csv $reportPath).Count )"

Report exported: C:\Reports\AD-UserReport_20260504.csv
Rows: 1935

Common Errors and Fixes

• Group membership requires a separate Get-ADPrincipalGroupMembership call. Group membership is not a flat attribute on the user object — it is a linked attribute resolved through a separate query. For 2,000 users, this means 2,000 extra AD queries, which can take several minutes. Scope it to privileged groups or run group lookups only on a subset of users to keep the runtime reasonable.
• Properties like mail empty if not populated in AD. Many attributes in AD are optional. EmailAddress, Department, and Title are commonly empty for some or all users. Use the null-coalescing approach ($_.EmailAddress ?? "" in PS 7, or if ($_.EmailAddress) { $_.EmailAddress } else { "" } in PS 5.1) to ensure clean CSV output without nulls.

Related Cmdlets / See Also

• Powershell Active Directory Users
• Powershell Export Csv

Wrapping Up

A comprehensive AD user report requires defining the right -Properties list, calculating derived columns with expressions, resolving linked attributes like Manager DNs, and exporting with -NoTypeInformation -Encoding UTF8 for Excel compatibility. Build it once, parameterize the output path, and schedule it to run automatically before each compliance cycle.