Skip to content

Update vulnerability statistics on report ingestion

Mehmet Emin INAC requested to merge 343332_update_vulnerability_statistics_after_ingestion into master

What does this MR do and why?

This MR introduces a new ingestion task to create/update vulnerability statistics record for the project.

What is the Vulnerability::Statistic model?

This model stores the count of vulnerabilities for each severity level(critical, high etc.).

Where is this data used/displayed?

This information is shown to end-users on the "Group-level Security Dashboard".

Screenshot_2021-11-25_at_21.43.55

Why do we use plain SQL queries instead of the ORM abstraction?

Due to the nature of the data we store in the vulnerability_statistics table, we can't use the ORM abstraction as it does not provide incremental updates at the moment(e.g. UPDATE ... SET count = count + 1).

Do we need a changelog entry for this?

No. The feature is behind a feature flag and hasn't been announced yet. Also, we already have a daily cronjob which updates the vulnerability_statistics for all projects so this is not a user-facing change.

Database Review

The plain SQL query introduced by this MR 
INSERT INTO vulnerability_statistics AS target (project_id, "critical", "medium", letter_grade, created_at, updated_at)
    VALUES (19, 1, 1, 4, now(), now())
ON CONFLICT (project_id)
    DO UPDATE SET
        "critical" = TARGET."critical" + 1, "medium" = TARGET."medium" + 1, letter_grade = (
            CASE WHEN TARGET.critical + EXCLUDED.critical > 0 THEN
                4
            WHEN TARGET.high + TARGET.unknown + EXCLUDED.high + EXCLUDED.unknown > 0 THEN
                3
            WHEN TARGET.medium + EXCLUDED.medium > 0 THEN
                2
            WHEN TARGET.low + EXCLUDED.low > 0 THEN
                1
            ELSE
                0
            END), updated_at = now()
Execution plan 
 ModifyTable on public.vulnerability_statistics target  (cost=0.00..0.02 rows=1 width=70) (actual time=0.609..0.610 rows=0 loops=1)
   Buffers: shared hit=47 read=1 dirtied=1
   I/O Timings: read=0.170 write=0.000
   ->  Result  (cost=0.00..0.02 rows=1 width=70) (actual time=0.097..0.098 rows=1 loops=1)
         Buffers: shared hit=14
         I/O Timings: read=0.000 write=0.000

Related to #343332 (closed).

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by Mehmet Emin INAC

Merge request reports