Skip to content

Harden security ci build job queries

  "usage_activity_by_stage_monthly": {
    "secure": {
    543:      "user_container_scanning_jobs": -1,
    544:      "user_dast_jobs": -1,
    545:      "user_dependency_scanning_jobs": -1,
SECURE_PRODUCT_TYPES = {
  container_scanning: {
    name: :container_scanning_jobs
  },
  dast: {
    name: :dast_jobs
  },
  dependency_scanning: {
    name: :dependency_scanning_jobs
  },
  license_management: {
    name: :license_management_jobs
  },
  license_scanning: {
    name: :license_scanning_jobs
  },
  sast: {
    name: :sast_jobs
  },
  secret_detection: {
    name: :secret_detection_jobs
  }
}


time_period = { created_at: 28.days.ago..Time.current }
SECURE_PRODUCT_TYPES.each do |secure_type, attribs|
  results["user_#{attribs[:name]}".to_sym] = distinct_count(::Ci::Build.where(name: secure_type).where(time_period), :user_id)
end


time_period = { created_at: 28.days.ago..Time.current }
Gitlab::UsageData::distinct_count(::Ci::Build.where(name: :container_scanning).where(time_period), :user_id)

SQL Generated

  • One security job is enough
SELECT MIN("ci_builds"."user_id") FROM "ci_builds" WHERE "ci_builds"."type" = 'Ci::Build' AND "ci_builds"."name" = 'container_scanning' AND "ci_builds"."created_at" BETWEEN '2020-05-08 14:22:45.537507' AND '2020-06-05 14:22:45.537759'
SELECT MAX("ci_builds"."user_id") FROM "ci_builds" WHERE "ci_builds"."type" = 'Ci::Build' AND "ci_builds"."name" = 'container_scanning' AND "ci_builds"."created_at" BETWEEN '2020-05-08 14:22:45.537507' AND '2020-06-05 14:22:45.537759'
SELECT COUNT(DISTINCT "ci_builds"."user_id") FROM "ci_builds" WHERE "ci_builds"."type" = 'Ci::Build' AND "ci_builds"."name" = 'container_scanning' AND "ci_builds"."created_at" BETWEEN '2020-05-08 14:22:45.537507' AND '2020-06-05 14:22:45.537759' AND "ci_builds"."user_id" BETWEEN 0 AND 9999
Edited by Alper Akgun