Insert value of vmstat to database.

Shell Programming and Scripting
1

Hi guys ,

I m trying to store the output of vmstat 1 10 into a database.

I have done the necessary homework to connect bash to interact with the database.
Program logic.
1)storing the output of vmstat 1 10 to a file named abc.txt
2)I m applying a filter to vmstat to get desired output containing cpu(user),cpu(system),cpu(idle) values.
3)

# cat abc.txt | grep -vE '(procs|free)'| awk '{print $13 " "$14 " " $15}'
0 1 97
0 0 100
0 1 99
0 2 94
0 1 90
0 0 100
0 0 100
0 0 100
1 1 90
0 1 96

My problem is how would i insert the values to the database.

I will have to include some sort of logic to read the output provided by

#cat abc.txt | grep -vE '(procs|free)'| awk '{print $13 " "$14 " " $15}'

and for every line i have to insert values to table.
Table :

The tough part is to assign values to bash variable CPU_USER,CPU_SYSTEM,CPU_IDLE.

script to connect to database(oracle xe).

#!/bin/bash
# Validate the value of ORACLE_HOME #
# If ORACLE_HOME is empty #

if [ -z $ORACLE_HOME ]
then
        echo "Set the ORACLE_HOME variable"
        exit 1
fi

# If ORACLE_HOME doesn't exist #
if [ ! -d $ORACLE_HOME ]
then
        echo "The ORACLE_HOME $ORACLE_HOME does not exist"
        exit 1
fi

# Validate the value of ORACLE_SID #
if [ -z $ORACLE_SID ]
then
        echo "Set the ORACLE_SID variable"
        exit 1
fi

SID=`ps -ef|grep smon|grep -v grep|awk '{print $8}'`

if [ 'xe_smon_'$ORACLE_SID != $SID ] ; then
echo "ORACLE_SID IS NOT SET"
exit 1
fi


# Enter the username and password to login to oracle #
echo "Enter the username"
read username

echo "Enter password"
stty -echo
read password
stty echo

# Get the query , no validation applied for query #
echo "Enter the query"
read query

# Login and execute the query.
echo "set feedback off verify off heading off pagesize 0
        $query
        exit" | $ORACLE_HOME/bin/sqlplus -s $username/$password | while read output ;
       do
                echo $output
        done
2 
$ vmstat 1 10 |awk -v s=39 '
BEGIN{ print "create table doctortux_vmstat_cpu(cpu_user number(3),cpu_system number(3),cpu_idle number(3))"
       print "statement to insert value to the table."}
NR>2 {printf "insert into doctortux_vmstat_cpu values(%c%d%c,%c%d%c,%c%d%c);\n",s,$13,s,s,$14,s,s,$15,s}'

create table doctortux_vmstat_cpu(cpu_user number(3),cpu_system number(3),cpu_idle number(3))
statement to insert value to the table.
insert into doctortux_vmstat_cpu values('4','1','4');
insert into doctortux_vmstat_cpu values('2','0','2');
insert into doctortux_vmstat_cpu values('7','0','7');
...
3

Being novice linux user i have difficulties understanding following section.
why the variable s is initialized with 39 values.
what does NR>2 do?
I guess this will only generate the statement but wont insert them into database.
M i correct?
Any way thanks for spending time to give me a reply it was indeed helpful.

4

39 is used to print ' in awk.
NR>2 is used to ignore the first two lines of vmware O/P.

if you need run the output, you can add |sh after that.

$ vmstat 1 10 |awk -v s=39 '
BEGIN{ print "create table doctortux_vmstat_cpu(cpu_user number(3),cpu_system number(3),cpu_idle number(3))"
       print "statement to insert value to the table."}
NR>2 {printf "insert into doctortux_vmstat_cpu values(%c%d%c,%c%d%c,%c%d%c);\n",s,$13,s,s,$14,s,s,$15,s}' |sh
5

I might not able to execute the command in this way.
as for executing sql command i have to write following code
How would i do this.

# Login and execute the query.
echo "set feedback off verify off heading off pagesize 0
        $query
        exit" | $ORACLE_HOME/bin/sqlplus -s $username/$password | while read output ;
       do
                echo $output
        done