Add by_mttr_over_time Elasticsearch aggregation

What does this MR do and why?

Adds the by_mttr_over_time Elasticsearch aggregation — the query-building groundwork for the MTTR-over-time security dashboard chart (phase 2; phase 1 added the remediated_at field). A follow-up finder will consume this aggregation.

• Buckets are one per ISO week (Monday–Sunday, UTC); the current week is partial and future weeks are dropped.
• Each bucket averages both remediated_at and detected_at, so MTTR comes out as a difference of means with no stored duration field needed.
• Each bucket only counts vulnerabilities detected on or after 90 days before that bucket's Monday: detected_at >= week_start - 90.days (constant MTTR_OVER_TIME_DETECTION_WINDOW = 90.days). This range clause replaces the previous exists: detected_at guard because a range query never matches a missing field. The 90-day window caps the influence of very old vulnerabilities, which made the chart noisy; the cap is the same for every bucket so it can be explained in a tooltip.
• Dismissed vulnerabilities are excluded via a single must_not clause on state in the top-level query, not repeated inside each weekly bucket. remediated_at can come from undetected_since, which ignores state, so a dismissed-then-undetected vulnerability would otherwise count as remediated.
• The top-level query is narrowed to what any week can match: remediated_at within the whole bucketed window, and detected_at on or after 90 days before the first week. Each bucket keeps its own exact ranges, so results are unchanged. Without this, every vulnerability in the namespace was checked against every week. In production, 12 weeks for gitlab-org went from 1,276 ms to 411 ms with identical results.
• Optional group_mttr_by option: a list (or single value) containing any of severity, report_type. Only the listed terms sub-aggregations (by_severity, by_report_type) are added; the weekly averages are always there. Unsupported values raise ArgumentError. It has its own name, like group_risk_score_by, because other aggregations read group_by from the same options as a single required value.
• The aggregation is gated on the add_remediated_at_to_vulnerability migration.

MTTR_OVER_TIME_MAX_WEEKS = 12, the range the chart shows (the frontend requests 12 weeks). Performance results are in #627301 (closed).

References

• Implements: #627299 (closed)
• Implements: #631001 (closed) (dismissed vulnerabilities)
• 90-day window decision: gitlab-org#19830
• Epic: &23458
• Phase 1 (merged): #627295 (closed)
• Next (finder): #627300 (closed)
• Performance testing: #627301 (closed)

Screenshots or screen recordings

N/A — backend only.

How to set up and validate locally

Run the unit specs:

bundle exec rspec ee/spec/lib/search/elastic/vulnerability_aggregations_spec.rb ee/spec/lib/search/elastic/vulnerability_query_builder_spec.rb

Manual validation against a live index

1. Enable advanced search in GDK and make sure vulnerabilities are indexed. Resolve a few vulnerabilities so some documents have remediated_at set, and confirm the migration has finished:

   Elastic::DataMigrationService.migration_has_finished?(:add_remediated_at_to_vulnerability) # => true

2. In gdk rails c, build the query and run it against the vulnerability index:

   group = Group.find_by_full_path('gitlab-org')
   
   options = {
     search_level: 'group',
     traversal_ids: [group.elastic_namespace_ancestry],
     mttr_over_time: true,
     group_mttr_by: %w[severity report_type],
     bucket_start_date: 8.weeks.ago,
     bucket_end_date: Time.current
   }
   
   query = Search::Elastic::VulnerabilityQueryBuilder.build(query: nil, options: options)
   
   Gitlab::Search::Client.execute_search(
     query: query,
     options: { index_name: Search::Elastic::References::Vulnerability.index, root_ancestor_ids: [group.root_ancestor.id] }
   ) { |r| r.dig('aggregations', 'remediated_by_week', 'buckets') }

3. Check that:

   • There is one bucket per Monday, and the last one is the current (partial) week.
   • doc_count matches the vulnerabilities you resolved that week, excluding any detected more than 90 days before its Monday.
   • avg_remediated_at - avg_detected_at (epoch ms) gives a sensible MTTR.
   • The by_severity and by_report_type buckets add up to the week's doc_count.

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.

🤖 Generated with Claude Code