使用Powershell对CSV数据进行分组和通过电子邮件发送

Question

我有一个CSV文件，其中列出了员工的电子邮件地址和每个事务的一些随机信息，我想单独通过电子邮件发送给他们。当前格式如下：

| Email        | risk_level | breach_title | breach_desc | breach_date
| ------------ | -----------| -------------| ------------|------------
| jeff@abc.com | High       | Hack 1       | Bad things  | 1/1/2021   
| jeff@abc.com | Medium     | Hack 2       | Not so bad  | 1/2/2021
| bill@abc.com | High       | Hack 1       | Bad things  | 1/1/2021

jeff@abc.com会收到一封正文为：

risk_level: High
breach_title: Hack 1
breach_desc: bad things
breach_date: 1/1/2021

risk_level: Medium
breach_title: Hack 2
breach_description: not so bad
breach_date: 1/2/2021

bill@abc.com会收到一封正文为：

risk_level: High
breach_title: Hack 1
breach_description: bad things
breach_date: 1/1/2021

下面是我尝试过但最终返回相同结果集的结果

$csv = Import-csv -Path C:\Users\jeffr\Downloads\eecpro_results.csv | Group-Object email

# Create a bucket
$email;
$breach_data = @()

$breaches = ForEach($u in $csv) {

    # Store the breach data
    $bd = $u.Group
    
    foreach ($b in $bd){
        $row = New-Object PSObject
        $row | Add-Member -MemberType NoteProperty -Name "Email" -Value $u.Name
        $row | Add-Member -MemberType NoteProperty -Name "Risk Level" -Value $b.risk_level
        $row | Add-Member -MemberType NoteProperty -Name "Breach Title" -Value $b.breach_title
        $row | Add-Member -MemberType NoteProperty -Name "Breach Description" -Value $b.breach_description
        $row | Add-Member -MemberType NoteProperty -Name "Breach Classification" -Value $b.breach_data_class
        $breach_data += $row
    }

}

# Export to CSV
$data | Export-Csv C:\Rec\test.csv -NoTypeInformation

目标将是某种类型的电子邮件合并，按电子邮件地址对所有记录进行分组。

Answer 1

在遍历列表之前，您希望按电子邮件地址分组。例如：

# Group the users first:
$Grouped = $csv | Group email

$Emails = Foreach ($user in $Grouped) {
 
  # Add "Private: " to the beginning of each entry
  $PrivateData = $user.Group.'Breach Description' | %{"Private: $_"}

  # Make a multi-line string out of the list of descriptions
  $user | select Name,
    @{l='Data';e={($PrivateData | Format-List | Out-String | Sort).Trim()}}
    # Other fields etc...

}

# Example of sending the emails
$Emails | Send-MailMessage -From 'me@abc.com' -Subject 'Breach' -To $_.Name -Body $_.Data -SmtpServer mail.abc.com

结果电子邮件如下：

Subject : Breach
To      : jeff@abc.com
Body    : Private: private data
          Private: some of jeffs different data

Subject : Breach
To      : bill@abc.com
Body    : Private: bills private message