commit fe789ee72a77a5247e6e0019adfe9bb6ed125298
parent 4bda2254b937d00fb6d7c4c76cea7e55f66dda2c
Author: Silas Brack <silasbrack@gmail.com>
Date: Sun, 5 Jul 2026 16:48:24 +0200
refactor: query-logs opens interactive DuckDB with app + nginx tables
Instead of taking a source and SQL as args, query-logs now opens
an interactive DuckDB session with pre-loaded `app` and `nginx`
tables for the given time range.
Usage:
query-logs # last 24h
query-logs 2026-07-05T16 2026-07-05T17 # specific range
# then in DuckDB:
SELECT * FROM app WHERE message ILIKE '%error%';
SELECT status, count(*) FROM nginx GROUP BY status;
Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>
Diffstat:
| M | flake.nix | | | 115 | ++++++++++++++++++++++++++++++++++--------------------------------------------- |
1 file changed, 50 insertions(+), 65 deletions(-)
diff --git a/flake.nix b/flake.nix
@@ -79,80 +79,65 @@
query-logs = pkgs.writeShellScriptBin "query-logs" ''
set -euo pipefail
BASE="http://10.100.0.7:9090"
+ SINCE="''${1:-$(date -d '24 hours ago' +%Y-%m-%dT%H)}"
+ UNTIL="''${2:-$(date +%Y-%m-%dT%H)}"
- usage() {
- echo "Usage: query-logs <source> [since] [until] [sql]"
- echo ""
- echo "Sources:"
- echo " app - simple-web-app journald logs"
- echo " nginx - nginx access logs"
- echo ""
- echo "Examples:"
- echo " query-logs app"
- echo " query-logs nginx 2026-07-01T00 2026-07-04T00"
- echo " query-logs app 2026-07-01T00 2026-07-02T00 \"SELECT * FROM logs WHERE message ILIKE '%error%'\""
- exit 1
+ collect_urls() {
+ local dir=$1 ext=$2 urls="" current end path url
+ current=$(date -d "''${SINCE}:00:00" +%s)
+ end=$(date -d "''${UNTIL}:00:00" +%s)
+ while [ "$current" -le "$end" ]; do
+ path=$(date -d "@$current" +%Y/%m/%d/%H)
+ url="$BASE/$dir/$path.$ext"
+ if ${pkgs.curl}/bin/curl -sf --head "$url" > /dev/null 2>&1; then
+ urls="$urls '$url',"
+ fi
+ current=$((current + 3600))
+ done
+ echo "''${urls%,}"
}
- [ "''${1:-}" = "-h" ] || [ "''${1:-}" = "--help" ] && usage
- SOURCE="''${1:-app}"
- SINCE="''${2:-$(date -d '24 hours ago' +%Y-%m-%dT%H)}"
- UNTIL="''${3:-$(date +%Y-%m-%dT%H)}"
- SQL="''${4:-SELECT * FROM logs ORDER BY timestamp DESC LIMIT 50}"
+ echo "Scanning $SINCE → $UNTIL ..."
+ app_urls=$(collect_urls app json.gz)
+ nginx_urls=$(collect_urls nginx log.gz)
- case "$SOURCE" in
- app) ext="json.gz"; dir="app" ;;
- nginx) ext="log.gz"; dir="nginx" ;;
- *) echo "Unknown source: $SOURCE"; usage ;;
- esac
+ init=""
+ init="$init LOAD httpfs;"
- # Generate hourly URLs, skipping missing files
- urls=""
- current=$(date -d "''${SINCE}:00:00" +%s)
- end=$(date -d "''${UNTIL}:00:00" +%s)
- while [ "$current" -le "$end" ]; do
- path=$(date -d "@$current" +%Y/%m/%d/%H)
- url="$BASE/$dir/$path.$ext"
- if ${pkgs.curl}/bin/curl -sf --head "$url" > /dev/null 2>&1; then
- urls="$urls '$url',"
- fi
- current=$((current + 3600))
- done
- if [ -z "$urls" ]; then
- echo "No log files found for $SOURCE in the given range."
- exit 0
+ if [ -n "$app_urls" ]; then
+ init="$init CREATE TABLE app AS SELECT
+ to_timestamp(json_extract_string(j, '\$.__REALTIME_TIMESTAMP')::BIGINT / 1000000) as timestamp,
+ json_extract_string(j, '\$.MESSAGE') as message,
+ json_extract_string(j, '\$.PRIORITY') as priority,
+ json_extract_string(j, '\$.SYSLOG_IDENTIFIER') as identifier
+ FROM read_json_auto([$app_urls], format='newline_delimited', ignore_errors=true) j;"
+ echo " app: ready"
+ else
+ echo " app: no logs found"
fi
- urls="[''${urls%,}]"
- if [ "$SOURCE" = "app" ]; then
- ${pkgs.duckdb}/bin/duckdb -c "
- LOAD httpfs;
- CREATE TEMP TABLE logs AS
- SELECT
- to_timestamp(json_extract_string(j, '\$.__REALTIME_TIMESTAMP')::BIGINT / 1000000) as timestamp,
- json_extract_string(j, '\$.MESSAGE') as message,
- json_extract_string(j, '\$.PRIORITY') as priority,
- json_extract_string(j, '\$.SYSLOG_IDENTIFIER') as identifier
- FROM read_json_auto($urls, format='newline_delimited', ignore_errors=true) j;
- $SQL;
- "
+ if [ -n "$nginx_urls" ]; then
+ init="$init CREATE TABLE nginx AS SELECT
+ regexp_extract(line, '^\S+', 0) as remote_addr,
+ regexp_extract(line, '\[([^\]]+)\]', 1) as timestamp,
+ regexp_extract(line, '\"(\S+) (\S+) \S+\"', 1) as method,
+ regexp_extract(line, '\"(\S+) (\S+) \S+\"', 2) as path,
+ CAST(regexp_extract(line, '\" (\d+) ', 1) AS INTEGER) as status,
+ CAST(regexp_extract(line, '\" \d+ (\d+)', 1) AS INTEGER) as bytes,
+ regexp_extract(line, '\"([^\"]*)\"\s*\$', 1) as user_agent
+ FROM read_csv([$nginx_urls], header=false, columns={'line': 'VARCHAR'}, auto_detect=false, delim=E'\x01', quote=E'\x02', escape=E'\x02') t
+ WHERE line IS NOT NULL AND length(line) > 0;"
+ echo " nginx: ready"
else
- ${pkgs.duckdb}/bin/duckdb -c "
- LOAD httpfs;
- CREATE TEMP TABLE logs AS
- SELECT
- regexp_extract(line, '^\S+', 0) as remote_addr,
- regexp_extract(line, '\[([^\]]+)\]', 1) as timestamp_raw,
- regexp_extract(line, '\"(\S+) (\S+) \S+\"', 1) as method,
- regexp_extract(line, '\"(\S+) (\S+) \S+\"', 2) as path,
- CAST(regexp_extract(line, '\" (\d+) ', 1) AS INTEGER) as status,
- CAST(regexp_extract(line, '\" \d+ (\d+)', 1) AS INTEGER) as bytes,
- regexp_extract(line, '\"([^\"]*)\"\s*$', 1) as user_agent
- FROM read_csv($urls, header=false, columns={'line': 'VARCHAR'}, auto_detect=false, delim=E'\x01', quote=E'\x02', escape=E'\x02') t
- WHERE line IS NOT NULL AND length(line) > 0;
- $SQL;
- "
+ echo " nginx: no logs found"
+ fi
+
+ if [ -z "$app_urls" ] && [ -z "$nginx_urls" ]; then
+ echo "No logs found for the given range."
+ exit 0
fi
+
+ ${pkgs.duckdb}/bin/duckdb -init <(echo "$init")
''
;
in
