Dump ipcad output into sqlite3

sqliteipcad is IP accounting daemon with Cisco-like ip accounting export. It runs in background, listens traffic on the specified interfaces, and records the traffic for later retrieval and analysis.

Here is a piece of shell code that allows to export ipcad output into sqlite3 database format:

echo "create table traffic (src, dst, pkt, bt);" | sqlite3 /tmp/throttle.db
rsh 127.0.0.1 show ip accounting | grep "^ " | grep -vi source | awk \
'{print"insert into traffic values (\""$1"\",\""$2"\",\""$3"\",\""$4"\");"}' \
| sqlite3 /tmp/throttle.db

To make this working ipcad should be configured not to capture ports and to enable rsh service. In my case ipcad has the following settings set in ipcad.conf:

capture-ports disable;
interface eth0;
rsh enable at 127.0.0.1;
rsh 127.0.0.1 admin;
rsh ttl = 3;
rsh timeout = 30;
pidfile = /var/run/ipcad.pid;
memory_limit = 100m;

and output (rsh 127.0.0.1 show ip accouting) is like:

192.168.0.7      192.168.0.1                 113241           166387462
192.168.0.1      192.168.0.7                  72117             4282846
192.168.0.77     66.235.184.245                2448              821095
66.235.184.245   192.168.0.77                  3995              697371

The main problem is that it sqlite3 is rather slow and it takes eleven (11!!!) seconds to export 1000 entries of ipcad’s output into database. This was got at PC with 1.4Ghz CPU and 512Mb RAM.

If anybody knows how to get it faster, PLEASE LET ME KNOW! Thanks.

Information Improvisation: Traffic Engineering Server is new Solution for Bandwidth Management and QoS. It’s especially suitable for Broadband ISPs and SMEs.

SHARE:
nv-author-image

Stefan Durand

My name is Stefan, I'm the admin of LinuxScrew. I am a full-time Linux/Unix sysadmin, a hobby Python programmer, and a part-time blogger. I post useful guides, tips, and tutorials on common Linux and Programming issues. Feel free to reach out in the comment section.

Leave a Reply

Your email address will not be published. Required fields are marked *