Grouping multiple lines in csv to one line and storing to variable

Question

Most likely very simple but I am new to this and not really sure how to search for what I am trying to do. So far I have the file imported and sorted but I am not sure how to combine the multiple failures for the customer and store them. I just have them writing to host to make sure I was capturing the failures.

CSV Example

Result,SystemID,Customer
SUCCESS,123,CustomerA
FAIL,456,CustomerB
FAIL,456,CustomerB
FAIL,789,CustomerB
SUCCESS,111,CustomerC
FAIL,321,CustomerD

I need to store the failures to be emailed separately for each customer.
Email Customer B
System/s (456) & (789) have failed
Email Customer D
System/s (321) have failed

Write-Host "CSVs: $CSVs"
foreach ($CSV in $CSVs) {
    $data = Import-Csv $CSV | Sort-Object Customer
    foreach ($line in $data) {
        if ($line.Result -eq "fail") {
            Write-Host  " Action should be taken for $($line.customer)" -ForegroundColor Green -BackgroundColor Black
            Write-Host "$($line.Result) on SystemID's $($line.SystemID)"
        }     
    }
}

Answer 1

You can use Group-Object and Where-Object to group your data based on Customer failures.

foreach ($CSV in $CSVs) {
    $data = Import-Csv $CSV | Sort-Object Customer
    $FailGroups = $data | Where Result -eq 'FAIL' | Group-Object Customer
    foreach ($FailGroup in $FailGroups) {
        $FailedCustomer = $FailGroup.Name
        $FailedSystems = ($FailGroup.Group.SystemID | Foreach-Object { "($_)" }) -join ' & '
        $MailMessage = "Systems/s {0} have failed" -f $FailedSystems
        $FailedCustomer # Outputting customers with failures
        $MailMessage # Outputting failed systems in formatted message
        # Below is a Custom Object that can be exported to CSV or retrieved later
        # [pscustomobject]@{'Customer' = $FailedCustomer; 'Message' = $MailMessage}
    }
}

You can now use $FailedCustomer and $MailMessage to craft your email message at the end of the inner foreach iteration.

Since Group-Object returns a GroupInfo object, you will need to access its Name property for the property values used to determine the grouping. The Group property contains the CSV rows (objects) that are included in the grouping.

---

If you go the custom object route, you can store all of the outputs into a single variable as an array. You can then export the array into a CSV file or iterate through the array for your email tasks.

$output = foreach ($CSV in $CSVs) {
    $data = Import-Csv $CSV | Sort-Object Customer
    $FailGroups = $data | Where Result -eq 'FAIL' | Group-Object Customer
    foreach ($FailGroup in $FailGroups) {
        $FailedCustomer = $FailGroup.Name
        $FailedSystems = ($FailGroup.Group.SystemID | Foreach-Object { "($_)" }) -join ' & '
        $MailMessage = "Systems/s {0} have failed" -f $FailedSystems
        [pscustomobject]@{'Customer' = $FailedCustomer; 'Message' = $MailMessage}
    }
}
# Export to CSV
$output | Export-Csv -Path C:\Path\FailedCustomers.csv -NoType
# Email customers example
foreach ($customer in $output) {
    $MailParams = @{
        'SmtpServer' = 'smtp.domain.com'
        'Subject' = 'Failures'
        'To' = $customer.Customer
        'From' = [email protected]
        'Body' = $customer.Message
    }
    Send-MailMessage @MailParams
}