Converts sync nginx logs to sqlite3 file

sync-logs-to-sqlite.sh

#!/bin/sh

# Extracts and converts a sync storage log file into an sqlite3
# database to make it easier to extract information

if [ ! -e $1 ]; then
    echo "Usage $0 <gzip sync log file>"
    exit 1
fi

BASE=$(basename -s '.gz' $1)
OUT=$BASE.csv
DB=$BASE.db

if [ -e $OUT ]; then
    rm $OUT
fi

echo "Creating $OUT"
gunzip -c $1 \
    | grep -E '(GET|POST|HEAD|DELETE|PUT)' \
    | sed 's/\/1.5\/\([0-9]*\)\//\1 /' \
    | tr -d '"[],' \
    | awk 'BEGIN {print "date,method,uid,path,code,resp_sz,req_sz,req_ms"} {printf "%s,%s,%s,%s,%s,%s,%s,%s\n", $2,$3,$4,$5,$7,$8,$9,int($(NF-1)*1000)}' \
    > $OUT

# stuff it into an sqlite3 database
if [ -e $DB ]; then
    rm $DB
fi

# Create a table named "d" with has the above data
echo "Creating $DB"
printf ".mode csv\n.import $OUT d" | sqlite3 $DB

rm $OUT