Howto import SAR data into a PostgreSQL database and create graphs in OpenOffice.org

This is a walkthrough on how to get SAR data into a database, and use OpenOffice.org to create nice graphs using a JDBC connection to that database, and SUN Report Builder to do the graphs.

In this example I will use SAR’s CPU utilisation data.

Software used:

 

1. Setup the Database

I use a PostgreSQL database named sar here. The SAR data is on the same host as the database, and the PostgreSQL user has read permission on the SAR files.

First we need to create a table

CREATE TABLE sar_cpu 
(
"host" varchar, 
"interval" numeric, 
"time" timestamp with time zone, 
"offset" numeric(1), 
"user" numeric(5,2), 
"nice" numeric(5,2), 
"system" numeric(5,2), 
"iowait" numeric(5,2), 
"steal" numeric(5,2), 
"idle" numeric(5,2)
)

 

 

2. Convert SAR data to CSV

Then we need to output the SAR data in a format we can load into the database, using sadf.

LC_ALL=en_US sadf -d /var/log/sa/sa15 -- -u > /tmp/sa20090215.csv

Note: The LC_ALL=en_US ensures that decimal values are seperated with a dot, not a comma.

 

3. Load the CSV directly into the database

Then we can load that CSV directly into the database:

echo "COPY sar_cpu FROM '/tmp/sa20090215.csv' DELIMITERS ';' CSV;"|psql -d sar

Cool, isn’t it? This can be scripted easily and controlled via a cronjob, like import “yesterday’s sar data, using a simple wrapper script and some date function.

 

4. Create an OpenOffice.org Base DB and connect it to PostgreSQL

Now start OOo Base, create a new DB -> JDBC.

See http://jdbc.postgresql.org/documentation/83/connect.html for an overwiev about the connection parameters:

URL:

jdbc:postgresql:sar

Class:

org.postgresql.Driver

 

5. Install SUN Report Builder

Then download SRB from http://extensions.services.openoffice.org/project/reportdesign, open Extras -> Extension Manager, and install it.

In OOo Base you’ll find it in Reports -> Report in Design View

There you have the possibility to create graphs and diagrams as usual in Calc.

Howto connect OpenOffice to MS SQL server

Just had to connect to a MS SQL server that holds collected SAR statistics from our production servers.

Until now I used Toad in a virtual XP machine, but there has to be some way to connect in a graphical way from Linux too. And indeed there is!

Tools needed:

I chose JTDS, got the JAR file and copied it somehwere in the classpath. On OpenSUSE that is /usr/share/java. You can also add it in OpenOffice directly: Extras -> Options -> Java -> Classpath

Then fire up OpenOffice Base, and in the database wizard

  1. Open existing Database
  2. JDBC
jdbc:jtds:sqlserver://hostname:portnumber/databasename

The port number usually is 1433. The JDBC driver class is

net.sourceforge.jtds.jdbc.Driver

Then enter the db username, in the next step enter the password, and connect.

That’s it.

 

BASH arrays and filenames with spaces

I’m sure I can use this one quite often.

1) Arrays are nice to avaoid temporaray files

2) Spaces in file names are a PITA. But there are nice ways to work around this

Array[0]="/some/path/to/mooo foo.txt"
Array[1]="/some/path/to/mooo foo bar 2.txt"
Array[2]="/some/path/to/mooo foo bar 3.txt"

There we have some example array.

