BigQueryで認証エラーとなっているアクセスログを検索する

GAEのアクセスログがBigQueryに連携されたので、認証関連のエラー(http error:401,403)がないか確認してみます。

※GAEのアクセスログのBigQuery連携については下記リンクの記事を参考にして下さい。

GAEのログをBigQueryに連携する

BigQueryuについて簡単なご紹介を。言わずとしれたGCPの人気サービスです。AWSで言えばAthenaでしょうか？Azureはよく分かりません、、、最大のメリットは、安い(特にデータの保管料が) 処理が超高速(...

実行SQL
BigQueryで実行

実行SQL

認証エラーとなっているログから

ステータスコード
IPアドレス
リソース
リファラ
タイムスタンプ

を抽出する簡単なSQLです。

※項目を絞ると取得データ量が小さくなります。

タイムスタンプはデフォルトではUTCになるのでJSTに変換しました。

SELECT
  protoPayload.status,
  protoPayload.ip,
  protoPayload.resource,
  protoPayload.referrer,
  FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', timestamp, 'Asia/Tokyo') AS JST
FROM
  gae_blog.appengine_googleapis_com_request_log
WHERE
  protoPayload.status IN (401,403)
  AND FORMAT_TIMESTAMP('%Y-%m-%d', timestamp, 'Asia/Tokyo') >= '2020-05-01'
  AND FORMAT_TIMESTAMP('%Y-%m-%d', timestamp, 'Asia/Tokyo') <= '2020-05-31'
ORDER BY
  protoPayload.status,
  timestamp;