Very basic CVE SQL

InsightVM SQL Queries
1

I need a report containing every vulnerability in the environment with 2 columns, 1 with the vulnerability title and 1 with the CVE number. The below SQL gets close:

select
dv.title AS “Title”,
dvr.reference AS “CVE”
FROM
fact_vulnerability fv
JOIN dim_vulnerability dv USING (vulnerability_id)
LEFT JOIN dim_vulnerability_reference dvr ON dv.vulnerability_id = dvr.vulnerability_id AND dvr.source = ‘CVE’::text
WHERE
fv.vulnerability_instances >=1

The issue is that it pulls the CVE numbers only when the source is “CVE” under the reference section, but it doesn’t pull the CVES where the source is “NVD”. How can I fix this? I would also only like to it to show only vulnerabilities that have an instance of at least 1 and to not duplicate any vulnerabilities, I would assume I could use the group by for that? Thanks!

2

select
dv.title AS “Title”,
dvr.reference AS “CVE”
FROM
fact_vulnerability fv
JOIN dim_vulnerability dv USING (vulnerability_id)
LEFT JOIN dim_vulnerability_reference dvr ON dv.vulnerability_id = dvr.vulnerability_id AND dvr.source = ‘CVE’::text
WHERE
(dvr.source = ‘CVE’::text OR dvr.source = ‘NVD’::text)
AND fv.vulnerability_instances >=1
GROUP BY
dv.title,
dvr.reference

select
dv.title AS “Title”,
dvr.reference AS “CVE”
FROM
fact_vulnerability fv
JOIN dim_vulnerability dv USING (vulnerability_id)
LEFT JOIN dim_vulnerability_reference dvr ON dv.vulnerability_id = dvr.vulnerability_id AND dvr.source = ‘CVE’::text
WHERE
dvr.vulnerability_id = ‘CVE’::text
AND fv.vulnerability_instances >=1
GROUP BY
dv.title,
dvr.reference

Both queries above seem to work except they do not give me the vulnerabilities that don’t have a CVE associated (i.e. TLS/SSL Server Supports SSLv2) and I don’t seem to get the Red Hat vulns? (i.e. Red Hat: CVE-2022-1292: c_rehash script allows command injection (Multiple Advisories))

3

It works better if you leave dvr.source out of the join condition and simply use it in the where clause.

select
dv.title AS “Title”,
dvr.reference AS “CVE”
FROM
fact_vulnerability fv
JOIN dim_vulnerability dv USING (vulnerability_id)
LEFT JOIN dim_vulnerability_reference dvr ON dv.vulnerability_id = dvr.vulnerability_id
WHERE
dvr.source = 'CVE'::text OR dvr.source = 'NVD'::text AND
fv.vulnerability_instances >=1
GROUP BY
dv.title,
dvr.reference