fabio revised this gist . Go to revision
1 file changed, 0 insertions, 0 deletions
query-http-log renamed to query-http-log.sh
File renamed without changes
fabio revised this gist . Go to revision
1 file changed, 4 insertions, 4 deletions
query-http-log
| @@ -13,9 +13,9 @@ | |||
| 13 | 13 | ||
| 14 | 14 | query-http-log() { | |
| 15 | 15 | if [[ -z "$1" ]]; then | |
| 16 | - | echo "Usage: [cat|head|tail] /var/log/nginx/[logfile] | query-http-log <query> [extra args for sqlite-utils] | |
| 17 | - | ||
| 18 | - | Example: cat /var/log/nginx/access.log | query-http-log "select * from stdin where response_code = 404 and date > '2023-01-01' limit 10" | |
| 16 | + | echo "Usage: [cat|head|tail] /var/log/nginx/[logfile] | query-http-log <query> [extra args for sqlite-utils]" | |
| 17 | + | echo "" | |
| 18 | + | echo "Example: cat /var/log/nginx/access.log | query-http-log \"select * from stdin where response_code = 404 and date > '2023-01-01\' limit 10" | |
| 19 | 19 | return 1 | |
| 20 | 20 | fi | |
| 21 | 21 | ||
| @@ -36,4 +36,4 @@ $( | |||
| 36 | 36 | done | |
| 37 | 37 | ) | |
| 38 | 38 | EOF | |
| 39 | - | } | |
| 39 | + | } | |
Fabio Manganiello revised this gist . Go to revision
1 file changed, 39 insertions
query-http-log(file created)
| @@ -0,0 +1,39 @@ | |||
| 1 | + | #!/bin/sh | |
| 2 | + | ||
| 3 | + | ################################################################################ | |
| 4 | + | # A utility function to convert an HTTP log stdin into CSV and pipe its output # | |
| 5 | + | # to sqlite-utils for easy querying # | |
| 6 | + | # # | |
| 7 | + | # NOTE: It requires the `sqlite-utils` command-line tool to be installed. # | |
| 8 | + | # Install it with `pip install sqlite-utils` # | |
| 9 | + | # # | |
| 10 | + | # @author: Fabio Manganiello <fabio@manganiello.tech> # | |
| 11 | + | # @license: MIT # | |
| 12 | + | ################################################################################ | |
| 13 | + | ||
| 14 | + | query-http-log() { | |
| 15 | + | if [[ -z "$1" ]]; then | |
| 16 | + | echo "Usage: [cat|head|tail] /var/log/nginx/[logfile] | query-http-log <query> [extra args for sqlite-utils] | |
| 17 | + | ||
| 18 | + | Example: cat /var/log/nginx/access.log | query-http-log "select * from stdin where response_code = 404 and date > '2023-01-01' limit 10" | |
| 19 | + | return 1 | |
| 20 | + | fi | |
| 21 | + | ||
| 22 | + | cat <<EOF | sqlite-utils memory stdin:csv --csv $* | |
| 23 | + | ip,date,query,response_code,response_size,user_agent | |
| 24 | + | $( | |
| 25 | + | while read -r line; do | |
| 26 | + | ip=$(echo "$line" | awk '{print $1}') | |
| 27 | + | timestamp=$(echo "$line" | awk '{print $4}' | sed 's/\[//;s/\]//') | |
| 28 | + | # Convert to "DD MMM YYYY HH:MM:SS" format that date can handle | |
| 29 | + | formatted=$(echo "$timestamp" | sed 's|/| |g; s|:| |') | |
| 30 | + | date=$(date -d "$formatted" -Iseconds) | |
| 31 | + | query=$(echo "$line" | awk -F'"' '{print $2}') | |
| 32 | + | response_code=$(echo "$line" | awk '{print $9}') | |
| 33 | + | response_size=$(echo "$line" | awk '{print $10}') | |
| 34 | + | user_agent=$(echo "$line" | awk -F'"' '{print $6}') | |
| 35 | + | echo "$ip,\"$date\",\"$query\",$response_code,$response_size,\"$user_agent\"" | |
| 36 | + | done | |
| 37 | + | ) | |
| 38 | + | EOF | |
| 39 | + | } | |