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 prod access so we don't fail without getting any work done.
prodcertstatus 1>/dev/null 2>/dev/null || prodaccess

when="last3days"
count_data=""
limit=30
field_limit=80
verbose=0
only_stdout=0
case_insensitive=1
string_data_array=()
not_string_data_array=()

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)]
     [-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).
     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
      ;;
    --only_stdout)
      only_stdout=1
      shift 1
      ;;
    --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 ]] && [[ ! -z ${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 [[ ! -z ${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="${query} LOWER(\"%${count_data}%\");"
  else
    query="${query} \"%${count_data}%\";"
  fi
  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="$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="$query WHERE ${source_data}"
  else
    query="$query AND ${source_data}"
  fi
  if [[ ${case_insensitive} -eq 1 ]]; then
    query="$query LIKE LOWER(\"%${string_data}%\")"
  else
    query="$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="$query WHERE ${source_data}"
  else
    query="$query AND ${source_data}"
  fi
  if [[ ${case_insensitive} -eq 1 ]]; then
    query="${query} NOT LIKE LOWER(\"%${not_string_data}%\")"
  else
    query="${query} NOT LIKE \"%${not_string_data}%\""
  fi
done

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

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