0

I only can get the desire output when I run the command one by one. How can I combine all the command in a shell script and get the desired output?

//insert timestamp column
$ vmstat -n 5 | (while read; do echo "$(date +%Y-%m-%d.%H:%M:%S) $REPLY"; done)> vm.txt 
//remove 1st & 2nd row
$ sed '1,2d' vm.txt > vm2.txt 
//convert text file to csv
$ sed 's/^  *//;s/  */,/g'  vm2.txt > vm2.csv 
//insert column name
$ echo $'datetime, r, b, swpd, free, buff, cache, si, so, bi, bo, in, cs, us, sy, id, wa, st' | cat - vm2.csv> chart.csv
$ psql -p 5432 -U postgres -c  "\copy vmstat FROM '/root/report/chart.csv' delimiter '|' csv header"
  • 1
    put the commands to a script, and run the script... – pLumo Jul 2 '18 at 8:19
  • How many lines do you need from vmstat (I'm assuming you are manually interrupting the command now)? – Kusalananda Jul 2 '18 at 8:50
0

Put all your commands in a file, let say myCommands. Note the #!/bin/sh at the first line. That is called a shebang, it tells the shell what program to interpret the script with, when executed.

#!/bin/sh
# insert timestamp column
vmstat -n 5 | (while read; do echo "$(date +%Y-%m-%d.%H:%M:%S) $REPLY"; done)> vm.txt 
# remove 1st & 2nd row
sed '1,2d' vm.txt > vm2.txt 
# convert text file to csv
sed 's/^  *//;s/  */,/g'  vm2.txt > vm2.csv 
# insert column name
echo $'datetime, r, b, swpd, free, buff, cache, si, so, bi, bo, in, cs, us, sy, id, wa, st' | cat - vm2.csv> chart.csv
psql -p 5432 -U postgres -c  "\copy vmstat FROM '/root/report/chart.csv' delimiter '|' csv header"

Next, make it executable:

chmod +x myCommands

Now, you can use it either by using ./myCommands if you are in the same folder, or symply myCommands if the script file is in a older included in the $PATH variable.

  • I try like what you said but I only able to get the text file. – Fayne Jul 2 '18 at 8:35
  • @Fayne It's a bit unclear what you expect to get. – Kusalananda Jul 2 '18 at 8:37
  • At the last, I need to get csv file and the data inside the csv file need to be copied into my database. – Fayne Jul 2 '18 at 8:39
  • @Fayne That is what the above script is doing, assuming it is run from /root/report. – Kusalananda Jul 2 '18 at 8:44
  • @Fayne It seems that vmstat keeps running indefinitely. That would explain why you are only getting the text file. How many output lines of vmstat do you need? – nxnev Jul 2 '18 at 8:48
1

The following script will do the equivalent things that your commands are doing, but without creating so many intermediate files:

#!/bin/sh

report=/root/report/chart.csv

vmstat -n 5 100 |
awk -vOFS=',' 'NR == 1 { next }
               NR == 2 { $1 = "datetime" OFS $1 }
               NR  > 2 { $1 = strftime("%F.%T", systime()) OFS $1 } 1' >"$report"

psql -p 5432 -U postgres -c "COPY vmstat FROM '$report' DELIMITER ',' CSV HEADER"

This uses a single awk script to insert the datestamp column at the start and to convert the output to comma-delimited records. It reuses the header outputted from vmstat and collects 100 lines of vmstat output with a five second interval. I have also changed the delimiter used in the PostrgeSQL statement to a comma, as that's what the data is using.

The script expects that awk is GNU awk as found on most Linux systems.

  • Thanks @Kusalananda . It works. Can you explain the commands as well? Cause I still didn't get it how you remove the header of the file – Fayne Jul 3 '18 at 0:46
  • It seems that the last part can't work. If there any other solution to copy to database? – Fayne Jul 3 '18 at 1:11
  • @Fayne It's the same command that you used (except for the changed delimiter), so if your command worked, this command would also work. How are you seeing that it does not? – Kusalananda Jul 3 '18 at 6:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.