Script to compile all of the findings in JSON format from multiple Prowler runs and export to a usable CSV

Get-ProwlerJSONFindingsCSV.ps1

# Script to compile all of the findings in JSON format from multiple Prowler runs and export to a usable CSV
# Note: will also run fine if there's just a single JSON file in the output folder
#
# Hard-coded to grab FAILures only, not PASSes
#
# Make sure that only relevant findings files are being merged to CSV. i.e. remove old output files from previous runs

# Path to the default Prowler output folder
$prowlerReportsFolder = Join-Path -Path $env:USERPROFILE -ChildPath "output"

# Grab a listing of all of the JSON files in the output folder
$jsonFiles = Get-ChildItem -Path $prowlerReportsFolder -Filter *.json

# Temp placeholder hashtable for the grouped JSON findings
$findings = @()

# Go through each file, grab the content, convert it from JSON to PSObjects, then add it to $findings
foreach ($file in $jsonFiles) {
    $findings += $file | Get-Content | ConvertFrom-Json
}

# Grab just FAIL findings, select specific fields and info that we want in our CSV file, then output to CSV file merged-prowler-output.csv in the Prowler Output folder
$findings | Where-Object {$_.status -eq "FAIL"} | Select-Object checkID,severity,CheckTitle,resourcetype,servicename,@{n="CheckType";e={$_.checktype -join "|"}},StatusExtended,Description,Risk,@{n="Categories";e={$_.categories -join "|"}},RelatedUrl,AccountId,ResourceId,FindingUniqueID,@{n="RecommendationText";e={$_.remediation.Recommendation.Text}},@{n="RecommendationURL";e={$_.remediation.Recommendation.Url}},@{n="Compliance";e={$_.compliance | ConvertTo-Json}} | Export-Csv (Join-Path -Path $prowlerReportsFolder -ChildPath "merged-prowler-output.csv") -NoTypeInformation -Force

$findings = $findings | Where-Object {$_.status -eq "FAIL"}
$findingsSummary = @()

foreach ($finding in ($findings | Group-Object -Property CheckID | Sort-Object -Property Count -Descending )) {
    $firstFindingInGroup = $finding.Group | Select-Object -First 1
    
    # Get a list of the standards applicable to this finding
    $standards = $firstFindingInGroup.Compliance | Get-Member -MemberType NoteProperty | select -ExpandProperty Name

    $tempObject = [pscustomobject][ordered]@{
        "ReportFindingID" = ""
        "ProwlerFindingID" = $finding.Name
        "Count" = $finding.Count
        "Severity" = $firstFindingInGroup.Severity
        "Title" = $firstFindingInGroup.CheckTitle
        "Description" = $firstFindingInGroup.Description
        "Risk" = $firstFindingInGroup.Risk
        "Recommendation" = $finding.group.remediation.recommendation.text | Sort-Object -Unique
        "RecommendationURL" = $finding.group.remediation.recommendation.Url | Sort-Object -Unique
        "Compliance" = ($standards | ForEach-Object {"$($_): $($firstFindingInGroup.Compliance.$_ -join ',')"}) -join "`n"
        "Accounts" = ($finding.group.AccountId | Sort-Object -Unique) -join "`n"
        "ResourceIDs" = ($finding.Group.ResourceId | Sort-Object -Unique) -join "`n"
        "ResourceARNs" = ($finding.Group.ResourceArn | Sort-Object -Unique) -join "`n"
    }

    $findingsSummary += $tempObject
    $reportFindingID++
}

$sortedFindings = @()
$sortedFindings += $findingsSummary | Where-Object {$_.Severity -eq "critical"} | Sort-Object -Property Count -Descending
$sortedFindings += $findingsSummary | Where-Object {$_.Severity -eq "high"} | Sort-Object -Property Count -Descending
$sortedFindings += $findingsSummary | Where-Object {$_.Severity -eq "medium"} | Sort-Object -Property Count -Descending
$sortedFindings += $findingsSummary | Where-Object {$_.Severity -eq "low"} | Sort-Object -Property Count -Descending

$findingCount = 1
foreach ($sortedFinding in $sortedFindings) {
    $sortedFinding.ReportFindingID = $findingCount
    $findingCount++
}

$sortedFindings | Export-Csv (Join-Path -Path $prowlerReportsFolder -ChildPath "merged-prowler-output_sorted-findings.csv") -NoTypeInformation -Force