Hi All,
Once a month, I provide a slide with the Overall Risk Trend chart, highlighting peaks and valleys corresponding to newly published/discovered vulnerabilities and those that have been remediated. Currently, I generate an SQL query report listing all new and remediated vulnerabilities from the past month to analyze the causes of these fluctuations. I then manually add comments to the chart to explain the peaks and valleys. This process is time-consuming.
To streamline this, I propose using the New and Remediated Vulnerabilities SQL report to show the percentages of the types of vulnerabilities contributing to the peaks and valleys. For example, 20% of new vulnerabilities might be related to Microsoft, 10% to Google Chrome, and for remediated vulnerabilities, 45% might be due to Microsoft patching, etc. This approach will save time and provide clearer insights into the data trends.
Here is the query I’m working with now that provides way more information than is needed -
WITH assets_vulns AS (
SELECT
fasv.asset_id,
fasv.vulnerability_id,
baselineComparison (fasv.scan_id, current_scan) AS baseline,
s.baseline_scan,
s.current_scan
FROM
fact_asset_scan_vulnerability_instance fasv
JOIN (
SELECT
asset_id,
previousScan (asset_id) AS baseline_scan,
lastScan (asset_id) AS current_scan
FROM
dim_asset
) s ON s.asset_id = fasv.asset_id
AND (
fasv.scan_id = s.baseline_scan
OR fasv.scan_id = s.current_scan
)
GROUP BY
fasv.asset_id,
fasv.vulnerability_id,
s.baseline_scan,
s.current_scan
HAVING
(
baselineComparison (fasv.scan_id, current_scan) = ‘Same’
)
OR (
baselineComparison (fasv.scan_id, current_scan) = ‘New’
)
OR (
baselineComparison (fasv.scan_id, current_scan) = ‘Old’
)
),
baseline_scan_date as (
SELECT
av.asset_id,
finished
FROM
assets_vulns av
LEFT JOIN dim_scan ds ON ds.scan_id = av.baseline_scan
GROUP BY
av.asset_id,
finished
),
current_scan_date AS (
SELECT
av.asset_id,
finished
FROM
assets_vulns av
LEFT JOIN dim_scan ds ON ds.scan_id = av.current_scan
GROUP BY
av.asset_id,
finished
),
new_vulns AS (
SELECT
av.asset_id,
av.vulnerability_id,
COUNT (av.vulnerability_id) AS new_vulns
FROM
assets_vulns AS av
WHERE
av.baseline = ‘New’
GROUP BY
av.asset_id,
av.vulnerability_id
),
remediated_vulns AS (
SELECT
av.asset_id,
av.vulnerability_id,
COUNT (av.vulnerability_id) AS remediated_vulns
FROM
assets_vulns AS av
WHERE
av.baseline = ‘Old’
GROUP BY
av.asset_id,
av.vulnerability_id
),
vuln_exploit_count AS (
SELECT
CASE
WHEN ec1.vulnerability_id IS NOT NULL THEN ec1.vulnerability_id
ELSE ec2.vulnerability_id
END AS vulnerability_id,
metasploit,
exploitdb
FROM
(
SELECT
av.vulnerability_id,
COUNT(dve.source) AS metasploit
FROM
assets_vulns av
JOIN dim_vulnerability_exploit dve ON av.vulnerability_id = dve.vulnerability_id
WHERE
dve.source = ‘Metasploit’
GROUP BY
av.vulnerability_id
) ec1 FULL
JOIN (
SELECT
av.vulnerability_id,
COUNT(dve.source) AS exploitdb
FROM
assets_vulns av
JOIN dim_vulnerability_exploit dve ON av.vulnerability_id = dve.vulnerability_id
WHERE
dve.source = ‘Exploit DB’
GROUP BY
av.vulnerability_id
) ec2 ON ec2.vulnerability_id = ec1.vulnerability_id
)
SELECT
‘Remediated’ AS status,
da1.ip_address AS ip_address,
da1.host_name AS hostname,
bsd.finished AS baseline_scan_datetime,
csd.finished AS current_scan_datetime,
dv1.vulnerability_id,
dv1.title,
CAST(dv1.cvss_score AS decimal(10, 2)) AS cvss_score,
CAST(dv1.riskscore AS decimal(10, 0)) AS riskscore,
dv1.malware_kits,
CASE
WHEN vec.metasploit IS NULL THEN 0
ELSE vec.metasploit
END AS metasploit,
CASE
WHEN vec.exploitdb IS NULL THEN 0
ELSE vec.exploitdb
END AS exploitdb
FROM
remediated_vulns rv
JOIN dim_asset da1 ON da1.asset_id = rv.asset_id
LEFT JOIN baseline_scan_date bsd ON bsd.asset_id = da1.asset_id
LEFT JOIN current_scan_date csd ON csd.asset_id = da1.asset_id
JOIN dim_vulnerability dv1 ON dv1.vulnerability_id = rv.vulnerability_id
LEFT JOIN vuln_exploit_count vec ON vec.vulnerability_id = rv.vulnerability_id
UNION
ALL
SELECT
‘New’ AS status,
da2.ip_address AS ip_address,
da2.host_name AS hostname,
bsd.finished AS baseline_scan_datetime,
csd.finished AS current_scan_datetime,
dv2.vulnerability_id,
dv2.title,
CAST(dv2.cvss_score AS decimal(10, 2)) AS cvss_score,
CAST(dv2.riskscore AS decimal(10, 0)) AS riskscore,
dv2.malware_kits,
CASE
WHEN vec.metasploit IS NULL THEN 0
ELSE vec.metasploit
END AS metasploit,
CASE
WHEN vec.exploitdb IS NULL THEN 0
ELSE vec.exploitdb
END AS exploitdb
FROM
new_vulns nv
JOIN dim_asset AS da2 ON da2.asset_id = nv.asset_id
LEFT JOIN baseline_scan_date bsd ON bsd.asset_id = da2.asset_id
LEFT JOIN current_scan_date csd ON csd.asset_id = da2.asset_id
JOIN dim_vulnerability dv2 ON dv2.vulnerability_id = nv.vulnerability_id
LEFT JOIN vuln_exploit_count vec ON vec.vulnerability_id = nv.vulnerability_id
ORDER BY
status DESC,
ip_address,
hostname,
title