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.

Category: