ipt-parse updated [per client bandwidth / website monitor]

Discussion in 'Tomato Firmware' started by SoftCoder, Nov 26, 2010.

  1. SoftCoder

    SoftCoder Addicted to LI Member

    I have update my ipt-parse tool to use an updated sqlite engine as well as performance, usability and stability improvements.

    Recap, this tool keeps track of Per Client bandwidth usage and now also adds Website monitoring. The website monitor requires more diskspace if you want to use it, so cifs or USB drive, but the bandwidth monitor WILL WORK standalone using jffs and keep about 3 months of bandwidth history (see the wiki below for details)

    The source code is available at launchpad here

    Prebuilt binaries for Tomato 1.23 and 1.28 as well as standard linux and windows binaries can be downloaded here

    v1.3.3 now adds the ability to read dns query entries in your /var/log/messages files and saves them per IP address to another table in the SQLite database. To setup website monitor in Tomato goto Advanced->DHCP / DNS Server and add the following line to dnsmasq:


    Now you can use ipt-parse to import dns queries and run sql queries per client IP to see which client is visiting which sites. USe this in combination with OpenDNS and you can pinpoint who visits those blocked websites from your network.

    For version info run:
    ./ipt-parse --version

    For help and examples:
    ./ipt-parse --help

    A wiki with detailed info exists here but it has not been updated to reflect this new release.

    For help etc, join me on IRC at freenode.net on #softhaus

  2. Toastman

    Toastman Super Moderator Staff Member Member

    I *think* I followed instructions as far as I could, but iptparse doesn't seem to show bandwidth stats in the daily or weekly html pages. I am using latest Tomato 1.28 USB for RT-N16. I saw the last post in your old thread also had a similar comment.
  3. CBR900

    CBR900 Network Guru Member


    I need detailed steps to get it working please....

    I just enabled cifs with my win7

    please help
  4. SoftCoder

    SoftCoder Addicted to LI Member

    First read the wiki for details:


    To actually SEE the status you need to use tomato's http 'extension' as also shown in the wiki. The scripts only capture and store the data AND produce the html file. To see the html file you need to copy it somehwere it can be seen. I show an example (And have it working on this end) of injecting javascript into tomato's menu system to show the stats via the /ext url:

    example: instead of the normal

    If you have followed the instructions on the wiki you should see the stats:


    P.S. you may ALSO run the linux or windows binaries against the BANDWIDTH database file and view stats on console or produce the html externally using the binaries for linux or windows.

  5. Toastman

    Toastman Super Moderator Staff Member Member

    The HTML files are being produced OK. I can access the html files by network share - apart from the headers and bandwidth in/out labels - they are both empty of data.

    BTW, one of my biggest problems is that the steps in the articles are not very clear, probably as a result of the continuing development. I did get the older version working and eventually filling up useagelogs/traffic in and out, but that also took several hours. I was prompted to try your mod by some local university students who are using it as an example SQlite application, but they could not get it working either.

    I'd love to se this as a built-in mod, did you ever get anywhere with it?
  6. SoftCoder

    SoftCoder Addicted to LI Member

    Ok, you can always ssh into your router and run the binary from a telnet session inside of your router to see what is going on. This version of ipt-parse (1.3.3) is fairly stable.

    1. First make sure you setup DHCP using Static IP's
    2. Setup the Tomato 'initialization' which registers which IP's will be monitored via looking at the static IP table. I do this in the 'Firewall' Script Event:

    cd /cifs1
    ./SetupUI.sh > SetupUI.log
    ./ipt-parse 6

    3. Setup tomato scheduler events (I setup for every minute):

    # This section imports data from iptables into the SQLite database
    cd /cifs1
    ./ipt-parse 2 BANDWIDTH flags=importdailyonly flags=purgeoldrecords=60 > ipt1.log

    # This section produces the html file from the SQLite database
    ./ipt-parse 4 today today BANDWIDTH flags=morehostinfo_append > dailybandwidthlive.html

    # This ensures all static IP's are registered
    ./ipt-parse 6

    # This section copies the bandwidth files to tomato's extension location
    mkdir /var/wwwext
    cp dailybandwidthlive.html /var/wwwext/dailybandwidthlive_1.asp
    cp /www/*.css /var/wwwext/

    # This manages website monitoring and is NOT required for bandwidth monitoring
    ./ipt-parse 8 flags=database=BANDWIDTH flags=dnslogfile=/var/log/messages flags=purgeoldrecords=60 flags=dnsignorehosts= flags=dnsignorewebsites=*kronos.com >dns.log

    ./ipt-parse 8 flags=database=BANDWIDTH flags=dnslogfile=/var/log/messages.0 flags=purgeoldrecords=60 flags=dnsignorehosts= flags=dnsignorewebsites=*kronos.com >>dns.log

    To trouble shoot feel free to ssh into your router and run these commands manually to see what output you get. Just curious what you meant 'get anywhere with it', with what?
  7. fngood

    fngood Networkin' Nut Member

    some fixes

    I had lots of initial problems setting things up, never having worked with Linux, but I managed to get everything working, using /jffs and then converting to /cifs1. I switched because I didn't want to lose any history if something happened to the NVRAM, plus I read that it can wear out, although some people have been using it for years with schedule rules set for every 5 minutes.

    The two things that helped me the most was being able to SSH into the router to test script commands and see the console output (I used the Terminal app on my Mac Mini) and the SQLite Manager plugin for FireFox to view the contents of the BANDWIDTH table.

    Below is a list of the things that tripped me up. Reading them may help solve some of the issues people have been having. They are in no particular order.

    1. to use SSH, go to Administration->Admin Access (in Tomato) and make sure "Enable at Startup" is checked off for the "SSH Daemon". I had all kinds of "Connection Refused" errors before I found that out. Also, the login is "root" and the password is whatever you set your router password to, so in the Terminal app (on my mac) I use "ssh root@" and then it asks me for the password.

    2. ssh into your your router and run the scripts individually. They'll output lots of info to the console. Conversely you can pip them to a file, like ".\ipt-parse 2 BANDWIDTH > log.txt". You'll be able to see if in fact rows are being imported to the tables. That's how I found my biggest problem (below).

    3. For some reason when I used the "importdailyonly" flag it erased all the rows of the "bandwidthusage" table in the BANDWIDTH database and then removed the table itself. Subsequent calls to ipt-parse would never add anything to the database because the table was gone. If you see no data in your "dailybandwithlive" page, that's probably why. I ended up using just ".\ipt-parse 2 BANDWIDTH" and it worked fine. - UPDATE: My BANDWIDTH database still gets corrupted periodically and the tables disappear.

    4. I used WinSCP on my Windows machine to copy the files to the router for the /jffs install. You can change the permissions using that too.

    5. Eventually I settled on a /cifs1 install. For that I'm using an IOMEGA iConnect device which shares drives (and printers) on a network without needing a machine running. Strangely, the drive I have hooked up is Windows formatted and I was able to run the "ipt-parse" program from the drive, which I read you're not supposed to be able to do. I did use "chmod 777 ipt-parse" from the Terminal app on my Mac. Not sure if I had to or not.

    6. The latest version that supports website monitoring is brilliant. The only issue I had was that the BANDWIDTH database I had (from the earlier version) didn't include the "dnsusage" table that is required. Using ssh and running the "ipt-parse 8" command I could see the SQL statements it was trying to use to insert information. From that I could see the table name and fields so I used the SQLite (Firefox plugin) to build the table in the database. One other small issue is that if you pipe the html output to a file the table has the headers from the dailybandwidthlive.html page.

    Can't think of anything else. Here's my Schedule #1 (every 5 mins) script for bandwidth monitoring per IP by day and week

    logger "Updating IP Bandwidth log"
    cd /cifs1/Tomato
    ./ipt-parse 6
    ./ipt-parse 2 BANDWIDTH > ipt1.log
    ./ipt-parse 4 today today BANDWIDTH flags=morehostinfo_append > dailybandwidthlive.html
    ./ipt-parse 4 today-7 today BANDWIDTH flags=morehostinfo_append > weeklybandwidthlive.html
    mkdir /var/wwwext
    cp dailybandwidthlive.html /var/wwwext/dailybandwidthlive.asp
    cp weeklybandwidthlive.html /var/wwwext/weeklybandwidthlive.asp
    cp /www/*.css /var/wwwext/

    and here's the one for website monitoring using schedule #2 (every 30 mins)

    cd /cifs1/Tomato
    ./ipt-parse flags=commandtype=8 flags=database=BANDWIDTH flags=purgeoldrecords=60 flags=dnslogfile=/var/log/messages,/var/log/messages.0 flags=dnsignorehosts=
    ./ipt-parse flags=commandtype=10 flags=database=BANDWIDTH flags=morehostinfo_append flags=startdate=today flags=enddate=today flags=sortby=host,website > dailywebusage1.html
    cp dailywebusage1.html /var/wwwext/dailywebusage1.asp
    ./ipt-parse flags=commandtype=10 flags=database=BANDWIDTH flags=morehostinfo_append flags=startdate=today flags=enddate=today flags=sortby=timestamp,website,host > dailywebusage2.html
    cp dailywebusage2.html /var/wwwext/dailywebusage2.asp

    Good luck!
  8. SoftCoder

    SoftCoder Addicted to LI Member


    Thanks for taking the time to document your experience, I am still working on this tool here and there. The latest code I have allows for an html 'template' file that uses special tags as an input and produces your customized html output file.

    What exact things should i change / fix to make this tool more easy to use?

  9. Toastman

    Toastman Super Moderator Staff Member Member

    The best thing would be to incorporate it into the GUI, make it part of Tomato - and upload the source code into the .git repository. That way, many more people will be able to use it!

    Thanks for your patience with this! Nice output.
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice