PowerShell: Import A List of Users In A CSV File and Lookup Their Details In Active Directory

I know I have written a similar post before, but its nice to go over topics again as more often than not they can be improved, which is the case here, so lets dive straight in.

PowerShell is a powerful and flexible command-line interface that provides easy access to various system management tasks, including managing Active Directory (AD) users and groups.

Using PowerShell to look up a list of users’ details in Active Directory provides several benefits, including:

1. Efficiency: PowerShell is designed to automate repetitive tasks, making it faster and more efficient to manage large numbers of users.
2. Accuracy: PowerShell provides a consistent and repeatable way to perform tasks, reducing the likelihood of errors and ensuring that tasks are performed consistently.
3. Flexibility: PowerShell provides access to a wide range of AD management tasks, allowing administrators to perform complex tasks with ease.
4. Scalability: PowerShell can be used to manage large numbers of users and groups in Active Directory, making it ideal for enterprise-level management.
5. Automation: PowerShell can be used to automate the management of users and groups in Active Directory, reducing the need for manual intervention and increasing the reliability of management tasks.

Overall, using PowerShell to look up a list of users’ details in Active Directory is a powerful and efficient way to manage AD users and groups, providing administrators with greater control and flexibility.

PowerShell Script 1 – Barebones

# Import the list of users from a CSV file
$users = Import-Csv -Path "C:\Path\To\Users.csv"

# Iterate over the list of users and lookup their properties in Active Directory
foreach ($user in $users) {
    $adUser = Get-ADUser -Identity $user.Username -Properties *

    Write-Output "User $($user.Username) properties:"
    $adUser | Select-Object -Property *
}

Replace C:\Path\To\Users.csv with the path to your CSV file containing the list of users. The script uses the Import-Csv cmdlet to import the list of users.

The script then iterates over the list of users using a foreach loop, and looks up their properties in Active Directory using the Get-ADUser cmdlet with the -Properties * parameter to retrieve all available properties.

The script then outputs the user’s properties to the console using the Write-Output cmdlet, and uses the Select-Object cmdlet with the -Property * parameter to display all available properties for the user.

Note that this script assumes that you have the necessary permissions to lookup user properties in Active Directory, and that the ActiveDirectory module is installed on your system. If the ActiveDirectory module is not installed, you can install it using the following command:

Install-Module -Name ActiveDirectory

PowerShell Script 2 – Improved

There is nothing wrong with the above script, it does the job, but then I thought, how could we improve it just a tad more. So, I amended it to the below

Filter out any empty or invalid usernames from the CSV file to avoid errors when querying Active Directory:

$users = Import-Csv -Path "C:\Path\To\Users.csv" | Where-Object { $_.Username -ne $null -and $_.Username -ne "" }

Specify a smaller set of properties to retrieve from Active Directory to avoid unnecessary overhead and speed up the script:

$adUser = Get-ADUser -Identity $user.Username -Properties Name, EmailAddress, Description

Use the -ErrorAction parameter on the Get-ADUser cmdlet to suppress errors caused by missing users, and output a more helpful message to the console:

$adUser = Get-ADUser -Identity $user.Username -Properties Name, EmailAddress, Description -ErrorAction SilentlyContinue
if ($adUser) {
    Write-Output "User $($user.Username) properties:"
    $adUser | Select-Object -Property *
} else {
    Write-Output "User $($user.Username) not found in Active Directory"
}

These improvements will make the script more robust and efficient, and provide better error handling and output.

PowerShell Script 2 – Improved + Export

You can use the Export-Csv cmdlet to export the output to a CSV file. Here’s an example of how you can modify the code to achieve that:

# Import the list of users from a CSV file
$users = Import-Csv -Path "C:\Path\To\Users.csv" | Where-Object { $_.Username -ne $null -and $_.Username -ne "" }

# Initialize an array to store the AD user objects
$adUsers = @()

# Iterate over the list of users and lookup their properties in Active Directory
foreach ($user in $users) {
    $adUser = Get-ADUser -Identity $user.Username -Properties Name, EmailAddress, Description -ErrorAction SilentlyContinue
    if ($adUser) {
        Write-Output "User $($user.Username) properties:"
        $adUser | Select-Object -Property *
        $adUsers += $adUser
    } else {
        Write-Output "User $($user.Username) not found in Active Directory"
    }
}

# Export the AD user objects to a CSV file
$adUsers | Export-Csv -Path "C:\Path\To\Output.csv" -NoTypeInformation

This modified code initializes an array to store the AD user objects, and then adds each user object to the array after retrieving their properties. Finally, the array is exported to a CSV file using the Export-Csv cmdlet. Note that the -NoTypeInformation parameter is used to exclude the object type information from the CSV file, making it easier to read.