Export Aquasec Trivy JSON report as flat CSV

export-trivy-json-as-csv.txt

trivy image --no-progress --light --vuln-type os -f json -o "trivy-report.json" --severity "UNKNOWN,LOW,MEDIUM,HIGH,CRITICAL" "foo/bar:latest"

cat trivy-report.json

# OUTPUT:
# {
#     "DockerImage": "foo/bar:latest",
#     "Results": [
#         {
#             "Target": "foo/bar:latest (foobar 3.12.1)",
#             "Vulnerabilities": [
#                 {
#                     "VulnerabilityID": "CVE-2021-44228",
#                     "PkgName": "logFooBar",
#                     "InstalledVersion": "2-foobar",
#                     "Severity": "CRITICAL"
#                 }
#             ]
#         }
#     ]
# }

cat trivy-report.json | jq '{ DockerImage: .DockerImage, Results: .Results | [.[]? | { Vulnerabilities: .Vulnerabilities | [.[]? | { PkgName: .PkgName, InstalledVersion: .InstalledVersion, VulnerabilityID: .VulnerabilityID, Severity: .Severity } ] } ] | add | add | .[]? } | { DockerImage: .DockerImage, PkgName: .Results.PkgName, InstalledVersion: .Results.InstalledVersion, VulnerabilityID: .Results.VulnerabilityID, Severity: .Results.Severity }' | jq -s

# OUTPUT:
# [
#   {
#     "DockerImage": "foo/bar:latest",
#     "PkgName": "logFooBar",
#     "InstalledVersion": "2-foobar",
#     "VulnerabilityID": "CVE-2021-44228",
#     "Severity": "CRITICAL"
#   }
# ]

cat trivy-report.json | jq '{ DockerImage: .DockerImage, Results: .Results | [.[]? | { Vulnerabilities: .Vulnerabilities | [.[]? | { PkgName: .PkgName, InstalledVersion: .InstalledVersion, VulnerabilityID: .VulnerabilityID, Severity: .Severity } ] } ] | add | add | .[]? } | { DockerImage: .DockerImage, PkgName: .Results.PkgName, InstalledVersion: .Results.InstalledVersion, VulnerabilityID: .Results.VulnerabilityID, Severity: .Results.Severity }' | jq -s -r '["DockerImage","PkgName","InstalledVersion","VulnerabilityID","Severity"], (.[]? | [.DockerImage, .PkgName, .InstalledVersion, .VulnerabilityID, .Severity]) | @csv'

# OUTPUT:
# "DockerImage","PkgName","InstalledVersion","VulnerabilityID","Severity"
# "foo/bar:latest","logFooBar","2-foobar","CVE-2021-44228","CRITICAL"


# COMMAND OVERVIEW:
# cat trivy-report.json | jq '{ 
#                             DockerImage: .DockerImage, 
#                             Results: .Results | [
#                                                   .[]? | { 
#                                                             Vulnerabilities: .Vulnerabilities | [
#                                                                                                   .[]? | { 
#                                                                                                             PkgName: .PkgName, 
#                                                                                                             InstalledVersion: .InstalledVersion,
#                                                                                                             VulnerabilityID: .VulnerabilityID, 
#                                                                                                             Severity: .Severity 
#                                                                                                          } 
#                                                                                                 ] 
#                                                           } 
#                                                 ] | 
#                                                     add | 
#                                                         add | 
#                                                               .[]? 
#                            } | 
#                            { 
#                                DockerImage: .DockerImage, 
#                                PkgName: .Results.PkgName, 
#                                InstalledVersion: .Results.InstalledVersion,
#                                VulnerabilityID: .Results.VulnerabilityID, 
#                                Severity: .Results.Severity 
#                            }'
#                       | jq -s -r '[
#                                     "DockerImage",
#                                     "PkgName",
#                                     "InstalledVersion",
#                                     "VulnerabilityID",
#                                     "Severity"
#                                   ], 
#                                   (.[]? | [
#                                             .DockerImage, 
#                                             .PkgName, 
#                                             .InstalledVersion, 
#                                             .VulnerabilityID, 
#                                             .Severity
#                                           ]
#                                   ) | @csv'

Hi guys, thanks a lot for the great jq csv export command : while running it, I always get empty DockerImage : any idea on how to fix it ?

I have switched to ArtifactName :

!cat work/*.json | jq '{ ArtifactName: .ArtifactName, Results: .Results | [.[]? | { Vulnerabilities: .Vulnerabilities | [.[]? | { PkgName: .PkgName, InstalledVersion: .InstalledVersion, VulnerabilityID: .VulnerabilityID, Severity: .Severity } ] } ] | add | add | .[]? } | { ArtifactName: .ArtifactName, PkgName: .Results.PkgName, InstalledVersion: .Results.InstalledVersion, VulnerabilityID: .Results.VulnerabilityID, Severity: .Results.Severity }' | jq -s -r '["ArtifactName","PkgName","InstalledVersion","VulnerabilityID","Severity"], (.[]? | [.ArtifactName, .PkgName, .InstalledVersion, .VulnerabilityID, .Severity]) | @csv' > work/report.csv