如何用PowerShell实现从CSV提取邮箱并单独发送对应数据邮件

问题场景 #

我有一个包含time、remip、user、msg字段的CSV文件，数据如下：

#TYPE System.Management.Automation.PSCustomObject
"time","remip","user","msg"
"11:12:15","124.29.228.164","abc@xyz.com.pk","SSL tunnel shutdown"

"11:12:43","124.29.228.164","efg@gmail.com","SSL tunnel established"

"11:13:25","124.29.228.164","abc@xyz.com.pk","SSL tunnel established"

"11:14:05","202.63.194.8","efg@gmail.com","SSL tunnel established"

当前使用的PowerShell脚本如下：

$Subject = " Alerts $(get-date -Format "dddd MM-dd-yyyy HH:mm")"
$Server = "qs42.xyz.com"
$From = "logalerts@abc.com"
$To = $ID = Import-Csv myfile.txt | Select-Object -ExpandProperty user -Unique

$PWD = ConvertTo-SecureString "test123" -AsPlainText -force
$Cred = New-Object System.Management.Automation.PSCredential("logalerts@abc.com" , $pwd)

$path = "myfile.txt"   
$file = (Import-Csv $path)  | Select-Object -Unique -Property Time , remip , user , Msg

Send-MailMessage -From $from -to $To -Port 587 -SmtpServer $server -Subject $Subject -Credential $cred -UseSsl -Body ($file | Out-String)

当前脚本会将所有数据发送给CSV中user列的所有邮箱，需求是每个邮箱仅收到与自身对应的日志条目。

修改后的脚本 #

$Subject = " Alerts $(get-date -Format "dddd MM-dd-yyyy HH:mm")"
$Server = "qs42.xyz.com"
$From = "logalerts@abc.com"

# 读取CSV数据并去重
$path = "myfile.txt"
$allLogs = Import-Csv $path | Select-Object -Unique -Property Time, remip, user, Msg

# 获取唯一的用户邮箱列表
$uniqueUsers = $allLogs | Select-Object -ExpandProperty user -Unique

# 转换密码并创建凭据
$PWD = ConvertTo-SecureString "test123" -AsPlainText -force
$Cred = New-Object System.Management.Automation.PSCredential("logalerts@abc.com" , $pwd)

# 遍历每个用户，筛选对应日志并发送邮件
foreach ($user in $uniqueUsers) {
    # 筛选当前用户的所有日志条目
    $userLogs = $allLogs | Where-Object { $_.user -eq $user }
    
    # 发送邮件，仅发给当前用户
    Send-MailMessage -From $From `
                     -To $user `
                     -Port 587 `
                     -SmtpServer $Server `
                     -Subject $Subject `
                     -Credential $Cred `
                     -UseSsl `
                     -Body ($userLogs | Out-String)
}

关键修改说明 #

• 一次性读取并处理所有日志数据，避免重复读取CSV文件
• 通过foreach循环遍历每个唯一用户，用Where-Object精准筛选该用户对应的日志条目
• 每次调用Send-MailMessage时，仅指定当前用户为收件人，确保每个邮箱只收到自身相关的日志内容

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