SQL to pull solution with vulnerability proof

InsightVM SQL Queries
1

All, I would like someone’s assistance. I do not see the same topic here, so I decided to create one.

summary:
I want to pull distinct solutions, summary, published_date, and vulnerability proof, along with affected assets and some other pieces of information. So far, everything works fine. Still, I run into trouble when I include the proof and key from fact_asset_scan_vulnerability_instance table. Can anyone please help? Thank you all in advance.

Here is the query I am currently using

SELECT DISTINCT proofAsText(ds.fix) AS “Solution Steps”, ds.summary AS “Solution Summary”, ds.solution_type AS “Solution Type”, dv.severity AS “Severity”, COUNT(DISTINCT da.asset_id) AS “Affected Assets”, proofAsText(pf.proof), proofAsText(pf.key), dv.date_published AS “Vulnerability Published Date”, round(va.age_in_days) AS “Vulnerability Age”, va.first_discovered, proofAsText(ds.url) AS “Additional Help Link”, proofAsText(ds.additional_data) AS “Additional Data”, pf.date FROM dim_asset_vulnerability_best_solution davbs JOIN dim_solution ds ON davbs.solution_id = ds.solution_id JOIN dim_vulnerability dv ON davbs.vulnerability_id = dv.vulnerability_id JOIN dim_asset da ON davbs.asset_id = da.asset_id JOIN dim_operating_system dos ON da.operating_system_id = dos.operating_system_id JOIN fact_asset_scan_vulnerability_instance pf ON dv.vulnerability_id = pf.vulnerability_id JOIN fact_asset_vulnerability_age va ON dv.vulnerability_id = va.vulnerability_id GROUP BY ds.summary, dv.severity, ds.solution_type, ds.fix, pf.proof, pf.key,pf.date,dv.date_published,ds.url,ds.additional_data,ds.url,va.age_in_days,va.first_discovered

2

select a.asset_id, c.nexpose_id as vuln_id, a.proof, a.key, d.name as service, a.port, e.name as protocol, max(f.nexpose_id) as sou_id
from fact_asset_vulnerability_instance a
join dim_vulnerability c on c.vulnerability_id = a.vulnerability_id
join dim_service d on d.service_id = a.service_id
join dim_protocol e on e.protocol_id = a.protocol_id
left join dim_asset_vulnerability_best_solution b on a.asset_id = b.asset_id and a.vulnerability_id = b.vulnerability_id
left join dim_solution f on f.solution_id = b.solution_id
group by a.asset_id, c.nexpose_id, a.proof, a.key, d.name, a.port, e.name
order by a.asset_id, c.nexpose_id, a.proof

3

Thank you for getting back to me this is something similar I was looking for, however it will not work with what I am to use it for. I ended up using the built-in report