#!/bin/sh

# Скрипт экспортирует статистику за день в БД

REP_Y=`date -v-1d "+%Y"` # report year
REP_M=`date -v-1d "+%m"` # report month
REP_D=`date -v-1d "+%d"` # report day

WORKDIR="/root/netflow"
TMPDIR="/tmp"
FLOWS_DIR="/var/db/flows"
EXP_FORMAT="DPKTS,DOCTETS,SRCADDR,DSTADDR,NEXTHOP,INPUT,OUTPUT,SRCPORT,DSTPORT,PROT,TOS,TCP_FLAGS,SRC_MASK,DST_MASK"

DB_HOST="192.168.7.253"
DB_PORT="5432"
DB_USER="netflow"
DB_PASS="1234"
DB_NAME="netflow"
DB_TABLE="${REP_Y}-${REP_M}"
DB_COLUMNS="\
\"dpkts\",\
\"doctets\",\
\"srcaddr\",\
\"dstaddr\",\
\"nexthop\",\
\"input\",\
\"output\",\
\"srcport\",\
\"dstport\",\
\"proto\",\
\"tos\",\
\"tcp_flags\""


EXP_FILE="${TMPDIR}/netflow.csv"
SQL_FILE="${TMPDIR}/netflow.sql"
LOG_FILE="${WORKDIR}/nf_daily.log"

flow-cat "${FLOWS_DIR}/${REP_Y}/${REP_Y}-${REP_M}/${REP_Y}-${REP_M}-${REP_D}" | \
flow-export -f2 -m ${EXP_FORMAT} 2> /dev/null | \
egrep -v "^#" > ${EXP_FILE}

if [ ! -f ${EXP_FILE} ]; then
	echo "Export file not found"
	exit 1
fi

if [ -f ${SQL_FILE} ]; then
	rm -f ${SQL_FILE}
fi

for line in `cat ${EXP_FILE}`; do
	data=`echo ${line} | awk '{ split($0, str, ",");
														printf("%u, %u, $$%s$$, $$%s$$, $$%s$$, %d, %d, %d, %d, %d, %d, %d",
																		str[1], str[2], str[3], str[4],
																		str[5], str[6], str[7], str[8],
																		str[9], str[10], str[11], str[12]);
														}'`
	echo "INSERT INTO \"${DB_TABLE}\"(${DB_COLUMNS}) VALUES (${data});" >> $SQL_FILE

continue;

	C_DFLOWS=`echo ${line}  | awk '{ split($0, str, ","); print str[1]; }'`
	C_DPKTS=`echo ${line}   | awk '{ split($0, str, ","); print str[2]; }'`
	C_DOCTETS=`echo ${line} | awk '{ split($0, str, ","); print str[3]; }'`
	C_SRCADDR=`echo ${line} | awk '{ split($0, str, ","); print str[4]; }'`
	C_DSTADDR=`echo ${line} | awk '{ split($0, str, ","); print str[5]; }'`
	C_NEXTHOP=`echo ${line} | awk '{ split($0, str, ","); print str[6]; }'`
	C_INPUT=`echo ${line}   | awk '{ split($0, str, ","); print str[7]; }'`
	C_OUTPUT=`echo ${line}  | awk '{ split($0, str, ","); print str[8]; }'`
	C_SRCPORT=`echo ${line} | awk '{ split($0, str, ","); print str[9]; }'`
	C_DSTPORT=`echo ${line} | awk '{ split($0, str, ","); print str[10]; }'`
	C_PROTO=`echo ${line}   | awk '{ split($0, str, ","); print str[11]; }'`
	C_TOS=`echo ${line}   | awk '{ split($0, str, ","); print str[12]; }'`
	C_TCP_FLAGS=`echo ${line}   | awk '{ split($0, str, ","); print str[13]; }'`

	echo "\
INSERT INTO \`${DB_TABLE}\`(${DB_COLUMNS}) VALUES (\
	${C_DFLOWS},\
	${C_DPKTS},\
	${C_DOCTETS},\
	'${C_SRCADDR}',\
	'${C_DSTADDR}',\
	'${C_NEXTHOP}',\
	${C_INPUT},\
	${C_OUTPUT},\
	${C_SRCPORT},\
	${C_DSTPORT},\
	${C_PROTO},\
	${C_TOS},\
	${C_TCP_FLAGS}
);" >> ${SQL_FILE}
done

psql -q -d ${DB_NAME} -h ${DB_HOST} -p ${DB_PORT} -U ${DB_USER}  -f $SQL_FILE

rm -f ${EXP_FILE}
rm -f ${SQL_FILE}

#echo "${REP_Y}-${REP_M}-${REP_D}" > $LOG_FILE

exit 0