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

2020.05.11
この記事は約2分で読めます。

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

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

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

実行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;
スポンサーリンク

BigQueryで実行

今回はコンソールから実行してみました。

GoogleCloudコンソールよりBigQueryを開きクエリエディタに上記SQLを入力します。

入力するとエディタ右下に処理されるデータサイズを事前計算し表示してくれます。

この機能をドライランといいます。

ドライランの自動計算結果からSQLの実行料金が計算出来ますので、SQL実行前には必ずドライランを実施しましょう。

※Cloud SDKで実行する場合は実行コマンドで明示的に指定する必要があります。詳しくは公式Docを参照して下さい。

あとは実行ボタンをクリックすると該当するレコードが表示されます。

今回はお試し的な軽い処理の記事となりましたが、普段使いでこういう用途でご利用されるのもありだと思います。

いろんなデータをBigQueryに放り込みご活用頂けたらなと思います。

ただし、ドライランはお忘れなく・・・