Skip to content

Count security jobs

Can Eldem requested to merge count-security-jobs into master

What does this MR do?

We want to count how many successful, non-repeated security jobs ran by scan type. Each time we ran security job it will create entity in security_scans table. We will leverage this table for counting.

Let's say we have JS project and we enabled dependency scanning. Pipeline is running for the first time with RetireJs and Gemnasium.(both are dependency scanning analysers). Query in issue description will give us following result,

Scan Type Count
2 2

User re-runs the job

Scan Type Count
2 2

Related issue

#229541 (closed)

Queries

SELECT MAX("security_scans"."build_id") FROM "security_scans"

https://explain.depesz.com/s/11EsB

Cold Cache

Time: 0.356 ms
  - planning: 0.108 ms
  - execution: 0.248 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 5 (~40.00 KiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Warm Cache

Time: 0.255 ms
  - planning: 0.165 ms
  - execution: 0.090 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 5 (~40.00 KiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0
SELECT MIN("security_scans"."build_id") FROM "security_scans"

https://explain.depesz.com/s/K9rJ

Cold cache

Time: 0.287 ms
  - planning: 0.106 ms
  - execution: 0.181 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 4 (~32.00 KiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Warm cache

Time: 0.164 ms
  - planning: 0.110 ms
  - execution: 0.054 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 4 (~32.00 KiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0
  explain SELECT COUNT(distinct "security_scans"."build_id")
     FROM "security_scans" 
     INNER JOIN "ci_builds" ON "ci_builds"."id" = "security_scans"."build_id" AND "ci_builds"."type" =  'Ci::Build'
     WHERE "security_scans"."scan_type"=2 AND "security_scans"."build_id" BETWEEN  685042733-30000 AND  685042733-20000

https://explain.depesz.com/s/VFvX

Cold cache

Time: 112.194 ms
  - planning: 0.654 ms
  - execution: 111.540 ms
    - I/O read: 110.669 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 101 (~808.00 KiB) from the buffer pool
  - reads: 45 (~360.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Warm cache

Time: 0.970 ms
  - planning: 0.671 ms
  - execution: 0.299 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 128 (~1.00 MiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0
   explain SELECT COUNT(distinct "security_scans"."build_id")
     FROM "security_scans" 
     INNER JOIN "ci_builds" ON "ci_builds"."id" = "security_scans"."build_id" AND "ci_builds"."type" =  'Ci::Build'
     WHERE "security_scans"."scan_type"=2 AND "security_scans"."build_id" BETWEEN  685042733-30000 AND  685042733-20000  AND security_scans.created_at BETWEEN
           '2020-07-20 12:12:35.535725' AND '2020-08-16 12:12:35.536078';

https://explain.depesz.com/s/fc3Z

Cold cache

Time: 26.379 ms
  - planning: 0.659 ms
  - execution: 25.720 ms
    - I/O read: 24.999 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 101 (~808.00 KiB) from the buffer pool
  - reads: 63 (~504.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Warm cache

Time: 1.273 ms
  - planning: 0.933 ms
  - execution: 0.340 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 146 (~1.10 MiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Conformity

Edited by Can Eldem

Merge request reports