Text to CSV

Shell Programming and Scripting
1

Hi,
My access log looks like this...

 192.168.50.184 - - [31/Aug/2009:11:56:59 +0530] "GET /ATIM_LATEST/ABC/ HTTP/1.1" 200 522 "-" "Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.0.13) Gecko/2009073022 Firefox/3.0.13" 
192.168.50.184 - - [31/Aug/2009:11:56:59 +0530] "GET /ATIM_LATEST/ABC/ATIM/syncdepot.php HTTP/1.1" 200 1463 "http://192.168.50.209/ATIM_LATEST/ABC/" "Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.0.13) Gecko/2009073022 Firefox/3.0.13"
192.168.50.184 - - [31/Aug/2009:11:56:59 +0530] "GET /ATIM_LATEST/ABC/js/ajax.js HTTP/1.1" 200 2623 "http://192.168.50.209/ATIM_LATEST/ABC/ATIM/syncdepot.php" "Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.0.13) Gecko/2009073022 Firefox/3.0.13"

I want to insert the data in MySQL
1) Is there any script that will do it automatically.
2) If no, then How do I convert the format to either comma separated text file or fields delimited by " "?

For e.g.

 192.168.50.184, -, -, [31/Aug/2009:11:56:59 +0530], "GET /ATIM_LATEST/ABC/ATIM/syncdepot.php HTTP/1.1", 200, 1463, "http://192.168.50.209/ATIM_LATEST/ABC/", "Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.0.13) Gecko/2009073022 Firefox/3.0.13"

OR

 "192.168.50.184" "-" "-" "[31/Aug/2009:11:56:59 +0530]" "GET /ATIM_LATEST/ABC/ATIM/syncdepot.php HTTP/1.1" "200" "1463" "http://192.168.50.209/ATIM_LATEST/ABC/" "Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.0.13) Gecko/2009073022 Firefox/3.0.13"
2

It looks like an http apache access log. Wouldn't it be easier to change the log format and to use another character than space as separator?

Look for the CustomLog and LogFormat directive in the apache.conf config file.
Log Files - Apache HTTP Server

You can use tab as separator. It is easy to parse and to read on a console.

Have also a look at mod_log_sql module for apache

3

Why not cut the middle man: mod_log_sql

4

I have changed the LogFormat of httpd
But the %t display the date in the following format.

[31/Aug/2009:11:56:59 +0530]

MySQL does not understand this format and needs to be formatted something lie this...
2009-09-03 12:58:43

I need to change the %{format}t But I am not sure about the syntax. Any help?

5

%{format}t uses the same operators as the PHP strftime function which, in turn, is similar to the usual format options for date:

LogFormat "%h %l %u %{[%Y-%m-%d %H:%M:%S %z]}t \"%r\" %>s %b" MyLogFormat

Why don't you use the mod_log_sql module as suggested above?

6

Thanks a lot for that syntax. I requested the change and I am now getting the correct results.
I can not use the module because I have only the access_log text files. :slight_smile: