Load Balancer access log の活用

VPC環境で利用できます。

Load Balancer access log の活用

データクエリを使用して、NCP Load Balancerで収集されたアクセスログを分析できます。
この例は、Application Load Balancer(ALB)のアクセスログをデータクエリサービスで精製し、分析テンプレートクエリで分析した後、Business Intelligence(BI)ツールで可視化するプロセスを説明します。

機能のサマリー

データ精製と活用のために、以下のサービス機能を使用します。

• スキャナーを通じたテーブル作成 - データに対するテーブル化 by Data Catalog
• Raw Dataテーブル精製 - 簡単な ETLタスク by Data Query (or Data Flow)
• BI連携による可視化 by Data Query
• 変更データの反映設定
  • Partition Valueアップデート、スキャナースケジューリング by Data Catalog
  • パラメータクエリ w/実行予約機能 by Data Query

1.LB AccessLog収集設定

ロードバランサのアクセスログ収集設定と CLAに保存されたログを保管するための自動エクスポート設定について確認できます。

Load Balancerアクセスログ収集設定

• ロードバランサのアクセスログ収集設定を有効にすることで、ログを CLAサービスに保存できます。
• 分析が必要なロードバランサのアクセスログ収集設定を有効にします。
• 詳しい設定方法は、Load Balancer ご利用ガイドをご参照ください。

CLA自動エクスポート設定

• CLAサービスに保存されたアクセスログを永続的に保管するため、Object Storageへの自動エクスポートを設定します。
• 自動エクスポート設定例

  項目	設定値
  エクスポート方式	タイプ基準保存方式
  バケットを選択	自動エクスポートデータが保存されるユーザーバケット
  ログ選択	application_loadbalancer_access

• 詳細は、CLA ご利用ガイドをご参照ください。

2.分析対象データテーブルの作成

ソースデータテーブルと精製データテーブルを作成する方法は、次の通りです。

ソースデータテーブルの作成(データに対するテーブル化)

• データカタログサービスのスキャナー機能を使用して、Object Storageに保存されたデータを参照するソースデータテーブルを作成します。
• 以下の設定例を参照して、スキャナーを作成して実行します。
• スキャナー設定例

  項目	設定値
  データタイプ	Object Storage
  パス	s3a://[CLA自動エクスポートを設定したユーザーバケット]/CLA/AutoExport/application_loadbalancer_access/
  スキャン範囲	一部、10個
  実行周期	オンデマンド
  パターン	使用しない
  パーティション設定	すべてのパーティションニング形式
  収集オプション	テーブル定義のアップデート
  テーブル結合	True
  テーブル数制限	1

• スキャナーに関する詳細は、Data Catalog ご利用ガイドをご参照ください。

精製データテーブルの作成(ETLタスク)

• データクエリサービスで、以下に提供されるサンプルクエリを参照して、精製データテーブルを作成します。
• データサイズが大きい場合は、データフローサービスを使用して ETLタスクを処理できます。

CREATE TABLE data_catalog.default.alb_access_log
WITH (
    format='parquet'
) AS
WITH parsed AS (
SELECT
    split(message, ' ') AS parts,
    regexp_extract_all(message, '\"([^\"]*)\"') AS quoted_fields
FROM data_catalog.default.application_loadbalancer_access
)
SELECT
    parts[1]  AS http_version,
    parts[2]  AS client_ip_port,
    CAST(parts[3] AS bigint)  AS request_processing_time_ms,
    CAST(parts[4] AS bigint)  AS target_connection_time_ms,
    CAST(parts[5] AS bigint)  AS target_response_time_ms,
    CAST(parts[6] AS bigint)  AS response_processing_time_ms,
    parts[7]                  AS target_status_code,
    CAST(parts[8] AS bigint)  AS received_bytes,
    CAST(parts[9] AS bigint)  AS sent_bytes,
    parts[10] AS http_method,
    quoted_fields[1] AS request,
    quoted_fields[2] AS user_agent,
    parts[ cardinality(parts) - 2 ] AS ssl_cipher,
    parts[ cardinality(parts) - 1 ] AS ssl_version,
    CAST(parse_datetime(parts[ cardinality(parts)], 'dd/MMM/yyyy:HH:mm:ss.SSS') AS timestamp) AS request_creation_time
FROM parsed 

3.テンプレートクエリを利用したトラフィック分析

提供されたテンプレートクエリを利用してトラフィック分析を実行する方法をご案内します。コンソールエディタ、BI、CLIなどでクエリしたり、JDBC、SDKを使用してユーザー Applicationビジネスロジックと統合できます。

