Report by vulnerability age

Hi all

is there kind of a report where we could state open vulnerabilities with a specific date filter?
Our sysadmins would like to know the progress they made based on the existing vulnerabilities, thus any new vulnerabilities should be filtered out, this would give a nicer progress :slight_smile:

Kind regards
Fabian

One option here is to create a custom SQL report where you define a SQL query to pull whatever info you’re looking for. Here’s an example of a SQL query that pulls basic asset, vuln, and solution info related to vulnerability instances.

SELECT da.ip_address, da.host_name, dv.title AS vulnerability_title,
       dv.description AS vulnerability_description, dv.severity_score,
       ds.fix AS solution_fix, ds.summary AS solution_summary
FROM fact_asset_vulnerability_instance favi
JOIN dim_asset da ON favi.asset_id = da.asset_id
JOIN dim_vulnerability dv ON favi.vulnerability_id = dv.vulnerability_id
JOIN dim_asset_vulnerability_best_solution davbs ON davbs.vulnerability_id = favi.vulnerability_id
JOIN dim_solution ds ON ds.solution_id = davbs.solution_id
WHERE favi.date > '2020-09-01 12:00:00.000000'
AND favi.date < '2020-10-01 12:00:00.000000'
GROUP BY da.ip_address, da.host_name, dv.title, dv.description, dv.severity_score, ds.fix, ds.summary

If you look at the WHERE clause towards the bottom, that provides the date filtering that you mentioned. So you can update those dates to be whatever you want, and that should filter for vulnerabilities in that specific time period.

Alright thanks

1 Like

Is there a documentation about the db structure? So that we could easily amend the query with other information. I didn’t find anything about it.

If you’re using the reporting data model (which means you’re just running these queries on the InsightVM console), then we’ve got some docs here.

If you’re connecting to the Data Warehouse (which is separate from the console), you can reference these docs to see the DB structure.

I wanted to mention both since their structures are slightly different and they don’t always line up 1:1 in terms of tables and fields.

Alright thanks! Somehow I didn’t see this part of documentation.

1 Like