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

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!

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:

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.