SQL query greater/less than

This is probably simple, I am looking to query my vulns with a cvss greater than 8 and a risk score greater than 900. I am stuck on the greater than function in the query.

This is just the canned query example from the Rapid 7 console that I am trying to alter to execute this. If there is something better to utilize I am open to it because I don’t think I am even close.

SELECT da.host_name, da.ip_address, fapr.proof, dos.family, dos.description, dv.title, dv.nexpose_id, (dv.cvss_score::numeric > 2) AS cvss_score,
dv.severity, dvc.category_name, ds.summary
FROM dim_asset da
LEFT JOIN dim_operating_system dos USING(operating_system_id)
JOIN fact_asset_vulnerability_finding favf ON da.asset_id=favf.asset_id
JOIN dim_vulnerability dv ON favf.vulnerability_id=dv.vulnerability_id
JOIN fact_asset_policy_rule fapr ON da.asset_id=fapr.asset_id
JOIN dim_asset_vulnerability_best_solution dvbs ON favf.asset_id=dvbs.asset_id AND favf.vulnerability_id=dvbs.vulnerability_id
JOIN dim_vulnerability_category dvc ON dv.vulnerability_id = dvc.vulnerability_id
JOIN dim_solution ds USING(solution_id)

I’ve got a query saved here that could be a good starting point. It pulls basic host and vulnerability info, and has conditions for the CVSS and risk score that you’re looking for.

select da.ip_address, da.host_name, dv.nexpose_id, dv.title,
       dv.cvss_score, dv.riskscore
from fact_asset_vulnerability_finding favf
join dim_asset da
on favf.asset_id = da.asset_id
join dim_vulnerability dv
on favf.vulnerability_id = dv.vulnerability_id
where dv.cvss_score > 8
and dv.riskscore > 900
group by da.ip_address, da.host_name, dv.nexpose_id, dv.title,
         dv.cvss_score, dv.riskscore

If there’s additional info you’d like to pull in for assets or vulns, you could look at the dim_asset or dim_vulnerability tables to see which fields are available.

1 Like