for ((i=0;i<${#Array[@]};i++)); do echo ${Array[$i]}; done
/some/path/to/mooo foo.txt
/some/path/to/mooo foo bar 2.txt
/some/path/to/mooo foo bar 3.txt

Ok, now what if want to pass that to ls, we could quote it somehow. Or use some pattern expansion to replace the space with backslash space, like

for ((i=0;i<${#Array[@]};i++)); do echo ${Array[$i]// /\\ }; done
/some/path/to/mooo\ foo.txt
/some/path/to/mooo\ foo\ bar\ 2.txt
/some/path/to/mooo\ foo\ bar\ 3.txt

Great! Another way would be to temporarily change the special variable $IFS that determines which character is used for word splitting. Let’s set this to “newline”

OLDIFS="$IFS"
IFS=$'\n'

for ((i=0;i<${#Array[$i]};i+=1)); do
   ls ${Array[$i]}
done

IFS="$OLDIFS"

That $IFS change can also be used in all sorts of other loops. Hell, I’m going to need that really often. Thanks to the guys on http://forums.gentoo.org/viewtopic-t-714971.html

Howto: Log firewall from OpenWrt to a remote rsyslog

This is how I got remote logging from my OpenWrt router to the syslog daemon on the server box.

On the server side, I enabled remote logging over UDP (refer to the rsyslog or syslog-ng documentation).

On the OpenWRT box following steps are needed

Enable remote syslog logging

Edit /etc/config/system and enable remote logging by adding:

option 'log_ip' '192.168.1.2'

Now reboot the router and see if it logs correctly.

Enable firewall logging (-j LOG)

Update (2013): In recent Openwrt builds this is as simple as editing /etc/config/firewall and adding a line to each zone that you want to get logged

config 'zone'
        option 'name' 'wan'
        ...
        option 'log' '1'

That’s all.

 

The info below is valid only for old OpenWRT builds Kamikaze 8.09 and older!

Then I had to get IPtables to produce some log output. With Kamikaze’s new firewall config layout this was a bit tricky. I decided to just log SYN flood protection actions, and the dropping of INVALID packets on INPUT and FORWARD chains. Therefore we need to edit /lib/firewall/uci_firewall.sh and add 3 lines (those with -j LOG)

In function fw_defaults()

$IPTABLES -A INPUT -m state --state INVALID -j LOG --log-prefix "DROP INVALID (INPUT): "
$IPTABLES -A INPUT -m state --state INVALID -j DROP
...
$IPTABLES -A FORWARD -m state --state INVALID -j LOG --log-prefix "DROP INVALID (FORWARD): "
$IPTABLES -A FORWARD -m state --state INVALID -j DROP

and for the SYN flood stuff, in function load_synflood()

$IPTABLES -A syn_flood -j LOG --log-prefix "SYN FLOOD: "
$IPTABLES -A syn_flood -j DROP

 

Bash pattern matching

BASH offers a nice way to replace patterns. Until now I always used basename and a variable.
For example moving all .jpeg files in a folder to .jpg (hey, it’s just an example..)

for moo in *.jpeg; do
    newname="`basename $moo .jpeg`.jpg"
    mv $moo $newname
done

But isn’t that just plain ugly? That one is lot nicer:

for i in *.jpeg; do
    mv "$i" "${i%.*}.jpg"
done

1337

Howto: receive mail and save attachment with fetchmail, procmail and metamail

At work I recently had to set up a solution that periodically checks a POP3 account on our M$ Exchange wannabe mailserver, and saves the attachments to some folder for further processing. As I didn’t find a ready-to-go-solution for this on the web, just snippets here and there, and of course hundrets of other people asking the same, here it is.

You will need

  • fetchmail
  • procmail
  • metamail (or uudeview, see first comment below)

In this example I’ll use a POP3 account, the full mail will be backed up to ~/mail_backup, and attachments will be unpacked to ~/attachments. fetchmail also handles IMAP accounts just fine. Please refer to the fetchmail documentation.

Setting up fetchmail

First create a file $HOME/.fetchmailrc

poll my.pop3.server 
protocol pop3 
user 'myuser' 
password 'mypassword' 
mda '/usr/bin/procmail -d %T'

Setting up procmail and metamail

Then we configure procmail so it forwards the messages to metamail in $HOME/.procmailrc

:0
*^content-Type:
{
        # backup the complete mail first..
        # you can leave out this part if you don't want a backup of the complete mail
        :0c:
        $HOME/mail_backup

        # Now the actual unpacking part
        #
        # this is the place where the attachments will be unpacked to
        METAMAIL_TMPDIR=$HOME/attachments

        # forward to metamail
        :0fw
        | metamail -w -y -x
}

Regarding metamail, we tell it to ignore any mailcap file, so it doesn’t use interpreters (-w), yanking the message and save the content raw (-y) and force it in non-interactive mode (-x).

That’s it about it. We are ready for testing.

Test run

Now we simply fire up fetchmail, the rest should be magic.

fetchmail -kv

HTH