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
This would only be achievable in the Data Warehouse. The console schema does not track remediations currently.
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
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
@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?
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.