contrib/query_build_logs

#!/bin/bash
# Copyright 2019 The Chromium OS Authors. All rights reserved.
# Use of this source code is governed by a BSD-style license that can be
# found in the LICENSE file.

# A helper to query build_log data contained in internal analysis_service
# events.

# Make sure we have gcert access so we don't fail without getting any work done.
gcertstatus >&/dev/null || gcert

when="last3days"
count_data=""
limit=30
field_limit=80
verbose=0
only_stdout=0
case_insensitive=1
execution_failure=0
execution_success=0
string_data_array=()
not_string_data_array=()
sort_order="DESC"
before_datestring=""
after_datestring=""

print_usage() {
  cat  <<HELP_USAGE

Usage:  $0 -c|--count <search_string>
           OR -s|--string <search_string>|-ns <search_string>
     [-w|-when] <when_string>
     [-l|--limit] <limit_string> [--field_limit <field_limit>]
     [--case_sensitive)]
     [--asc]
     [--es|--ef]
     [--join <string1> <string2>]
     [--before <datestring>]
     [--after <datestring>]
     [-v|--verbose] [--usage|-u|--help|-h]

     If -c is specified, count the occurrences of the search string.
     If -s is specified, display the matching occurences based on the other
       arguments (-l, --field_limit, etc).
     --asc changes sort order (for non-count queries) from DESC to ASC.
     if --join is specified with <string1> and <string2>, then find
       cases where one stage matches <string1> and another stage
       matches <string2> and both stages have the same build_id.
     NOTE: -c, -s, and --join are mutually exclusive options.
     If --es is specified, then only steps with a successful (NULL)
       step_execution_result are considered.
     If --ef is specified, then only steps with a failing (non-NULL)
       step_execution_result are considered.
     The datestring for --before and --after is 'YYYY-MM-DD'.  Either
       --before or --after (or both) can be specified, each with its
       own datestring.
     Multiple -s arguments can be supplied, which allows an AND query,
     so that
       query_build_logs -s "String1" -s "String2" -s "String3"
     returns run where the output contains all 3 search strings.
     The -ns species results that do NOT match. An -ns args can
     be used alone but is typically paired with a -s arg to eliminate some
     matches, such as
       query_build_logs -s "String1" -s "String2" -ns "string3"
     to find logs that contain String1 and String2 but not String3

     search_string : What to search for in build_cmd logs.
     when_string   : Which dremel table, such as last7days, last1days, etc.
                     (default=last3days)
     limit_string  : Limits numbers of rows to this value (default=30).
     field_limit   : Show <field_limit> characters of stdout starting with
                     <search_string> (default=80).
     only_stdout   : Show only stdout and date, not buildId, stepName, etc.
     case_sensitive: Match (or for 'ns', don't match) case sensitive.
                     Note that case-insensitive is the default.
     verbose       : Show dremel query before executing it.
HELP_USAGE
  exit 0
}

while (( "$#" )); do
  case "$1" in
    -s|--string)
      string_data_array=( "${string_data_array[@]}" "$2" )
      shift 2
      ;;
    -ns|--not_string)
      not_string_data_array=( "${not_string_data_array[@]}" "$2" )
      shift 2
      ;;
    -c|--count)
      count_data=$2
      shift 2
      ;;
    -w|--when)
      when=$2
      shift 2
      ;;
    -l|--limit)
      limit=$2
      shift 2
      ;;
    --field_limit)
      field_limit=$2
      shift 2
      ;;
    --before)
      before_datestring=$2
      shift 2
      ;;
    --after)
      after_datestring=$2
      shift 2
      ;;
    --only_stdout)
      only_stdout=1
      shift 1
      ;;
    --asc)
      sort_order="ASC"
      shift 1
      ;;
    --ef)
      execution_failure=1
      shift 1
      ;;
    --es)
      execution_success=1
      shift 1
      ;;
    --join)
      join_1=$2
      join_2=$3
      shift 3
      ;;
    --case_sensitive)
      case_insensitive=0
      shift 1
      ;;
    -v|--verbose)
      verbose=1
      shift
      ;;
    --usage|-u|--help|-h)
      print_usage
      shift
      ;;
    *)
      echo "Unknown arg: $1"
      print_usage
      shift
  esac
done

