Add API endpoint for vulnerability history at group level
What does this MR do?
Add the API endpoint for vulnerability history at group level. This provides vulnerability counts per day and per severity for the last 90 days.
TODO:
-
DB Index
current plan:
explain analyze
SELECT CAST(vulnerability_occurrence_pipelines.created_at AS DATE) AS DAY,
"vulnerability_occurrences"."severity",
count(DISTINCT vulnerability_occurrences.id)
FROM "vulnerability_occurrences"
INNER JOIN "vulnerability_occurrence_pipelines" ON "vulnerability_occurrence_pipelines"."occurrence_id" = "vulnerability_occurrences"."id"
WHERE "vulnerability_occurrence_pipelines"."pipeline_id" IN
(SELECT "ci_pipelines"."id"
FROM "ci_pipelines"
WHERE "ci_pipelines"."project_id" IN
(SELECT "projects"."id"
FROM "projects"
INNER JOIN routes rs ON rs.source_id = projects.id
AND rs.source_type = 'Project'
WHERE (rs.path LIKE 'olivier/%'))
AND (EXISTS
(SELECT 1
FROM "vulnerability_occurrence_pipelines"
WHERE (ci_pipelines.id=vulnerability_occurrence_pipelines.pipeline_id)))
AND "ci_pipelines"."status" = 'success')
AND (vulnerability_occurrence_pipelines.created_at >= '2018-08-27')
GROUP BY "day",
"vulnerability_occurrences"."severity"
ORDER BY DAY;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=12105.85..12531.93 rows=18937 width=14) (actual time=1.370..1.505 rows=18 loops=1)
Group Key: ((vulnerability_occurrence_pipelines.created_at)::date), vulnerability_occurrences.severity
-> Sort (cost=12105.85..12153.19 rows=18937 width=14) (actual time=1.335..1.367 rows=390 loops=1)
Sort Key: ((vulnerability_occurrence_pipelines.created_at)::date), vulnerability_occurrences.severity
Sort Method: quicksort Memory: 43kB
-> Nested Loop (cost=15.70..10760.48 rows=18937 width=14) (actual time=0.185..1.235 rows=390 loops=1)
-> Nested Loop (cost=15.28..419.50 rows=18937 width=16) (actual time=0.179..0.361 rows=390 loops=1)
-> HashAggregate (cost=14.86..14.87 rows=1 width=8) (actual time=0.176..0.178 rows=11 loops=1)
Group Key: ci_pipelines.id
-> Nested Loop Semi Join (cost=7.13..14.85 rows=1 width=8) (actual time=0.091..0.171 rows=11 loops=1)
-> Nested Loop (cost=6.71..8.20 rows=15 width=4) (actual time=0.051..0.086 rows=31 loops=1)
Join Filter: (ci_pipelines.project_id = rs.source_id)
-> Unique (cost=6.44..6.45 rows=2 width=8) (actual time=0.045..0.048 rows=5 loops=1)
-> Sort (cost=6.44..6.44 rows=2 width=8) (actual time=0.045..0.047 rows=5 loops=1)
Sort Key: projects.id
Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=0.29..6.43 rows=2 width=8) (actual time=0.017..0.032 rows=5 loops=1)
-> Index Scan using index_routes_on_path on routes rs (cost=0.14..3.09 rows=2 width=4) (actual time=0.008..0.013 rows=5 loops=1)
Index Cond: (((path)::text >= 'olivier/'::text) AND ((path)::text < 'olivier0'::text))
Filter: (((path)::text ~~ 'olivier/%'::text) AND ((source_type)::text = 'Project'::text))
Rows Removed by Filter: 2
-> Index Only Scan using idx_projects_on_repository_storage_last_repository_updated_at on projects (cost=0.14..1.66 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=5)
Index Cond: (id = rs.source_id)
Heap Fetches: 5
-> Index Scan using index_ci_pipelines_on_project_id_and_status_and_config_source on ci_pipelines (cost=0.27..0.76 rows=9 width=8) (actual time=0.004..0.006 rows=6 loops=5)
Index Cond: ((project_id = projects.id) AND ((status)::text = 'success'::text))
-> Index Only Scan using index_vulnerability_occurrence_pipelines_on_pipeline_id on vulnerability_occurrence_pipelines vulnerability_occurrence_pipelines_1 (cost=0.42..242.28 rows=12989 width=4) (actual time=0.002..0.002 rows=0 loops=31)
Index Cond: (pipeline_id = ci_pipelines.id)
Heap Fetches: 11
-> Index Scan using index_vulnerability_occurrence_pipelines_on_pipeline_id on vulnerability_occurrence_pipelines (cost=0.42..274.75 rows=12988 width=20) (actual time=0.003..0.011 rows=35 loops=11)
Index Cond: (pipeline_id = ci_pipelines.id)
Filter: (created_at >= '2018-08-27 00:00:00-04'::timestamp with time zone)
-> Index Scan using vulnerability_occurrences_pkey on vulnerability_occurrences (cost=0.42..0.53 rows=1 width=10) (actual time=0.002..0.002 rows=1 loops=390)
Index Cond: (id = vulnerability_occurrence_pipelines.occurrence_id)
Planning time: 1.891 ms
Execution time: 1.693 ms
(36 rows)What are the relevant issue numbers?
Does this MR meet the acceptance criteria?
-
Changelog entry added, if necessary -
Documentation created/updated -
Tests added for this feature/bug -
Conforms to the code review guidelines -
Conforms to the merge request performance guidelines -
Conforms to the style guides -
Conforms to the database guides -
Link to e2e tests MR added if this MR has Requires e2e tests label. See the Test Planning Process. -
EE specific content should be in the top level /eefolder -
For a paid feature, have we considered GitLab.com plans, how it works for groups, and is there a design for promoting it to users who aren't on the correct plan?
Edited by Kamil Trzciński