openqa-query-for-job-label

#!/bin/sh -e
host="${host:-"openqa.opensuse.org openqa.suse.de"}"
scheme="${scheme:-"https"}"
interval="${interval:-"30 day"}"
failed_since="${failed_since:-"(timezone('UTC', now()) - interval '$interval')"}"
comment="${1:?"Need comment to search for"}"
limit="${limit:-10}"
query="${query:-"select jobs.id,t_finished,state,result,test,reason,host from jobs, comments, workers where t_finished >= $failed_since and jobs.assigned_worker_id = workers.id and jobs.id = comments.job_id and comments.text ~ '$comment' order by t_finished desc limit $limit;"}"
dry_run="${dry_run:-"0"}"
[ "$dry_run" = "1" ] && _dry_run="echo"
for h in $host; do
    $_dry_run ssh "$h" "cd /tmp; sudo -u geekotest psql --no-align --tuples-only --command=\"$query\" openqa"
done
: