Parse log file and enter into database

[zoolu]

Hi,

I am find it difficult to parse a log file with the format below.

CLIENT LIST
Updated,Mon Nov 17 13:59:22 2008
Common Name,Real Address,Bytes Received,Bytes Sent,Connected Since
224.8.168.192.in-addr.arpa.dev,192.168.8.224:52352,54977,65102,Mon Nov 17 11:08:58 2008
206.8.168.192.in-addr.arpa.dev,192.168.8.206:46147,34965,35410,Mon Nov 17 12:31:27 2008
ROUTING TABLE
Virtual Address,Common Name,Real Address,Last Ref
10.8.0.6,224.8.168.192.in-addr.arpa.dev,192.168.8.224:52352,Mon Nov 17 11:08:58 2008
10.8.0.10,206.8.168.192.in-addr.arpa.dev,192.168.8.206:46147,Mon Nov 17 12:31:29 2008
GLOBAL STATS
Max bcast/mcast queue length,0
END


I want to extract the entries(currently 2 in the example) below "Common Name,Real Address,Bytes Received,Bytes Sent,Connected Since,Virtual Address" fields and store them in database.

If any one could help me with a awk/sed or perl example that would be great.
Thanks in advance.

[creepy cripple]

First you have to distinguish the lines you are interested in. In this particular case, lines which start with an IP address and with 5 fields (watch out lines with only 4 fields because they dont interest!)

You can search those lines with AWK. You can construct the insert clauses with AWK, store them in a bash variable and finally treat that variable to store them in a database.

It could be something like this:

file.sh -contents:
consult=$(awk -f file.awk data_file)
echo $consult | xargs | sqlplus -s user/passwd@database

file.awk -contents:

{
BEGIN (FS=",")
if (NF==5){
if ($1 ~ /[0-9]+.[0-9]+.[0-9]+.[0-9]+./){
print "INSERT INTO TABLE_X (COLUMN1,COLUMN2...) VALUES (" $1 "," $2 "," $3...");\n";
}
}
}

this is just pseudo-code but the idea is right

[delovelady]

Yes, that'll get you close. Remember, though, to watch out for the wildcard period (.) character... you really want to escape that (\.) so that it's not treated as a wildcard. Also, there needs to be some sort of flag to prevent this catching lines 4-5.