Add secure lighthouse metric
What does this MR do and why?
In support of Secure Stage Lighthouse metric (&12499 - closed) we need to add a new metric definition for the Secure stage lighthouse metric. This is essentially tracking the count of unique report types with at least 1 resolved vulnerability, per project. Here is the equivalent query for GitLab.com although I simplified it a bit as we shouldn't need the CTE to run distinct counts on report type
Terminology note: scanner type and report type are used interchangeably
Fixes #461890 (closed)
Query Plan
I'm not entirely sure how we check this against SM since this is impactful to usage ping but here's a postgres.ai equivalent of the 28d time dimension which will be far larger than most SM instances I'm aware of.
MR acceptance checklist
Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.
How to set up and validate locally
Two alternative ways to load instrumentation class or class itself:
[1] pry(main)> require_relative 'spec/support/helpers/service_ping_helpers.rb'; ServicePingHelpers.get_current_usage_metric_value('usage_activity_by_stage.count_distinct_report_types_with_resolved_vulnerabilities_per_project_metric')
Vulnerabilities::Read Count (25.2ms) SELECT COUNT(*) FROM (SELECT DISTINCT "vulnerability_reads"."project_id", "vulnerability_reads"."report_type" FROM "vulnerability_reads" INNER JOIN vulnerability_state_transitions
ON vulnerability_state_transitions.vulnerability_id = vulnerability_reads.vulnerability_id WHERE "vulnerability_state_transitions"."to_state" = 3 AND "vulnerability_state_transitions"."created_at" BETWEEN '2024-05-12 13:26:13.532474' AND '2024-06-18 13:26:13.532486' GROUP BY "vulnerability_reads"."project_id", "vulnerability_reads"."report_type") subquery /*application:console,db_config_name:main,console_hostname:Tohil,console_username:theoretick,line:/ee/lib/gitlab/usage/metrics/instrumentations/count_distinct_report_types_with_resolved_vulnerabilities_per_project_metric.rb:14:in `value'*/
=> 0
[2] pry(main)> Gitlab::Usage::Metrics::Instrumentations::CountDistinctReportTypesWithResolvedVulnerabilitiesPerProjectMetric.new(time_frame: '28d').value
Vulnerabilities::Read Count (6.3ms) SELECT COUNT(*) FROM (SELECT DISTINCT "vulnerability_reads"."project_id", "vulnerability_reads"."report_type" FROM "vulnerability_reads" INNER JOIN vulnerability_state_transitions
ON vulnerability_state_transitions.vulnerability_id = vulnerability_reads.vulnerability_id WHERE "vulnerability_state_transitions"."to_state" = 3 AND "vulnerability_state_transitions"."created_at" BETWEEN '2024-05-12 13:26:27.221639' AND '2024-06-18 13:26:27.221651' GROUP BY "vulnerability_reads"."project_id", "vulnerability_reads"."report_type") subquery /*application:console,db_config_name:main,console_hostname:Tohil,console_username:theoretick,line:/ee/lib/gitlab/usage/metrics/instrumentations/count_distinct_report_types_with_resolved_vulnerabilities_per_project_metric.rb:14:in `value'*/
=> 0