Improve how we count vulnerabilities for history on group security dashboard
Problem to solve
We currently count the number of unique vulnerabilities reported each day (by looking at pipelines) but this doesn't provide reliable values in several cases:
- when no pipeline was run for a day there is no report available so no data: #9237
- when all vulnerabilities are fixed there is no record to count so no data: #9069
Delaney, Development Team Lead, https://design.gitlab.com/research/personas#persona-delaney
Sam, Security Analyst, https://design.gitlab.com/research/personas#persona-sam
Instead of counting each pipeline's result we should provide the known state at that given date. The assumption that vulnerabilities stay constant until there is a newer report (thus the records are updated) is the closest to the reality.
This is achievable with a stateful model that provides
fixed_at timestamps. Such a model allows us to easily fetch the number of added and fixed vulnerabilities by day.
For given history period, provide:
- number of added vulnerabilities by day
- number of fixed vulnerabilities by day
- cumulative number of active vulnerabilities by day
Here is a rough example:
Get number of open vulnerabilities prior to given period:
SELECT COUNT(id) as previous_total FROM vulnerability_occurrences WHERE created_at < date_trunc('day', NOW() - interval '6 months');
Get number of added vulnerabilities by day for given period:
SELECT date_trunc('day', created_at) as day, COUNT(id) FROM vulnerability_occurrences WHERE created_at >= date_trunc('day', NOW() - interval '6 months') GROUP by day;
Get number of fixed vulnerabilities by day for given period:
SELECT date_trunc('day', fixed_at) as day, COUNT(id) FROM vulnerability_occurrences WHERE fixed_at >= date_trunc('day', NOW() - interval '6 months') GROUP by day;
Let's say first query returns 50, this gives us the following aggregated results:
Having the API returning such data would allow frontend to plot a nice chart with meaningful values independently from pipelines execution.
NOTE: this requires to update the data model first: #9524 (closed)
What does success look like, and how can we measure that?
Reliable history chart on the Group Security Dashboard.