Documentation Index

Fetch the complete documentation index at: https://guide.ncloud-docs.com/llms.txt

Use this file to discover all available pages before exploring further.

Load Balancer access log の活用

Prev Next

VPC環境で利用できます。

Load Balancer access log の活用

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

機能のサマリー

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

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
参考
  • テーブルは、データのスキーマや保存場所(Location)などのデータを読み取って管理するために必要なメタデータを定義する単位です。
  • 作成されたテーブルリソースを通じて、ストレージに保存されたファイルにアクセスします。

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

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

提供テンプレートクエリ

  • トラフィック分析
-- 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を活用した可視化ダッシュボード

dataquery-example-lb-accesslog-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
事業者登録番号: 129-86-31394 通信販売業申告番号:第2009-京畿城南-0510号
代表取締役: キム・ユウォン アドレス: 13561 京畿道 城南市 盆唐区 仏亭路6 グリーンファクトリー, 17~26F お客様センターの代表電話: 1544-5876
© NAVER Cloud Corp. All Rights Reserved.