Hello everyone,
I’m a newbie here 
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!
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
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!
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.
How about if i have the same scenario but i want all 45 days old vulnerabilities with 8 or greater. It takes the cvssv2 if there is NO v3 but the cvssv2 is not 45 days old when it replaces it??
We use this: for Datawarehouse not internal to the GUI reporting. We are using pgadmin so its little different then the regular sql internal reporting sql inside R7
JOIN dim_vulnerability USING (vulnerability_id)
WHERE
–date < (NOW() - (45 * INTERVAL ‘1 day’))
–AND
COALESCE ( cvss_v3_score, cvss_score ) >= 8