PowerShell脚本报错排查：AD用户状态校验Excel数据异常问题

问题概述 #

• 脚本运行报错：Get-ADUser : Variable: 'userUPN' found in expression: $userUPN is not defined
• Excel存储用户DisplayName（示例：SMITH John），但原脚本错误使用UserPrincipalName查询AD
• 无法正确识别AD中已禁用用户，误标记为启用状态

报错详情 #

$user = Get-ADUser -Filter {UserPrincipalName -eq $userUPN}-Prope ...
        ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

• CategoryInfo: InvalidArgument: (:) [Get-ADUser], ArgumentException
• FullyQualifiedErrorId : ActiveDirectoryCmdlet:System.ArgumentException,Microsoft.ActiveDirectory.Management.Commands.GetADUser

原脚本代码 #

Set-ExecutionPolicy -Scope CurrentUser -ExecutionPolicy Unrestricted
# Import the required AD module
# Install the Import-Excel module if not already installed
# You can run this command once: Install-Module -Name ImportExcel

# Import the required module
Import-Module -Name ImportExcel
Import-Module -Name ActiveDirectory

# Specify the path to your input Excel file DevicesWithInventory_8962b92a-5661-4aa7-addd-40c3fb4a8a6e.csv
$excelFile = "C:\Users\toulayoh\Downloads\testdoc.xlsx"

# Specify the path to the output CSV file
$excelFile2 = "C:\Users\toulayoh\Downloads\outputdata.xlsx"
# Specify the name of the worksheet containing the user data
$worksheetName = "Sheet1"

$excelData = Import-Excel -Path $excelFile

$users = Import-Excel -Path $excelFile -WorksheetName $worksheetName


# Import the Excel data

# Create an array to store the results
$results = @()

# Iterate through each user and check their AD status
foreach ($users in $excelData) {
    $userUPN = $users.'Column 6'
    $outputcolumn = $users.'Column 10'

    $user = Get-ADUser -Filter {UserPrincipalName -eq $userUPN}-Properties extensionAttribute15

$result = [PSCustomObject]@{
    UPN = $userUPN
    ExtensionAttribute15 = $user.extensionAttribute15
}
$results += $result
}

$results | Export-Excel -Path $excelFile2 -WorksheetName "Sheet1" -AutoSize -FreezeTopRow

修复方案 #

1. 解决$userUPN未定义报错 #

AD模块的Filter脚本块不支持直接引用外部变量，改用字符串拼接传递参数，同时将查询字段改为Excel存储的DisplayName：

# 替换原Get-ADUser行
$user = Get-ADUser -Filter "DisplayName -eq '$($users.'Column 6')'" -Properties extensionAttribute15, Enabled

2. 正确识别AD用户禁用状态 #

在Get-ADUser的-Properties参数中添加Enabled属性，然后在结果对象中加入该字段，即可准确获取用户启用/禁用状态：

# 替换结果对象定义
$result = [PSCustomObject]@{
    DisplayName = $users.'Column 6'
    ExtensionAttribute15 = if ($user) { $user.extensionAttribute15 } else { "未找到用户" }
    AccountStatus = if ($user) { if ($user.Enabled) { "启用" } else { "禁用" } } else { "未找到用户" }
}

3. 其他优化点 #

• 移除重复的Excel数据导入操作，保留一次即可
• 添加用户不存在的判断逻辑，避免$user为null时访问属性报错
• 修改循环变量名（原$users与数组名冲突），改为$userRow提升可读性

完整修复后脚本 #

Set-ExecutionPolicy -Scope CurrentUser -ExecutionPolicy Unrestricted -Force
# 若未安装ImportExcel模块，先执行：Install-Module -Name ImportExcel -Scope CurrentUser -Force

# 导入所需模块
Import-Module ImportExcel
Import-Module ActiveDirectory

# 文件路径配置
$inputExcel = "C:\Users\toulayoh\Downloads\testdoc.xlsx"
$outputExcel = "C:\Users\toulayoh\Downloads\outputdata.xlsx"
$worksheetName = "Sheet1"

# 读取Excel数据
$excelData = Import-Excel -Path $inputExcel -WorksheetName $worksheetName

# 存储结果数组
$results = @()

# 遍历每行用户数据
foreach ($userRow in $excelData) {
    $displayName = $userRow.'Column 6'
    $outputColumn = $userRow.'Column 10' # 根据实际需求使用该字段

    # 查询AD用户，获取DisplayName、extensionAttribute15和启用状态
    $adUser = Get-ADUser -Filter "DisplayName -eq '$displayName'" -Properties extensionAttribute15, Enabled -ErrorAction SilentlyContinue

    # 构造结果对象
    $result = [PSCustomObject]@{
        DisplayName = $displayName
        OriginalColumn10 = $outputColumn
        ExtensionAttribute15 = $adUser.extensionAttribute15 ?? "未找到用户/无属性值"
        AccountStatus = if ($adUser) {
            $adUser.Enabled ? "启用" : "禁用"
        } else {
            "AD中未找到该用户"
        }
    }

    $results += $result
}

# 导出结果到Excel
$results | Export-Excel -Path $outputExcel -WorksheetName "Sheet1" -AutoSize -FreezeTopRow

内容的提问来源于stack exchange，提问作者Misaki Takahashi