trdsql convert log

Posted on:

Convert existing logs to LTSV

Convert existing logs to LTSV.

mingrammer/flog makes it easy to output fake logs, so I’ll show you how to convert the output logs to LTSV format.

Apache common log

Save the log in apache_common format as access.common.log with the following command.

flog -f apache_common -t log -o access.common.log

The content will be as follows.

92.129.44.198 - metz3917 [30/Dec/2019:17:02:27 +0900] "DELETE /infomediaries/e-markets HTTP/2.0" 500 24843
246.54.243.199 - - [30/Dec/2019:17:02:27 +0900] "POST /24%2f7 HTTP/1.1" 302 8879
9.172.27.159 - - [30/Dec/2019:17:02:27 +0900] "DELETE /convergence/best-of-breed HTTP/1.1" 203 3252
49.129.77.219 - kozey2248 [30/Dec/2019:17:02:27 +0900] "PUT /embrace HTTP/1.1" 301 2812
216.42.120.216 - - [30/Dec/2019:17:02:27 +0900] "HEAD /infomediaries HTTP/2.0" 204 12516

When parsed by trdsql with -id " “, it is separated by c4 and c5, but other than that, it seems to be no problem.

+---------------+----+----------+-----------------------+--------+--------------------------------+-----+-------+
|      c1       | c2 |    c3    |          c4           |   c5   |               c6               | c7  |  c8   |
+---------------+----+----------+-----------------------+--------+--------------------------------+-----+-------+
| 92.129.44.198 | -  | metz3917 | [30/Dec/2019:17:02:27 | +0900] | DELETE                         | 500 | 24843 |
|               |    |          |                       |        | /infomediaries/e-markets       |     |       |
|               |    |          |                       |        | HTTP/2.0                       |     |       |
+---------------+----+----------+-----------------------+--------+--------------------------------+-----+-------+

Output in LTSV with appropriate labels.

trdsql -id " " -oltsv \
"SELECT c1 AS host, c2 AS ident, c3 as user, c4||' '||c5 AS time, c6 AS req, c7 AS status, c8 as size "\ 
"  FROM access.common.log"

host:92.129.44.198	ident:-	user:metz3917	time:[30/Dec/2019:17:02:27 +0900]	req:DELETE /infomediaries/e-markets HTTP/2.0	status:500	size:24843
host:246.54.243.199	ident:-	user:-	time:[30/Dec/2019:17:02:27 +0900]	req:POST /24%2f7 HTTP/1.1	status:302	size:8879
host:9.172.27.159	ident:-	user:-	time:[30/Dec/2019:17:02:27 +0900]	req:DELETE /convergence/best-of-breed HTTP/1.1	status:203	size:3252
host:49.129.77.219	ident:-	user:kozey2248	time:[30/Dec/2019:17:02:27 +0900]	req:PUT /embrace HTTP/1.1	status:301	size:2812
host:216.42.120.216	ident:-	user:-	time:[30/Dec/2019:17:02:27 +0900]	req:HEAD /infomediaries HTTP/2.0	status:204	size:12516

Apache Combined Log

The Combined Log is basically the same except that the number of items increases.

The creation was done as follows.

flog -f apache_combined -t log -o access.combined.log

$ trdsql -id " " -oltsv \
"SELECT c1 AS host, c2 AS ident, c3 AS user ,c4||' '||c5 AS time, c6 AS req , c7 AS status, c8 AS size, c9 AS refer, c10 AS ua "\
 " FROM access.combined.log"

host:25.54.196.41	ident:-	user:-	time:[30/Dec/2019:17:13:22 +0900]	req:POST /harness/deliver HTTP/2.0	status:204	size:8501	refer:https://www.directconvergence.biz/real-time/web-readiness/models/facilitate	ua:Opera/9.84 (X11; Linux x86_64; en-US) Presto/2.10.211 Version/12.00
host:60.50.255.15	ident:-	user:-	time:[30/Dec/2019:17:13:22 +0900]	req:PATCH /end-to-end HTTP/2.0	status:405	size:3834	refer:http://www.district24/365.net/schemas	ua:Mozilla/5.0 (iPad; CPU OS 7_2_1 like Mac OS X; en-US) AppleWebKit/532.17.5 (KHTML, like Gecko) Version/5.0.5 Mobile/8B119 Safari/6532.17.5
host:56.46.161.47	ident:-	user:-	time:[30/Dec/2019:17:13:22 +0900]	req:PATCH /architect/turn-key/clicks-and-mortar/killer HTTP/1.1	status:304	size:63656	refer:http://www.principalbest-of-breed.com/morph/magnetic/turn-key/cross-media	ua:Mozilla/5.0 (Windows NT 5.2; en-US; rv:1.9.3.20) Gecko/1931-04-08 Firefox/36.0
host:48.195.162.51	ident:-	user:-	time:[30/Dec/2019:17:13:22 +0900]	req:PATCH /enhance/extend HTTP/2.0	status:405	size:13091	refer:http://www.districtmorph.name/customized/cutting-edge	ua:Opera/10.97 (X11; Linux i686; en-US) Presto/2.8.259 Version/10.00