string_data_arraylen=${#string_data_array[@]}
not_string_data_arraylen=${#not_string_data_array[@]}

if [[ $string_data_arraylen -ne 0 ]] && [[ -n ${count_data} ]]; then
  echo "Specifying both -s and -c is not allowed."
  print_usage
fi

source_data="stdout"
if [[ ${case_insensitive} -eq 1 ]]; then
  source_data="LOWER(stdout)"
fi

# If a count was requested, build and execute the query. This code path is
# separate because it is much simpler than search queries.
if [[ -n ${count_data} ]]; then
  # Note that we use count(stdout) regardless of case-sensitivity. The WHERE
  # part of the query will use ${source_data} to handle case-sensitivity.
  query="SELECT count(stdout) AS Count FROM
  chromeos_ci_eng.analysis_event_log.${when} WHERE ${source_data} LIKE "
  if [[ ${case_insensitive} -eq 1 ]]; then
    query+=" LOWER(\"%${count_data}%\")"
  else
    query+=" \"%${count_data}%\""
  fi
  if [ "${execution_failure}" -eq 1 ]; then
    query+=" AND step_execution_result IS NOT NULL;"
  elif [ "${execution_success}" -eq 1 ]; then
    query+=" AND step_execution_result IS NULL;"
  else
    query+=";"
  fi
  if [ "${verbose}" -eq 1 ]; then
    echo "QUERY: ${query}"
    echo "Executing..."
  fi
  echo "${query}" | dremel --min_completion_ratio 1
  exit 0
fi

# This is a special branch for handling the --join case, which does an
# INNER JOIN with the two join arguments to find cases where one step
# matches the first arg and another step matches the second arg and
# both steps are part of the same build.
if [[ -n ${join_1} ]]; then
  source_1="stdout"
  if [[ ${case_insensitive} -eq 1 ]]; then
    source_1="LOWER(stdout)"
  fi
  source_2="stdout_2"
  if [[ ${case_insensitive} -eq 1 ]]; then
    source_2="LOWER(stdout_2)"
  fi
  echo "JOIN $join_1 AND $join_2"
  query="SELECT CONCAT('https://ci.chromium.org/b/', CAST(build_id AS STRING))
  as BuildId, step_name, SUBSTR(${source_1}, STRPOS(${source_1}, \"${join_1}\"),
  ${field_limit}) AS stdout_1, step_name1,
  SUBSTR(${source_2}, STRPOS(${source_2}, \"${join_2}\"), ${field_limit})
  AS stdout_2, DATETIME(TIMESTAMP_SECONDS(request_time.seconds)) AS DateTime"


  query+=" FROM chromeos_ci_eng.analysis_event_log.${when}"
  query+=" INNER JOIN (select build_id AS build_id1,"
  query+=" step_name AS step_name1, stdout AS stdout_2"
  query+=" FROM chromeos_ci_eng.analysis_event_log.${when})"
  query+=" ON build_id = build_id1 AND step_name != step_name1"
  query+=" WHERE $source_1 "
  if [[ ${case_insensitive} -eq 1 ]]; then
    query+=" LIKE LOWER(\"%${join_1}%\")"
  else
    query+=" LIKE \"%${join_1}%\""
  fi
  query+=" AND $source_2 "
  if [[ ${case_insensitive} -eq 1 ]]; then
    query+=" LIKE LOWER(\"%${join_2}%\")"
  else
    query+=" LIKE \"%${join_2}%\""
  fi

  if [[ -n ${before_datestring} ]]; then
    query+=" AND DATETIME(TIMESTAMP_SECONDS(request_time.seconds))"
    query+=" < '${before_datestring}'"
  fi

  if [[ -n ${after_datestring} ]]; then
    query+=" AND DATETIME(TIMESTAMP_SECONDS(request_time.seconds))"
    query+=" > '${after_datestring}'"
  fi

  query+=" ORDER BY DateTime ${sort_order} LIMIT ${limit};"
  if [ "${verbose}" -eq 1 ]; then
    echo "QUERY: ${query}"
    echo "Executing..."
  fi
  echo "${query}" | dremel --min_completion_ratio 1
  exit 0
fi

if [[ $string_data_arraylen -eq 0 && $not_string_data_arraylen -eq 0 ]]; then
  echo "No search string specified."
  echo " -s|--string <search_string> or -ns <search_string> is required."
  print_usage
fi


select_fields=""
for (( index=0; index < $string_data_arraylen; index++ ))
do
  string_data=${string_data_array[$index]}

  # Field_limit is always set (even w/o cmd line arg), so set select_field
  # based on array and field_limit.
  select_field="SUBSTR(${source_data}, STRPOS(${source_data},"
  select_field="${select_field} \"${string_data}\"),"
  select_field="${select_field} ${field_limit}) AS Stdout$index,"
  select_fields="$select_fields $select_field"
done

if [ "${only_stdout}" -eq 1 ]; then
  query="SELECT ${select_fields}
  DATETIME(TIMESTAMP_SECONDS(request_time.seconds)) AS DateTime"
else
  query="SELECT ${select_fields}
  CONCAT('https://ci.chromium.org/b/', CAST(build_id AS STRING)) as BuildId,step_name,
  DATETIME(TIMESTAMP_SECONDS(request_time.seconds)) AS DateTime"
fi

query+=" FROM chromeos_ci_eng.analysis_event_log.${when}"

for (( index=0; index < $string_data_arraylen; index++ ))
do
  string_data=${string_data_array[$index]}
  if [[ $index -eq 0 ]]; then
    query+=" WHERE ${source_data}"
  else
    query+=" AND ${source_data}"
  fi
  if [[ ${case_insensitive} -eq 1 ]]; then
    query+=" LIKE LOWER(\"%${string_data}%\")"
  else
    query+=" LIKE \"%${string_data}%\""
  fi
done

for (( index=0; index < $not_string_data_arraylen; index++ ))
do
  not_string_data=${not_string_data_array[$index]}
  if [[ $index -eq 0 && $string_data_arraylen -eq 0 ]]; then
    query+=" WHERE ${source_data}"
  else
    query+=" AND ${source_data}"
  fi
  if [[ ${case_insensitive} -eq 1 ]]; then
    query+=" NOT LIKE LOWER(\"%${not_string_data}%\")"
  else
    query+=" NOT LIKE \"%${not_string_data}%\""
  fi
done

if [ "${execution_failure}" -eq 1 ]; then
  query+=" AND step_execution_result IS NOT NULL"
elif [ "${execution_success}" -eq 1 ]; then
  query+=" AND step_execution_result IS NULL"
fi

if [[ -n ${before_datestring} ]]; then
  query+=" AND DATETIME(TIMESTAMP_SECONDS(request_time.seconds))"
  query+=" < '${before_datestring}'"
fi

if [[ -n ${after_datestring} ]]; then
  query+=" AND DATETIME(TIMESTAMP_SECONDS(request_time.seconds))"
  query+=" > '${after_datestring}'"
fi

query+=" ORDER BY DateTime ${sort_order} LIMIT ${limit};"

if [ "${verbose}" -eq 1 ]; then
  echo "QUERY: ${query}"
  echo "Executing..."
fi
echo "${query}" | dremel --min_completion_ratio 1