Linux – How to store sar output on mysql database.

>This approach will store sar information on mysql database. This script is ongoing process which will be enrich by the time.

Currently this script is serving the purpose of the following things.

#Complete sar information [ sar -u ]
#Network_statistics_information [ sar -n DEV ]
#Process Queue Length and Load Avg [ sar -q ]
#Memory & Swap Space Utilization Statistics [ sar -r ]

Database information

CREATE DATABASE sysstat;
GRANT ALL PRIVILAGES ON systat.* TO ‘sysstat’@’localhost’ IDENTIFIED BY ‘sysstat123’;

CREATE TABLE sarinformation
(
hostname VARCHAR(20),
datestamp VARCHAR(15),
time VARCHAR(8),
timeformat VARCHAR(2),
cpu VARCHAR(3),
pct_user DECIMAL(10,2),
pct_nice DECIMAL(10,2),
pct_system DECIMAL(10,2),
pct_iowait DECIMAL(10,2),
pct_steal DECIMAL(10,2),
pct_idle DECIMAL(10,2)
);

CREATE TABLE net_stat_info
(
hostname VARCHAR(20),
datestamp VARCHAR(15),
time VARCHAR(8),
timeformat varchar(2),
iface VARCHAR(7),
rxpck_persec DECIMAL(10,2),
txpck_persec DECIMAL(10,2),
rxbyt_persec DECIMAL(10,2),
txbyt_persec DECIMAL(10,2),
rxcmp_persec DECIMAL(10,2),
txcmp_persec DECIMAL(10,2),
rxcst_persec DECIMAL(10,2)
)

CREATE TABLE queue_len_n_load_avg
(
hostname VARCHAR(20),
datestamp VARCHAR(15),
time VARCHAR(8),
timeformat varchar(2),
runq_sz int,
plist_sz int,
ldavg_1 DECIMAL(10,2),
ldavg_5 DECIMAL(10,2),
ldavg_15 DECIMAL(10,2)
)

CREATE TABLE mem_n_swap_space_utilization_stat
(
hostname VARCHAR(20),
datestamp VARCHAR(15),
time VARCHAR(8),
timeformat varchar(2),
kbmemfree int,
kbmemused int,
Per_memused DECIMAL(10,2),
kbbuffers int,
kbcached int,
kbswpfree int,
kbswpused int,
Per_swpused DECIMAL(10,2),
kbswpca int)

Created a script sar_load_information.sh as below

#!/bin/bash

#Author Satyendra Singh
#This script will store sar data on database
#This script will take the latest sar file i.e sar23 and put the data on sysstat database;
#The latest sar file i.e sa23 will be copy on temporary location /tmp/x and after parsing sar file will be deleted from temporary location.

# This is highly recommended that, DO NOT PUT ANYTHING ON TEMPORARY LOCATION /tmp/x

#+————+—————+——+—–+———+——-+
#+————+—————+——+—–+———+——-+
WORKDIR=/tmp/x
FMATDIR=/tmp/y
SAR=`which sar`
HEAD=`which head`
AWK=`which awk`
cd $WORKDIR
COPY=`which cp`
SAR_LOG=/var/log/sa
cd $SAR_LOG
LATEST_DATA=`ls -tlr $SAR_LOG | tail -1 | awk ‘{print $9}’`
$COPY $LATEST_DATA $WORKDIR
for file in `dir $WORKDIR`;
do
#
DATESTAMP=`$SAR -f $file | $HEAD -1 | $AWK ‘{print $4}’| $AWK -F ‘/’ ‘{print $3″-“$1”-“$2}’`
#Complete sar information
$SAR -f $file | sed “$ d” | tr -s [:blank:] | sed -n ‘1h;2,$H;${g;s/ /,/g;p}’ | sed ‘/Average:/ d’ | sed “s/^/$HOSTNAME,$DATESTAMP,/” | sed ‘$d’ > “$FMATDIR”/”$file”-cpuutilization.csv
#Network_statistics_information
$SAR -n DEV -f $file | sed “$ d” | tr -s [:blank:] | sed -n ‘1h;2,$H;${g;s/ /,/g;p}’ | sed ‘/Average:/ d’ | sed “s/^/$HOSTNAME,$DATESTAMP,/” | sed ‘$d’ > “$FMATDIR”/”$file”-network_statistics_information.csv
#Process Queue Length and Load Avg
$SAR -q -f $file | sed “$ d” | tr -s [:blank:] | sed -n ‘1h;2,$H;${g;s/ /,/g;p}’ | sed ‘/Average:/ d’ | sed “s/^/$HOSTNAME,$DATESTAMP,/” | sed ‘$d’ > “$FMATDIR”/”$file”-queue_length_and_load_avg.csv
#Memory & Swap Space Utilization Statistics
$SAR -r -f $file | sed “$ d” | tr -s [:blank:] | sed -n ‘1h;2,$H;${g;s/ /,/g;p}’ | sed ‘/Average:/ d’ | sed “s/^/$HOSTNAME,$DATESTAMP,/” | sed ‘$d’ > “$FMATDIR”/”$file”-memory_and_swap_space_utilization_statistics.csv
done

