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.