SQL example - recently published vulnerabilities

Depending on how you run your program, you may have a need to report on new vulnerabilities, such as anything that came out in the last 2 months. One of the reasons may be that you have an SLA around remediation of new vulnerabilities. This example query provides that information.

You can adjust 2 months to the most appropriate interval for you.


sql
1
SELECT da.host_name AS "Host Name", da.ip_address AS "IP Address", nexpose_id AS "Nexpose ID", title AS "Vulnerability Title", proofAsText(Description) AS Description, fasvi.date AS "Date Discovered", date_published AS "Date Published",
2
3
severity_score AS "Severity Score", severity AS "Severity", round(riskscore::numeric, 0) AS "Risk score",
4
5
round(cvss_score::numeric, 2) AS "CVSS Score", exploits AS Exploits, malware_kits AS "Malware Kits"
6
7
FROM dim_vulnerability
8
9
JOIN fact_asset_vulnerability_finding favf USING (vulnerability_id)
10
11
JOIN dim_asset da USING (asset_id)
12
13
JOIN fact_asset_scan_vulnerability_instance fasvi USING (vulnerability_id)
14
15
WHERE now() - date_published < INTERVAL '2 months'
16
17
ORDER BY da.ip_address ASC