====== Flow to SQLite ======


<code bash>

#!/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

</code>

<code sql>
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
);
</code>