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)
and for every line i have to insert values to table.
Table :
Quote:
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('$CPU_USER','$CPU_SYSTEM','$CPU_IDLE');
The tough part is to assign values to bash variable CPU_USER,CPU_SYSTEM,CPU_IDLE.
script to connect to database(oracle xe).
Code:
#!/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
$ 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');
...
$ 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');
...
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.
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.
Code:
$ 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
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.
Code:
# 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
I have a log file that's created daily by this command:
sar -u 300 288 >> /var/log/usage/$(date "+%Y-%m-%d")_$(hostname)_cpu.log
It that contains data like this:
Linux 3.16.0-4-amd64 (myhostname) 08/15/2015 _x86_64_ (1 CPU)
11:34:17 PM CPU %user %nice ... (12 Replies)
Hello All Members,
I am new to this forum and to the shell scripting. I want to write a shell script to do the following:
Scenario: I have a pipe delimited .txt file with multiple fields in it. The very first row of the file contains the name of the column which resembles the name of the... (18 Replies)
Hi guys, I would like to seek help on inserting data whenever the switch is on or off to my sensor mySQL database in phpMyAdmin from my control.php. I'm using Raspberry PI as my hardware and follow a few tutorials to create my own Web Control Interface, it works perfectly without insert method.... (1 Reply)
I have a table in an Informix DB into which I want to insert multiple records at a time. Data for one of the column should be unique & other column data may be the same for all the records I insert
Typical Insert Statement I use to insert one row :
insert into employee(empid, country, state)... (5 Replies)
Hello,
I am writting a procedure in shell script as,
set serveroutput on
declare
date_gen DATE := $DATEGEN;
begin
INSERT INTO TBL1 ( EMPNAME,EMPID,EMPBDATE)
VALUES($EMPNAME,$EMPID,TO_DATE(date_gen,'YYYY-MM-DD'));
end;
/
Where DATEGEN is unix string variable which I need to use into... (10 Replies)
If you have a text file and if you want to Insert data to your Database Table, You can do it with these queries
LOAD DATA LOCAL INFILE '/path/yourTextFile.txt' INTO TABLE yourTableName
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n' (0 Replies)
hello all,
I am new to shell scripting and to unix... so the following is my assignment..
here i am trying to insert a values into sqlplus database using shell script.
the following is my shell script
InsertDelete.sh
#! /bin/sh
echo "*********The MENU********
1.Insert The Values... (2 Replies)
Hi....can you guys help me out in this script??
Below is a text file and it contains these:
GEF001 000093625 MKL002510 000001 000000 000000 000000 000000 000000 000001
GEF001 000093625 MKL003604 000001 000000 000000 000000 000000 000000 000001
GEF001 000093625 MKL005675 000001... (4 Replies)
Hi,
Need to get help from you guys about this issue.
I need to insert data into MySql database from a text file which is located in other server.
The text file is something look like below:
Date | SubscriberNo | Call Duration
20/7/07 | 123456788 | 20
20/7/07 | 123412344 | 30
The... (4 Replies)
Hi....can you guys help me out in this script??
Below is a text file script....called Bukom.txt and it contains these:
BUKOM 20060101 2.5 2.6 2.7 2.8 2.9 2.3 2.1
BUKOM 20060102 2.4 2.5 2.6 2.7 2.7 2.6 2.4
BUKOM 20060103 2.1 ... (9 Replies)