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.
1SELECT 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",23severity_score AS "Severity Score", severity AS "Severity", round(riskscore::numeric, 0) AS "Risk score",45round(cvss_score::numeric, 2) AS "CVSS Score", exploits AS Exploits, malware_kits AS "Malware Kits"67FROM dim_vulnerability89JOIN fact_asset_vulnerability_finding favf USING (vulnerability_id)1011JOIN dim_asset da USING (asset_id)1213JOIN fact_asset_scan_vulnerability_instance fasvi USING (vulnerability_id)1415WHERE now() - date_published < INTERVAL '2 months'1617ORDER BY da.ip_address ASC