read line by line with awk and parse variables

Question

I have a script that read log files and parse the data to insert them to mysql table..

My script looks like

while read x;do
var=$(echo ${x}|cut -d+ -f1) 
var2=$(echo ${x}|cut -d_ -f3)
...
echo "$var,$var2,.." >> mysql.infile 
done<logfile

The Problem is that log files are thousands of lines and taking hours....

I read that awk is better, I tried, but don't know the syntax to parse the variables...

EDIT: inputs are structure firewall logs so they are pretty large files like

@timestamp $HOST reason="idle Timeout" source-address="x.x.x.x" source-port="19219" destination-address="x.x.x.x" destination-port="53" service-name="dns-udp" application="DNS"....

So I'm using a lot of grep for ~60 variables e.g

sourceaddress=$(echo ${x}|grep -P -o '.{0,0} 
source-address=\".{0,50}'|cut -d\" -f2)

if you think perl will be better I'm open to suggestions and maybe a hint how to script it...

Answer 1

To answer your question, I assume the following rules of the game:

• each line contains various variables
• each variable can be found by a different delimiter.

This gives you the following awk script :

awk 'BEGIN{OFS=","}
     { FS="+"; $0=$0; var=$1;
       FS="_"; $0=$0; var2=$3;
               ...
       print var1,var2,... >> "mysql.infile"
     }' logfile

It basically does the following :

• set the output separator to ,
• read line
• set the field separator to +, re-parse the line ($0=$0) and determine the first variable
• set the field separator to '_', re-parse the line ($0=$0) and determine the second variable
• ... continue for all variables
• print the line to the output file.

Answer 2

The perl script below might help:

perl -ane '/^[^+]*/;printf "%s,",$&;/^([^_]*_){2}([^_]*){1ntf "%s\n",$+' logfile

Since, $& can result in performance penalty, you could also use the /p modifier like below :

perl -ane  '/^[^+]*/p;printf "%s,",${^MATCH};/^([^_]*_){2}([^_]*){1}_.*/;printf "%s\n",$+' logfile

For more on perl regex matching refer to [ PerlDoc ]

Answer 3

if you're extracting the values in order, something like this will help

$ awk -F\" '{for(i=2;i<=NF;i+=2) print $i}' file 

idle Timeout
x.x.x.x
19219
x.x.x.x
53
dns-udp
DNS

you can easily change the output format as well

$ awk -F\" -v OFS=, '{for(i=2;i<=NF;i+=2) 
                        printf "%s", $i ((i>NF-2)?ORS:OFS)}' file

idle Timeout,x.x.x.x,19219,x.x.x.x,53,dns-udp,DNS