Solved

Shell Script to insert into a database

Posted on 2011-03-15
2
545 Views
Last Modified: 2012-05-11
Hi,

I need a shell script that can insert the output of df -h every night into a database table. The table structure is shown below. Will appreciate any pointers on it

thanks
-anshu


[root@myserver ~]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda1              15G  4.0G  9.8G  29% /
/dev/sda6              27G  6.9G   19G  28% /omega
/dev/sda3             4.9G  390M  4.3G   9% /var
/dev/sda5             4.9G  139M  4.5G   3% /tmp
tmpfs                 127G     0  127G   0% /dev/shm
/dev/sdb2              35G   15G   19G  44% /alpha
/dev/sdc1             135G   28G  100G  22% /alpha1
/dev/sdd1             135G  105G   23G  83% /alpha2


Table Structure

SERVER_MACHINE | DATE_ID | MONTH_ID | YEAR_ID | Filesystem | Size_GB | Used_GB | Available_GB | USE_PCNT | Mounted On

myserver       | 20110315| 201103   | 2000    | /dev/sda1  | 15      |  4.0     | 9.8         | 29       | root
myserver       | 20110315| 201103   | 2000    | /dev/sda6  | 27      |  6.9     | 19.0        | 28       | omega


0
Comment
Question by:anshuma
  • 2
2 Comments
 
LVL 8

Expert Comment

by:point_pleasant
ID: 35150403
give this a try assumes db is mysql but its generic sql

df -P >/tmp/dfout
server=`hostname`
d_date=`date +"%Y%m%d"`
m_date=`date +"%Y%m"`
y_date=`date +"%Y"`
toss_header=0
while read line
do
        if [ "$toss_header" -eq 0 ]
        then {
                toss_header=1
        } else {
                vol=`echo $line | awk '{print $1 }'`
                blocks=`echo $line | awk '{print $2 }'`
                used=`echo $line | awk '{print $3 }'`
                avail=`echo $line | awk '{print $4 }'`
                cap=`echo $line | awk '{print $5 }'`
                mnt_on=`echo $line | awk '{print $6 }'`
                mysql --user=your_id --password=your_password yourdb << EOF
                        echo "$server|$d_date|$m_date|$y_date|$vol|$blocks|$used|$avail|$cap|$mnt_on"
                        insert into your_table (SERVER_MACHINE,DATE_ID,MONTH_ID,YEAR_ID,Filesystem,Size_GB,Used_GD,Available_GB,USE_PCNT,Mounted_on) \
                        values ($server,$d_date,$m_date,$y_date,$vol,$blocks,$used,$avail,$cap,$mnt_on);
                        EOF
        }
        fi

done < /tmp/dfout
0
 
LVL 8

Accepted Solution

by:
point_pleasant earned 500 total points
ID: 35150477
oops left a debug in and forgot to toss the % sigh in space used

df -P >/tmp/dfout
server=`hostname`
d_date=`date +"%Y%m%d"`
m_date=`date +"%Y%m"`
y_date=`date +"%Y"`
toss_header=0
while read line
do
        if [ "$toss_header" -eq 0 ]
        then {
                toss_header=1
        } else {
                vol=`echo $line | awk '{print $1 }'`
                blocks=`echo $line | awk '{print $2 }'`
                used=`echo $line | awk '{print $3 }'`
                avail=`echo $line | awk '{print $4 }'`
                cap=`echo $line | awk '{print $5 }' | tr "%" " "`
                mnt_on=`echo $line | awk '{print $6 }'`
                mysql --user=your_id --password=your_password yourdb << EOF
                         insert into your_table (SERVER_MACHINE,DATE_ID,MONTH_ID,YEAR_ID,Filesystem,Size_GB,Used_GD,Available_GB,USE_PCNT,Mounted_on) values ($server,$d_date,$m_date,$y_date,$vol,$blocks,$used,$avail,$cap,$mnt_on);
                        EOF
        }
        fi

done < /tmp/dfout
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Over the years I've spent many an hour playing on hardened, DMZ'd servers, with only a sub-set of the usual GNU toy's to keep me company; frequently I've needed to save and send log or data extracts from these server back to my PC, or to others, and…
Utilizing an array to gracefully append to a list of EmailAddresses
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now