Tadek’s Blog

Now that I have the infrastrucutre for asking meaningful queries, here’s what I could be interesting in. These can be nicely scripted and shown on the webpage using CGI (all these are sliding windows with no input parameters, thus no worries about SQL injections).

BTW: I know this SQL is ugly like hell, but the framwork is really flexible and these queries are very easy to write. Here they go:

(more…)

Since a while I have been (experimentally) running Bro on my server collecting connection summaries. This is a very nice feature of Bro, generating really accurate TCP flows (including information whether the connection was terminated correctly or not and others). The big advantage of connection summaries (over e.g. NetFlow) is their high accuracy and yet compact representation (for over 2 months ov data, I have only 21MB (250k lines) of data.

I wrote a simple perl parser parsing Bro’s connection status and writing it to a relational database (in this case Postgres). The parser can also run in “query” mode, producing a comma/space separated data for easy visualization (using AfterGlow or even GnuPlot).

Here’s how I run my collection: sudo bin/bro -i eth0 -f "host 85.10.194.212" conn

And parsing: tail -f conn.log | ../conn2db2csv.pl -i -d "dbi:Pg:dbname=bro;host=localhost" -u bro -p <password>

And plotting: ./conn2db2csv.pl -q "select time::date,dport, sum(brecv) from conn where dip='85.10.194.212' group by 1,2 having(sum(brecv) >0) order by 1,2" -d "dbi:Pg:dbname=bro;host=localhost" -u bro -p <password>

This can be nicely used by gnuplot. For example to plot daily traffic on let’s say port 22: ./conn2db2csv.pl -q "select time::date, sum(brecv) from conn where dip='85.10.194.212' and dport = 22 group by 1 having(sum(brecv) >0) order by 1" -d "dbi:Pg:dbname=bro;host=localhost" -u bro -p <password> -s ' ' > testplot

gnuplot set timefmt "%Y-%m-%d %H:%M:%S" set xdata time plot 'testplot' using 1:3

A few links I found useful while installing DB2 on Debian/Gentoo, etc:

• An alternate method of installing DB2 on Unix platforms
• RedBook: Up and Running with DB2 on Linux

Being new to the Gentoo world, I had to quickly write a few ebuilds. This process is reasonably well documented in Ebuild howto and wiki, however, some issues are not very clear.

Here’s what wasn’t obvious to me:

1. Installing packages using ebuild and a full path to your ebuild doesn’t work (from the man-page: The implementation of emerge /path/to/ebuild is broken and so this syntax shouldn’t be used).
2. In this case, to install your package you should probably create PORTDIR_OVERLAY in /etc/make.conf (e.g., PORTDIR_OVERLAY="/usr/local/portage", see this wiki page).
3. Your package has to be in one of the predefined categories. If you come up with something new, Portage will happily ignore it.
4. Before emerging, you should recompute the digest of your package using emerge /path/to/ebuild digest command.
5. It seems that from within the package, it is difficult to distinguish whether the package is being installed for the first time vs. updated and updated vs. removed. For this, I wrote a hack, which I documented in my blog.

I’ve recently been writing Gentoo ebuilds (interestingly enough, not being a Gentoo user myself) and faced the follwing problem: “How to distinguish between a package upgrade and a package removal from within the package?”

Gentoo has only a standard set of “hooks” that get called during package installation (related to install and uninstall, but not to upgade). The upgrade stage P1 -> P2 is performed as follows:

• P2.configure/compile/install
• P1.prerm()
• remove files from P1 that are not in P2
• P1.postrm()

This works fine for simple file replacement tasks, which are handled automatically. However, there are situations where you want something more sophisticated:

• preinst/postinst: configure the system to use the package (services, system variables, etc).
• prerm/postrm: remove the remnants of system configuration when the package is deleted.

For this to work, package P1 needs to know if it’s being upgraded and removed (no system conf. needed). Simiarly, P2 needs to know if P1 exists and the system has already been configured (so that the configuration is not removed on update). To make things even more difficult, a package can be upgraded, downgraded or reinstalled (for reinstall rm not called). After a bit of experimenting and scratching my head, here’s what I came up with:

#detecting upgrades while installing
pkg_preinst () {
    has_version "<${PF}" && UPGRADE=1 || UPGRADE=0
    has_version ">${PF}" && DOWNGRADE=1 || DOWNGRADE=0
    has_version "=${PF}" && REINSTALL=1 || REINSTALL=0

   #now in the preinst or postinst code we can 
   #perform different actions depending on the variables set
}

#detecting upgrades while removing
pkg_prerm () {
    has_version ">${PF}" && UPGRADE=1 || UPGRADE=0
    has_version "<${PF}" && DOWNGRADE=1 || DOWNGRADE=0  
    [ -n -${A} ] && REINSTALL=1 || REINSTALL=0

  #perform different actions depending on the variables set 
  #(either here or in posrm - the variables have to be be set right here
}

I wonder whether there’s abetter solution to this problem?

Diego pointed out that I have been doing it all wrong using standard getopt. Getopt::Long is much neater and more powerful than its predecessor.

Two pointers for how to use it: active-venture and perl.com.

Here’s my sample code: GetOptions('d=s' => \$C{'DB_DSN'}, 'u=s' => \$C{'DB_USER'}, 'p=s' => \$C{'DB_PASS'}, 'c' => \$C{'DB_CREATE'}, 't=s' => \$C{'DB_TABLE'}, 's=s' => \$C{'SEP'}, 'q:s' => sub { $mode = 2; $C{'DB_QUERY'}=$[1] if ($[1] ne '');}, 'i' => sub { $mode = 1; } ) or usage();