背景

特異なアクセス数があり、ALBのログを分析したくてやってみました。

やり方

ログをローカルにダウンロードするのもいいのですが、クラウドっぽくAthenaで実施しました。

ALBのログは圧縮された状態でS3に保管されるのですが、Athenaだとお構いなしに分析してくれます。

テーブル作成

まずはテーブルを作ります。

ネット上にある古い記事だと、最新のカラムに対応していませんでした。なので、ちゃんとAWSのドキュメントを参照します。

CREATE EXTERNAL TABLE IF NOT EXISTS alb_access_logs (
            type string,
            time string,
            elb string,
            client_ip string,
            client_port int,
            target_ip string,
            target_port int,
            request_processing_time double,
            target_processing_time double,
            response_processing_time double,
            elb_status_code int,
            target_status_code string,
            received_bytes bigint,
            sent_bytes bigint,
            request_verb string,
            request_url string,
            request_proto string,
            user_agent string,
            ssl_cipher string,
            ssl_protocol string,
            target_group_arn string,
            trace_id string,
            domain_name string,
            chosen_cert_arn string,
            matched_rule_priority string,
            request_creation_time string,
            actions_executed string,
            redirect_url string,
            lambda_error_reason string,
            target_port_list string,
            target_status_code_list string,
            classification string,
            classification_reason string,
            conn_trace_id string
            )
            ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
            WITH SERDEPROPERTIES (
            'serialization.format' = '1',
            'input.regex' = 
        '([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*):([0-9]*) ([^ ]*)[:-]([0-9]*) ([-.0-9]*) ([-.0-9]*) ([-.0-9]*) (|[-0-9]*) (-|[-0-9]*) ([-0-9]*) ([-0-9]*) \"([^ ]*) (.*) (- |[^ ]*)\" \"([^\"]*)\" ([A-Z0-9-_]+) ([A-Za-z0-9.-]*) ([^ ]*) \"([^\"]*)\" \"([^\"]*)\" \"([^\"]*)\" ([-.0-9]*) ([^ ]*) \"([^\"]*)\" \"([^\"]*)\" \"([^ ]*)\" \"([^\s]+?)\" \"([^\s]+)\" \"([^ ]*)\" \"([^ ]*)\" ?([^ ]*)?( .*)?')
            LOCATION 's3://alb-logs/AWSLogs/9999999999999/elasticloadbalancing/ap-northeast-1/'

なお、以下に書いているそのものを使用してます。

Application Load Balancer ログのクエリ - Amazon Athena

実際に動かしたのは以下のクエリです。

動作確認

ちゃんと動くかを確認します。

SELECT * FROM 
alb_access_logs
limit 10

アクセス数の多いIPアドレスを上位から表示する

上位から表示します。なお、タイムゾーンはUTCなので、差分の9時間は考慮が必要です。

SELECT distinct client_ip, count() as count 
FROM alb_access_logs 
WHERE parse_datetime(time,'yyyy-MM-dd''T''HH:mm:ss.SSSSSS''Z')
    BETWEEN parse_datetime('2024-07-13-00:00:00','yyyy-MM-dd-HH:mm:ss') 
    AND     parse_datetime('2024-07-14-00:00:00','yyyy-MM-dd-HH:mm:ss') 
    and request_url not like '%.css'
    and request_url not like '%.js'
    and request_url not like '%.png'
    and request_url not like '%.jpg'
GROUP BY client_ip 
ORDER BY count() DESC

CSSなどを除外して件数を表示

HTTPリクエストにはCSS、JS、画像ファイルがあるのでそれらは除外します。

ちなみにでいうと、IPアドレスでアクセスしてきたり、.envを探していたり、SSLしか対応していないのに80番でアクセスしてきたり。いろいろとやられているのがわかりました。

SELECT request_url,count(request_url) as url
FROM alb_access_logs
WHERE parse_datetime(time,'yyyy-MM-dd''T''HH:mm:ss.SSSSSS''Z')
    BETWEEN parse_datetime('2024-07-13-00:00:00','yyyy-MM-dd-HH:mm:ss') 
    AND     parse_datetime('2024-07-14-00:00:00','yyyy-MM-dd-HH:mm:ss') 
    and request_url not like '%.css'
    and request_url not like '%.js'
    and request_url not like '%.png'
    and request_url not like '%.jpg'
group by request_url
order by url desc

特定のIPドレスのログを出力

SELECT *
FROM alb_access_logs
WHERE parse_datetime(time,'yyyy-MM-dd''T''HH:mm:ss.SSSSSS''Z')
    BETWEEN parse_datetime('2024-07-13-00:00:00','yyyy-MM-dd-HH:mm:ss') 
    AND     parse_datetime('2024-07-14-00:00:00','yyyy-MM-dd-HH:mm:ss') 
    and request_url not like '%.css'
    and request_url not like '%.js'
    and request_url not like '%.png'
    and request_url not like '%.jpg'
    and client_ip = '216.144.248.27'

タイムゾーンをJSTにする

SELECTのwhere句でJST指定しているけど、表示はUTCなのでややこしい。

何か他にうまい方法があるのかも。

SELECT time
FROM alb_access_logs
WHERE 
 parse_datetime(time,'yyyy-MM-dd''T''HH:mm:ss.SSSSSS''Z')  AT TIME ZONE 'Asia/Tokyo'
between
TIMESTAMP '2024-07-13 19:00:00 Asia/Tokyo'
and
TIMESTAMP '2024-07-13 22:00:00 Asia/Tokyo'
    and request_url not like '%.css'
    and request_url not like '%.js'
    and request_url not like '%.png'
    and request_url not like '%.jpg'
order by time