SQL query CVSSv3>=9 AND (CVSSv2>=9 if CVSSv3=NULL)

InsightVM SQL Queries
1

Hello everyone,

I’m a newbie here :slight_smile: I would like to make an SQL query where I would get vulnerabilities with CVSSv3>=9 and also the vulnerabilities that have CVSSv2>=9 but only if there is no CVSSv3 available (if CVSSv3=NULL). So this report would be the addition of two sets of vulnerabilities. The information to be included in the report is at least: hostname, IP, vulnerability title, cvssv3, cvssv2.

Please, could you help me with this one? Thanks in advance!

2

the cvss_score column already does that for you. It uses the v3 score if available, otherwise it grabs the v2 score.

SELECT da.hostname, da.ip_address, dv.title, dv.cvss_score, dv.cvss_v2_score, dv.cvss_v3_score

FROM fact_asset_vulnerability_finding favf

JOIN dim_asset da USING(asset_id)
JOIN dim_vulnerability dv USING(vulnerability_id)

WHERE cvss_score >= 9
3

Hello John!

I did not know that there was a “cvss_v2_score” field! I do not see it in the dimensional data warehouse schema, but I have tried in a query and it works! I thought that “cvss_score” was the cvssv2. This is the DB schema:
https://help.rapid7.com/nexpose/en-us/warehouse/warehouse-schema.html#dim_vulnerability.cvss_confidentiality_impact

So thank you very much!

4

Keep in mind that the dimensional data warehouse schema is specifically for the external data warehouse. It is not to be used within the console reporting section.