Report Building SQL Query. Identify machines that have vulnerabilities and also those with vulnerabilities remediated

InsightVM SQL Queries
1

This my first foray into SQL so bear with me.

I am looking to create a report based on vulnerability that tells me what hosts have been patched and those that are not patched how long they have been unpatched.

This is the query that I stitched together:
> SELECT favf.vulnerability_id, favf.asset_id, da.ip_address, da.host_name, fava.first_discovered, fava.age_in_days, dv.title, favrd.date
> FROM fact_asset_vulnerability_finding favf
> JOIN fact_asset_vulnerability_remediation_date favrd ON favf.asset_id = da.asset_id
> JOIN dim_asset da ON favf.asset_id = da.asset_id
> JOIN dim_vulnerability dv ON favf.vulnerability_id = dv.vulnerability_id
> JOIN fact_asset_vulnerability_age fava ON favf.vulnerability_id = fava.vulnerability_id
> WHERE dv.title LIKE ‘%CVE-2020-16947%’

its failed with the following error:
Error: relation “fact_asset_vulnerability_remediation_date” does not exist
Character: 185

More than anything I am intrigued by fact_asset_vulnerability_remediation_date not existing. Am I not calling upon it right?

2

It sounds like you’re running your query from the InsightVM console here, in which case the fact_asset_vulnerability_remediation_date table doesn’t exist. This table does exist in the data warehouse, which is an external warehouse where you can export your InsightVM data to get richer data for things like reporting.

If you have queries that you want to run from the console itself, then you can reference the reporting data model here and here to see what tables + fields exist.

If you do want to setup the data warehouse and run queries there (where the table you mentioned does exist), we’ve got some info on how to get started. We also have the schema for the data warehouse if you want to further delve into its tables + fields.

So in short, there’s the reporting data model vs. the data warehouse model, and they don’t always line up 1:1 in terms of table names, columns, and relationships.