Create SQL Query that lists Severity alongside CVEs, Hosts

InsightVM SQL Queries
, ,
1

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

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

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

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!

2

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:

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'