如何用PowerShell从CSV批量导出AD用户姓名、邮箱及单行ProxyAddresses

问题背景 #

我正在编写PowerShell脚本，需要从仅包含samaccountname列的CSV文件中批量提取指定AD用户的Name、mail，以及将每个ProxyAddresses单独成行的数据。

CSV格式如下：

samaccountname
User1
User2
USer3

现有单用户查询脚本可正常运行，输入samaccountname即可获取所需数据：

$user = Read-Host "prompt samaccountname"
$get = get-aduser -identity $user -properties * -server domain.name
$get | foreach-object { 
    foreach($smtp in $_.proxyaddresses) {
        $_ | select-object Name,mail,@{n="proxyaddress";e={$smtp}}
    }
} | ft -autosize

read-host "press any key to continue"

输出示例：

Name           mail                proxyAddress
----           ----                ------------
User JohnDoe0  User0@asd.com       SMTP:User0@asd.com
User JohnDoe0  User0sec@asd.com    smtp:User0sec@asd.com
User JohnDoe1  User1@asd.com       smtp:User1@asd.com
User JohnDoe1  User1sec2asd.com    SMTP:User1sec2asd.com

错误的批量脚本及问题 #

尝试编写的批量处理脚本如下：

$csv = Import-CSV -path "c:\file.csv" -encoding utf8 -delimiter ";"
$get = get-aduser -identity $user -properties * -server domain.name
$csv | foreach-object {
$get | foreach $smtp in $get.proxyaddress { 
foreach($smtp in $_.proxyaddresses {
    $_ | select-object Name,mail,@{n="proxyaddress";e={$smtp}}
    }
} | ft -autosize

read-host "press any key to continue"

执行后出现以下错误：

Get-ADUser : Variable: '_' found in expression: $_.samaccountname is not defined.
At C:\TEMP\tivit\act\scripts\consulta dados de email\consulta proxyaddress em massa.ps1:14 char:8
+ $get = Get-ADUser -filter {samaccountname -eq $_.samaccountname} -Pro ...
+        ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidArgument: (:) [Get-ADUser], ArgumentException
    + FullyQualifiedErrorId : ActiveDirectoryCmdlet:System.ArgumentException,Microsoft.ActiveDirectory.Management.Commands.GetADUser
 
ForEach-Object : Cannot bind parameter 'RemainingScripts'. Cannot convert the "in" value of type "System.String" to type "System.Management.Automation.ScriptBlock".
At C:\TEMP\tivit\act\scripts\consulta dados de email\consulta proxyaddress em massa.ps1:18 char:8
+ $get | foreach $smtp in $get.proxyaddresses {
+        ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidArgument: (:) [ForEach-Object], ParameterBindingException
    + FullyQualifiedErrorId : CannotConvertArgumentNoMessage,Microsoft.PowerShell.Commands.ForEachObjectCommand

修复后的批量脚本 #

错误原因主要有三点：

• 批量脚本中Get-ADUser调用位置错误，应在遍历CSV的循环内，且需引用当前行的samaccountname
• 循环语法错误：foreach $smtp in ...不符合PowerShell语法规范，正确写法为foreach($smtp in ...)
• 变量引用混乱，未正确区分循环内的当前用户对象

修复后的脚本如下：

# 导入CSV文件，若实际CSV为逗号分隔可省略-delimiter参数
$csv = Import-CSV -path "c:\file.csv" -encoding utf8
$domainServer = "domain.name"

# 遍历CSV中的每个用户条目
$csv | ForEach-Object {
    # 查询当前用户的AD信息，仅加载需要的属性（避免用-properties *，提升性能）
    $adUser = Get-ADUser -Identity $_.samaccountname -Properties Name, mail, proxyAddresses -Server $domainServer
    
    # 遍历每个ProxyAddress，生成单独行的输出数据
    foreach ($smtp in $adUser.proxyAddresses) {
        [PSCustomObject]@{
            Name         = $adUser.Name
            Mail         = $adUser.Mail
            ProxyAddress = $smtp
        }
    }
} | Format-Table -AutoSize

Read-Host "按任意键继续"

关键优化点 #

• 避免使用-properties *，明确指定所需属性（Name, mail, proxyAddresses），减少AD查询负载与耗时
• 使用[PSCustomObject]直接构造输出对象，比Select-Object更直观高效
• 修正循环逻辑，确保每个CSV条目对应一次AD查询，再遍历其ProxyAddresses生成多行数据
• 统一变量命名，避免变量混淆

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