Resolve: Security Dashboard: GraphQL Support / Expose meanAge and medianAge [BE]
What does this MR do and why?
References
- Main issue: Security Dashboard: GraphQL Support / Expose me... (#578072 - closed) • Charlie Kroon • 18.6
- Parent issue: Security Dashboard: Show Mean/Median Age (Vulne... (#577550 - closed) • Subashis Chakraborty, Charlie Kroon • 18.6
- Related MR: Resolve "Security Dashboard: ES migration to ba... (!210086 - merged) • Charlie Kroon • 18.6
- Related MR: Add detected_at field to ES vulnerability index (!209927 - merged) • Charlie Kroon • 18.6
Query Performance on Staging
Tested on staging with the largest group containing 57,783 vulnerabilities.
- Query executed successfully in 130ms
- Total vulnerabilities: 57,650+ (10,000+ shown, relation: "gte")
- Aggregations returned for 6 severity levels with mean and median detection times
- Largest group: MEDIUM severity (20,884 vulnerabilities)
Executed Query:
top_result = Gitlab::Search::Client.new.search(
index: 'gitlab-production-vulnerabilities',
routing: 'xxx',
body: {
query: {
bool: {
filter: [
{ term: { traversal_ids: 'xxx' } }
]
}
},
size: 0,
aggs: {
severity_counts: {
terms: {
field: 'severity',
size: 10
},
aggs: {
avg_detected_at: {
avg: {
field: 'detected_at'
}
},
median_detected_at: {
percentiles: {
field: 'detected_at',
percents: [50]
}
}
}
}
}
}
)Results:
{
"took" => 130,
"timed_out" => false,
"_shards" => {"total" => 1, "successful" => 1, "skipped" => 0, "failed" => 0},
"hits" => {"total" => {"value" => 10000, "relation" => "gte"}, "max_score" => nil, "hits" => []},
"aggregations" => {
"severity_counts" => {
"doc_count_error_upper_bound" => 0,
"sum_other_doc_count" => 0,
"buckets" => [
{
"key" => 2, # MEDIUM severity
"doc_count" => 20884,
"avg_detected_at" => {
"value" => 1748746139649.4724,
"value_as_string" => "2025-06-01T02:48:59.649Z"
},
"median_detected_at" => {
"values" => {
"50.0" => 1749519419551.5264,
"50.0_as_string" => "2025-06-10T01:36:59.551Z"
}
}
},
{
"key" => 6, # INFO severity
"doc_count" => 15133,
"avg_detected_at" => {
"value" => 1732364461560.7078,
"value_as_string" => "2024-11-23T12:21:01.560Z"
},
"median_detected_at" => {
"values" => {
"50.0" => 1729773956324.3164,
"50.0_as_string" => "2024-10-24T12:45:56.324Z"
}
}
},
{
"key" => 5, # LOW severity
"doc_count" => 14994,
"avg_detected_at" => {
"value" => 1731504723454.992,
"value_as_string" => "2024-11-13T13:32:03.454Z"
},
"median_detected_at" => {
"values" => {
"50.0" => 1725755764635.761,
"50.0_as_string" => "2024-09-08T00:36:04.635Z"
}
}
},
{
"key" => 4, # HIGH severity
"doc_count" => 4462,
"avg_detected_at" => {
"value" => 1732346184541.7268,
"value_as_string" => "2024-11-23T07:16:24.541Z"
},
"median_detected_at" => {
"values" => {
"50.0" => 1725328917412.4814,
"50.0_as_string" => "2024-09-03T02:01:57.412Z"
}
}
},
{
"key" => 7, # UNKNOWN severity
"doc_count" => 2177,
"avg_detected_at" => {
"value" => 1727896935376.8223,
"value_as_string" => "2024-10-02T19:22:15.376Z"
},
"median_detected_at" => {
"values" => {
"50.0" => 1724734643361.5151,
"50.0_as_string" => "2024-08-27T04:57:23.361Z"
}
}
},
{
"key" => 1, # CRITICAL severity
"doc_count" => 133,
"avg_detected_at" => {
"value" => 1730346552168.0151,
"value_as_string" => "2024-10-31T03:49:12.168Z"
},
"median_detected_at" => {
"values" => {
"50.0" => 1724734398567.0,
"50.0_as_string" => "2024-08-27T04:53:18.567Z"
}
}
}
]
}
}
}How to set up and validate locally
Step 1: ElasticSearch Setup + Feature Flag
- Make sure you have ElasticSearch running on your local env. Follow the steps: https://gitlab.com/gitlab-org/gitlab-development-kit/blob/main/doc/howto/elasticsearch.md#setup
- Run the migration in Rails console:
Elastic::DataMigrationService[20251024110346].migrate- Index vulnerability data:
Vulnerabilities::Read.all.each { |v| ::Elastic::ProcessBookkeepingService.track!(Search::Elastic::References::Vulnerability.new(v.vulnerability_id, "group_#{v.project.namespace.root_ancestor.id}")) }- Then run:
Elastic::ProcessBookkeepingService.new.execute- Now enable the feature flag in Rails console:
Feature.enable(:group_security_dashboard_new)Now, go to: Go to http://gdk.test:3000/-/graphql-explorer and run:
{
group(fullPath: "gitlab-org") {
id
securityMetrics {
vulnerabilitiesPerSeverity(
startDate: "2025-01-01T00:00:00Z"
endDate: "2025-06-01T00:00:00Z"
) {
info {
count
meanAge
medianAge
}
unknown {
count
meanAge
medianAge
}
high {
count
meanAge
medianAge
}
low {
count
meanAge
medianAge
}
medium {
count
meanAge
medianAge
}
critical {
count
meanAge
medianAge
}
}
}
}
}This should return something like:
{
"data": {
"group": {
"id": "gid://gitlab/Group/24",
"securityMetrics": {
"vulnerabilitiesPerSeverity": {
"info": {
"severity": "INFO",
"count": 6,
"meanAge": 202.4290970335998,
"medianAge": 202.42909703412064
},
"unknown": {
"count": 6,
"meanAge": 203.22131320320625,
"medianAge": 203.22131320328728
},
"high": {
"count": 3,
"meanAge": 203.22131320358818,
"medianAge": 203.22131320364605
},
"low": {
"count": 6,
"meanAge": 203.2213132033567,
"medianAge": 203.22131320341455
},
"medium": {
"count": 48,
"meanAge": 203.22131320347248,
"medianAge": 203.22131320353031
},
"critical": {
"count": 9,
"meanAge": 203.22131320370391,
"medianAge": 203.22131320504653
}
}
}
}
},
"correlationId": "01K9AQR78ABZ2Z87MT3JCVGEQ3"
}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 Charlie Kroon