openqa-monitor-investigation-candidates

#!/bin/bash -e
set -euo pipefail
host="${host:-"openqa.opensuse.org"}"
ssh_host="${ssh_host:-"$host"}"
scheme="${scheme:-"https"}"
failed_since="${failed_since:-"(timezone('UTC', now()) - interval '24 hour')"}"
# use an environment variable "group_id" to limit the search to that job group, e.g.
# "1" for "openSUSE Tumbleweed" on openqa.opensuse.org
# shellcheck disable=SC2154
group_query="${group_id+" group_id=$group_id and"}"
# use an environment variable "exclude_group" with an SQL regex string to
# exclude all jobs that match the group name specified, e.g.
# "%Not This Group%"
exclude_group="${exclude_group:-"%Kernel%|%Development%|%Staging%|%MicroOS%"}"
exclude_group_query="${exclude_group+" and job_groups.name not similar to '${exclude_group}'"}"
# use an environment variable "exclude_parent" with an SQL regex string to
# exclude all jobs that match the parent group name specified, e.g.
# "%Development|Open Build Service%"
exclude_parent="${exclude_parent:-"%Development|Open Build Service|Others%"}"
exclude_parent_query="${exclude_parent+" where job_group_parents.name not similar to '${exclude_parent}'"}"
result="${result:-'failed'}"
comment_query="${comment_query:-" and jobs.id not in (select job_id from comments where job_id is not null)"}"

# Define common query parts that we can reuse when we look for both jobs in groups with a parent groups and jobs in groups without a parent group
query_common_prefix="${query_common_prefix:-"
  select jobs.id,jobs.test, job_groups.parent_id from jobs left join job_groups on jobs.group_id = job_groups.id
"}"
query_common_suffix="${query_common_suffix:-"
  result=$result and clone_id is null and$group_query t_finished >= $failed_since$comment_query$exclude_group_query
"}"
query="${query:-"
  with included_jobs as ($query_common_prefix where $query_common_suffix) select included_jobs.id, test from included_jobs left join job_group_parents on parent_id = job_group_parents.id$exclude_parent_query
  union all
  select included_jobs.id, test from included_jobs where parent_id is null;
"}"

# shellcheck disable=SC2029
for i in $(ssh "$ssh_host" "cd /tmp; sudo -u geekotest psql --no-align --tuples-only --command=\"$query\" openqa"); do
    url="$scheme://$host/tests/${i%|*}"
    details="${i#*|}"
    echo "$url" "$details"
done