Security Dashboard: Show Mean/Median Age (VulnerabilitiesPerSeverity) [BE]

Planning Breakdown / Implementation Plan

The age fields are currently not yet indexed in ES. We can use the group_security_dashboard_new feature flag.

Phase 1: Elastic Search Indexing

Goal: Add age-related fields to the vulnerability Elasticsearch document and make them available for querying and aggregation.

Update ES Vulnerability

• Update ee/lib/search/elastic/references/vulnerability.rb
  • Add detected_at field
  • Update as_indexed_json to include detected_at
  • Add field mapping for detected_at
  • Bump the ES schema version
  • Add Specs for:
    • as_indexed_json output should include detected_at
    • mapping + schema version

Backfilling

• Add an ES data migration to populate detected_at for existing vulnerabilities
• Ensure newly created/updated vulnerabilities set detected_at correctly
• Add Specs for:
  • Backfilling logic

Phase 2: GraphQL Exposure

Goal: Expose mean and median age (in days) per severity.

• Use ES aggregations in the query:
  • avg(detected_at) → for mean
  • percentiles(detected_at, [50]) → for median
• Add meanAge andmedianAgefields to VulnerabilitySeverityCountType.
• Update the VulnerabilitiesPerSeverityResolver
  • Convert timestamps from aggregations into days:

mean_age_days = (Time.current - avg_detected_at) / 1.day
median_age_days = (Time.current - p50_detected_at) / 1.day

• Make sure the fields are filterable and work with existing dashboard filters.
• Update belonging specs

Notes

• No daily reindexing
• Median is based on the ES percentile(50) aggregation (see documentation)

In Scope

1. VulnerabilitiesPerSeverity Chart
2. Mean and median age per severity for open vulnerabilities
3. Filtering support (all dashboard filters must affect mean/median)
4. Daily updates of vulnerabilitity ages - (via cron job? reindexing?)

Out of Scope

1. Aggregations
2. Pre-calculation

Outstanding Questions

Question	Answer	Assignee	Priority	Blocking?

Resources

1. Show Mean and Median Age in Chart 1 (Static Vul... (&18536)
2. Discussion reference: #571079 note
3. Example MR: !209324 (merged) (ES pattern)
4. Elastic Search documentation: https://docs.gitlab.com/integration/advanced_search/elasticsearch/