日付と時刻を使用してクエリする
このセクションの例には、日付と時刻の値を使用するクエリが含まれています。
例 – 人が読み込み可能な ISO 8601 形式のタイムスタンプフィールドを返す
以下のクエリは、from_unixtime
および to_iso8601
関数を使用して、timestamp
フィールドを人が読み込み可能な ISO 8601 形式 (例えば、1576280412771
ではなく 2019-12-13T23:40:12.000Z
) で返します。このクエリは、HTTP ソース名、ソース ID、およびリクエストも返します。
SELECT to_iso8601(from_unixtime(timestamp / 1000)) as time_ISO_8601, httpsourcename, httpsourceid, httprequest FROM waf_logs LIMIT 10;
例 – 過去 24 時間のレコードを返す
以下のクエリは、WHERE
句でフィルターを使用して、過去 24 時間のレコードに関する HTTP ソース名、HTTP ソース ID、および HTTP リクエストフィールドを返します。
SELECT to_iso8601(from_unixtime(timestamp/1000)) AS time_ISO_8601, httpsourcename, httpsourceid, httprequest FROM waf_logs WHERE from_unixtime(timestamp/1000) > now() - interval '1' day LIMIT 10;
例 – 指定された日付範囲と IP アドレスのレコードを返す
以下のクエリは、指定されたクライアント IP アドレスの指定された日付範囲内のレコードをリストします。
SELECT * FROM waf_logs WHERE httprequest.clientip='53.21.198.66' AND "date" >= '2021/03/01' AND "date" < '2021/03/31'
例 – 指定された日付範囲について、5 分間隔で IP アドレスの数を計上する
以下のクエリは、特定の日付範囲について、5 分間隔で IP アドレスの数を計上します。
WITH test_dataset AS (SELECT format_datetime(from_unixtime((timestamp/1000) - ((minute(from_unixtime(timestamp / 1000))%5) * 60)),'yyyy-MM-dd HH:mm') AS five_minutes_ts, "httprequest"."clientip" FROM waf_logs WHERE "date" >= '2021/03/01' AND "date" < '2021/03/31') SELECT five_minutes_ts,"clientip",count(*) ip_count FROM test_dataset GROUP BY five_minutes_ts,"clientip"
例 – 過去 10 日間の X-Forwarded-For IP の数をカウントする
次のクエリは、リクエストヘッダーをフィルタリングし、過去 10 日間の X-Forwarded-For IP の数をカウントします。
WITH test_dataset AS (SELECT header FROM waf_logs CROSS JOIN UNNEST (httprequest.headers) AS t(header) WHERE from_unixtime("timestamp"/1000) > now() - interval '10' DAY) SELECT header.value AS ip, count(*) AS COUNT FROM test_dataset WHERE header.name='X-Forwarded-For' GROUP BY header.value ORDER BY COUNT DESC
日付関数と時刻関数の詳細については、Trino ドキュメントの「Date and Time Functions and Operators