Total vs Remediated for History

InsightVM SQL Queries
1

Hi,

Any idea/query that you can help me with on how I can retrieve Total vs Remediated Vulns for certain period from the history. Example: I need Total Vulns & Remediated Vulns for specific asset group in the month Jan 2023.

1 Like
2

@john_hartman Can you please help me with this?

3

This would only be achievable in the Data Warehouse. The console schema does not track remediations currently.

4

Can you give me few ideas/tables&columns I need to consider to get the historical data.

I’m getting confused between periods(day) & fact_all_date & fact_asset_vulnerability_remediation_date

5

maybe this will help, this is what i wrote for count of total vulns remediated on workstations:
SELECT da.os_description, dv.title, count (vrd.asset_id) from fact_asset_vulnerability_remediation_date vrd JOIN dim_vulnerability dv using (vulnerability_id) JOIN dim_asset da using (asset_id) WHERE da.os_description ~* ‘Windows 10’ and (vrd.day between ‘2023-06-01’ and ‘2023-06-30’) GROUP BY da.os_description,dv.title

6

@mseiler I have similar requirement but I need
Total number of vulnerabilities, New vulnerabilities and Remediated vulnerabilities for each month from Jan 2023 to July 2023.
Is it possible to get these numbers?

7

here is what I use for the data warehouse.

COUNT OF TOTAL VULNS
Select assets, vulnerabilities, malware_kits, exploits, round( risk_score::numeric, 0) from fact_all;

ALL NEW DISCOVERED VULNS FOR LAST WEEK
SELECT fv.affected_assets, fv.first_discovered, dv.title, dv.date_published, GREATEST(cvss_score,cvss_v3_score) AS “CVSS”, dv.exploits FROM dim_vulnerability dv JOIN fact_vulnerability fv USING(vulnerability_id) WHERE fv.first_discovered > now()::date - 8 ORDER BY fv.first_discovered DESC;

Remediated vulns - see able query and change the date as needed.

8

@mseiler Thanks for the help.
I will test and let you know.

9

I still don’t get the remediated vulns