#!/usr/bin/env bash
#
# daily_tibero_check. - Extracts logs from a remote server by period/keyword and saves them locally
#
# Usage:
# ./daily_tibero_check. # Previous day (default)
# ./daily_tibero_check. 2026-10-01 # Specific day
# ./daily_tibero_check. 2026-10-01 2026-10-05 # Period (start date–end date, inclusive)
#
# The settings below can also be overridden with environment variables when running the script.
# REMOTE_HOST=10.0.0.5 KEYWORDS="ERROR,FATAL" ./daily_tibero_check.
# OUTPUT_DIR=/data/check ./daily_tibero_check. # → /data/check/10.0.0.5_2026-10-05_check.log
# DATE_FORMAT="YYYY/MM/DD hh:mm:ss" ./daily_tibero_check.
# SSH_PASSWORD='password' ./daily_tibero_check.
#
# Exit codes: 0 normal / 1 execution error / 2 unable to check because there are no logs for the period in the target file
# On failure: Creates IP_date_check_fail.log in OUTPUT_DIR instead of the result file and records the error details # ============================ Settings ============================
REMOTE_HOST="${REMOTE_HOST:-10.14.85.86}" # IP address to connect to
REMOTE_USER="tibero" # User to connect with # Connection password: Enter it exactly as written inside single quotation marks ($, ", !, and other special characters are allowed as-is)
# If the password contains a single quotation mark ('), enter it as '\''.
# If left blank, connects using an existing SSH key or by prompting for input.
DEFAULT_SSH_PASSWORD='tibero'
SSH_PASSWORD="${SSH_PASSWORD:-$DEFAULT_SSH_PASSWORD}" KEYWORDS="${KEYWORDS:-failed,shutdown}" # Keywords (comma-separated; extracts if any one is included) # Log path: Specify an absolute path (* can be used; .gz files are also read)
# Check the actual path on the server with echo $TB_HOME/instance/$TB_SID/log/slog/sys.log
REMOTE_LOG_PATH="${REMOTE_LOG_PATH:-/home/tibero/tibero7/instance/tibero/log/slog/sys.log}" # Log time format (case-sensitive)
# YYYY year MM month (01–12) Mon English month (Jan–Dec) DD day
# hh hour mm minute ss second SSS millisecond Other characters must match literally
# Example) 2026-09-04T16:04:40.048 → YYYY-MM-DDThh:mm:ss.SSS
# 2026/09/04 16:04:40 → YYYY/MM/DD hh:mm:ss
# 04-Sep-2026 16:04:40 → DD-Mon-YYYY hh:mm:ss
DATE_FORMAT="${DATE_FORMAT:-YYYY-MM-DDThh:mm:ss.SSS}" OUTPUT_DIR="${OUTPUT_DIR:-./daily_tibero_check_logs}" # Result directory (automatically creates IP_date_check.log, or IP_date_check_fail.log on failure)
SSH_PORT="${SSH_PORT:-22}"
SSH_KEY="${SSH_KEY:-}" # Key file path (if blank, uses the default SSH configuration)
IGNORE_CASE="${IGNORE_CASE:-0}" # 1 ignores case
# ============================================================== set -u FAIL_FILE="" # Set after preparing the output directory (errors before then are displayed only on the screen)
ERR_LOG="" # Temporary file for receiving SSH/remote-side error messages # Create the failure record file: execution information + remote error message + additional message
write_fail() {
[ -n "$FAIL_FILE" ] || return 0
{
echo "Execution time: $(date '+%Y-%m-%d %H:%M:%S')"
echo "Result : Failure"
echo "Server : ${REMOTE_USER}@${REMOTE_HOST}:${SSH_PORT}"
echo "Log : ${REMOTE_LOG_PATH}"
echo "Period : ${START_DATE} ~ ${END_DATE}"
echo "Keywords : ${KEYWORDS}"
echo "------------------------------------------------------------"
if [ -n "$ERR_LOG" ] && [ -s "$ERR_LOG" ]; then tr -d '\r' < "$ERR_LOG"; fi
if [ $# -gt 0 ]; then printf '%s\n' "$@"; fi
} > "$FAIL_FILE" 2>/dev/null \
&& echo "Failure record: ${FAIL_FILE}" >&2 \
|| echo "[WARN] Unable to write the failure record file: ${FAIL_FILE}" >&2
} die() { echo "[ERROR] $*" >&2; write_fail "[ERROR] $*"; exit 1; }
warn() { echo "[WARN] $*" >&2; } # Clean up temporary files on exit
CLEANUP_FILES=()
cleanup() { [ ${#CLEANUP_FILES[@]} -gt 0 ] && rm -f "${CLEANUP_FILES[@]}"; }
trap cleanup EXIT # Previous day's date (supports both Linux GNU date and macOS BSD date)
yesterday() {
if date --version >/dev/null 2>&1; then
date -d yesterday +%F
else
date -v-1d +%F
fi
} is_date() { [[ "$1" =~ ^[0-9]{4}-[0-9]{2}-[0-9]{2}$ ]]; } # Calculate the awk regular expression and the positions of the year/month/day (starting from 1) from DATE_FORMAT
# Result: DATE_RE, Y_POS, M_POS, D_POS, MON_TEXT (1 if English month)
build_date_regex() {
local fmt="$1" pos=1 c
DATE_RE=""; Y_POS=0; M_POS=0; D_POS=0; MON_TEXT=0
while [ -n "$fmt" ]; do
case "$fmt" in
YYYY*) DATE_RE+="[0-9][0-9][0-9][0-9]"; Y_POS=$pos; pos=$((pos+4)); fmt="${fmt:4}" ;;
SSS*) DATE_RE+="[0-9][0-9][0-9]"; pos=$((pos+3)); fmt="${fmt:3}" ;;
Mon*) DATE_RE+="(Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)"
M_POS=$pos; MON_TEXT=1; pos=$((pos+3)); fmt="${fmt:3}" ;;
MM*) DATE_RE+="[0-9][0-9]"; M_POS=$pos; pos=$((pos+2)); fmt="${fmt:2}" ;;
DD*) DATE_RE+="[0-9][0-9]"; D_POS=$pos; pos=$((pos+2)); fmt="${fmt:2}" ;;
hh*|mm*|ss*)
DATE_RE+="[0-9][0-9]"; pos=$((pos+2)); fmt="${fmt:2}" ;;
*)
c="${fmt:0:1}"
case "$c" in
[A-Za-z0-9:,\ -]) DATE_RE+="$c" ;;
'^'|'\') die "This character cannot be used in DATE_FORMAT: $c" ;;
*) DATE_RE+="[$c]" ;; # . [ ] / ( ) etc. are treated as literal characters
esac
pos=$((pos+1)); fmt="${fmt:1}" ;;
esac
done
[ "$Y_POS" -gt 0 ] && [ "$M_POS" -gt 0 ] && [ "$D_POS" -gt 0 ] \
|| die "DATE_FORMAT must contain all of YYYY, MM (or Mon), and DD: $1"
} # ---------- Search period ----------
case $# in
0) START_DATE="$(yesterday)"; END_DATE="$START_DATE" ;;
1) START_DATE="$1"; END_DATE="$1" ;;
2) START_DATE="$1"; END_DATE="$2" ;;
*) die "Usage: $0 [start date YYYY-MM-DD] [end date YYYY-MM-DD]" ;;
esac
is_date "$START_DATE" && is_date "$END_DATE" || die "The date format is YYYY-MM-DD."
[[ "$START_DATE" > "$END_DATE" ]] && die "The start date ($START_DATE) is later than the end date ($END_DATE)." # ---------- Output file ----------
OUTPUT_DIR="${OUTPUT_DIR%/}"
if [ "$START_DATE" = "$END_DATE" ]; then
OUT_BASE="${OUTPUT_DIR}/${REMOTE_HOST}_${START_DATE}_check"
else
OUT_BASE="${OUTPUT_DIR}/${REMOTE_HOST}_${START_DATE}_${END_DATE}_check"
fi
OUT_FILE="${OUT_BASE}.log"
mkdir -p "$OUTPUT_DIR" || die "Unable to create the output directory: $OUTPUT_DIR"
FAIL_FILE="${OUT_BASE}_fail.log" # Errors from this point are also recorded in the failure file # ---------- Check log path / time format ----------
[[ "$REMOTE_LOG_PATH" == /* ]] || die "Specify REMOTE_LOG_PATH as an absolute path (starting with /): $REMOTE_LOG_PATH"
[ -n "$DATE_FORMAT" ] || die "DATE_FORMAT is empty."
build_date_regex "$DATE_FORMAT" # ---------- Keyword parsing (comma-separated, remove leading/trailing spaces) ----------
[ -n "$KEYWORDS" ] || die "KEYWORDS is empty."
IFS=',' read -r -a RAW_KW <<< "$KEYWORDS"
KW=()
for k in "${RAW_KW[@]}"; do
k="${k#"${k%%[![:space:]]*}"}"
k="${k%"${k##*[![:space:]]}"}"
[ -n "$k" ] && KW+=("$k")
done
[ ${#KW[@]} -gt 0 ] || die "There are no valid keywords." # ---------- Temporary files ----------
TMP_FILE="$(mktemp "${OUT_FILE}.XXXXXX")" || die "Failed to create a temporary file"
ERR_LOG="$(mktemp "${OUTPUT_DIR}/.err.XXXXXX")" || die "Failed to create a temporary file"
CLEANUP_FILES+=("$TMP_FILE" "$ERR_LOG") # ---------- SSH options / authentication ----------
SSH_OPTS=(-p "$SSH_PORT" -o ConnectTimeout=10)
[ -n "$SSH_KEY" ] && SSH_OPTS+=(-i "$SSH_KEY")
SSH_CMD=(ssh)
AUTH_DESC="SSH key or direct input" if [ -n "$SSH_PASSWORD" ]; then
# Prevent the password from being entered instead of the host-key confirmation prompt for an unfamiliar server
# → When connecting to a server for the first time, connect manually once to register the host key
SSH_OPTS+=(-o StrictHostKeyChecking=yes) if command -v sshpass >/dev/null 2>&1; then
# sshpass -e: Pass the password through an environment variable (SSHPASS) so it is not exposed in the ps command
export SSHPASS="$SSH_PASSWORD"
SSH_CMD=(sshpass -e ssh)
AUTH_DESC="Automatic password input (sshpass)"
else
# If sshpass is unavailable, use SSH_ASKPASS_REQUIRE=force from OpenSSH 8.4 or later
SSH_VER="$(ssh -V 2>&1)"
read -r V_MAJOR V_MINOR <<< "$(printf '%s' "$SSH_VER" | sed -n 's/^OpenSSH_\([0-9]*\)\.\([0-9]*\).*/\1 \2/p')"
if [ "${V_MAJOR:-0}" -lt 8 ] || { [ "${V_MAJOR:-0}" -eq 8 ] && [ "${V_MINOR:-0}" -lt 4 ]; }; then
die "Automatic password input requires sshpass or OpenSSH 8.4 or later. (Current: $SSH_VER)"
fi
# Create a temporary askpass script that only outputs the password environment variable without writing the password to a file
ASKPASS_FILE="$(mktemp "${OUTPUT_DIR}/.askpass.XXXXXX")" || die "Failed to create a temporary file"
CLEANUP_FILES+=("$ASKPASS_FILE")
cat > "$ASKPASS_FILE" <<'EOF'
#!/bin/sh
printf '%s\n' "$FETCH_LOG_SSH_PASSWORD"
EOF
chmod 700 "$ASKPASS_FILE"
export FETCH_LOG_SSH_PASSWORD="$SSH_PASSWORD" SSH_ASKPASS="$ASKPASS_FILE" SSH_ASKPASS_REQUIRE=force
SSH_OPTS+=(-o NumberOfPasswordPrompts=1)
AUTH_DESC="Automatic password input (SSH_ASKPASS)"
fi
fi echo "Server : ${REMOTE_USER}@${REMOTE_HOST}:${SSH_PORT}"
echo "Authentication: ${AUTH_DESC}"
echo "Log : ${REMOTE_LOG_PATH}"
echo "Time format : ${DATE_FORMAT}"
echo "Period : ${START_DATE} ~ ${END_DATE}"
echo "Keywords : $(IFS='|'; echo "${KW[*]}")" # ---------- Remote execution ----------
# Safely quote arguments with printf %q and pass them to the remote bash (supports spaces/special characters/Korean keywords)
{
printf 'set -- '
printf '%q ' "$START_DATE" "$END_DATE" "$REMOTE_LOG_PATH" "$IGNORE_CASE" \
"$DATE_FORMAT" "$DATE_RE" "$Y_POS" "$M_POS" "$D_POS" "$MON_TEXT" "${KW[@]}"
printf '\n'
cat <<'REMOTE_SCRIPT'
start="$1"; end="$2"; log_path="$3"; ignore_case="$4"
date_format="$5"; date_re="$6"; y_pos="$7"; m_pos="$8"; d_pos="$9"; mon_text="${10}"
shift 10 shopt -s nullglob
files=()
for f in $log_path; do [ -f "$f" ] && files+=("$f"); done
if [ ${#files[@]} -eq 0 ]; then
echo "Log file not found: $log_path" >&2
exit 13
fi grep_opts=(-F)
[ "$ignore_case" = "1" ] && grep_opts+=(-i)
for kw in "$@"; do grep_opts+=(-e "$kw"); done # Temporary file to receive the number of lines in the period / first date in the file / last date aggregated by awk
stat_file="$(mktemp)" || exit 1
trap 'rm -f "$stat_file"' EXIT # zcat -f: Reads both regular and compressed files
# awk: Finds the time matching DATE_FORMAT, filters by date period, and aggregates
# grep -F: Matches keywords as literal strings rather than regular expressions (OR condition)
# sort: Sorts by time when reading multiple files
zcat -f -- "${files[@]}" \
| DATE_RE="$date_re" awk -v s="$start" -v e="$end" -v sf="$stat_file" \
-v yp="$y_pos" -v mp="$m_pos" -v dp="$d_pos" -v mt="$mon_text" '
BEGIN { re = ENVIRON["DATE_RE"]; months = "JanFebMarAprMayJunJulAugSepOctNovDec" }
match($0, re) {
y = substr($0, RSTART + yp - 1, 4)
if (mt == 1) m = sprintf("%02d", (index(months, substr($0, RSTART + mp - 1, 3)) + 2) / 3)
else m = substr($0, RSTART + mp - 1, 2)
d = y "-" m "-" substr($0, RSTART + dp - 1, 2)
if (min == "" || d < min) min = d
if (d > max) max = d
if (d >= s && d <= e) { n++; print }
}
END { printf "%d %s %s\n", n, min, max > sf }' \
| grep "${grep_opts[@]}" \
| LC_ALL=C sort
st=("${PIPESTATUS[@]}")
read -r in_range fmin fmax < "$stat_file" # If there is not even one log line for the period in the target file, treat it as unable to check
if [ "${in_range:-0}" -eq 0 ]; then
{
echo "[ERROR] Unable to check messages for the period ${start} ~ ${end}."
echo " There are no logs for the specified dates in the target file: ${files[*]}"
if [ -n "$fmin" ]; then
echo " (Period recorded in the target file: ${fmin} ~ ${fmax})"
else
echo " (No time matching the DATE_FORMAT(${date_format}) format was found in the target file)"
fi
} >&2
exit 16
fi
[ "${st[0]}" -eq 1 ] && exit 14
exit 0
REMOTE_SCRIPT
} | "${SSH_CMD[@]}" "${SSH_OPTS[@]}" "${REMOTE_USER}@${REMOTE_HOST}" 'bash -s' > "$TMP_FILE" 2> "$ERR_LOG"
RC=$? # Display messages from SSH/remote execution on the screen (also recorded in the failure file on failure)
[ -s "$ERR_LOG" ] && tr -d '\r' < "$ERR_LOG" >&2 # 5–7 are sshpass-specific exit codes; 13–16 are remote script exit codes
case $RC in
0) ;;
5) die "The password is incorrect: ${REMOTE_USER}@${REMOTE_HOST}" ;;
6) die "The host key is not registered. First connect once with 'ssh -p ${SSH_PORT} ${REMOTE_USER}@${REMOTE_HOST}' and register it by entering yes." ;;
7) die "The server host key has changed. After checking the cause, remove it with 'ssh-keygen -R ${REMOTE_HOST}' and register it again." ;;
13) die "No log file exists on the remote server: $REMOTE_LOG_PATH" ;;
14) warn "An error occurred while reading some log files. Only the portions that were read will be saved." ;;
16) write_fail; exit 2 ;; # No logs for the period (records the remote error message in the failure file)
255)
if [ -n "$SSH_PASSWORD" ]; then
die "SSH connection failed: ${REMOTE_USER}@${REMOTE_HOST}:${SSH_PORT} (Check the password, host-key registration, and network. If this is the first connection to the server, connect once with 'ssh -p ${SSH_PORT} ${REMOTE_USER}@${REMOTE_HOST}' and register it by entering yes)"
else
die "SSH connection failed: ${REMOTE_USER}@${REMOTE_HOST}:${SSH_PORT}"
fi ;;
*) die "Remote command execution failed (exit $RC)" ;;
esac mv "$TMP_FILE" "$OUT_FILE" || die "Failed to save the result file: $OUT_FILE"
rm -f "$FAIL_FILE" # If rerunning for the same date succeeds, delete the previous failure record
LINES=$(wc -l < "$OUT_FILE" | tr -d ' ') if [ "$LINES" -eq 0 ]; then
echo "Complete : No keyword logs during the period (empty file created: $OUT_FILE)"
else
echo "Complete : ${LINES} lines → ${OUT_FILE}"
fi |