SQL that I have had for years now is no longer working!

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
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

I don’t know what could be going wrong because your query is working for me.

@mmusgrove man i dont know what gives then. Sometimes it will work for me then when i’m ready to release it to the admin I run it and i get blank results in the report. So wierd.