github.com/munnerz/test-infra@v0.0.0-20190108210205-ce3d181dc989/metrics/configs/presubmit-health.yaml (about)

     1  metric: presubmit-health
     2  description: Calculate daily health metrics for presubmits
     3  query: |
     4    #standardSQL
     5    SELECT
     6      UNIX_SECONDS(day) day,
     7      job,
     8      ROUND(prs_failed/prs * 100, 1) pr_failure_perc,
     9      prs,
    10      prs_failed,
    11      total_runs,
    12      ROUND(total_elapsed / total_runs / 60, 1) avg_run_time_minutes,
    13      IF(total_passes>0,
    14        ROUND(total_pass_elapsed / total_passes / 60, 1),
    15        0) avg_pass_time_minutes
    16    FROM (
    17      SELECT
    18        day,
    19        job,
    20        COUNT(job) prs,
    21        SUM(IF(passes<runs,
    22            1,
    23            0)) prs_failed,
    24        SUM(passes_elapsed) total_pass_elapsed,
    25        SUM(passes) total_passes,
    26        SUM(total_elapsed) total_elapsed,
    27        SUM(runs) total_runs
    28      FROM (
    29        SELECT
    30          day,
    31          job,
    32          pr,
    33          COUNTIF(result='SUCCESS') passes,
    34          COUNT(result) runs,
    35          SUM(elapsed) total_elapsed,
    36          SUM(IF(result='SUCCESS',elapsed,0)) passes_elapsed
    37        FROM (
    38          SELECT
    39            TIMESTAMP_TRUNC(started, day) day,
    40            path,
    41            job,
    42            result,
    43            elapsed,
    44            REGEXP_EXTRACT(path, "/(\\d+)/") pr
    45          FROM
    46            `k8s-gubernator.build.all`
    47          WHERE
    48            REGEXP_CONTAINS(job, '^pr:')
    49            AND started > TIMESTAMP_SECONDS(<LAST_DATA_TIME>)
    50            AND started < TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), day)
    51            AND elapsed IS NOT NULL)
    52        GROUP BY
    53          day,
    54          job,
    55          pr)
    56      GROUP BY
    57        day,
    58        job)
    59    ORDER BY
    60      day DESC,
    61      pr_failure_perc DESC
    62  jqfilter: |
    63    ([(.[] | .day|tonumber)] | max) as $newestday |
    64    [(.[] | select((.day|tonumber)==$newestday) | {
    65      day: (.day|tonumber|todateiso8601[:10]),
    66      job: .job,
    67      pr_failure_perc: (.pr_failure_perc|tonumber),
    68      prs: (.prs|tonumber),
    69      prs_failed: (.prs_failed|tonumber),
    70      total_runs: (.total_runs|tonumber),
    71      avg_run_time_minutes: (.avg_run_time_minutes|tonumber),
    72      avg_pass_time_minutes: (.avg_pass_time_minutes|tonumber),
    73    })]
    74  measurements:
    75    backfill: presubmit_health
    76    jq: |
    77      [(.[] | {
    78        measurement: "presubmit_health",
    79        time: (.day|tonumber),
    80        tags: {
    81          job: .job,
    82        },
    83        fields: {
    84          pr_failure_perc: (.pr_failure_perc|tonumber),
    85          prs: (.prs|tonumber),
    86          prs_failed: (.prs_failed|tonumber),
    87          total_runs: (.total_runs|tonumber),
    88          avg_run_time_minutes: (.avg_run_time_minutes|tonumber),
    89          avg_pass_time_minutes: (.avg_pass_time_minutes|tonumber),
    90        }
    91      })]