Skip to content

Implement cronjob to adjust vulnerability statistics everyday

What does this MR do?

This MR introduces a new cronjob which runs on every midnight to adjust the vulnerability statistics for all projects which have vulnerabilities.

Since calculating the vulnerability statistics for all projects can take quite long, we are scheduling new jobs for each 1000 projects and calculating the vulnerability statistics in those jobs.

The reason for this approach is;

  1. In case if the Sidekiq process crushes(segfault etc.), calculation will fail and the statistics will not be updated
  2. In case if the Sidekiq process receives SIGINT or SIGTERM, it will wait for preconfigured duration to let the jobs finish for graceful shutdown but in case if there are some jobs still running, it just stops the thread without pushing the job back to the queue on Redis. Which means, if we have long running jobs, this can cause problems.
  3. In case if the Sidekiq process receives SIGKILL, there is no way the Sidekiq process can push the job back so the update will be missing for all the remaining projects.

Doing the calculations in separate jobs will mitigate or solve the issues mentioned above.

Note: In the future, we will also use this service class to create historical statistics for the previous day so the endpoint which returns vulnerability statistics will just read records instead of doing heavy calculations with intensive joins on database layer.

For Database Review

The query for gitlab-org/gitlab

EXPLAIN ANALYZE INSERT INTO vulnerability_statistics (project_id, total, info, unknown, low, medium, high, critical, letter_grade, created_at, updated_at) (
            CASE WHEN severity_counts.critical > 0 THEN
            WHEN severity_counts.high > 0
                OR severity_counts.unknown > 0 THEN
            WHEN severity_counts.medium > 0 THEN
            WHEN severity_counts.low > 0 THEN
            END) AS letter_grade,
        now() AS created_at,
        now() AS updated_at
    FROM (
            vulnerabilities.project_id AS project_id,
            COUNT(*) AS total,
            COUNT(*) FILTER (WHERE severity = 1
                AND state IN (1, 4)) AS info,
            COUNT(*) FILTER (WHERE severity = 2
                AND state IN (1, 4)) AS unknown,
            COUNT(*) FILTER (WHERE severity = 4
                AND state IN (1, 4)) AS low,
            COUNT(*) FILTER (WHERE severity = 5
                AND state IN (1, 4)) AS medium,
            COUNT(*) FILTER (WHERE severity = 6
                AND state IN (1, 4)) AS high,
            COUNT(*) FILTER (WHERE severity = 7
                AND state IN (1, 4)) AS critical
            vulnerabilities.project_id = 278964
        GROUP BY
            vulnerabilities.project_id) AS severity_counts)
ON CONFLICT (project_id)
        total =,
        info =,
        unknown = EXCLUDED.unknown,
        low = EXCLUDED.low,
        medium = EXCLUDED.medium,
        high = EXCLUDED.high,
        critical = EXCLUDED.critical,
        letter_grade = EXCLUDED.letter_grade,
        updated_at = EXCLUDED.updated_at

The query plan for on database labs;

1st run(internal only link);

Time: 1.693 s
  - planning: 0.319 ms
  - execution: 1.693 s
    - I/O read: 1.417 s
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 692 (~5.40 MiB) from the buffer pool
  - reads: 987 (~7.70 MiB) from the OS file cache, including disk I/O
  - dirtied: 56 (~448.00 KiB)
  - writes: 0

2nd run(internal only link);

Time: 5.825 ms
  - planning: 0.584 ms
  - execution: 5.241 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 1649 (~12.90 MiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Does this MR meet the acceptance criteria?


Availability and Testing


If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:

  • [-] Label as security and @ mention @gitlab-com/gl-security/appsec
  • [-] The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
  • [-] Security reports checked/validated by a reviewer from the AppSec team
Edited by Mehmet Emin INAC

Merge request reports