redash可視化！

前回は/var/log/secureを解析。

blog.vtryo.me

今回はh2oのaccess_logを解析する。

h2oのログフォーマットをLTSVにしておく

LTSVのよさみはこことか、ここで。

しかもtd-agent.confのformatにltsvってしとけばすごい楽にconfigが書けるので、これはやっておきたい。（td-agent.conf内でformatで正規表現書かなくてよくなる）

ということでさくっとconfigを変更する。（※request_timeの型はredshiftの規定に合わせること）

~省略~
access-log:
  path: /var/log/h2o/access.log
  format: "remote_address:%h\tremote_logname:%l\tremote_user:%u\trequest_time:%{%Y-%m-%d %H:%M:%S}t\trequest_line:\"%r\"\tstatus_code:%s\tsize_of_the_response_body:%b\tuser_agent:\"%{User-agent}i\"\treferer:\"%{Referer}i\""

redshiftのテーブル作成

加工したLTSVに合わせてテーブルを作成する。

以下のコマンドでredshiftにログイン（posgresqlインストール後）して、テーブルを作成する。

psql -U root -W -h endpoint -d dbname -p port

=# create table access_log (
(# remote_address varchar(255) not null default '-',
(# remote_logname varchar(255) not null default '-',
(# remote_user varchar(255) not null default '-',
(# request_time timestamp not null,
(# request_line varchar(255) not null default '-',
(# status_code integer,
(# size integer,
(# user_agent varchar(255) not null default '-',
(# referer varchar(65535) not null default '-'
(# );

テーブル確認

=# \d access_log;
                               Table "public.access_log"
     Column     |            Type             |                Modifiers
----------------+-----------------------------+-----------------------------------------
 remote_address | character varying(255)      | not null default '-'::character varying
 remote_logname | character varying(255)      | not null default '-'::character varying
 remote_user    | character varying(255)      | not null default '-'::character varying
 request_time   | timestamp without time zone | not null
 request_line   | character varying(255)      | not null default '-'::character varying
 status_code    | integer                     |
 size           | integer                     |
 user_agent     | character varying(255)      | not null default '-'::character varying
 referer        | character varying(65535)    | not null default '-'::character varying

td-agent.conf

access_log用のconfigを作成。

前回のsecure用のconfigも作ってあったので、「buffer_path /var/log/td-agent/buffer」に関しては適当に数字を付けておいた。（適当はよくないけど）

<source>
  type tail
  path /var/log/h2o/access.log
  pos_file /tmp/access.log.pos
  tag access.log
  format ltsv
  keep_time_key true

</source>

<match access.log>
  type jsonbucket
  output_tag redshift.access.log
  keep_time_key true
  format_json true
  json_key log
  file_type json
</match>

<match redshift.access.log>
  type redshift

  # s3
  aws_key_id xxx
  aws_sec_key xxx
  s3_bucket redash-log
  s3_endpoint s3-xxxx.amazonaws.com
  path h2o_access_log/
  keep_time_key true
  timestamp_key_format %Y%m%d/%Y%m%d-%H%M

  # redshift
  redshift_host xxxx
  redshift_port xxxx
  redshift_dbname dbname
  redshift_user username
  redshift_password xxxx
  redshift_schemaname public
  redshift_tablename access_log
  file_type json

  include_time_key true

  # buffer
  buffer_type file
  buffer_path /var/log/td-agent/buffer02
  flush_interval 1m
  buffer_chunk_limit 1g
</match>

サービス再起動

/etc/init.d/h2o restart

/etc/init.d/td-agent restart

redashクエリ

redash側でクエリを書く。クエリ得意じゃないのでもっといいクエリがあると思うので随時学びを増やす・・・。

SELECT remote_address,COUNT(*) AS COUNT FROM access_log group by remote_address limit 3;

↓redashクエリ結果。

f:id:vtryo:20180714213208p:plain

あとは例によってダッシュボードを作成して追加してあげる。

トラブルシューティング

カラムが違ったりするとredshiftがエラーを吐く。エラーの確認は/var/log/td-agent/td-agent.logで確認。

詳細の確認の仕方をメモ。

2017-08-09 13:09:42 +0900 [error]: failed to copy data into redshift due to load error. s3_uri=s3://xxx/h2o_access_log/20170809/20170809-1309_00.gz error="ERROR:  Load into table 'access_log' failed.  Check 'stl_load_errors' system table for details.\n"

「stl_load_errors」に書いてあるってよ。

redshiftにログインして、コマンドを打つ。

=# SELECT starttime, TRIM(colname), err_code, TRIM(err_reason)
=# FROM stl_load_errors
=# ORDER BY starttime DESC LIMIT 2;

すると詳細が見れる。

starttime          |    btrim     | err_code |                           btrim

----------------------------+--------------+----------+---------------------------------------------------------
--
2017-08-09 04:04:39.920398 | request_time |     1206 | Invalid timestamp format or value [YYYY-MM-DD HH24:MI:SS]
2017-08-09 04:03:39.459179 | request_time |     1206 | Invalid timestamp format or value [YYYY-MM-DD HH24:MI:SS]

1206のエラーコードで調べると、「タイムスタンプがおかしい」とわかる。（上記のh2oログフォーマットは正しく修正してある）