# Create SQL Query that lists Severity alongside CVEs, Hosts

**URL:** <https://discuss.rapid7.com/t/create-sql-query-that-lists-severity-alongside-cves-hosts/30096>\
**Category:** SQL Queries\
**Tags:** InsightVM, sql, queries\
**Created:** [November 21, 2023, 11:07pm UTC](https://discuss.rapid7.com/t/create-sql-query-that-lists-severity-alongside-cves-hosts/30096 "2023-11-21T23:07:12Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![czappe](https://discuss.rapid7.com/user_avatar/discuss.rapid7.com/czappe/32/2068_2.png) [@czappe](https://discuss.rapid7.com/u/czappe)\
**Post date:** [November 21, 2023, 11:07pm UTC](https://discuss.rapid7.com/t/create-sql-query-that-lists-severity-alongside-cves-hosts/30096/1 "2023-11-21T23:07:12Z")

</div>

I’m trying to build a basic SQL query to manually import vulnerabilities into Automox based on the example they provide here: [Rapid7 - Vulnerability Report Export](https://help.automox.com/hc/en-us/articles/8273475853076-Exporting-Vulnerability-Scanner-Reports#ExportingVulnerabilityScannerReports-CreatingaRapid7vulnerabilityreport)

I’m hitting a snag when I attempt to add the CVE severity (medium, high, critical) to the query as in below:

```auto
select da.host_name as Host, dvf.reference as CVE, dv.severity as “Severity”
FROM fact_asset_vulnerability_finding favf
JOIN dim_vulnerability_reference dvf ON dvf.vulnerability_id = favf.vulnerability_id
JOIN dim_asset da ON da.asset_id = favf.asset_id
WHERE dvf.source = 'CVE'

```

Validation returns the following error

```auto
Error:missing FROM-clause entry for table "dv"

```

If I remove “dv.severity as “Severity”” from the end of the first line, I am able to successfully pull 2/3 of the information I need. What would the correct formatting be to include severity levels in this query? Thanks!

---

<div class="post-metadata">

**Author:** ![czappe](https://discuss.rapid7.com/user_avatar/discuss.rapid7.com/czappe/32/2068_2.png) [@czappe](https://discuss.rapid7.com/u/czappe)\
**Post date:** [November 22, 2023, 6:38pm UTC](https://discuss.rapid7.com/t/create-sql-query-that-lists-severity-alongside-cves-hosts/30096/2 "2023-11-22T18:38:08Z")

</div>

I took this same question to the Automox support team and received the following query in response that generates the correct report containing Host, CVE, and Severity data. The additional JOIN line is what did it:

```auto
select da.host_name as Host, dvf.reference as CVE, dv.severity as “Severity”
FROM fact_asset_vulnerability_finding favf
JOIN dim_vulnerability_reference dvf ON dvf.vulnerability_id = favf.vulnerability_id
JOIN dim_vulnerability dv ON favf.vulnerability_id = dv.vulnerability_id
JOIN dim_asset da ON da.asset_id = favf.asset_id
WHERE dvf.source = 'CVE'

```