I need to create a report to meet regulatory requirements that can provide the following information:
• Time period: 12 month period in the past.
• Vulnerability Counts, broken down by CVSS v3 Low, Med, High, Critical severities:
○ Detected vulnerabilities.
○ Remediated vulnerabilities.
○ Open vulnerabilities.
• The above counts should include vulnerabilities on assets that have been removed.
• Asset Scoping to be determined by a regex pattern matching against asset names.
• Notes
○ Our Console has been configured to use Active Risk strategy.
I reviewed this SQL Query Export, but it looks to use a baseline scan rather than a historical time period.
I’ve explored various Dashboard cards but the appropriate cards provide counts for just a few weeks.
It seems that the Query Builder doesn’t have the capability to report on historical counts, especially for deleted assets.
I’ve explored the Console-generated reports, as well, but I can’t find a report that can be configured to the above requirements.
What about changing up that query a tad to fit your needs, i took a stab at it, i had something similar… YOu will need to adjust your REGEX pattern to match what you need, or create and asset group based on that regex in the Asset name filter…
WITH
-- Identify vulnerabilities detected within the past 12 months
vulnerabilities_detected AS (
SELECT
fasv.asset_id,
fasv.vulnerability_id,
dv.cvss_v3_score,
dv.title,
dv.riskscore,
dv.malware_kits,
ds.finished AS scan_date
FROM fact_asset_scan_vulnerability_instance fasv
JOIN dim_vulnerability dv ON fasv.vulnerability_id = dv.vulnerability_id
JOIN dim_scan ds ON fasv.scan_id = ds.scan_id
WHERE ds.finished BETWEEN CURRENT_DATE - INTERVAL '12 months' AND CURRENT_DATE
),
-- Identify remediated vulnerabilities (found in past scans but not present in recent scans)
remediated_vulns AS (
SELECT
vd.asset_id,
vd.vulnerability_id
FROM vulnerabilities_detected vd
LEFT JOIN fact_asset_scan_vulnerability_instance fasv
ON vd.asset_id = fasv.asset_id
AND vd.vulnerability_id = fasv.vulnerability_id
WHERE fasv.asset_id IS NULL -- If not found in the latest scans, it's remediated
),
-- Count open vulnerabilities (still present in latest scans)
open_vulns AS (
SELECT
vd.asset_id,
vd.vulnerability_id
FROM vulnerabilities_detected vd
JOIN fact_asset_scan_vulnerability_instance fasv
ON vd.asset_id = fasv.asset_id
AND vd.vulnerability_id = fasv.vulnerability_id
)
-- Final Report
SELECT
da.host_name AS "Host Name",
da.ip_address AS "IP Address",
ds.name AS "Site Name",
dos.description AS "OS Name",
COUNT(CASE WHEN vd.cvss_v3_score >= 9.0 THEN 1 END) AS "Critical Detected",
COUNT(CASE WHEN vd.cvss_v3_score BETWEEN 7.0 AND 8.9 THEN 1 END) AS "High Detected",
COUNT(CASE WHEN vd.cvss_v3_score BETWEEN 4.0 AND 6.9 THEN 1 END) AS "Medium Detected",
COUNT(CASE WHEN vd.cvss_v3_score BETWEEN 0.1 AND 3.9 THEN 1 END) AS "Low Detected",
COUNT(CASE WHEN rv.vulnerability_id IS NOT NULL AND vd.cvss_v3_score >= 9.0 THEN 1 END) AS "Critical Remediated",
COUNT(CASE WHEN rv.vulnerability_id IS NOT NULL AND vd.cvss_v3_score BETWEEN 7.0 AND 8.9 THEN 1 END) AS "High Remediated",
COUNT(CASE WHEN rv.vulnerability_id IS NOT NULL AND vd.cvss_v3_score BETWEEN 4.0 AND 6.9 THEN 1 END) AS "Medium Remediated",
COUNT(CASE WHEN rv.vulnerability_id IS NOT NULL AND vd.cvss_v3_score BETWEEN 0.1 AND 3.9 THEN 1 END) AS "Low Remediated",
COUNT(CASE WHEN ov.vulnerability_id IS NOT NULL AND vd.cvss_v3_score >= 9.0 THEN 1 END) AS "Critical Open",
COUNT(CASE WHEN ov.vulnerability_id IS NOT NULL AND vd.cvss_v3_score BETWEEN 7.0 AND 8.9 THEN 1 END) AS "High Open",
COUNT(CASE WHEN ov.vulnerability_id IS NOT NULL AND vd.cvss_v3_score BETWEEN 4.0 AND 6.9 THEN 1 END) AS "Medium Open",
COUNT(CASE WHEN ov.vulnerability_id IS NOT NULL AND vd.cvss_v3_score BETWEEN 0.1 AND 3.9 THEN 1 END) AS "Low Open"
FROM vulnerabilities_detected vd
JOIN dim_asset da USING (asset_id)
JOIN dim_operating_system dos USING (operating_system_id)
JOIN dim_site_asset dsa USING (asset_id)
JOIN dim_site ds USING (site_id)
LEFT JOIN remediated_vulns rv USING (asset_id, vulnerability_id)
LEFT JOIN open_vulns ov USING (asset_id, vulnerability_id)
WHERE da.host_name ~* 'YOUR_REGEX_PATTERN_HERE' -- Apply regex pattern match on asset name
GROUP BY da.host_name, da.ip_address, ds.name, dos.description
ORDER BY ds.name, da.ip_address;