• CLI ご利用ガイド: CLI接続と使用方法
• JDBC ご利用ガイド: DB Toolと BIの接続をサポート
• SDK ご利用ガイド: ユーザー Applicationを統合
• 外部 BI接続ガイド: Superset、Zeppelinなどの BIと統合

提供テンプレートクエリ

• トラフィック分析

-- 1-1.ユーザーエージェント(user_agent)別のリクエスト数
SELECT
  user_agent,
  count(*) AS count
FROM data_catalog.default.alb_access_log
GROUP BY 1
ORDER BY count DESC
LIMIT 5;

-- 1-2.クライアント IPアドレス別の総リクエスト数
SELECT
  split_part(client_ip_port, ':', 1) AS client_ip,
  count(*) AS request_count
FROM data_catalog.default.alb_access_log
GROUP BY 1
ORDER BY request_count DESC
LIMIT 10;

-- 1-3.最も多くリクエストされたパス(requestフィールド基準)
SELECT
  regexp_extract(request, '/[^ ]*') AS uri_path,
  count(*) AS count
FROM data_catalog.default.alb_access_log
GROUP BY 1
ORDER BY count DESC
LIMIT 10;

-- [バックアップ] 時間帯別リクエスト数分析(分/時間単位)
SELECT
  date_trunc('minute', request_creation_time) AS minute,
  count(*) AS request_count
FROM data_catalog.default.alb_access_log
GROUP BY 1
ORDER BY 1
LIMIT 50 ;

-- [バックアップ] レスポンスコード別分布
SELECT
  target_status_code,
  count(*) AS count
FROM data_catalog.default.alb_access_log
GROUP BY 1
ORDER BY count DESC;

• セキュリティ分析

-- 2-1.異常な HTTPステータスコードのリクエストを検知(例: 4xx、5xx)
SELECT
  split_part(client_ip_port, ':', 1) AS client_ip,
  target_status_code,
  count(*) AS error_count
FROM data_catalog.default.alb_access_log
WHERE target_status_code LIKE '4%' OR target_status_code LIKE '5%'
GROUP BY 1, 2
ORDER BY error_count DESC
LIMIT 50;

-- 2-2.短時間で多くのリクエスト(DoS検知の基礎)
SELECT
  split_part(client_ip_port, ':', 1) AS client_ip,
  date_trunc('minute', request_creation_time) AS minute,
  count(*) AS request_count
FROM data_catalog.default.alb_access_log
GROUP BY 1, 2
HAVING count(*) > 100 -- Modify Threshold Here
ORDER BY request_count DESC
LIMIT 10;

-- 2-3.異常に長いリクエスト URLのフィルタリング(例: 攻撃ベクトル)
SELECT
  split_part(client_ip_port, ':', 1) AS client_ip,
  regexp_extract(request, '/[^ ]*') AS uri_path,
  length(request) AS request_length
FROM data_catalog.default.alb_access_log
WHERE length(regexp_extract(request, '/[^ ]*')) > 500 -- Modify Threshold Here
ORDER BY request_length DESC
LIMIT 20;

• パフォーマンス分析

--- 3-1.クライアント IPアドレス別の平均レスポンス速度
SELECT
  split_part(client_ip_port, ':', 1) AS client_ip,
  round(avg(cast(request_processing_time_ms AS double)), 2) AS request_processing_time_ms,
  round(avg(cast(target_connection_time_ms AS double)), 2) AS target_connection_time_ms,
  round(avg(cast(target_response_time_ms AS double)), 2) AS target_response_time_ms,
  round(avg(cast(response_processing_time_ms AS double)), 2) AS response_processing_time_ms
FROM data_catalog.default.alb_access_log
GROUP BY 1
ORDER BY request_processing_time_ms DESC
LIMIT 10;

-- 3-2.リクエスト URI別の平均レスポンス速度(遅い URI Top 10)
SELECT
  regexp_extract(request, '/[^ ]*') AS uri_path,
  round(avg(cast(request_processing_time_ms AS double)), 2) AS request_processing_time_ms,
  round(avg(cast(target_connection_time_ms AS double)), 2) AS target_connection_time_ms,
  round(avg(cast(target_response_time_ms AS double)), 2) AS target_response_time_ms,
  round(avg(cast(response_processing_time_ms AS double)), 2) AS response_processing_time_ms
FROM data_catalog.default.alb_access_log
GROUP BY 1
ORDER BY request_processing_time_ms DESC
LIMIT 10;

-- 3-3.分単位の平均処理時間の推移
SELECT
  date_trunc('minute', request_creation_time) AS minute,
  round(avg(cast(request_processing_time_ms AS double)), 2) AS request_processing_time_ms,
  round(avg(cast(response_processing_time_ms AS double)), 2) AS response_processing_time_ms
