Convert log files to SQL

Ahhhh. Awk. What can't it do?

Let's take this, for example:

## Set variables for your environment
ftp_log_path="/var/log/ftp/"  
db_name="LOGS"  
table_name="ftp_logs"  
date_column_name="date"  
time_column_name="time"  
ip_column_name="ip"  
file_column_name="file_name"

### Let's cat out some files and pass them on to awk to convert to SQL:
cat ${ftp_file_path}*.log| grep "sent" |awk '{print "INSERT INTO "ndb"."ntable_name onc ndate","ntime","nip","nfile enc " VALUES" onc q $1 q "," q $2 q "," q $3 q "," q $7 q enc";"}' q="'" enc=")" onc="(" ntable_name="$table_name" ntime="$time_column_name" nip="$ip_column_name" nfile="$file_column_name" ndb="$db_name" ndate="$date_column_name" > ftp.sql  
## Let's compare against the master list to ensure we don't have duplicates:
if [ -f ftp_master_list.sql ]; then  
    grep -Fxv -f ftp_master_list.sql ftp.sql > ftp_new_sql.sql
else  
    cp ftp.sql ftp_master_list.sql;
    cp ftp.sql ftp_new_sql.sql;
fi  
## Remove the temporary file, and vola. We now have ftp_new_sql.sql that has all entries in a clean file.
rm -rf ftp.sql

This bit of code will convert logs like this:

2014-08-17 05:00:29 1.2.3.4 - anonymous [27856]user anonymous - 331 - - - 21  
2014-08-17 05:00:29 5.6.7.8 - anonymous [27856]pass ****** - 230 - - - 21  
2014-08-17 05:01:36 9.10.11.12 - anonymous [27853]sent /pub/product/product.exe - 226 20513472 - - 21  
2014-08-17 05:01:38 9.10.11.12 - anonymous [27858]user anonymous - 331 - - - 21  
2014-08-17 05:01:38 13.14.15.16 - anonymous [27858]pass ****** - 230 - - - 21  
2014-08-17 05:01:38 14.15.16.17 - anonymous [27860]user anonymous - 331 - - - 21  
2014-08-17 05:01:38 14.15.16.17 - anonymous [27860]pass ****** - 230 - - - 21  
2014-08-17 05:01:38 14.15.16.17 - anonymous [27859]user anonymous - 331 - - - 21  
2014-08-17 05:01:38 14.15.16.17 - anonymous [27859]pass ****** - 230 - - - 21  
2014-08-17 05:01:38 14.15.16.17 - anonymous [27861]user anonymous - 331 - - - 21  
2014-08-17 05:01:38 14.15.16.17 - anonymous [27861]pass ****** - 230 - - - 21  
2014-08-17 05:01:55 14.15.16.17 - anonymous [27858]sent /pub/product/product.exe - 426 - - - -  
2014-08-17 05:01:55 14.15.16.17 - anonymous [27860]sent /pub/product/product.exe - 500 - - - -  
2014-08-17 05:01:55 14.15.16.17 - anonymous [27859]sent /pub/product/product.exe - 500 - - - -  
2014-08-17 05:01:55 14.15.16.17 - anonymous [27861]sent /pub/product/product.exe - 500 - - - -  

Into stuff like this:

INSERT INTO LOGS.ftp_logs(date,time,ip,file_name) VALUES('2014-08-17','05:01:36','9.10.11.12','/pub/product/product.exe');  
INSERT INTO LOGS.ftp_logs(date,time,ip,file_name) VALUES('2014-08-17','05:01:55','14.15.16.17','/pub/product/product.exe');  
INSERT INTO LOGS.ftp_logs(date,time,ip,file_name) VALUES('2014-08-17','05:01:55','14.15.16.17','/pub/product/product.exe');  
INSERT INTO LOGS.ftp_logs(date,time,ip,file_name) VALUES('2014-08-17','05:01:55','14.15.16.17','/pub/product/product.exe');  
INSERT INTO LOGS.ftp_logs(date,time,ip,file_name) VALUES('2014-08-17','05:01:55','14.15.16.17','/pub/product/product.exe');  

Now, with a little work, you can covert this to any type of log usage.

Just remember that awk starts at one, and moves on from there, rather than zero [curly brackets added to make it easier for you to see the breaks]:

{2014-08-17} {05:01:55} {14.15.16.17} {-} {anonymous} {[27861]sent} {/pub/product/product.exe} {-} {500} {-} {-} {-} {-}  
    ^$1          ^$2        ^$3        ^$4    ^$5         ^$6             ^$7                   ^$8  ^$9  ^$10^$11^$12^$13