# Very basic CVE SQL

**URL:** <https://discuss.rapid7.com/t/very-basic-cve-sql/34663>\
**Category:** SQL Queries\
**Created:** [March 15, 2024, 12:44pm UTC](https://discuss.rapid7.com/t/very-basic-cve-sql/34663 "2024-03-15T12:44:26Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![henryaj](https://discuss.rapid7.com/letter_avatar_proxy/v4/letter/h/f9ae1b/32.png) [@henryaj](https://discuss.rapid7.com/u/henryaj)\
**Post date:** [March 15, 2024, 12:44pm UTC](https://discuss.rapid7.com/t/very-basic-cve-sql/34663/1 "2024-03-15T12:44:26Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![henryaj](https://discuss.rapid7.com/letter_avatar_proxy/v4/letter/h/f9ae1b/32.png) [@henryaj](https://discuss.rapid7.com/u/henryaj)\
**Post date:** [March 18, 2024, 12:06pm UTC](https://discuss.rapid7.com/t/very-basic-cve-sql/34663/2 "2024-03-18T12:06:39Z")

</div>

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))

---

<div class="post-metadata">

**Author:** ![mmusgrove](https://discuss.rapid7.com/user_avatar/discuss.rapid7.com/mmusgrove/32/2226_2.png) [@mmusgrove](https://discuss.rapid7.com/u/mmusgrove)\
**Post date:** [March 26, 2024, 7:03pm UTC](https://discuss.rapid7.com/t/very-basic-cve-sql/34663/3 "2024-03-26T19:03:55Z")

</div>

> [@henryaj](#):
>
> 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

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

```auto
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

```