SQL Query - Remediation Timeframe

cdurham-admin_cdurham-admin · September 16, 2020, 9:57pm

I am trying to create a SQL query that would show:

IP Address, Hostname, discovery date of vulnerability, time vulnerability was remediated. Basically looking for an average time a vulnerability is remediated on the assets

holly_wilsey · September 17, 2020, 4:04pm

If you’re currently using the Data Warehouse, I believe you can use the table fact_asset_vulnerability_remediation_date to get the date of remediation, and fact_asset_vulnerability_finding_remediation for the vulnerability’s discovery date. The Data Warehouse schema is here.

If not, I don’t think there’s a way to retrieve the date/time of the vulnerability remediation. There is a table that provides info on the age of vulnerabilities. Here’s a query that pulls some of this info.

SELECT favf.vulnerability_id, favf.asset_id, da.ip_address, da.host_name, fava.first_discovered, fava.age_in_days
FROM fact_asset_vulnerability_finding favf
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

rekladios · February 2, 2023, 9:23pm

Hello Holly,
every time I tried to query
fact_asset_vulnerability_remediation_date or
fact_asset_vulnerability_finding_remediation

I got the Error: relation “fact_asset_vulnerability_remediation_date” does not exist

Any recommendations?

sstevetest · February 3, 2023, 1:26pm

Hello,
Are you running the query against the console or the data warehouse ? I believe both of the tables listed exist only in the datawarehouse, not the Insightvm console database.

rekladios · February 3, 2023, 9:43pm

rekladios · February 3, 2023, 9:45pm

Hello sstevetest,

Please find the screenshot enclosed.

john_hartman · February 3, 2023, 10:37pm

@rekladios what @sstevetest was trying to say is that the tables don’t exist in the console schema. So the error your are looking at is expected.

Those fields only exist within the data warehouse schema as @holly_wilsey mentioned above.

rekladios · February 6, 2023, 1:28pm

Hi John,

How can I use the data warehouse?
Thanks for your help

john_hartman · February 6, 2023, 1:59pm

The data warehouse is a separate server that you would deploy and install postgresql on. You would need to edit the configuration to allow connection from the console with a specified database and username. From there you configure the export from within InsightVM by going to the Administration Page > Data Warehousing (at the bottom).