blob: 9909ef2039e77cdf140ca796544d7a4762e8fcfa [file] [log] [blame]
#!/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
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