We had some Rapid7 Professional Service days left and we decided to have some SQL queries built. The query below is one of them, however it’s not pulling the right solution. For instance one vulnerability title is: TLS Server Supports TLS version 1.1 and the solution its giving in the report is: Microsoft ADV180002 Guidance to mitigate speculative execution side-channel vulnerabilities. Can anyone please help sort the query out below so it will pull the right solution for the vulnerability.
SELECT
da.host_name AS “Name”,
da.ip_address AS “IP”,
da.sites AS “Site(s)”,
dos.description AS “OS”,
dos.version AS “OS Version”,
fa.riskscore::Integer AS “Risk Score”,
fa.malware_kits AS “# Malware Kits”,
fa.exploits AS “# Exploits”,
dv.title AS “Vuln Title”,
dv.cvss_score::Integer AS “CVSS v2”,
dv.cvss_v3_score::Integer AS “CVSS v3”,
CAST(fava.first_discovered as date) AS “Vulnerability Discovered Date”,
round(fava.age_in_days::Integer) AS “Vulnerability Age”,
ds.summary AS “Best Solution”,
ds.url AS “Solution URL”,
ds.solution_type AS “Solution Type”,
dt.tag_name AS “Owner”
FROM dim_asset da
JOIN dim_operating_system dos ON dos.operating_system_id = da.operating_system_id
LEFT OUTER JOIN fact_asset fa ON fa.asset_id = da.asset_id
LEFT OUTER JOIN fact_asset_vulnerability_finding favf ON favf.asset_id = da.asset_id
LEFT OUTER JOIN dim_vulnerability dv ON dv.vulnerability_id = favf.vulnerability_id
LEFT OUTER JOIN fact_asset_vulnerability_age fava ON fava.vulnerability_id = dv.vulnerability_id
LEFT OUTER JOIN dim_asset_vulnerability_best_solution davbb ON davbb.asset_id = da.asset_id
LEFT OUTER JOIN dim_solution ds ON ds.solution_id = davbb.solution_id
LEFT OUTER JOIN dim_tag_asset dta ON dta.asset_id = da.asset_id
LEFT OUTER JOIN dim_tag dt ON dt.tag_id = dta.tag_id WHERE dt.tag_type = ‘OWNER’