Hello everyone,
I’ve retrieved this script from the GitHub library and made some enhancements that are functioning smoothly. This SQL query retrieves every unique instance of a vulnerability ID, IP address, and hostname by utilizing the SELECT DISTINCT function. The initial script provided below operates without any issues, covering details such as vulnerability ID, description, hostname, IP address, CVSS scores, severity rating, solution, vulnerability details, and more.
However, I aim to extend its functionality by incorporating the Proof of Vulnerability, which can be achieved by leveraging the FAVI table in InsightVM SQL queries to list each individual proof. For instance, if an asset has 5 local accounts with passwords set to never expire, currently only one row is generated for that vulnerability for that asset. I intend to modify this so that 5 rows are produced for that vulnerability and asset, each listing a unique proof. Essentially, all information in the 5 rows would remain identical, except for the proof field, which would contain details about each distinct account detected for that vulnerability.
The second query below started to work but I noticed there are proofs related to vulnerability ID’s that do not make sense. So I think I’m close, but the logic is not quite right to get the proofs associated with the right vulnerability ID.
Original SQL Query (with no Proof Column)
SELECT DISTINCT ON (dv.vulnerability_id,da.ip_address,da.host_name)
da.ip_address AS “IP Address”,
da.host_name AS “Hostname”,
dos.description AS “Operating System”,
da.sites AS “Member of Sites”,
dv.severity AS “Severity”,
round(dv.riskscore::numeric, 0) AS “Risk”,
round(dv.cvss_score::numeric, 2) AS “CVSS Score”,
round(dv.cvss_v3_score::numeric, 2) AS “CVSSv3 Score”,
dv.exploits AS “Number of Public Exploits”,
dv.malware_kits AS “Number of Malware Kits Known”,
dv.vulnerability_id AS “Vulnerability ID”,
dv.title AS “Vulnerability Name”,
proofAsText(dv.description) AS “Vulnerability Details”,
proofAsText(favi.proof) AS “Proof”,
fasvf.vulnerability_instances AS “Vulnerability Count on Asset”,
dv.date_published AS “Date Vulnerability First Published”,
CURRENT_DATE - dv.date_published::date AS “Days Since Vulnerability First Published”,
round(fava.age_in_days::numeric, 0) AS “Days Present on Asset”,
fava.first_discovered AS “Date First Seen on Asset”,
fava.most_recently_discovered AS “Date Most Recently Seen on Asset”,
proofAsText(ds.fix) AS “Best Solution”,
ds.estimate AS “Estimated Time To Fix Per Asset”,
proofAsText(ds.solution_type) AS “Solution Type”
FROM dim_asset da
JOIN dim_operating_system dos ON dos.operating_system_id = da.operating_system_id
JOIN dim_asset_vulnerability_best_solution davbs ON davbs.asset_id = da.asset_id
JOIN dim_solution ds ON ds.solution_id = davbs.solution_id
JOIN dim_vulnerability dv ON dv.vulnerability_id = davbs.vulnerability_id
JOIN fact_asset_vulnerability_age fava ON dv.vulnerability_id = fava.vulnerability_id
JOIN fact_asset_vulnerability_finding fasvf ON dv.vulnerability_id = fasvf.vulnerability_id
New SQL Query (Proof column exists. Some rows have columns with looks like is correct proofs, but many not have the correct proof information)
SELECT DISTINCT ON (dv.vulnerability_id,da.ip_address,da.host_name,favi.proof)
da.ip_address AS “IP Address”,
da.host_name AS “Hostname”,
dos.description AS “Operating System”,
da.sites AS “Member of Sites”,
dv.severity AS “Severity”,
round(dv.riskscore::numeric, 0) AS “Risk”,
round(dv.cvss_score::numeric, 2) AS “CVSS Score”,
round(dv.cvss_v3_score::numeric, 2) AS “CVSSv3 Score”,
dv.exploits AS “Number of Public Exploits”,
dv.malware_kits AS “Number of Malware Kits Known”,
dv.vulnerability_id AS “Vulnerability ID”,
dv.title AS “Vulnerability Name”,
proofAsText(dv.description) AS “Vulnerability Details”,
proofAsText(favi.proof) AS “Proof”,
fasvf.vulnerability_instances AS “Vulnerability Count on Asset”,
dv.date_published AS “Date Vulnerability First Published”,
CURRENT_DATE - dv.date_published::date AS “Days Since Vulnerability First Published”,
round(fava.age_in_days::numeric, 0) AS “Days Present on Asset”,
fava.first_discovered AS “Date First Seen on Asset”,
fava.most_recently_discovered AS “Date Most Recently Seen on Asset”,
proofAsText(ds.fix) AS “Best Solution”,
ds.estimate AS “Estimated Time To Fix Per Asset”,
proofAsText(ds.solution_type) AS “Solution Type”
FROM dim_asset da
JOIN dim_operating_system dos ON dos.operating_system_id = da.operating_system_id
JOIN dim_asset_vulnerability_best_solution davbs ON davbs.asset_id = da.asset_id
JOIN dim_solution ds ON ds.solution_id = davbs.solution_id
JOIN dim_vulnerability dv ON dv.vulnerability_id = davbs.vulnerability_id
JOIN fact_asset_vulnerability_age fava ON dv.vulnerability_id = fava.vulnerability_id
JOIN fact_asset_vulnerability_finding fasvf ON dv.vulnerability_id = fasvf.vulnerability_id
JOIN fact_asset_vulnerability_instance favi ON favi.asset_id = da.asset_id