How to record log file to mysql database

Question

I have a log file that looks like this:

11:34:17 PM     CPU     %user     %nice   %system   %iowait    %steal     %idle
11:39:17 PM     all      0.09      0.00      0.07      0.09      0.00     99.75
11:44:17 PM     all      0.04      0.00      0.03      0.00      0.00     99.92
11:49:17 PM     all      1.49      0.00      0.49      0.06      0.00     97.96
11:54:17 PM     all     23.27      0.00      0.51      0.05      0.03     76.14
Average ....

I need to insert the date, time, %user, %system, %idle into the table

row_id (int) | date (date) | time (time) | hostname (varchar) | user (decimal) | system (decimal) | idle (decimal)

I need the time converted to hh:mm:ii (24 hr) as well. The date column needs to derive from the log file called YYYY-MM-DD_hostname_cpu.log

How can I do this in a shell script? I need help converting the time and grabbing the hostname which can be any length from 1 and 20.

#: awk 'BEGIN { OFS = "," } FNR == 1 { d = substr(FILENAME, 1, 10) } $2 ~ /^[AP]M$/ && $3 != "CPU" { print d, $1 " " $2, $4+$5, $6, $7+$8+$9 }' *_cpu.log >> new_file.log

http://stackoverflow.com/questions/17997558/bash-script-to-insert-values-in-mysql — M0rtiis, Aug 16 '15 at 05:14
@M0rtiis would you suggest using a temp table and only inserting the columns I need? — user1052448, Aug 16 '15 at 05:19
Why temp? i believe it should be regular table. About other - im not familiar with bash. sorry — M0rtiis, Aug 16 '15 at 05:21
I'm not sure how to only grab the columns I need from the log file to insert. Probably do a cat YYYY-MM-DD_hostname_cpu.log | awk '{ ~ }'. I'd also need a way to ignore the first and last line — user1052448, Aug 16 '15 at 05:26
ignore the first line with (if NR != 1 ), whats wrong with the last line.. — amdixon, Aug 16 '15 at 05:27

score 0 · Answer 1 · answered Aug 16 '15 at 10:20

0

I can give you a sort of pseudo code:

Considering you have multiple files for YYYY-MM-DD_hostname_cpu.log.

# Loop over each file
for inputfile in `ls *.log`; do
# grab date from filename
date=echo $0 | awk -F"_" '{print $0}'
# read line by line from file into variables
cat $inputfile | while read time user system idle; do
# Generate insert statements & pipe them  to mysql.
echo "insert into TABLE values (null, '$date', '$time', '$user', '$system', '$idle');"
done | mysql -uUSER -pPASS database;
done;

Usually to load such files in batch we can use MySQL's Load Data Infile syntax, you may want to review few use cases.

answered Aug 16 '15 at 10:20

mysql_user

382
3
11

Will MySQL automatically convert 12hr time into 24hr time? I'm assuming writing the cpu log file into a cleaned up csv version would be better. I can then use load data infile to process the entire contents rather than doing multiple inserts. – user1052448 Aug 16 '15 at 16:26
Also, how can I get the hostname from the filename to input into the table? (see updated question) – user1052448 Aug 16 '15 at 16:32
Use the same `awk` that you used for grabbing YYYY-MM-DD. You run following command and see what it returns: `echo "YYYY-MM-DD_hostname_cpu.log" | awk -F"_" '{print $1 $2 $3}'` – mysql_user Aug 16 '15 at 16:38
Got it. And to finish the last part of the original question - how can i convert 12hr time to 24hr time for MySQL? – user1052448 Aug 16 '15 at 17:16
oh I missed that one.... You could use date_format or str_to_date functions. For eg. here you can try replacing `$date` with `STR_TO_DATE( '$date', '%l:%i:%S %p' )`. – mysql_user Aug 17 '15 at 10:05

How to record log file to mysql database

1 Answers1