Flow to SQLite

#!/bin/sh
 
#
# Insert into /etc/flow-tools/flow-capture.conf
# -p /var/run/flow-capture.pid -n 287 -N 0 -w /var/netflow/ -R /root/bin/flow-to-sqlite.sh -S 5 0/0/8818
#
 
FLOWDATA=/var/netflow
WORKDIR=/var/sqlite/netflow
TEMPLATE=db-template.db
 
flow_file=$FLOWDATA/$1
 
cd $WORKDIR
 
dt=`echo $1 | cut -d "." -f2`
ddt=`echo $dt | cut -d "-" -f1,2`
 
# echo `date` "Processing on: " $flow_file " -- " traf-$ddt.db >> /var/log/alxtest.log
 
if [ ! -f "traf-$ddt.db" ]; then
    cp $TEMPLATE traf-$ddt.db
fi
 
flow-print -f5 < $flow_file | awk -v dt=$dt '.
    FNR < 3 {next}.
    BEGIN { print "PRAGMA foreign_keys=OFF;\nBEGIN TRANSACTION;" }.
 
    {.
        split($2, t, ".");
        split(t[2], hms, ":");
        split(dt, dty, "-");
        secs=mktime(dty[1]" "dty[2]" "dty[3]" "hms[1]" "hms[2]" "hms[3]) + 36000;
 
        print "INSERT INTO traf VALUES (" secs ",\"" $4 "\"," $5 ",\"" $7 "\"," $8 "," $9 "," $12 ");"
    }.
 
    END { print "COMMIT;" }
' | sqlite3 traf-$ddt.db
 
# ' | sqlite3 traf-$ddt.db && mv $flow_file $FLOWDATA/arch

CREATE TABLE traf (
    dt          INTEGER  NOT NULL DEFAULT 0,
    sip         CHAR(15) NOT NULL DEFAULT '0.0.0.0',
    sport       INTEGER  NOT NULL DEFAULT 0,
    dip         CHAR(15) NOT NULL DEFAULT '0.0.0.0',
    dport       INTEGER  NOT NULL DEFAULT 0,
    proto       INTEGER  NOT NULL DEFAULT 0,
    ds          INTEGER  NOT NULL DEFAULT 0
);