run2-vulnerability-csv-reporting

name	run2_vulnerability-csv-reporting
description	Generate structured CSV security audit reports from Trivy JSON output with severity filtering, deduplication, and proper field mapping.

Vulnerability CSV Reporting (Round 2)

CSV Schema (exact column names required)

Package,Version,CVE_ID,Severity,CVSS_Score,Fixed_Version,Title,Url

Complete, Production-Ready Script

import json, csv, sys

def get_cvss_score(vuln):
    """Extract CVSS v3 score: NVD > GHSA > RedHat > N/A"""
    cvss = vuln.get('CVSS', {})
    if not isinstance(cvss, dict):
        return 'N/A'
    for source in ['nvd', 'ghsa', 'redhat']:
        entry = cvss.get(source, {})
        if isinstance(entry, dict):
            score = entry.get('V3Score')
            if score is not None and isinstance(score, (int, float)):
                return score
    return 'N/A'

def generate_csv(json_input, csv_output, severities=('HIGH', 'CRITICAL')):
    with open(json_input, encoding='utf-8') as f:
        data = json.load(f)

    records = []
    seen = set()  # Deduplicate by (package, version, CVE)

    for result in data.get('Results', []):
        for vuln in (result.get('Vulnerabilities') or []):  # Handle None
            severity = vuln.get('Severity', '')
            if severity not in severities:
                continue

            key = (vuln.get('PkgName'), vuln.get('InstalledVersion'), vuln.get('VulnerabilityID'))
            if key in seen:
                continue
            seen.add(key)

            records.append({
                'Package': vuln.get('PkgName') or 'N/A',
                'Version': vuln.get('InstalledVersion') or 'N/A',
                'CVE_ID': vuln.get('VulnerabilityID') or 'N/A',
                'Severity': severity,
                'CVSS_Score': get_cvss_score(vuln),
                'Fixed_Version': vuln.get('FixedVersion') or 'N/A',  # handles None AND ""
                'Title': vuln.get('Title') or 'N/A',
                'Url': vuln.get('PrimaryURL') or 'N/A',
            })

    headers = ['Package', 'Version', 'CVE_ID', 'Severity',
               'CVSS_Score', 'Fixed_Version', 'Title', 'Url']

    with open(csv_output, 'w', newline='', encoding='utf-8') as f:
        writer = csv.DictWriter(f, fieldnames=headers)
        writer.writeheader()
        writer.writerows(records)

    print(f"[+] Wrote {len(records)} HIGH/CRITICAL records to {csv_output}")
    return records

if __name__ == '__main__':
    generate_csv('/root/trivy_report.json', '/root/security_audit.csv')

Key Improvements Over Round 1

1. Deduplication: Use a seen set to avoid duplicate (pkg, version, CVE) entries
2. Null safety: result.get('Vulnerabilities') or [] handles None Vulnerabilities
3. Empty string fix: vuln.get('FixedVersion') or 'N/A' catches both None and ""
4. Type safety: Added isinstance checks in CVSS extraction
5. Encoding: Always use encoding='utf-8' for both read and write

Field Mapping Reference

CSV Column	Trivy JSON Field	Notes
Package	PkgName	Package name
Version	InstalledVersion	Installed version string
CVE_ID	VulnerabilityID	CVE/GHSA identifier
Severity	Severity	HIGH or CRITICAL (filtered)
CVSS_Score	CVSS.{source}.V3Score	Via priority extraction
Fixed_Version	FixedVersion	Empty/None → 'N/A'
Title	Title	Short description
Url	PrimaryURL	AVD/NVD reference link