cd $FMATDIR
for file in `dir -d *`;
do
/usr/bin/mysql -usysstat -psysstat123 -h localhost -D sysstat -e “LOAD DATA LOCAL INFILE ‘$PARSEDIR/${file}’ INTO TABLE `echo

$file | sed ‘s/.csv//g’ | awk -F_ ‘{print $2}’` FIELDS TERMINATED BY ‘,’ IGNORE 1 LINES;”
done

rm -fr $FMATDIR/*.csv
rm -fr $WORKDIR/sa*

queue_len_n_load_avg Table output
+——————+————+———-+————+———+———-+———+———+———-+

+——————+————+———-+————+———+———-+———+———+———-+
| hostname | datestamp | time | timeformat | runq_sz | plist_sz | ldavg_1 | ldavg_5 | ldavg_15 |
+——————+————+———-+————+———+———-+———+———+———-+
| satya | 2010-01-25 | 12:00:02 | AM | 0 | 0 | 0.00 | 0.00 | 0.00 |
| satya | 2010-01-25 | 12:10:01 | AM | 0 | 197 | 0.00 | 0.00 | 0.00 |
| satya | 2010-01-25 | 12:20:01 | AM | 2 | 199 | 0.00 | 0.00 | 0.00 |
| satya | 2010-01-25 | 12:30:01 | AM | 2 | 199 | 0.00 | 0.00 | 0.00 |
| satya | 2010-01-25 | 12:40:01 | AM | 1 | 198 | 0.00 | 0.00 | 0.00 |
| satya | 2010-01-25 | 12:50:01 | AM | 2 | 199 | 0.00 | 0.02 | 0.00 |
| satya | 2010-01-25 | 01:00:01 | AM | 2 | 199 | 0.00 | 0.00 | 0.00 |
| satya | 2010-01-25 | 01:10:02 | AM | 2 | 199 | 0.00 | 0.00 | 0.00 |
| satya | 2010-01-25 | 01:20:01 | AM | 1 | 198 | 0.03 | 0.01 | 0.00 |
| satya | 2010-01-25 | 01:30:01 | AM | 0 | 197 | 0.00 | 0.01 | 0.00 |
| satya | 2010-01-25 | 01:40:01 | AM | 0 | 197 | 0.00 | 0.00 | 0.00 |
| satya | 2010-01-25 | 01:50:01 | AM | 1 | 199 | 0.00 | 0.00 | 0.00 |
| satya | 2010-01-25 | 02:00:02 | AM | 2 | 199 | 0.00 | 0.00 | 0.00 |
+——————+————+———-+————+———+———-+———+———+———-+

Advertisements

3 thoughts on “Linux – How to store sar output on mysql database.

  1. Cassandra November 17, 2011 at 11:42 pm Reply

    Well put, sir, well put. I’ll cetarnliy make note of that.

    • Rosa December 1, 2011 at 8:27 am Reply

      Enligthnenig the world, one helpful article at a time.

    • Tessa December 3, 2011 at 7:02 am Reply

      Information is power and now I’m a !@#$ing dicattor.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: