Load Balancer access log 활용

VPC 환경에서 이용 가능합니다.

Load Balancer access log 활용

데이터 쿼리를 사용하여 NCP Load Balancer에서 수집된 액세스 로그를 분석할 수 있습니다.
이 예시는 ALB(Application Load Balancer) 액세스 로그를 데이터 쿼리 서비스로 정제하고, 분석 템플릿 쿼리로 분석한 뒤 BI(Business Intelligence) 도구로 시각화하는 과정을 설명합니다.

기능 요약

데이터 정제 및 활용을 위해 아래 서비스 기능을 사용합니다.

스캐너 통한 테이블 생성 - 데이터에 대한 테이블화 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 사용 가이드를 참고해 주십시오.

항목	설정 값
내보내기 방식	타입 기준 저장 방식
버킷 선택	자동 내보내기 데이터가 저장될 사용자 버킷
로그 선택	application_loadbalancer_access

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 비즈니스 로직과 통합할 수 있습니다.

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별 평균 응답 속도 (Top 10 느린 URI)
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회 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

항목	설정 값
예약 설정	ON
실행 유형	주기 실행
실행 주기	매일
실행 시간	03:45