Exclude no longer detected vulnerabilities from inventory counts
What does this MR do and why?
Exclude no longer detected vulnerabilities from inventory counts
The recompute in AdjustmentService only filtered on state, so vulnerabilities marked resolved_on_default_branch=true (no longer detected by the latest scan) were still counted by Security Inventory. This made its counts different from the Security Dashboard.
Next
- A follow-up change will close the live-update path so counts stay correct between recomputes.
- The feature flag will be used to enable the new behaviour in sync across the scheduled recompute and live-update path AND to show an alert message in UI Security Inventory vulnerability counts should exclude "no longer detected" vulnerabilities
References
#600455 - issue
!239298 (merged) - live update path
Query
Note, I had to rely on ids provided by LLM based on our knowledge base, in particular traversal ids. If anyone can suggest appropriate values, and there's a need o re-run it please comment. With that seems OK, no performance degradation.
More thorough analysis which closer mimics real life scenario: !238773 (comment 3432567114)
Performance Details
New query
WITH new_values AS (
SELECT
project_ids.project_id AS project_id,
project_attributes.archived AS archived,
project_attributes.traversal_ids AS traversal_ids,
COALESCE(severity_counts.total, 0) AS total,
COALESCE(severity_counts.info, 0) AS info,
COALESCE(severity_counts.unknown, 0) AS unknown,
COALESCE(severity_counts.low, 0) AS low,
COALESCE(severity_counts.medium, 0) AS medium,
COALESCE(severity_counts.high, 0) AS high,
COALESCE(severity_counts.critical, 0) AS critical,
(
CASE
WHEN severity_counts.critical > 0 THEN
4
WHEN severity_counts.high > 0 OR severity_counts.unknown > 0 THEN
3
WHEN severity_counts.medium > 0 THEN
2
WHEN severity_counts.low > 0 THEN
1
ELSE
0
END
) AS letter_grade,
now() AS created_at,
now() AS updated_at
FROM unnest(ARRAY[278964]) project_ids(project_id)
JOIN (VALUES (278964, FALSE, ARRAY[9970, 15846663]::bigint[])) project_attributes(project_id, archived, traversal_ids)
ON project_attributes.project_id = project_ids.project_id
LEFT OUTER JOIN(
SELECT
vulnerability_reads.project_id AS project_id,
COUNT(*) AS total,
COUNT(*) FILTER (WHERE severity = 1) as info,
COUNT(*) FILTER (WHERE severity = 2) as unknown,
COUNT(*) FILTER (WHERE severity = 4) as low,
COUNT(*) FILTER (WHERE severity = 5) as medium,
COUNT(*) FILTER (WHERE severity = 6) as high,
COUNT(*) FILTER (WHERE severity = 7) as critical
FROM vulnerability_reads
WHERE
vulnerability_reads.project_id IN (278964) AND
vulnerability_reads.state IN (1, 4) AND
vulnerability_reads.resolved_on_default_branch = FALSE AND
(vulnerability_reads.security_project_tracked_context_id IS NULL
OR vulnerability_reads.security_project_tracked_context_id IN (278964))
GROUP BY vulnerability_reads.project_id
) AS severity_counts ON severity_counts.project_id = project_ids.project_id
), old_values AS (
SELECT
project_id,
traversal_ids,
total,
critical,
high,
medium,
low,
unknown,
info
FROM vulnerability_statistics
WHERE project_id IN (SELECT project_id FROM new_values)
), upserted AS (
INSERT INTO vulnerability_statistics
(project_id, archived, traversal_ids, total, info, unknown, low, medium, high, critical, letter_grade, created_at, updated_at)
(SELECT project_id, archived, traversal_ids, total, info, unknown, low, medium, high, critical, letter_grade, created_at, updated_at
FROM new_values
)
ON CONFLICT (project_id)
DO UPDATE SET
total = EXCLUDED.total,
info = EXCLUDED.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
RETURNING project_id
), diff_values AS (
SELECT
new_values.traversal_ids[array_length(new_values.traversal_ids, 1)] AS namespace_id,
new_values.traversal_ids,
ARRAY_AGG(DISTINCT new_values.project_id ORDER BY new_values.project_id) AS affected_project_ids,
SUM(new_values.total - COALESCE(old_values.total, 0)) AS total,
SUM(new_values.info - COALESCE(old_values.info, 0)) AS info,
SUM(new_values.unknown - COALESCE(old_values.unknown, 0)) AS unknown,
SUM(new_values.low - COALESCE(old_values.low, 0)) AS low,
SUM(new_values.medium - COALESCE(old_values.medium, 0)) AS medium,
SUM(new_values.high - COALESCE(old_values.high, 0)) AS high,
SUM(new_values.critical - COALESCE(old_values.critical, 0)) AS critical
FROM new_values
LEFT JOIN old_values
ON new_values.project_id = old_values.project_id
WHERE EXISTS (
SELECT 1
FROM upserted
WHERE upserted.project_id = new_values.project_id
)
AND (
new_values.total != COALESCE(old_values.total, 0) OR
new_values.info != COALESCE(old_values.info, 0) OR
new_values.unknown != COALESCE(old_values.unknown, 0) OR
new_values.low != COALESCE(old_values.low, 0) OR
new_values.medium != COALESCE(old_values.medium, 0) OR
new_values.high != COALESCE(old_values.high, 0) OR
new_values.critical != COALESCE(old_values.critical, 0)
)
GROUP BY namespace_id, new_values.traversal_ids
)
SELECT *
FROM diff_values
WHERE
total != 0 OR
info != 0 OR
unknown != 0 OR
low != 0 OR
medium != 0 OR
high != 0 OR
critical != 0;Plan: https://console.postgres.ai/gitlab/gitlab-production-sec/sessions/52130/commands/153571
Aggregate (cost=1279.52..1279.62 rows=1 width=296) (actual time=2.293..2.295 rows=1 loops=1)
Group Key: (new_values.traversal_ids[array_length(new_values.traversal_ids, 1)]), new_values.traversal_ids
Filter: ((sum((new_values.total - COALESCE(vulnerability_statistics.total, 0))) <> '0'::numeric) OR (sum((new_values.info - COALESCE(vulnerability_statistics.info, 0))) <> '0'::numeric) OR (sum((new_values.unknown - COALESCE(vulnerability_statistics.unknown, 0))) <> '0'::numeric) OR (sum((new_values.low - COALESCE(vulnerability_statistics.low, 0))) <> '0'::numeric) OR (sum((new_values.medium - COALESCE(vulnerability_statistics.medium, 0))) <> '0'::numeric) OR (sum((new_values.high - COALESCE(vulnerability_statistics.high, 0))) <> '0'::numeric) OR (sum((new_values.critical - COALESCE(vulnerability_statistics.critical, 0))) <> '0'::numeric))
Rows Removed by Filter: 0
Buffers: shared hit=646 read=1 dirtied=3
WAL: records=4 fpi=1 bytes=1677
CTE new_values
-> Nested Loop Left Join (cost=0.57..1275.91 rows=1 width=113) (actual time=1.367..1.369 rows=1 loops=1)
Buffers: shared hit=603
-> Function Scan on unnest project_ids (cost=0.00..0.02 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=1)
Filter: (278964 = project_ids.project_id)
Rows Removed by Filter: 0
-> Aggregate (cost=0.57..1275.85 rows=1 width=64) (actual time=1.358..1.359 rows=1 loops=1)
Buffers: shared hit=603
-> Index Scan using index_vulnerability_reads_for_filtered_removal on public.vulnerability_reads (cost=0.57..1262.06 rows=424 width=10) (actual time=0.227..1.354 rows=1 loops=1)
Index Cond: ((vulnerability_reads.project_id = 278964) AND (vulnerability_reads.resolved_on_default_branch = false))
Filter: ((vulnerability_reads.state = ANY ('{1,4}'::integer[])) AND ((vulnerability_reads.security_project_tracked_context_id IS NULL) OR (vulnerability_reads.security_project_tracked_context_id = 278964)))
Rows Removed by Filter: 1837
Buffers: shared hit=603
CTE upserted
-> ModifyTable on public.vulnerability_statistics vulnerability_statistics_1 (cost=0.00..0.04 rows=1 width=123) (actual time=0.167..0.168 rows=1 loops=1)
Buffers: shared hit=29 dirtied=2
WAL: records=3 fpi=0 bytes=268
-> CTE Scan on new_values new_values_2 (cost=0.00..0.04 rows=1 width=123) (actual time=0.090..0.091 rows=1 loops=1)
Buffers: shared hit=15 dirtied=1
WAL: records=1 fpi=0 bytes=99
-> Sort (cost=3.57..3.57 rows=1 width=128) (actual time=2.281..2.282 rows=1 loops=1)
Sort Key: (new_values.traversal_ids[array_length(new_values.traversal_ids, 1)]), new_values.traversal_ids, new_values.project_id
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=646 read=1 dirtied=3
WAL: records=4 fpi=1 bytes=1677
-> Nested Loop Semi Join (cost=0.42..3.56 rows=1 width=128) (actual time=2.249..2.253 rows=1 loops=1)
Buffers: shared hit=635 read=1 dirtied=3
WAL: records=4 fpi=1 bytes=1677
-> Nested Loop Left Join (cost=0.42..3.52 rows=1 width=120) (actual time=2.079..2.082 rows=1 loops=1)
Filter: ((new_values.total <> COALESCE(vulnerability_statistics.total, 0)) OR (new_values.info <> COALESCE(vulnerability_statistics.info, 0)) OR (new_values.unknown <> COALESCE(vulnerability_statistics.unknown, 0)) OR (new_values.low <> COALESCE(vulnerability_statistics.low, 0)) OR (new_values.medium <> COALESCE(vulnerability_statistics.medium, 0)) OR (new_values.high <> COALESCE(vulnerability_statistics.high, 0)) OR (new_values.critical <> COALESCE(vulnerability_statistics.critical, 0)))
Rows Removed by Filter: 0
Buffers: shared hit=606 read=1 dirtied=1
WAL: records=1 fpi=1 bytes=1409
-> CTE Scan on new_values (cost=0.00..0.02 rows=1 width=92) (actual time=1.369..1.371 rows=1 loops=1)
Buffers: shared hit=603
-> Nested Loop Semi Join (cost=0.42..3.48 rows=1 width=36) (actual time=0.705..0.707 rows=1 loops=1)
Buffers: shared hit=3 read=1 dirtied=1
WAL: records=1 fpi=1 bytes=1409
-> Index Scan using index_vuln_stats_on_project_id_and_tracked_context_id on public.vulnerability_statistics (cost=0.42..3.44 rows=1 width=36) (actual time=0.701..0.702 rows=1 loops=1)
Index Cond: (vulnerability_statistics.project_id = new_values.project_id)
Buffers: shared hit=3 read=1 dirtied=1
WAL: records=1 fpi=1 bytes=1409
-> CTE Scan on new_values new_values_1 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)
-> CTE Scan on upserted (cost=0.00..0.02 rows=1 width=8) (actual time=0.167..0.167 rows=1 loops=1)
Buffers: shared hit=29 dirtied=2
WAL: records=3 fpi=0 bytes=268
Settings: effective_cache_size = '338688MB', jit = 'off', random_page_cost = '1.5', work_mem = '100MB', seq_page_cost = '4'Summary
Time: 6.538 ms
- planning: 4.017 ms
- execution: 2.521 ms
- I/O read: N/A
- I/O write: N/A
Shared buffers:
- hits: 646 (~5.00 MiB) from the buffer pool
- reads: 1 (~8.00 KiB) from the OS file cache, including disk I/O
- dirtied: 3 (~24.00 KiB)
- writes: 0 VS
Query before
WITH new_values AS (
SELECT
project_ids.project_id AS project_id,
project_attributes.archived AS archived,
project_attributes.traversal_ids AS traversal_ids,
COALESCE(severity_counts.total, 0) AS total,
COALESCE(severity_counts.info, 0) AS info,
COALESCE(severity_counts.unknown, 0) AS unknown,
COALESCE(severity_counts.low, 0) AS low,
COALESCE(severity_counts.medium, 0) AS medium,
COALESCE(severity_counts.high, 0) AS high,
COALESCE(severity_counts.critical, 0) AS critical,
(
CASE
WHEN severity_counts.critical > 0 THEN
4
WHEN severity_counts.high > 0 OR severity_counts.unknown > 0 THEN
3
WHEN severity_counts.medium > 0 THEN
2
WHEN severity_counts.low > 0 THEN
1
ELSE
0
END
) AS letter_grade,
now() AS created_at,
now() AS updated_at
FROM unnest(ARRAY[278964]) project_ids(project_id)
JOIN (VALUES (278964, FALSE, ARRAY[9970, 15846663]::bigint[])) project_attributes(project_id, archived, traversal_ids)
ON project_attributes.project_id = project_ids.project_id
LEFT OUTER JOIN(
SELECT
vulnerability_reads.project_id AS project_id,
COUNT(*) AS total,
COUNT(*) FILTER (WHERE severity = 1) as info,
COUNT(*) FILTER (WHERE severity = 2) as unknown,
COUNT(*) FILTER (WHERE severity = 4) as low,
COUNT(*) FILTER (WHERE severity = 5) as medium,
COUNT(*) FILTER (WHERE severity = 6) as high,
COUNT(*) FILTER (WHERE severity = 7) as critical
FROM vulnerability_reads
WHERE
vulnerability_reads.project_id IN (278964) AND
vulnerability_reads.state IN (1, 4) AND
(vulnerability_reads.security_project_tracked_context_id IS NULL
OR vulnerability_reads.security_project_tracked_context_id IN (278964))
GROUP BY vulnerability_reads.project_id
) AS severity_counts ON severity_counts.project_id = project_ids.project_id
), old_values AS (
SELECT
project_id,
traversal_ids,
total,
critical,
high,
medium,
low,
unknown,
info
FROM vulnerability_statistics
WHERE project_id IN (SELECT project_id FROM new_values)
), upserted AS (
INSERT INTO vulnerability_statistics
(project_id, archived, traversal_ids, total, info, unknown, low, medium, high, critical, letter_grade, created_at, updated_at)
(SELECT project_id, archived, traversal_ids, total, info, unknown, low, medium, high, critical, letter_grade, created_at, updated_at
FROM new_values
)
ON CONFLICT (project_id)
DO UPDATE SET
total = EXCLUDED.total,
info = EXCLUDED.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
RETURNING project_id
), diff_values AS (
SELECT
new_values.traversal_ids[array_length(new_values.traversal_ids, 1)] AS namespace_id,
new_values.traversal_ids,
ARRAY_AGG(DISTINCT new_values.project_id ORDER BY new_values.project_id) AS affected_project_ids,
SUM(new_values.total - COALESCE(old_values.total, 0)) AS total,
SUM(new_values.info - COALESCE(old_values.info, 0)) AS info,
SUM(new_values.unknown - COALESCE(old_values.unknown, 0)) AS unknown,
SUM(new_values.low - COALESCE(old_values.low, 0)) AS low,
SUM(new_values.medium - COALESCE(old_values.medium, 0)) AS medium,
SUM(new_values.high - COALESCE(old_values.high, 0)) AS high,
SUM(new_values.critical - COALESCE(old_values.critical, 0)) AS critical
FROM new_values
LEFT JOIN old_values
ON new_values.project_id = old_values.project_id
WHERE EXISTS (
SELECT 1
FROM upserted
WHERE upserted.project_id = new_values.project_id
)
AND (
new_values.total != COALESCE(old_values.total, 0) OR
new_values.info != COALESCE(old_values.info, 0) OR
new_values.unknown != COALESCE(old_values.unknown, 0) OR
new_values.low != COALESCE(old_values.low, 0) OR
new_values.medium != COALESCE(old_values.medium, 0) OR
new_values.high != COALESCE(old_values.high, 0) OR
new_values.critical != COALESCE(old_values.critical, 0)
)
GROUP BY namespace_id, new_values.traversal_ids
)
SELECT *
FROM diff_values
WHERE
total != 0 OR
info != 0 OR
unknown != 0 OR
low != 0 OR
medium != 0 OR
high != 0 OR
critical != 0;Plan: https://console.postgres.ai/gitlab/gitlab-production-sec/sessions/52130/commands/153573
Aggregate (cost=2313.78..2313.88 rows=1 width=296) (actual time=9.316..9.319 rows=1 loops=1)
Group Key: (new_values.traversal_ids[array_length(new_values.traversal_ids, 1)]), new_values.traversal_ids
Filter: ((sum((new_values.total - COALESCE(vulnerability_statistics.total, 0))) <> '0'::numeric) OR (sum((new_values.info - COALESCE(vulnerability_statistics.info, 0))) <> '0'::numeric) OR (sum((new_values.unknown - COALESCE(vulnerability_statistics.unknown, 0))) <> '0'::numeric) OR (sum((new_values.low - COALESCE(vulnerability_statistics.low, 0))) <> '0'::numeric) OR (sum((new_values.medium - COALESCE(vulnerability_statistics.medium, 0))) <> '0'::numeric) OR (sum((new_values.high - COALESCE(vulnerability_statistics.high, 0))) <> '0'::numeric) OR (sum((new_values.critical - COALESCE(vulnerability_statistics.critical, 0))) <> '0'::numeric))
Rows Removed by Filter: 0
Buffers: shared hit=1002 read=20
WAL: records=2 fpi=0 bytes=169
CTE new_values
-> Nested Loop Left Join (cost=0.57..2310.17 rows=1 width=113) (actual time=8.939..8.941 rows=1 loops=1)
Buffers: shared hit=960 read=20
-> Function Scan on unnest project_ids (cost=0.00..0.02 rows=1 width=4) (actual time=0.006..0.007 rows=1 loops=1)
Filter: (278964 = project_ids.project_id)
Rows Removed by Filter: 0
-> Aggregate (cost=0.57..2310.12 rows=1 width=64) (actual time=8.927..8.928 rows=1 loops=1)
Buffers: shared hit=960 read=20
-> Index Scan using idx_vuln_reads_for_filtering on public.vulnerability_reads (cost=0.57..2274.19 rows=1105 width=10) (actual time=3.773..8.916 rows=6 loops=1)
Index Cond: ((vulnerability_reads.project_id = 278964) AND (vulnerability_reads.state = ANY ('{1,4}'::integer[])))
Filter: ((vulnerability_reads.security_project_tracked_context_id IS NULL) OR (vulnerability_reads.security_project_tracked_context_id = 278964))
Rows Removed by Filter: 1771
Buffers: shared hit=960 read=20
CTE upserted
-> ModifyTable on public.vulnerability_statistics vulnerability_statistics_1 (cost=0.00..0.04 rows=1 width=123) (actual time=0.177..0.177 rows=1 loops=1)
Buffers: shared hit=27
WAL: records=2 fpi=0 bytes=169
-> CTE Scan on new_values new_values_2 (cost=0.00..0.04 rows=1 width=123) (actual time=0.101..0.101 rows=1 loops=1)
Buffers: shared hit=15
-> Sort (cost=3.57..3.57 rows=1 width=128) (actual time=9.303..9.305 rows=1 loops=1)
Sort Key: (new_values.traversal_ids[array_length(new_values.traversal_ids, 1)]), new_values.traversal_ids, new_values.project_id
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=1002 read=20
WAL: records=2 fpi=0 bytes=169
-> Nested Loop Semi Join (cost=0.42..3.56 rows=1 width=128) (actual time=9.163..9.167 rows=1 loops=1)
Buffers: shared hit=991 read=20
WAL: records=2 fpi=0 bytes=169
-> Nested Loop Left Join (cost=0.42..3.52 rows=1 width=120) (actual time=8.982..8.985 rows=1 loops=1)
Filter: ((new_values.total <> COALESCE(vulnerability_statistics.total, 0)) OR (new_values.info <> COALESCE(vulnerability_statistics.info, 0)) OR (new_values.unknown <> COALESCE(vulnerability_statistics.unknown, 0)) OR (new_values.low <> COALESCE(vulnerability_statistics.low, 0)) OR (new_values.medium <> COALESCE(vulnerability_statistics.medium, 0)) OR (new_values.high <> COALESCE(vulnerability_statistics.high, 0)) OR (new_values.critical <> COALESCE(vulnerability_statistics.critical, 0)))
Rows Removed by Filter: 0
Buffers: shared hit=964 read=20
-> CTE Scan on new_values (cost=0.00..0.02 rows=1 width=92) (actual time=8.943..8.945 rows=1 loops=1)
Buffers: shared hit=960 read=20
-> Nested Loop Semi Join (cost=0.42..3.48 rows=1 width=36) (actual time=0.034..0.035 rows=1 loops=1)
Buffers: shared hit=4
-> Index Scan using index_vuln_stats_on_project_id_and_tracked_context_id on public.vulnerability_statistics (cost=0.42..3.44 rows=1 width=36) (actual time=0.031..0.032 rows=1 loops=1)
Index Cond: (vulnerability_statistics.project_id = new_values.project_id)
Buffers: shared hit=4
-> CTE Scan on new_values new_values_1 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)
-> CTE Scan on upserted (cost=0.00..0.02 rows=1 width=8) (actual time=0.177..0.178 rows=1 loops=1)
Buffers: shared hit=27
WAL: records=2 fpi=0 bytes=169
Settings: effective_cache_size = '338688MB', jit = 'off', random_page_cost = '1.5', work_mem = '100MB', seq_page_cost = '4'Summary
Time: 13.827 ms
- planning: 4.272 ms
- execution: 9.555 ms
- I/O read: N/A
- I/O write: N/A
Shared buffers:
- hits: 1002 (~7.80 MiB) from the buffer pool
- reads: 20 (~160.00 KiB) from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0How to set up and validate locally
Given a project with 0 critical vulnerabilities
-
Create a vulnerability via UI Project → Secure → Vulnerability report → Submit vulnerability. Severity
Critical, statusNeeds triage, identifierhttps://cwe.mitre.org/data/definitions/89.html. Submit. -
Confirm initial count Group → Secure → Security Inventory. Project row shows 1 Critical.
-
Mark as "no longer detected" ( not available via UI ):
v = Vulnerability.last Vulnerability.id_in(v.id).update_all(resolved_on_default_branch: true) Vulnerabilities::Read.where(vulnerability_id: v.id).update_all(resolved_on_default_branch: true) -
Switch to the MR branch and trigger the recompute (simulates the twice-daily cron):
Vulnerabilities::Statistics::AdjustmentWorker.new.perform([Project.find(<id>).id]) -
Confirm fix: refresh Security Inventory → project shows 0 Critical.
MR acceptance checklist
Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.
Edited by Vasyl Pedak