Report by vulnerability age

InsightVM SQL Queries
1

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

2

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.

1 Like
3

Alright thanks

1 Like
4

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.

5

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.

6

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

1 Like
7

Could you help me how to build a query that exports the vulnerabilities by age of publication, severity and also tells me the affected servers?

Thanks

8

Little late but someone may find it useful. Here’s a data warehouse query I wrote to list vulnerabilities by group(s) with age, severity, risk, etc. as of a specific date, in this case 12/31/22:

SELECT dag.name AS team,
    da.host_name,
    dv.nexpose_id,
    dv.title,
    favfd.asset_id,
    favfd.vulnerability_id,
    favfd.date,
    dv.severity,
    round(dv.risk_score::numeric, 0) AS risk,
    dv.exploit,
    now()::date - favfd.date::date AS days_since_discovery
   FROM dim_asset da
     LEFT JOIN fact_asset_vulnerability_finding_date_2022_12_31 favfd ON favfd.asset_id = da.asset_id
     LEFT JOIN dim_vulnerability dv ON favfd.vulnerability_id = dv.vulnerability_id
     LEFT JOIN dim_asset_group_asset daga ON daga.asset_id = da.asset_id
     LEFT JOIN dim_asset_group dag ON dag.asset_group_id = daga.asset_group_id
  WHERE dag.name ilike '%somegroup' OR dag.name ilike 'someothergroup%';
9

Hi Holly, i really don’t understand well from where obtain all information to obtain the queries, i try to only download a report that includes the Vulnerability Title and Vulnerability Summary and Vulnerability/Solution Famliy