k8s.io/test-infra@v0.0.0-20240520184403-27c6b4c223d8/metrics/configs/job-health.yaml (about)

     1  metric: job-health
     2  description: Calculate daily health metrics for jobs
     3  query: |
     4    #standardSQL
     5    select
     6      unix_seconds(start_day) as day,
     7      job_name as job,
     8      runs,
     9      passed_runs,
    10      if(runs != 0,
    11        round(1 - (passed_runs / runs), 3),
    12        0) as failure_rate,
    13      tests,
    14      failed_tests,
    15      if (tests != 0,
    16        round(failed_tests / tests, 3),
    17        0) as test_failure_rate,
    18      p99_duration,
    19      p75_duration,
    20      p50_duration
    21    from (
    22      select
    23        start_day,
    24        job_name,
    25        count(*) as runs,
    26        countif(passed) as passed_runs,
    27        sum(tests_run) as tests,
    28        sum(tests_failed) as failed_tests,
    29        round(max(_p99_duration), 3) as p99_duration,
    30        round(max(_p75_duration), 3) as p75_duration,
    31        round(max(_p50_duration), 3) as p50_duration
    32      from (
    33        select
    34          timestamp_trunc(b.started, day) start_day,
    35          b.job job_name,
    36          percentile_cont(b.elapsed, 0.50) over(partition by b.job, timestamp_trunc(b.started, day)) / 60.0 as _p50_duration,
    37          percentile_cont(b.elapsed, 0.75) over(partition by b.job, timestamp_trunc(b.started, day)) / 60.0 as _p75_duration,
    38          percentile_cont(b.elapsed, 0.99) over(partition by b.job, timestamp_trunc(b.started, day)) / 60.0 as _p99_duration,
    39          b.tests_run tests_run,
    40          b.tests_failed tests_failed,
    41          b.passed passed
    42        from
    43          `k8s-gubernator.build.all` AS b
    44        where
    45          b.started > timestamp_seconds(<LAST_DATA_TIME>)
    46          and started < timestamp_trunc(current_timestamp(), day)
    47          and b.elapsed is not null
    48      )
    49      group by
    50        start_day, job_name
    51    )
    52    order by
    53      start_day, job_name
    54  
    55  jqfilter: |
    56    ([(.[] | .day|tonumber)] | max) as $newestday |
    57    [(.[] | select((.day|tonumber)==$newestday) | {
    58      day: (.day|tonumber|todateiso8601[:10]),
    59      job: .job,
    60      runs: (.runs|tonumber),
    61      failure_rate: (.failure_rate|tonumber),
    62      tests: (.tests|tonumber),
    63      test_failure_rate: (.test_failure_rate|tonumber),
    64      p50_duration: (.p50_duration|tonumber),
    65      p75_duration: (.p75_duration|tonumber),
    66      p99_duration: (.p99_duration|tonumber),
    67    })]