This sql is suppose to give my admins ip address, description, category of vuln, proof and remediation information but its not wanting to work with cvss of 8 or greater for both versions of cvssv2 and 3?? I’m not sure why I thought at first it was the categories being filterd but its not providing anything with all included categories but its not working i dont understand why this there have been complaints that vulnerabilities are not working IS SQL becoming deprecated??? Within R7 I think when rapid7 generated this query for me way back when it was gathering the top 10 riskiest assets by risk score i think i took out that line and it worked way back when cause we didnt ask for that. I think it still trying to looking for that. I dont know though i’m no sql queen.
WITH site_info AS (
SELECT site_id, ds.name, asset_id, riskscore, rank() over (partition BY site_id ORDER BY riskscore DESC) AS risk_rank
FROM dim_site_asset
JOIN dim_site ds USING (site_id)
JOIN fact_asset da USING (asset_id)
), riskiest_assets AS (
SELECT site_id, name, asset_id, riskscore
FROM site_info
GROUP BY site_id, name, asset_id, riskscore
Order BY site_id, riskscore DESC
), vulnerability_solutions AS (
SELECT asset_id, avi.vulnerability_id, csv(htmlToText(proof, false)) AS proofs, csv(htmlToText(ds.fix, false)) AS solutions
FROM riskiest_assets
JOIN fact_asset_vulnerability_instance avi USING (asset_id)
JOIN dim_asset_vulnerability_solution avs USING (asset_id, vulnerability_id)
left JOIN dim_solution_highest_supercedence dshs USING (solution_id)
left JOIN dim_solution ds ON ds.solution_id = dshs.superceding_solution_id
GROUP BY asset_id, avi.vulnerability_id
), asset_os_info AS (
SELECT DISTINCT ON (asset_id, operating_system_id) asset_id, operating_system_id, certainty, site_id, ra.name, riskscore,
da.ip_address, da.host_name, dos.description, dos.version
FROM riskiest_assets ra
JOIN dim_asset da USING (asset_id)
JOIN dim_operating_system dos USING (operating_system_id)
JOIN fact_asset_scan_operating_system aos USING (asset_id, operating_system_id)
ORDER BY asset_id, operating_system_id, certainty DESC
),
owner_tags AS (
SELECT asset_id, CSV(tag_name ORDER BY tag_name) AS owner_tags
FROM dim_tag
JOIN dim_scope_tag USING(tag_id)
JOIN dim_tag_asset USING (tag_id)
WHERE tag_type = ‘OWNER’
GROUP BY asset_id
)
SELECT
aoi.name AS “Site Name”, aoi.ip_address AS “IP Address”, aoi.host_name AS “Hostname”, aoi.description AS “OS”,
dv.title AS “Vulnerability Title”, dv.severity AS “Vulnerability Severity”, csv(dvc.category_name) AS “Category Names”,
round(dv.riskscore) AS “Vulnerability Risk Score”, round(dv.cvss_v2_score::numeric, 1) AS “CVSSv2”,round(dv.cvss_score::numeric, 1) AS “CVSSv3”, htmlToText(dv.description) AS “Vulnerability Description”,
proofs AS “Proof”, solutions AS “Solution”, ot.owner_tags AS “Owner”
FROM asset_os_info aoi
JOIN vulnerability_solutions vs USING (asset_id)
JOIN dim_vulnerability dv USING (vulnerability_id)
JOIN dim_vulnerability_category dvc USING (vulnerability_id)
LEFT OUTER JOIN owner_tags ot USING (asset_id)
WHERE dv.cvss_score >= 8 AND dv.cvss_v2_score >= 8
GROUP BY aoi.name, aoi.ip_address, aoi.host_name, aoi.description, aoi.version, aoi.certainty, aoi.riskscore, vulnerability_id,
dv.title, dv.severity, dv.riskscore, dv.cvss_score,dv.cvss_v2_score, dv.description, vs.proofs, ot.owner_tags, vs.solutions
ORDER BY aoi.name, aoi.ip_address, aoi.host_name, aoi.description, aoi.version, aoi.certainty, aoi.riskscore, vulnerability_id