PostgreSQL analytics for vulnerability metrics and trends

vulnerability-metrics.sql

-- Vulnerability Tracking Metrics Queries
-- Source: https://williamzujkowski.github.io/posts/2025-10-06-automated-security-scanning-pipeline/
-- Purpose: PostgreSQL queries for tracking vulnerability trends and metrics
-- Usage: Run against vulnerability tracking database

-- Total vulnerabilities by severity over last 30 days
SELECT
    severity,
    COUNT(*) as count,
    DATE(scan_date) as date
FROM vulnerabilities
WHERE scan_date > NOW() - INTERVAL '30 days'
GROUP BY severity, DATE(scan_date)
ORDER BY date DESC;

-- Mean time to remediate (MTTR) in days
SELECT
    AVG(EXTRACT(EPOCH FROM (fixed_date - discovered_date)) / 86400) as mttr_days
FROM vulnerabilities
WHERE fixed_date IS NOT NULL;

-- Weekly vulnerability trends by severity
SELECT
    DATE_TRUNC('week', scan_date) as week,
    severity,
    COUNT(*) as count
FROM vulnerabilities
GROUP BY week, severity
ORDER BY week DESC;