FROM data_catalog.default.alb_access_log
GROUP BY 1
ORDER BY 1
LIMIT 20;

-- 3-4.ステータスコード別の平均レスポンス時間の分析
SELECT
  target_status_code,
  round(avg(cast(request_processing_time_ms AS double)), 2) AS request_processing_time_ms,
  count(*) AS request_count
FROM data_catalog.default.alb_access_log
GROUP BY 1
ORDER BY request_processing_time_ms DESC;

BI活用例

作成した精製データテーブルとテンプレートクエリを活用して、BIツールで可視化できます。外部 BI接続に関する詳細は、Data Query ご利用ガイドをご参照ください。

• 例: Superset BIを活用した可視化ダッシュボード

4.変更データの反映設定(自動更新)

LBから作成されるアクセスログデータは CLAに保存され、CLAのデータは自動エクスポート機能を通じて1日1回 Object Storageに保存されます。追加されるデータをソースデータテーブルと精製データテーブルにアップデートするには、データカタログスキャナーの周期実行設定を行います。

• 日別に追加されるデータに対してアップデートが必要ない場合、この設定は省略できます。

データカタログスキャナーの周期実行設定

データカタログスキャナーの周期実行設定を通じて、ソースデータテーブルのメタデータ(パーティション)が自動的に更新されるように設定できます。CLA自動エクスポート機能の動作時間を考慮して、スキャナーの周期実行設定を実行します。

• スキャナーの周期実行設定例

  項目	設定値
  データタイプ	Object Storage
  パス	s3a://[CLA自動エクスポートを設定したユーザーバケット]/CLA/AutoExport/application_loadbalancer_access/
  スキャン範囲	一部、10個
  実行周期	毎日03:30
  パターン	使用しない
  パーティション設定	すべてのパーティションニング形式
  収集オプション	テーブル定義のアップデート
  テーブル結合	True
  テーブル数制限	1

• データカタログスキャナーに関する詳細は、Data Catalog ご利用ガイドをご参照ください。

データクエリパラメータクエリ予約実行設定

データクエリパラメータクエリ機能と予約実行設定を通じて定期的にクエリを実行し、パラメータをバインドして動的にクエリを実行できます。以下に提供されたサンプルクエリをマイクエリとして保存した後、パラメータおよび予約実行設定を実行します。詳細は、Data Query ご利用ガイドをご参照ください。

• クエリ文保存ガイド
• パラメータ使用クエリ実行ガイド
• 保存されたクエリ予約実行ガイド

INSERT INTO data_catalog.default.alb_access_log
WITH parsed AS (
SELECT
    split(message, ' ') AS parts,
    regexp_extract_all(message, '\"([^\"]*)\"') AS quoted_fields
FROM data_catalog.default.application_loadbalancer_access
WHERE partition_0 = ?
  AND partition_1 = ?
  AND partition_2 = ?
)
SELECT
    parts[1]  AS http_version,
    parts[2]  AS client_ip_port,
    CAST(parts[3] AS bigint)  AS request_processing_time_ms,
    CAST(parts[4] AS bigint)  AS target_connection_time_ms,
    CAST(parts[5] AS bigint)  AS target_response_time_ms,
    CAST(parts[6] AS bigint)  AS response_processing_time_ms,
    parts[7]                  AS target_status_code,
    CAST(parts[8] AS bigint)  AS received_bytes,
    CAST(parts[9] AS bigint)  AS sent_bytes,
    parts[10] AS http_method,
    quoted_fields[1] AS request,
    quoted_fields[2] AS user_agent,
    parts[ cardinality(parts) - 2 ] AS ssl_cipher,
    parts[ cardinality(parts) - 1 ] AS ssl_version,
    CAST(parse_datetime(parts[ cardinality(parts)], 'dd/MMM/yyyy:HH:mm:ss.SSS') AS timestamp) AS request_creation_time
FROM parsed 

• パラメータ設定

  項目	設定値
  パラメータ1	date_format((current_timestamp AT TIME ZONE 'Asia/Seoul') - interval '1' day, '%Y')
  パラメータ2	date_format((current_timestamp AT TIME ZONE 'Asia/Seoul') - interval '1' day, '%m')
  パラメータ3	date_format((current_timestamp AT TIME ZONE 'Asia/Seoul') - interval '1' day, '%d')

• 予約実行設定例

  項目	設定値
  予約設定	ON
  実行タイプ	周期実行
  実行周期	毎日
  実行時間	03:45