conntrack2sql

Today im wrote script that adding NAT table to SQL, because ISP want to know when and which IP assigned to user in NAT pool.
Simple

#!/bin/bash
###Placing NAT current users to DB
##################################
#Creating vars
mysql_host=x.y.z.a
mysql_user=user
mysql_pass=xxxx
mysql_db=netflow
local_net=10.66.68
out=`conntrack -L|awk '{print$(4),$(9)}'|grep dst|sort -n|uniq -d|sed 's/src\=//;s/dst\=//'`
srv_ip=`ip addr |grep $local_net|sed 's/\// /g'|awk '{print$(2)}'`
#Doing Job
echo "$out" | while read src_ip dst_ip; do
mysql -u$mysql_user -p$mysql_pass -h$mysql_host $mysql_db << EOF INSERT INTO pool (src_ip, dst_ip, srv_ip) values ('$src_ip','$dst_ip','$srv_ip') on duplicate key update date_end=CURRENT_TIMESTAMP(); EOF done

Leave a Reply